Planning SQL Server for System Center 2012




Updates

26-02-2013 Added support for SQL Server 2012 SP1 for Config Mgr

11-01-2013 Added references for System Center 2012 Service Pack 1

19-07-2012 Added support for SQL Server 2008 R2 SP1 for DPM

25-05-2012 Added reference to Travis’ post on SQL Collation

27-04-2012 Added support for SQL 2008 R2 CU6 for ConfigMgr



One of the most common questions I get when planning for the deployment of System Center 2012 (SC from now on) is: can we have one SQL Server box for all the System Center databases? Where with “one SQL box” we consider a single SQL Server system that can be structured in multiple nodes in case of failover clustering.

This article has the goal to answer this question putting order in all the requirements for the SC components. I will try to keep it up to date as soon as support statements for the single components change.

Planning for SQL Server is one of the most critical steps in a SC deployment since SQL is typically the most limiting factor for performance and scalability.

All in all proper planning needs to answer 4 questions:

·       How big needs to be my SQL Server in terms of CPU, RAM and disks. For disks you need to take into account not only space, but also IOps.

·       What kind of availability does SQL Server need? (This is a strategic decision based on the overall SC expected availability)

·       Which is the supported SQL Server configuration? Version, modules needed, collation, clustering, mirroring, log shipping and so on

