Now that System Center 2012 is one “product” we’re seeing a heavier focus towards using a common back-end for the products. Microsoft has done a good (not great) job on providing a consistent set of back-end database requirements so that a single version of SQL 2008 R2 SP1 CU6 should be sufficient for all of the System Center components.
<Start disclaimer here>
Before really getting into this let me state up front – this is not the approach that every environment will want to take. Some environments will require more dedicated resources including breaking down separate System Center components onto different database servers. Other environment (such as a lab) may be small enough that all of the databases could be configured to run in a single instance (if reporting services conflicts between the components was not a constraint).
<End disclaimer here>
In terms of database sizing, the largest databases we are seeing in our labs (and those requiring at least the most disk resources) are Service Manager, Operations Manager and Configuration Manager. Based upon lab testing up to this point, the following is the breakdown of database size for a lab environment which includes 25 servers, no workstations and a small number of users.
- System Center Service Manager (ServiceMgr): 25 GB
- System Center Operations Manager (OpsMgr): 12 GB
- System Center Configuration Manager (ConfigMgr): 6 GB
- System Center Data Protection Manager (DPM): 1 GB
- System Center App Controller (App Controller): < .1 GB
- System Center Virtual Machine Manager (VMM): .2 GB
- System Center Orchestrator (Orchestrator): .1 GB
What about IOPS? (Input/output Operations Per Second)There is a lot of discussion on the number of IOPS required for various System Center components, do they really require the number of IOPS specified in the sizer? Sometimes a picture is worth a thousand words. The next picture shows the IOPS for a database server with about 2000 agents that has LUNS shared for the OperationsManager database and the OperationsManagerDW.
So… That 1000 IOPS estimation (mentioned later in this article) on the data warehouse for OpsMgr doesn’t seem so far fetched now! (total hovering around 1000, primarily write but with a significant amount of read)
The following is IOPS for the same environment (approximately 2000 agents) just for the OperationsManager database: (total hovering around 400, primarily write)
And the IOPS for the same environment for just the OperationsManagerDW: (total hovering around 200, primarily write)
What about SQL Collation? That’s a big question that’s been going around recently – I’ve heard rumor that an official writeup will be available on this soon and when that occurs I’ll update this blog entry to point to that discussion.
Can System Center components share an instance? There is not documented requirements to have a separate instance for each component of System Center. As long as the requirements for each database are met they can share but you need to take scale and performance into consideration. SRS is the one exception to this rule that I am aware of, requiring separate instances of reporting services for each component of System Center that uses SRS.
Case Studies:
For this blog article we are showing two similar case studies.
Case Study #1:
As a first case study example we installed a single two-node cluster to provide System Center back-end database functionality for ConfigMgr, OpsMgr, ServiceMgr and DPM. The client has approximately 200 servers that they are monitoring, and they have approximately 3000 workstations that they are managing and there are 5000 users in the environment. They are interested in potentially deploying all of the System Center components off of this back-end database. The following are the key pieces of the proposed back-end database:
Installing two physical servers on Windows 2008 R2 which are running a quad core with 32 gb of memory.
The two servers are connected to a SAN infrastructure to store all of the database and log files.
Instances are created on a per-component basis:
- ConfigMgr
- OpsMgr
- ServiceMgr (2x)
- DPM
Each instance is configured with separate spindles on the SAN for that instance’s database and log files.
Creating a separate reporting instance for each component that requires it (OpsMgr, ConfigMgr, Service Manager)
This cluster will only be for System Center back-end functionality.
Disk Sizing for Case Study #1: (based upon the Microsoft sizers)
- ServiceMgr: 225 GB
- OpsMgr: 160 GB
- ConfigMgr: 100 GB
- Total: 341 GB
Case Study #2:
As a second case study example, speaking with a client recently, we came to the consensus of creating a single two-node cluster to provide all System Center back-end database functionality. The client has approximately 2500 servers that they are monitoring, and they have approximately 6500 workstations that they are managing and there are 3500 users in the environment. They are interested in potentially deploying all of the System Center components off of this back-end database. The following are the key pieces of the proposed back-end database:
- Installing two physical servers on Windows 2008 R2 which are running two socket/six core with 96 gb of memory.
- The two servers are connected to a SAN infrastructure to store all of the database and log files.
Instances are created on a per-component basis: (gathering smaller sized databases into a single instance)
- ConfigMgr
- OpsMgr
- ServiceMgr (2x)
- DPM, App Controller, VMM, Orchestrator
Each instance is configured with separate spindles on the SAN for that instance’s database and log files.
Creating a separate reporting instance for each component that requires it (OpsMgr, ConfigMgr, Service Manager)
This cluster will only be for System Center back-end functionality.
Disk Sizing for Case Study #2: (based upon the Microsoft sizers)
- ServiceMgr: 225 GB
- OpsMgr: 1900 GB [Based on lab tests performed up to this point in time, it is reasonable to believe that the estimate from the sizer may be over-estimating for OpsMgr. We have one client with 2000 servers and a fully populated data warehouse in OpsMgr 2007 R2 has not crossed 500 GB yet, and another with about 1100 servers that has not crossed 1000 GB yet]
- ConfigMgr: 100 GB
- Total: 2225 GB
Sizing per Microsoft estimation methods for case study #2: (details on ServiceMgr, OpsMgr and ConfigMgr are contained in the appendix of this blog article)
ServiceMgr: 225 GB (http://www.microsoft.com/en-us/download/details.aspx?id=27850 orhttp://t.co/8ht9rAOQ)
OpsMgr: 1900 GB (http://www.microsoft.com/en-us/download/details.aspx?id=29270)
ConfigMgr: 100 GB (http://technet.microsoft.com/en-us/library/hh846235.aspx#BKMK_ReqDiskSpace)
DPM: .9 GB (http://technet.microsoft.com/en-us/library/hh757757.aspx)
App Controller: 1 GB (http://technet.microsoft.com/en-us/library/gg696060.aspx)
VMM: 150 GB (http://technet.microsoft.com/en-us/library/gg610574.aspx)
Orchestrator: 1 GB (http://blogs.technet.com/b/meamcs/archive/2011/11/02/orchestrator-2012-deployment-amp-configuration-step-by-step-part-2-system-requirements.aspx)
In terms of database sizing from the Microsoft sizing estimates, the largest databases (and those requiring the most resources) are Operations Manager, Service Manager, Virtual Machine Manager and Configuration Manager which represent the statistically relevant database sizes.
Common System Center 2012 Database Recommendations:
- Limit each SQL Instance memory where the SQL total memory setting allows a minimum of 1 GB and preferably 2 GB of free memory after all services are running.
- SQL Server will need to be Standard or Enterprise edition (Datacenter in some situations)
- Install dual gigabit network adapters teamed with full duplex
- Disks should be formatted to a 64K allocation unit size
- Disks should be configured to allow 20% free space after all calculations for monitoring and set growth upper limits to not exceed this space limit.
- The SQL Cluster should be running Active-Passive unless resources are proved to be insufficient
- Common objections to sharing databases is contention for resources resulting in bottlenecks – processors, memory, and especially disk spindles. Use Operations Manager to identify these potential bottlenecks.
- OpsMgr will not support SQL dynamic ports which is the default SQL setting, change each SQL instance dynamic port to a TCP port.
- If OpsMgr will be providing monitoring for network devices, increase the size of the SQL temp in that instance.
- Set the SQL Database Engine Domain Account(s) to auto register the SQL SPN values which occurs every time the service starts up
- If you are using heart beat and iSCSI connections – make sure to remove all connection options except IPv4 and remove any DNS and Gateway entries as well.
Summary: Do you want to use a common SQL server for multiple components of System Center 2012? You can, but be sure to consider the requirements of each component and see the guidance above for sizing, instance details and general recommendations.
Special thanks on this one to: Travis Wright from the Microsoft product team for his second perspective, Louis Oliver for his help with the IOPS statistics, Chris Ross for his assistance with this from a Service Manager perspective!
Appendix: Sizing for OpsMgr, Service Manager and ConfigMgr from the Microsoft sizing estimators.
OpsMgr 2012 sizing:
In terms of currently available tools, there is a sizer for OpsMgr available at:http://www.microsoft.com/en-us/download/details.aspx?id=29270.
For the OpsMgr database in an organization this size the results are:
(Bolded Red items below are showing the Case Study #2 example with approximately 2000 OpsMgr agents)
| DB Estimated Random IO Per Second for Maximum Load Configuration [80% Write, 20% Read] | |
| # of Agents | Estimated IOPS |
| 1-500 | 250 |
| 501-1000 | 500 |
| 1001-3000 | 750 |
| 3001-6000 | 1125 |
| 6001-10000 | 1250 |
| 10001-15000 | 1500 |
| OpsMgr DB: Number of Days for Data Retention | 7 |
| Number of Server Computers | 2500 |
| Number of Network Devices | 50 |
| Number of APM-enabled Computers | 0 |
| Total Size (MB) | 42276.18 |
| Total Size (GB) | 41.29 |
| Total Size (GB) with 50% Buffer | 61.93 |
For the OpsMgr DW in an organization this size the results are:
| DW Estimated Random IO Per Second for Maximum Load Configuration [80% Write, 20% Read] | |
| # of Agents | Estimated IOPS |
| 1-500 | 500 |
| 501-1000 | 875 |
| 1001-3000 | 1000 |
| 3001-6000 | 1500 |
| 6001-10000 | 2000 |
| 10001-15000 | 2500 |
| OpsMgr DW: Number of Days for Data Retention | 365 |
| Number of Server Computers | 2500 |
| Number of Network Devices | 50 |
| Number of APM-enabled Computers | 0 |
| Total Size (MB) | 1707732.56 |
| Total Size (GB) | 1667.71 |
| Total Size (GB) with 10% Buffer | 1834.48 |
ServiceMgr 2012 sizing:
http://www.microsoft.com/en-us/download/details.aspx?id=27850
| Service Manager database Size | |||||
| Retention for Work Items (days) | 90 | ||||
| Number of work items for computers/month | 6150 | ||||
| Total Size (GB) | 6.84 | ||||
| Suggested Space Allocation | |||||
| with 50% buffer (GB) | 10.26 | ||||
| Service Manager data warehouse Size Note: The details below for each database include the recommended 50% buffer. | |||||
| We also recommend placing each database on a separate physical drive if possible to optimize performance. | |||||
| Number of Days | 1095 | ||||
| DWStagingandConfig | 20.53 | ||||
| Repository | 61.58 | ||||
| DWDataMart | 61.58 | ||||
| TempDB | 51.32 | ||||
| DWASDataBase | 18.47 | ||||
| Note: The DWASDataBase size approximation is based on the default aggregation level set by Service Manager. | |||||
| Total Size (GB) | 213.49 | ||||
| Suggested Space Allocation | 213.49 | ||||
| with 50% buffer (GB) |
From download.microsoft.com/…/SC2012_ServiceMgr_Planning.docx
| Service Manager database | Dual Quad-Core 2.66 gigahertz (GHz) CPU 8 gigabytes (GB) of RAM for 20,000 users, 32 GB of RAM for 50,000 users (See the Hardware Performance section in this guide.) 80 GB of available disk space RAID Level 1 or Level 10 drive* |
| Data warehouse databases | Dual Quad-core 2.66 GHz CPU 8 GB of RAM for 20,000 users, 32 GB of RAM for 50,000 users (See theHardware Performance section in this guide.) 400 GB of available disk space |
ConfigMgr 2012 sizing:
from http://technet.microsoft.com/en-us/library/hh846235.aspx#BKMK_ReqDiskSpace
| Data usage | Minimum disk space1 | 25,000 clients | 50,000 clients | 100,000 clients |
| Operating system | See guidance for the operating system. | See guidance for the operating system. | See guidance for the operating system. | See guidance for the operating system. |
| Configuration Manager Application and Log Files | 10 GB | 10 GB | 10 GB | 10 GB |
| Site database .mdf file | 75 GB for every 25,000 clients | 75 GB | 150 GB | 300 GB |
| Site database .ldf file | 25 GB for every 25,000 clients | 25 GB | 50 GB | 100 GB |
| Temp database files (.mdf and .ldf) | As needed | As needed | As needed | As needed |
| Content (distribution point shares) | As needed | As needed | As needed | As needed |