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 the SCCM Primary server installation.
Let’s see what ConfigMgr’s SQL Server DB collation configuration is required. This post mainly talks about SQL Database collation settings and options to change the collation settings.
It would help ensure 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
The 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 format.
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 incorrect, the SCCM server installation prerequisite fails.
NOTE! – Changing the server collation in SQL Server
- Ensure you have all the information or scripts needed to re-create your user databases and objects.
- Export all your data using a tool such as the BCP Utility. See Bulk Import and Export of Data (SQL Server) for more information.
- 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……………