SQL Server Collation for System Center Products

A customer asks me, what is the collation for System Center products?
Do we have a general collation for all products?
And what is the BP for Tempdb?
And how work the licencing for SQL Server in System Center (is SQL Server included)???

I answer just “OK, OK, I understand, I wil check those points…”
I  search those information on Microsoft TechNet website, blogs, presentation, etc.. and I  think that it is interesting to share this/those info with all of you 😉

Why Collation is so important?

Collation defines how SQL Server will work on data level with indexing, sorting,…
If a bad collation is chosen, it could cause various problems around the manipulation and  research of data.
There are two types of collation:

• SQL Server collations (SQL_*)
• Windows collations

If you want more info about collation, I suggest to read my article in SQL Magazine: “A Seven-Step Process for Changing a Database’s Collation”

A good practice is to keep the same collation between the instance and these databases.
At the beginning, System Center products have been only supported with the SQL_Latin1_General_CP1_CI_AS collation.
It is the default collation when you install SQL Server but the Best Practice is to use Windows Collation(SQL_* collations will be deprecated).
System Center will change it but for the moment you need to follow the recommendation for each product.

Collation recommendation per System Center Product

System Center has many products and each product has his self-databases.

System Center App Controller Server

App Controller helps to configure, deploy, and manage virtual machines and services across private and public clouds.
Supported Collations: Must be set to one of the _100_ collations like Latin1_General_100_CI_AS
Link: https://technet.microsoft.com/en-us/library/gg696046.aspx

System Center Configuration Manager (SCCM)

Configuration Manager is used  to change and configure management like Deploy operating systems, software applications, and software updates.
Supported Collations: only SQL_Latin1_General_CP1_CI_AS
Link: https://technet.microsoft.com/en-us/library/gg682077.aspx#BKMK_SupConfigSQLSrvReq

System Center Service Manager (SCSM)

Service Manager provides built-in processes for incident and problem resolution, change control, and asset lifecycle management.
Supported Collations:

• Latin1_General_100_CI_AS
• SQL_Latin1_General_CP1_CI_AS
• Latin1_General_CI_AS
• Chinese_Simplified_Pinyin_100_CI_AS
• Czech_100_CI_AS
• Danish_Norwegian_CI_AS
• Finnish_Swedish_100_CI_AS
• Greek_100_CI_AS
• Hungarian_100_CI_AS
• Japanese_XJIS_100_CI_AS
• Korean_100_CI_AS
• Norwegian_100_CI_AS
• Polish_100_CI_AS
• Cyrillic_General_100_CI_AS
• Modern_Spanish_100_CI_AS
• Finnish_Swedish_100_CI_AS
• Turkish_100_CI_AS

Link: https://technet.microsoft.com/en-us/library/hh495583.aspx
Note: You must use the same supported language collations if you intend to import data from Operations Manager into Service Manager

System Center Operation Manager (SCOM)

Operations Manager helps for the availability of applications and monitors private and public clouds.
Supported Collations:

• SQL_Latin1_General_CP1_CI_AS
• French_CI_AS
• Cyrillic_General_CI_AS
• Chinese_PRC_CI_AS
• Japanese_CI_AS
• Traditional_Spanish_CI_AS
• Latin1_General_CI_AS

link: http://technet.microsoft.com/en-us/library/jj656649.aspx

System Center Orchestrator

Orchestrator is a workflow management solution to automate the creation, monitoring, and deployment of resources.
Supported Collations: only SQL_Latin1_General_CP1_CI_AS
link: https://technet.microsoft.com/en-us/library/hh420361.aspx

System Center Data Protection Manager (DPM)

DPM is used to back up servers, computers, Microsoft workloads, system state, and bare metal recovery (BMR).
Supported Collations:only SQL_Latin1_General_CP1_CI_AS
link:https://technet.microsoft.com/en-us/library/hh758176.aspx

System Center Virtual Machine Manager (VMM)

VMM is a management solution for virtualized datacenter.
Instance collations: Any case insensitive (CI) collation can be used.
link:https://technet.microsoft.com/library/dn771747.aspx
Database collations: only Latin1_General_100_CI_AS

Collation table summary

72%(5/7) of System Center products use the collation SQL_Latin1_General_CP1_CI_AS but few products (3/7) can be set with the Windows collation Latin1_General_100_CI_AS.
I recommend to have at least 2 instances, one with SQL_Latin1_General_CP1_CI_AS and one with like Latin1_General_100_CI_AS.
If you have money and will build a good architecture for System Center, I recommend to have dedicated instances for each product but be careful with the interaction between them.

TempDB configuration recommendation

Tempdb can be the cause of performance problem for System Center products if it is not well configured.
Then, I give you some generic rules to configure it correctly.
Collation: Same Collation as databases from System Center Products
Files: multiple files – one data file per core with the same size (max to 8)
Size: 20-30% of the DB file size and if you don’t know the size 8GB is a good starting point and minimum 2GB
Auto-Growth: 512MB for data files and 128 for the 128MB

SQL Server License

System Center includes a SQL Server Standard Edition License. You don’t need to pay an extra for the Database
The global limitation is that instances must be dedicated to System Center Products and you cannot installed other databases on it.
The second limitation is for Configuration Manager: must be under 50000 clients.
These 2 limitations are the only one on a Licence Level.
The thing is that you are limited by the Standard Edition like no AlwaysOn, no multi-node clustering, no online or parallel commands, memory, cores, etc.

My Advice:
If System Center becomes the heart of you IT infrastructure, don’t think a lot and go directly to SQL Server Enterprise Editions with System Center Products and have a dedicated instance for each product.

links

SQL Server version support for all System Center Products:
https://technet.microsoft.com/en-us/library/dn281933.aspx
Another source of information for System Center Services Manager is the blog from Travis Wright:
http://blogs.technet.com/b/servicemanager/archive/2012/05/24/clarification-on-sql-server-collation-requirements-for-system-center-2012.aspx?pi168154=2