SQL Instances and System Center 2012 (#SYSCTR #SQL #SCOM #SCCM #SCSM)

I started this blog post from a System Center MVP perspective, but with internal conversations with our SQL MVP I realized that this would be far more interesting as a point/counter-point discussion between several of our Catapult MVP’s based upon our different SME experiences. This isn’t a cut and dry – this is the answer type situation. This is more of a debate on the different approaches available for these designs.

First, let me introduce everyone:

clip_image002

Wes Brown – Catapult consultant and SQL MVP who blogs at http://www.sqlserverio.com.

clip_image004

Chris Ross – Catapult consultant and CDM MVP who blogs at http://scsm.us/.

clip_image006

Jason Sandys – Catapult consultant and Configuration Manager MVP who blogs at http://blog.configmgrftw.com/.

clip_image008

David Baur – Catapult consultant and Configuration Manager SME.

clip_image010

Cameron Fuller – Catapult consultant and CDM MVP who blogs at http://www.systemcentercentral.com/blogs/cameronfuller.

[In the discussion below, the specific discussion topics being discussed are in red]

So with that said, here we go!

Question: What are the benefits and negatives of splitting SQL server into separate instances when designing a System Center 2012 database back-end?

Topic: Splitting to multiple instances creates a different Tempdb for each instance

clip_image002[1]

This isn’t a plus. If you need more performance out of tempdb you add more files. Having multiple instances on the same disks is a net zero gain.

clip_image006[1]

Totally agree; however, different instances for System Center definitely means different disks as this also enables manual load-balancing when the SQL system is clustered. I guess that was an implication (for me at least) when using different instances.

clip_image011

I have seen multiple situations where multiple application databases were put into the same instance, one of the applications has an issue which maxes out the tempdb and brings down all databases in the instance.

Topic: Splitting to multiple instances allows you to configure memory for each instance

clip_image002[2]

You can also have some wasted memory as each instance requires some memory held over.

Topic: By splitting to multiple instances you can manage CPU resources separately using WSRM

clip_image002[3]

You can use SQL Server’s Resource Governor for everything but SSRS and I would in most cases http://technet.microsoft.com/en-us/library/bb895232(v=sql.105).aspx

Topic: Splitting to multiple instances provides the ability to have different SA accounts which can be configured for improved security

clip_image002[4]

You shouldn’t be using SA accounts period. If someone gets on the server as an admin with privileges he will own the instances anyway.

clip_image006[2]

This one’s not just about the SA account, it’s about all accounts and their possible access to the various dbs. ConfigMgr specifically requires sysadm permissions on the entire SQL instance.

clip_image004[1]

Remember, since Service Manager has connectors you will need to plan for this as well. Specifically, the account you use for the Configuration Manager connector will need to have smsdbrole_extract and db_datareader permissions on the System Center Configuration Manager database. SA accounts don’t get you anything in this respect.

Topic: Splitting to multiple instances provides different Server and Agent Services (databases on each instance can be managed independently)

clip_image002[5]

Again, you are adding support overhead without any real gain. HA is the answer for any service failure.

Topic: Splitting to multiple instances may be required to support different collations (reflected in Appendix A)

clip_image002[6]

If you don’t have to support different collations in tempdb this is also a moot point. I’m digging into this.

clip_image004[2]

Collations can be a tricky topic with Service Manager, characteristically because of the reach that the Service Desk has in global companies. Support for multiple languages in Service Manager is not possible when you are using the default collation (SQL_Latin1_General_CP1_CI_AS). The recommended collation is Latin1_General_100_CI_AS and therefore typically requires a separate instance from the norm because most DBA’s use the default collation in general.

Topic: Splitting to multiple instances may be required to support different SQL version, SP, and maybe even CU support levels (reflected in Appendix A)

clip_image002[7]

Again, a support nightmare but if you have to support different versions this is the only way to do it.

clip_image006[3]

Hands are tied here. 8 different products, 8 different product groups, 8 different timelines and support statements.

clip_image004[3]

What Jason said… J

Topic: Splitting to multiple instances provides a division/separation for maintenance. IE: Taking down one instance does not impact multiple system center components.

clip_image002[8]

Again, more complexity = danger. The SQL Agent is pretty scalable on its own’

clip_image006[4]

This isn’t only referring to SQL maintenance tasks. Referring to applying updates (CUs, SPs) for similar reason to the last one. One other task I can remember specifically that required taking an entire instance offline is enabling Kerberos on a cluster resource in Win 2003. This is no longer an issue with Win 2008, but limiting the effected scope for any change is a good thing.

Topic: What other relevant points to splitting the instances from a System Center 2012 perspective?

clip_image012

The SQL version requirements vary on different components of System Center 2012 which makes shared instances in System Center 2012 more complicated to implement (see Appendix A).

Can System Center 2012 components share an instance? As long as the requirements for each database are met they can share but you need to take scale and performance into consideration. [Subset from http://blogs.catapultsystems.com/cfuller/archive/2012/05/23/system-center-2012–using-a-common-sql-backend-database-sysctr-scom-scsm.aspx)

In any configuration, the drive requirements for IOPS needs to be sufficient to provide the required level on a per-component basis.

Contrary to a commonly held belief, it is not financially beneficial for Microsoft to recommend additional instances for SQL server as SQL server is licensed by the processor not the instance.

Topic: Are there any required System Center instance splits:

clip_image013

Each components of System Center 2012 which uses SRS cannot share the reporting services instance. Components with SRS based reporting include: Operations Manager, Service Manager, Configuration Manager, Data Protection Manager.

clip_image008[1]

Configuration Manager sites cannot share an instance. “SQL Server instance: You must use a dedicated instance of SQL Server for each site.” subset from: http://technet.microsoft.com/en-us/library/gg682077.aspx

clip_image004[4]

Service Manager does not support case-sensitive instance names, and therefore will require a new instance if the database is not compatible.

Topic: What are any recommendations in terms of System Center best practice instance splits:

clip_image004[5]

For Service Manager splitting the CMDB from the data warehouse is a recommended best practice. Based on first-hand experience I believe this should be considered a requirement – but that’s just my opinion. FYI: Service Manager is extremely SQL intensive.

clip_image010[1]

For Operations Manager splitting the OperationsManager database from the data warehouse is a recommended best practice.

Sharing an instance between multiple components of System Center 2012 can occur but will need to take into consideration the server default collation, SQL version requirements, and security requirements.

Topic:  What additional negatives are there to using multiple instances?

clip_image010[2]

As Wes mentioned, the big one is overhead: Each additional SQL instance requires it’s own footprint.

It is also less intuitive to have multiple instances to manage patches, memory, space and such.

Finally any tools which are licensed by instance (such as SQLSafe and Diagnostic Manager) will increase with the additional instances which are added.

Appendix A:

From: http://nocentdocent.wordpress.com/articles-and-series/planning-sql-server-for-system-center-2012/

clip_image015

System Center 2012–using a common SQL backend database: http://www.systemcentercentral.com/tabid/143/indexid/93572/default.aspx

Appendix B:

The following is a case study example of instance split options for a company deploying OpsMgr, ServiceMgr, ConfigMgr, Orchestrator and VMM.

Case study example: Unsupported minimal instance approach

INST01 – SCCM (CAS) [with SCCM reporting], SCOR, VMM

INST02 SCCM (Primary) , SCOM [with SCOM reporting]

INST03 – SCSM (Primary) & (Data Warehouse)  [with SCSM reporting]

Case study example: Compromise instance approach

INST01 – SCCM (CAS) [with SCCM reporting]

INST02 SCCM (Primary)

INST03 – SCSM (Primary) & (Data Warehouse)  [with SCSM reporting]

INST04 – SCOR, VMM

INST05 – SCOM [with SCOM reporting]

Case study example: Separation of instance approach

INST01 – SCCM (CAS)

INST02 SCCM (Primary)

INST03 – SCSM (Primary)

INST04 – SCSM (Data Warehouse)

INST05 – SCOR & VMM

INST06 – SCOM (Primary)

INST07 – SCOM (Data Warehouse)

One thought on “SQL Instances and System Center 2012 (#SYSCTR #SQL #SCOM #SCCM #SCSM)

  1. Pingback: DevAdmin » Blog Archive » System Center 2012 R2 e SQL Server

Leave a Reply