System Center 2012 R2 and SQL Pit Falls



I have been deploying a large number of Configuration Manager DPs and clients as well as 1 console connection per location. We have also been deploying Operations Manager agents to all of our servers at each location. We have done about 40 locations so far. We have know we have had SQL cluster shortfalls but some times the powers that be don't always understand the severity of your warnings. That being said 2 weeks ago I strated to see signs of issues starting. We had to reboot our Primary site servers a few times the during the first of the 2 weeks. last week, we had to reboot 4 of 7 days. Over the weekend we had updates going as well as the deployment of Windows 7 to 30 XP workstations. Needless to say SQL broke. 

System Center Configuration Manager 2012 R2​

Adding 150-200 clients and 5-7 DPs per week has proved taxing for our site.Minimizing complexity, we have only 1 Primary site and no secondary site servers. With the number of clients we have this is well with in the sizing specs for SCCM. What is not up to snuff is our SQL instance for SCCM, namely Disk latency. Disk Latency is very important to pay attention to. Latency for the DB disks was running between 80- 502 ms depending on what was going on. TempDB was seeing 600-800 ms. Latency should be no more than 15-20 ms for SCCM to work properly. You also want your TempDB on the fastest possible disks. 

Now our immediate issue is getting fast enough disks, unfortunately expanding our NAS is an expansive and drawn out process so there is no immediate fix. To get by we have limited the amount that is happening. Collection queries which were automatically refreshed after a given period are being increased or made manual. we are increasing the length of time any machine waits to check in. For our high speed domestic locations it has been set to 15 minutes. we are changing this to 30 minutes, and slower, international location are being changed to 90 minutes.

System Center Operations Manager 2012 R2

Our Ops instance which had the DB on a 100GB LUN from our NAS filled up. We have about 300 Servers on it bringing and it brought Operations Manager to a stand still. There were several issues contributing to this, 

  1. DB growth was not taken into consideration when the DB storage was created and the plans to deploy to field offices.
  2. Free space monitoring was being ignored for the DB. 
  3. While minor, "Disable Operations Manager Alerts While while software updates run" was not checked in our Update deployment adding alerts from every server which was adding to the number of alerts being added to the DB. This has been made part of the Update SOP:


SQL Recommendations

My recommendations for SQL which is not always easy to get the powers that be to sign off on is:

  • 8-16GB ram per instance
  • Each System Center 2012 component on its own instance
  • Separate (RAID 10 and fast such as 15k SAS or Fibre Channel) disk spindles for each of the following:
    • TempDB
    • DB
    • Logs
  • 2.6GHz Cpu

​DON'T let the powers that be let you skimp on SQL especially in medium to large deployments (1000+ clients in SCCM and 300 Clients in SCOM)

This website and its content is copyright of ITHierarchy Inc - © ITHierarchy Inc 2013-2015. All rights reserved.

Any redistribution or reproduction of part or all of the contents in any form is prohibited other than the following:

  • you may print or download to a local hard disk extracts for your personal and non-commercial use only
  • you may copy the content to individual third parties for their personal use, but only if you acknowledge the website as the source of the material

You may not, except with our express written permission, distribute or commercially exploit the content. Nor may you transmit it or store it in any other website or other form of electronic retrieval system.