SCCM SQL Database Collation Setting Options SQL_Latin1_General_CP1_CI_AS

by

Let’s learn how to correctly configure SCCM SQL Database Collation Setting Options SQL_Latin1_General_CP1_CI_AS. This should be done during the installation of SQL DB for the SCCM Primary server.

Let’s see what ConfigMgr’s SQL Server DB collation configuration is required. In this post, I mainly discuss SQL Database collation settings and options for changing them.

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).

Patch My PC
Index
Collation Settings for ConfigMgr DB
SQL_Latin1_General_CP1_CI_AS
How to Change Collation Settings
SCCM SQL Database Collation Setting Options SQL_Latin1_General_CP1_CI_AS – Table 1

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. You will need to configure the following format during the SQL DB installation.

Read More – How To Install SQL Server As Part SCCM Migration SQL Server – Installation | Part 1 HTMD Blog (anoopcnair.com).

SCCM SQL Database Collation Setting Options SQL_Latin1_General_CP1_CI_AS - Fig.1
SCCM SQL Database Collation Setting Options SQL_Latin1_General_CP1_CI_AS – Fig.1

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.

You can specify a default collation for each new database you create by using the COLLATE clause in the CREATE DATABASE and ALTER DATABASE statements instead of changing the default collation of an instance of SQL Server.

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 master Database 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
SCCM SQL Database Collation Setting Options SQL_Latin1_General_CP1_CI_AS - Fig.2
SCCM SQL Database Collation Setting Options SQL_Latin1_General_CP1_CI_AS – Fig.2

Resources

We are on WhatsApp. To get the latest step-by-step guides and news updates, Join our Channel. Click here –HTMD WhatsApp.

Author

Anoop C Nair is Microsoft MVP! He is a Device Management Admin with more than 20 years of experience (calculation done in 2021) in IT. He is a 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.

Leave a Comment