·       Given all these factors which edition of SQL Server do I need? (more info here http://technet.microsoft.com/en-us/library/cc645993.aspx)

I don’t consider Disaster Recovery because in my opinion it’s a different matter and a topic on its own that don’t have to limit my choices for the main production environment. I’ve seen too many DR technologies that limit what you can implement for the live system, this is not the way to go, you cannot castrate your architecture to implement something that it’s highly improbable. DR options should be limited by production decisions and not vice versa (immo).

So, to answer our initial question (can we have a single SQL Server box?) we need to be sure to conciliate:

·       Scalability

·       Availability

·       Compatibility

Scalability

Almost all the SC components have their scalability figures:

·       Operations Manager (OM) Download

·       Configuration Manager (CM) http://technet.microsoft.com/en-us/library/gg682077.aspx#BKMK_SiteAndRoleScale

·       Orchestrator (Orc) http://technet.microsoft.com/en-us/library/hh420359.aspx

·       Virtual Machine Manager (VMM) http://technet.microsoft.com/en-us/library/gg610562.aspx

·       App Controller (AC) http://technet.microsoft.com/en-us/library/gg696060.aspx

·       Data Protection Manager (DPM) http://technet.microsoft.com/en-us/library/hh757988.aspx

·       Service Manager (SM) http://technet.microsoft.com/en-us/library/hh519636.aspx

When planning for a single SQL Server box you must consider this requirements as additive (with a grain of salt obviously), for medium and large environments when there’s a plan to implement all the SC components this could lead to huge SQL Server boxes. Two considerations here:

·       There’s a cost break even between scaling up and having just one box to manage and scaling out with multiple boxes to manage

·       The resulting configuration could demand for SQL Server Enterprise edition, since in the SC licensing is included SQL Standard edition, you would need, in this case, to buy the SQL Enterprise licenses. Again, there’s a cost/benefit consideration to be done: will it cost less to manage one SQL Enterprise solution or multiple SQL Standard systems?

·       In general it’s better to have multiple SQL Server boxes rather than multiple SQL instances on the same server

Availability

The SQL Server availability should reflect the SC availability model, so if you go for an highly available SC architecture you should implement a SQL Server failover cluster solution. There are a couple of caveats here:

1.    If the results from the capacity planning requires more than 2 nodes, then you need SQL Server Enterprise edition. Is this the cheaper solution or a scale out model is better?

2.    DPM 2012 doesn’t support clustered SQL instances (http://technet.microsoft.com/en-us/library/hh758153.aspx)

3.    DPM and CM don’t support SQL Server 2012 Always On as well, while other SC components do (in this case SC SP1 is a requirement). Remember that SQL 2012 Always On requires a SQL Server 2012 Enterprise license.

Compatibility

This is the most complex topic, even if today SC is a single product, we all know its components used to be separate products with their own requirements. In this version this has not been completely normalized yet.

This is a collection of requirements for the each single component:

·       Operations Manager (OM) http://technet.microsoft.com/en-us/library/hh205990#BKMK_RBF_OperationsDatabase

·       Configuration Manager (CM) http://technet.microsoft.com/en-us/library/gg682077.aspx#BKMK_SupConfigSQLSrvReq

·       Orchestrator (Orc) http://technet.microsoft.com/en-us/library/hh420342.aspx

·       Virtual Machine Manager (VMM) http://technet.microsoft.com/en-us/library/gg610574.aspx

·       App Controller (AC) http://technet.microsoft.com/en-us/library/gg696060.aspx

·       Data Protection Manager (DPM) http://technet.microsoft.com/en-us/library/hh757876.aspx

·       Service Manager (SM) http://technet.microsoft.com/en-us/library/hh495585.aspx

·       With Service Pack 1 things, at least for SQL versions, are converging see http://technet.microsoft.com/en-us/library/jj628198.aspx 

I try to summarize these requirements in the following table for RTM products:

Component

SQL Versions

Collation required

HA

Needs dedicated instance?

Modules

Other

OM

SQL Server 2008 SP1
SQL Server 2008 R2
SQL Server 2008 R2 SP1

SQL_Latin1_General_CP1_CI_AS
Latin1_General_100_CI_AS for many western Europe locales

Y

SSRS only

Full text search, SSRS, SSAS (for VMM)

 

CM

SQL Server 2008 SP2 CU9
SQL Server 2008 SP3 CU4
SQL Server 2008 R2 SP1 CU6

SQL_Latin1_General_CP1_CI_AS

limited

support shared instance with other SC components, not between CM sites

SSRS

No support for SQL dynamic ports

SM

SQL Server 2008 SP1
SQL Server 2008 SP2
SQL Server 2008 R2
SQL Server 2008 R2 SP1

SQL_Latin1_General_CP1_CI_AS  Latin1_General_100_CI_AS for many Western Europe locales

Y

SSRS Only

Full text, search, SSRS, SSAS

Special config for remote SSRS

ORC

SQL Server 2008 R2

SQL_Latin1_General_CP1_CI_AS

Y

     

VMM

SQL Server 2008 SP2
SQL Server 2008 R2
SQL Server 2008 R2 SP1

any CI collation

Y

     

DPM

SQL Server 2008 R2
SQL Server 2008 R2 SP1

not specified

N

 

SSRS

 

AC

SQL Server 2008 SP2
SQL Server 2008 R2

not specified

Y

     

For Service Pack 1 release this is the state of the art:

Component

SQL Versions

Collation required

HA

Always On

Needs dedicated instance?

Modules

Other

OM

SQL Server 2008 R2 SP1
SQL Server 2008 R2 SP2

SQL Server 2012

SQL Server 2012 SP1

SQL_Latin1_General_CP1_CI_AS
Latin1_General_100_CI_AS for many western Europe locales

Y

Y

SSRS only

Full text search, SSRS, SSAS (for VMM)

 

CM

SQL Server 2008 SP CU 9

SQL Server 2008 SP3 CU 4

SQL Server 2008 R2 SP1 CU 6
SQL Server 2008 R2 SP2

SQL Server 2012 CU 2

SQL Server 2012 SP 1

SQL_Latin1_General_CP1_CI_AS

Y

N

support shared instance with other SC components, not between CM sites

SSRS

No support for SQL dynamic ports

SM

SQL Server 2008 R2 SP1
SQL Server 2008 R2 SP2

SQL Server 2012

SQL Server 2012 SP1

SQL_Latin1_General_CP1_CI_AS  Latin1_General_100_CI_AS for many Western Europe locales

Y

Y

SSRS Only

Full text, search, SSRS, SSAS

Special config for remote SSRS

ORC

SQL Server 2008 R2 SP1
SQL Server 2008 R2 SP2

SQL Server 2012

SQL Server 2012 SP1

SQL_Latin1_General_CP1_CI_AS

Y

Y

     

VMM

SQL Server 2008 R2 SP1
SQL Server 2008 R2 SP2

SQL Server 2012

SQL Server 2012 SP1

any CI collation

Y

Y

     

DPM

SQL Server 2008 R2 SP1
SQL Server 2008 R2 SP2

SQL Server 2012

SQL Server 2012 SP1

not specified

N

N

 

SSRS

 

AC

SQL Server 2008 R2 SP1
SQL Server 2008 R2 SP2

SQL Server 2012

SQL Server 2012 SP1

not specified

Y

Y

     

To get more information about SQL support across all SC components read here

To get more info on SQL collations support please refer to this post from Travis’ http://blogs.technet.com/b/servicemanager/archive/2012/05/24/clarification-on-sql-server-collation-requirements-for-system-center-2012.aspx

Common denominators for SC RTM

·         SQL Server 2008 R2 SP1 is the platform of choice for OM, SM and VMM

·         CM requires SQL Server 2008 R2 SP1 CU6 that’s not the same of plain SP1

·         SQL Server 2008 R2 RTM can be used for Orc, DPM and AC

·         DPM databases cannot be clustered, that’s silly in my opinion since afaik there’s nothing different on a functional perspective in a cluster SQL instance, but that’s it

·         OM and SM need dedicated SSRS instances

·         For English only SC deployments we can standardize on SQL Collation SQL_Latin1_General_CP1_CI_AS, but if we need to support full text for different languages then SM requires specific collation, hence dedicated SQL server instances or you must live with a partial full text match for your searches

It is clear to me that a great leap forward has been taken to improve SQL compatibility with Service Pack 1.

Returning to our original question, the answer is: even if on a performance and scalability point of view we have a configuration that can be supported by single SQL system we cannot have a single SQL Server instance, but now we can have a limited number of instances:

·         1 SQL Server 2012 CU 2 Engine for the whole of SC

·         1 SQL Server 2012 CU 2 SSRS for OM

·         1 SQL Server 2012 CU 2 SSRS for SM

·         1 SQL Server 2012 CU 2 SSRS for DPM

·         1 SQL Server 2012 CU 2 SSRS for CM

Common denominators for SC Service Pack 1

·         SQL Server 2012 with Service Pack 1 is the common denominator for all the System Center 2012 components

·         DPM databases cannot be clustered, that’s silly in my opinion since afaik there’s nothing different on a functional perspective in a cluster SQL instance, but that’s it

·         OM and SM need dedicated SSRS instances

·         SQL Server 2012 Always On cannot be used with CM and DPM

·         For English only SC deployments we can standardize on SQL Collation SQL_Latin1_General_CP1_CI_AS, but if we need to support full text for different languages then SM requires specific collation, hence dedicated SQL server instances or you must live with a partial full text match for your searches

Returning to our original question, the answer is: even if on a performance and scalability point of view we have a configuration that can be supported by single SQL system we cannot have a single SQL Server instance, at a bare minimum we need:

· 1 SQL Server 2012 SP1 Engine for OM, SM, CM, Orc, AC and VMM

· 1 SQL Server 2012 SP1 SSRS for OM

· 1 SQL Server 2012 SP1 SSRS for SM

· 1 SQL Server 2012 SP1 SSRS for DPM

· 1 SQL Server 2012 SP1 SSRS for CM

Conclusions

First of all we must remember that “supported” and “works” are different. Supported means the product has been tested in that configuration and in case of issues we have access to the whole support chain up to the product team. This implies we can have working configurations that are not supported. In this case we can still ask Microsoft for help but we have access to a “best effort” support, we may be asked to revert to a supported configuration and probably our incident will never be escalated up to the product team (if needed).

Things are very different between RTM and Service Pack 1 and the way to go, to consolidate the SQL server workload in a supported scenario, is definitely SC 2012 Service Pack 1. In this case SQL Server 2012 SP1  is the obvious choice. Obviously the collation issue for full text search can be a problem, honestly I don’t think there’s too much to do here and I prefer anyway to manage Knowledge Base articles outside Service Manager. The multiple SSRS instances are not an issue (immo) since they can be standardized on one SQL Server version.

If you are stuck with RTM things are a little complicated. But if you turn to real word scenarios things appear a little different. Obviously what follows are my own opinions so use them at your own risk.

·         The worst issue is the need to support different SQL Server versions, standardizing on one SQL version is the most important thing in terms of support. We can live with multiple instances, if performance are ok we can even have multiple instances on the same SQL system, it can even help in prioritizing SQL server resources between components.

·         In a lab/poc environment we can think to standardize to SQL 2008 R2 SP1 CU4 – in my experience everything works properly. You will still need separate SSRS instances.

·         In small deployments we can still remain on SQL 2008 R2 SP1 CU4. CUs can introduce changes in behavior and disrupt specific functionalities but CU4 *seems* to work with OM and SM. Orchestrator doesn’t do so much with SQL so you can bet it works, same consideration for AC. DPM can be trickier and anyway in small deployment where we need just one DPM server the best choice is to have an “all in one” server with a local SQL Server installation. In any case even DPM, in my experience, works fine with SQL server 2008 R2 SP1 CU4.

For medium and large deployments many factors step in discouraging a single SQL Server system

·         We probably need HA, in this case the DPM SQL Instance needs to be local to the DPM servers or on a separate not clustered SQL system.

·         We probably need to support multiple locales for SM, in this case we better isolate SM database and data warehouse. They are anyway labor intensive and planning for scale up can be expensive.

·         We still need to have multiple SSRS instances

·         We will probably end up with a dedicated SQL system for OM as well for performance reasons and for scale up costs

·         We could probably consolidate CM, VMM, Orc and AC on a single SQL system

- Daniele

This posting is provided “AS IS” with no warranties, and confers no rights.

 

  1. #1 by freemanru on April 17, 2012 - 1:57 pm

    SQL_Latin1_General… isn’t supported for SCSM 2012. http://technet.microsoft.com/en-us/library/hh519608.aspx and http://technet.microsoft.com/en-us/library/hh495583.aspx. So you can’t share it with over instances.

    • #2 by Daniele Grandini on April 17, 2012 - 1:59 pm

      Sorry but you’re wrong, this collation is supported for “English only” deployments as I stated in my post.

      – Daniele

      • #3 by freemanru on April 17, 2012 - 2:56 pm

        Links? From my above: “There are no issues with using the default collation with the English-only installations of Service Manager.” But that not mean “supported”.

      • #4 by Daniele Grandini on April 17, 2012 - 4:52 pm

        actually that’s it :-), that statement make it supported accordingly to the product group, then we can speculate if this is the right way to communicate and if saying in the official documentation “there are no issues…” makes it supported or not. Confusing, isn’t it? From the talking I had with the product group this makes it a supported collation. Obviously it’s up to you to follow this indication or keep SM database instances separated (btw this is higly probable in enterprise deployments). Then as Aengus pointed out we need to take into account the compatibility between OM and SM on the DW side of the fence. The good news is it’s just a matter of testing and it will probably be addressed with SP1, the bad news is today we need to take decisions and sometimes they could collide with official statement supports.

        Thanks for reading this blog and keep posting your comments.
        Daniele

      • #5 by freemanru on April 18, 2012 - 1:03 am

        Unfortunately PSS, DevTeam and “guys who write docs” is a three different departments inside MSFT. And sometimes they all have different answer on same question. And almost always PSS’s answer is not as you waiting )))
        BTW1. Collations “confusing” is under investigation now. So lets wait some times.
        BTW2. About DW. http://social.technet.microsoft.com/Forums/en-US/setup/thread/1c5ed23d-c663-4ded-86cd-f04af2090442/#c307ec9f-1821-4588-b2f2-0017ba210d06. Because if it’s not then ALL languages except US will be unsupported. And again – that’s just my opinion not yet approved or rejected by anyone from MSFT.

  2. #6 by Aengus on April 16, 2012 - 12:27 am

    and lets not forget that if you want your OM to talk to your SM, the two products SQL need the same collation, i.e. SQL_Latin1_General_CP1_CI_AS.

  1. Siti Internet Aziendali » In pratica: un Private Cloud con System Center 2012 R2
  2. System Center 2012 R2 e SQL Server | DevAdmin Blog
  3. DevAdmin » Blog Archive » System Center 2012 R2 e SQL Server
  4. Cameron Fuller - SQL Instances and System Center 2012 (#SYSCTR #SQL #SCOM #SCCM #SCSM)
  5. Planning SQL Server for System Center 2012 « Quae Nocent Docent

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Follow

Get every new post delivered to your Inbox.

Join 348 other followers

%d bloggers like this: