Create SCCM Collection for SQL Server

In this post, I will show you how to create SCCM collection for SQL Server. You can group all the Windows servers that are running SQL Server into a device collection in Configuration Manager.

Each Configuration Manager site requires a supported SQL Server version and configuration to host the site database. A dedicated SQL server device collection can help you understand what versions of SQL are currently installed in your environment and help you manage them using SCCM. You can deploy SQL updates to this collection, perform maintenance tasks and much more.

SCCM includes built-in collections, but you may need to create additional collections based on your needs. A WQL query can help you determine the SQL server versions that are installed in your setup. You can modify this query by specifying the SQL version that you’re looking for and creating a device collection.

Also Read: Add Multiple Devices to SCCM collection using PowerShell

Prerequisites

• The Windows server that hosts the SQL Server should be installed with a client agent.
• The client agent should be updated to the latest version. You can use the automatic client upgrade method to update all clients to the latest version.

WQL Query for Microsoft SQL Server

Use this WQL query to find all the Microsoft SQL Server versions running in your environment.

select * from  SMS_R_System inner join SMS_G_System_COMPUTER_SYSTEM on SMS_G_System_COMPUTER_SYSTEM.ResourceID = SMS_R_System.ResourceId where SMS_G_System_COMPUTER_SYSTEM.Roles like "%SQLServer%"

WQL Query for Microsoft SQL Server 2022

Use this WQL query to find all the SQL Server 2022 in your environment.

select SMS_R_SYSTEM.ResourceID, SMS_R_SYSTEM.ResourceType, SMS_R_SYSTEM.Name, SMS_R_SYSTEM.SMSUniqueIdentifier, SMS_R_SYSTEM.ResourceDomainORWorkgroup, SMS_R_SYSTEM.Client from SMS_R_System where SMS_R_System.ResourceId in (select distinct SMS_G_System_ADD_REMOVE_PROGRAMS.ResourceID from SMS_G_System_ADD_REMOVE_PROGRAMS where SMS_G_System_ADD_REMOVE_PROGRAMS.DisplayName = 'Microsoft SQL Server 2022')

WQL Query for Microsoft SQL Server 2019

Use this WQL query to find all the SQL Server 2019 in your environment.

select SMS_R_SYSTEM.ResourceID, SMS_R_SYSTEM.ResourceType, SMS_R_SYSTEM.Name, SMS_R_SYSTEM.SMSUniqueIdentifier, SMS_R_SYSTEM.ResourceDomainORWorkgroup, SMS_R_SYSTEM.Client from SMS_R_System where SMS_R_System.ResourceId in (select distinct SMS_G_System_ADD_REMOVE_PROGRAMS.ResourceID from SMS_G_System_ADD_REMOVE_PROGRAMS where SMS_G_System_ADD_REMOVE_PROGRAMS.DisplayName = 'Microsoft SQL Server 2019')

WQL Query for Microsoft SQL Server 2017

Use this WQL query to find all the SQL Server 2017 in your environment.

select SMS_R_SYSTEM.ResourceID, SMS_R_SYSTEM.ResourceType, SMS_R_SYSTEM.Name, SMS_R_SYSTEM.SMSUniqueIdentifier, SMS_R_SYSTEM.ResourceDomainORWorkgroup, SMS_R_SYSTEM.Client from SMS_R_System where SMS_R_System.ResourceId in (select distinct SMS_G_System_ADD_REMOVE_PROGRAMS.ResourceID from SMS_G_System_ADD_REMOVE_PROGRAMS where SMS_G_System_ADD_REMOVE_PROGRAMS.DisplayName = 'Microsoft SQL Server 2017')

Create SCCM Collection for SQL Server

Let’s create a device collection in SCCM that groups all your SQL servers present in the setup. Open the Configuration Manager console and click Assets and Compliance > Device Collections. On the top ribbon, select Create Device Collection.

Enter the device collection name, choose the limiting collection and click Next.

On the Membership Rules window, click Add Rule and from the drop-down, select Query Rule.

In the Query Rule Properties box, enter the query name and click Edit Query Statement. In the Query statement properties window, select the Criteria tab. Click Show query language and enter the below WQL query to create a Microsoft SQL Server device collection.

select SMS_R_SYSTEM.ResourceID, SMS_R_SYSTEM.ResourceType, SMS_R_SYSTEM.Name, SMS_R_SYSTEM.SMSUniqueIdentifier, SMS_R_SYSTEM.ResourceDomainORWorkgroup, SMS_R_SYSTEM.Client from SMS_R_System where SMS_R_System.ResourceId in (select distinct SMS_G_System_ADD_REMOVE_PROGRAMS.ResourceID from SMS_G_System_ADD_REMOVE_PROGRAMS where SMS_G_System_ADD_REMOVE_PROGRAMS.DisplayName = '%Microsoft SQL Server%')

Click OK three times and continue with the Create Device Collection Wizard. The Membership Rules page now displays the rule name and the query information that you just specified. Click Next.

Review the changes on the Summary page. Complete the remaining steps and close the wizard.

After a few minutes, in the SCCM console, check the member count of the SQL Server device collection. The collection should group all the SQL servers in your hierarchy. If the device collection is not updating, review the colleval.log which records details about when collections are created, changed, and deleted by the collection evaluator.

Collection Creation Guides

• Create Device Collection for Windows Server 2025 in SCCM
• Create SCCM Collection for Co-Managed Devices
• Setup Windows 11 SCCM Device Collection 23H2 | 22H2 | 21H2
• Create SCCM Device Collection for DHCP Servers
• Create SCCM Collections based on Active Directory OU