Your browser (Internet Explorer 6) is out of date. It has known security flaws and may not display all features of this and other websites. Learn how to update your browser.

ConfigMgr 2012: Quick Model Count in SQL

Here is just a quick SQL query you can use to show how many of each model you have in your environment.  I’ve got a Manufacturer filter on it, but you could remove that or modify as necessary.


   1: SELECT

   2:   C.Model0,

   3:   COUNT(*) AS 'Count'

   4: FROM

   5:     dbo.v_gs_computer_system C

   6: WHERE

   7:     C.Manufacturer0 like '%lenovo%'

   8: GROUP BY

   9:   C.Model0

  10: ORDER by Count


System Center 2012 Configuration Manager: New site install with SQL 2014 = Not Supported

Recently was standing up a new site and decided to use SQL 2014. According to the support page, SQL 2014 is supported. It is supported to upgrade from SQL 2012 to SQL 2014, but not for new site installs.

However, if you use SQL 2014, you will encounter an error during the install process.


Additionally attempting to restore a site against SQL 2014 will cause errors as well, so another thing to take into consideration.

Also, WSUS isn’t fully supported on SQL 2014 with Server 2008 R2. So there’s another gotcha you’ll run into. 

Here is a support statement from the Configuration Manager Team Blog:

Couple key notes from that blog posting:

It is not supported to install a new Configuration Manager site with SQL Server 2014 installed.

The following software updates are required to use SQL Server 2014 to host the site database:

Cumulative Update 1 for System Center 2012 R2 Configuration Manager -

