How to Create an SCCM Collection for SQL Servers

If you’re managing a Microsoft Configuration Manager (SCCM) environment, organizing your infrastructure efficiently is key. One essential task is building a dynamic device collection that targets all your SQL Servers. In this quick tutorial, I’ll walk you through how to set up that collection step-by-step.

Step 1: Open SCCM and Navigate to Device Collections

Launch your SCCM Configuration Manager console (also known as Microsoft Endpoint Configuration Manager). Once it’s open, look to the left-hand pane and locate Device Collections. Right-click it and select Create Device Collection.

From here, we’ll name our new collection. In this example, I’m calling it All Microsoft SQL Servers — clear and self-explanatory. Next, we’ll choose a limiting collection. Ideally, you’d pick All Servers, since SQL Server typically runs on server machines. Nevertheless, in some environments, SQL Server is installed on desktop OS machines like Windows 10 or 11. This situation, while not ideal, does happen. For this demo, I’ll choose All Desktop and Server Clients.

Step 2: Add a Query Rule

Once the basics are set, click Next and then Add Rule. Choose Query Rule and give it a relevant name. After that, click Edit Query Statement.

Navigate to the Criteria tab and enable Show Query Language. This allows us to input a custom WQL query. Paste in a SQL query that filters devices based on SQL Server presence. This query targets any machine with SQL Server installed. It could be version 2016, 2019, or 2022. It may even be running on non-standard machines.

You can even run a test query to make sure it’s picking up valid SQL servers within your environment. For example, in my case, it correctly identified BJ-SQL, my dedicated SQL Server — success!

---

At this point, you’re just a few clicks away from finishing the collection setup. But if you want to learn how to fine-tune the collection for specific SQL Server versions, watch the video. This applies to versions like 2019 or 2022. I show you how in the video below.