How to configure SCCM SQL Database Collation Setting Options correctly SQL_Latin1_General_CP1_CI_AS. This should be done during the installation of SQL DB for SCCM Primary server installation.
Let’s see what is required for SQL Server DB collation configuration for ConfigMgr. This post mainly talks about SQL Database collation settings and options to change the collation settings. You need to make sure that the SCCM SQL Database Collation Setting is SQL_Latin1_General_CP1_CI_AS.
Read More – New ConfigMgr Primary Server Installation Step By Step Guide (anoopcnair.com).
Collation Settings for ConfigMgr DB
Site database must use the following collation: SQL_Latin1_General_CP1_CI_AS for all SCCM servers installed with all operating systems except Chinese. ConfigMgr supports two exceptions to this collation for the China GB18030 standards (Configuration Manager must use a Chinese operating system).
- Chinese_Simplified_Pinyin_100_CI_AI
- Only applicable for Chinese Operating System
- Chinese_Simplified_Stroke_Order_100_CI_AI
- Only applicable for Chinese Operating System
SQL_Latin1_General_CP1_CI_AS
SQL Server configuration – Specify the collation configuration for SCCM Server DB. During the SQL DB installation, you will need to configure the following configuration.
Read More – How To Install SQL Server As Part SCCM Migration SQL Server – Installation | Part 1 HTMD Blog (anoopcnair.com).
How to Change Collation Settings
Microsoft documented the supported way to change the SQL DB collation. If the collation setting is not correct, then the SCCM server installation prerequisite fails.
NOTE! – Changing the server collation in SQL Server – More details here.
- Make sure you have all the information or scripts needed to re-create your user databases and all the objects in them.
- Export all your data using a tool such as the bcp Utility. For more information, see Bulk Import and Export of Data (SQL Server).
- Drop all the user databases.
- Rebuild the
masterdatabase specifying the new collation in the SQLCOLLATION property of the setup command. For example:ConsoleCopy
Setup /QUIET /ACTION=REBUILDDATABASE /INSTANCENAME=InstanceName
/SQLSYSADMINACCOUNTS=accounts /[ SAPWD= StrongPassword ]
/SQLCOLLATION=CollationName
Resources
Author
Anoop is Microsoft MVP! He is a Solution Architect in enterprise client management with more than 20 years of experience (calculation done in 2021) in IT. He is Blogger, Speaker, and Local User Group HTMD Community leader. His main focus is on Device Management technologies like SCCM 2012, Current Branch, and Intune. He writes about ConfigMgr, Windows 11, Windows 10, Azure AD, Microsoft Intune, Windows 365, AVD, etc………