System Center 2012 Configuration Manager SP1 – To apply this hotfix, you must have Cumulative Update 4 (  for System Center 2012 Configuration Manager SP1 installed.

Known limitations:

Windows Server Update Services (WSUS) 3.2 Server cannot be installed on Windows Server 2008 R2 SP1 and Windows Server 2008 SP2 with SQL Server 2014 installed. With this configuration, the Configuration Manager Software updates feature will not work correctly.


ConfigMgr 2012: SQL Maintenance Plans and Index Optimization Recommendations

Recently caught some emails going around about recommended backup and maintenance plans as it relates to ConfigMgr. Also plenty of discussions around whether or not addition index optimization is required beyond the built-in Rebuild Indexes task that can be configured as part of the built-in Site Maintenance task. 

As far as what I recommend and configure for the clients I work with. I use the recommendations from Steve Thompson a fellow MVP and former SQL MVP.

SQL Server Backup Recommendations

How to determine if indexing task is running

Optimizing the ConfigMgr databases

I would highly recommend you read through the above linked posts. The maintenance plan is what I use for backing up the CM database and supporting databases. I do not use the built-in backup task with ConfigMgr 2012.  

If you are having issues with indexing or slow performance, then it’s a good idea to look into the index optimization recommendations as well. 


SQL Server: Cannot Connect to WMI Provider


Recently encountered a SQL issue when trying to open the SQL Server Configuration Manager on my ConfigMgr Database server. This KB was the resolution for me:

I received the following error when attempting to open the tool.

Cannot connect to WMI provider. You do not have permission or the server is unreachable. Note that you can only manage SQL Server 2005 and later servers with SQL Server Configuration Manager.
Invalid namespace [0x8004100e]

My SQL Server install was SQL Server 2012 SP1.

Running the mofcomp of sqlmgmproviderxpsp2up.mof resolved the issue for me after a restart of the WMI service.



ConfigMgr 2012 : Unable to Import Drivers After Moving SQL Files

Recently had an issue at a client site where we suddenly couldn’t import drivers, manually or via a PowerShell script. 


When importing manually, we were seeing the following error:

"An error occurred while importing the selected driver"

When importing via the script, we were seeing:

"Unexpected error, skipping INF : CSspConfigurationItem: SQL_ERROR 3242722562"

After some creative searching, I can across this KB article:

After moving the System Center 2012 Configuration Manager SQL Site Database to another drive, creating a new Software Update package or a new application fails.

Well as it turns out, I had just moved around the DB and Log files for this client to new drives. 


I used the following commands to alter the DB, verify the Isolation level, and then finally change the DB owner back to SA. 

CM_XXX = ConfigMgr DB Name



Step 2:


Verify that Isolation level is "READ COMMITTED SNAPSHOT"

Step 3:

Use [CM_XXX]


Exec sp_changedbowner ‘SA’


After completing those steps, I was able to import drivers again.


SQL Server Backup Recommendations for Configuration Manager

Great post by fellow MVP Steve Thompson.

Read his full post here.

In Configuration Manager 2012 SP1 and later, note that you have the ability to recover a site (secondary sites don’t count) using a standard, native SQL Server database backup.

From the following link:

“Configuration Manager can recover the site database from the Configuration Manager backup maintenance task or from a site database backup that you perform by using another process. For example, you can restore the site database from a backup that is performed as part of a Microsoft SQL Server maintenance plan.”

Why is this important?

The standard ConfigMgr backup task will simply copy all of the database data and log files to the backup folder location. During the SQL Backup task, you have the ability to specify compression for a database backup.


ConfigMgr 2012 Database Requirements

Posting this for all my RSS subscribers that might not have picked up the change.

I updated my previous post to reflect to the new DB requirements for ConfigMgr 2012 final release.

Read that post here.


List of Computers from an Active Directory User Security Group

Garth Jones (fellow ConfigMgr MVP) blogged a really great query for get a list o PC’s by using a user security group.

Read his original blog post here.

   1: select 


   3:   CS.Name0,


   5:   CS.UserName0


   7: from 


   9:   dbo.v_R_User U


  11:   join dbo.v_RA_User_UserGroupName UGN on U.ResourceID = UGN.ResourceID


  13:   join dbo.v_GS_COMPUTER_SYSTEM CS on CS.UserName0 = U.Unique_User_Name0


  15: Where


  17:   UGN.User_Group_Name0 = 'GARTEK\Domain Users'


System Center 2012 Configuration Manager Database Requirements – 4/26/2012

The below requirements are the listed requirements as of 4/26/2012 for ConfigMgr 2012.

Make sure to review the supported configurations on the TechNet site.

SQL Server Versions

  • SQL Server 2008 SP2 with CU9 (Standard or Enterprise)
  • SQL Server 2008 SP3 with CU4 (Standard or Enterprise)
  • SQL Server 2008 R2 with SP1 and CU6 (Standard or Enterprise)
  • SQL Server Express 2008 R2 with SP1 and CU4 (Secondary Sites)

NOTE: If you use SQL Server Standard, your CAS will only support 50k clients

Database Collation

ConfigMgr 2012 requires SQL_Latin1_General_CP1_CI_AS collation.


SQL Server Features

  • Only Database Engine Services is required. SQL Server replication is not needed.
  • Windows Authentication is required.
  • You must use a dedicated instance of SQL server.
  • It is HIGHLY recommended to limit the amount of memory assigned to SQL.

Editing CM12 reports in the ConfigMgr console with SQL Server 2008 R2

Fellow MVP Steve Thompson has created a nice post for reporting in ConfigMgr 2012.

Read the full post here.

Presently, if you have installed ConfigMgr 2012 with SQL Server 2008 R2, and attempt to edit a report from the console, you will get an error message that states “Report Builder not available”. The problem here, ConfigMgr 2012 is designed to work out of the box with Report Builder 2.0 as explained here:

Since you have installed SQL Server 2008 R2, which ships with Report Builder 3.0, you can indeed use Report Builder 3.0, just open it directly from SSRS Report Manager.

While not supported or endorsed by Microsoft, a workaround is available to integrate RB 3.0 from the console, as follows:

You’ll need to set a registry key to name the RB 3.0 application manifest.  The key in question is “HKLM\Software\Wow6432Node\Microsoft\ConfigMgr10\AdminUI\Reporting\ReportBuilderApplicationManifestName”.  Change its value from “ReportBuilder_2_0_0_0.application” to “ReportBuilder_3_0_0_0.application” on any machine from which you want to launch RB 3.0.