System Center 2012–using a common SQL backend database (#SYSCTR, #SCOM, #SCSM)

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

image

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.

image

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)

image

And the IOPS for the same environment for just the OperationsManagerDW: (total hovering around 200, primarily write)

image

What about SQL Collation?  A blog post was recently added on this topic which is available at:

 

 

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.

UPDATE 6/4/2014: Per David Baur:

The official Configuration Manager 2012 Support states:
 
At the above link, Go to and Expand-> Configurations for the SQL Server Site Database and then expand SQL Server Requirements and you will see the following in the tables:
 
SQL Server instance
You must use a dedicated instance of SQL Server for each site.
 

 

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 or http://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.

image

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 the Hardware 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

 

3 Comments

  1. James August 30, 2012
  2. Cameron Fuller September 7, 2012
  3. David Baur June 4, 2014

Leave a Reply

x

We use cookies to ensure the best possible experience on our website. Detailed information on the use of cookies on this site is provided in our Privacy and Cookie Policy. Further instruction on how to disable our cookies can be found there.