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

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

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

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

David Baur – Catapult consultant and Configuration Manager SME.

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

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.

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.

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.

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

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

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.

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.

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.

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

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

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.

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

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

What Jason said… J

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

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.

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 https://www.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.

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.

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

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

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.

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.

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.