SQL Server Collation Settings for MCM Database

Question

There is a lot of conflicting or confusing information surrounding the required collation settings for SQL Server as it pertains to hosting the MCM database.

My SQL Server is installed with a collation Latin1_General_CI_AS, which won’t work.

How can the SQL collation be changed?

Answer 1

To change the collation, follow https://learn.microsoft.com/en-us/sql/relational-databases/collations/set-or-change-the-server-collation?view=sql-server-ver16 "

Regarding the requirements, always refer to the Microsoft official sources - i.e. MS Learn:

https://learn.microsoft.com/en-us/mem/configmgr/core/plan-design/configs/support-for-sql-server-versions

Database collation

At each site, both the instance of SQL Server that's used for the site and the site database must use the following collation: SQL_Latin1_General_CP1_CI_AS.

Configuration Manager supports two exceptions to this collation for the China GB18030 standard. For more information, see International support.

Note that this is a database collation - not instance collation - so even if you implemented different collation on the instance level, you can explicitly set one to a different value on the database level. When creating the database, you can specify the collation using the COLLATE clause in the CREATE DATABASE statement. Here's an example:

CREATE DATABASE [YourDatabaseName]
COLLATE SQL_Latin1_General_CP1_CI_AS

---

If the above response helps answer your question, remember to "Accept Answer" so that others in the community facing similar issues can easily find the solution. Your contribution is highly appreciated.

hth

Marcin

Answer 2

Changing the collation of a database is hard, although how hard it is depends on how complex the database is.

If you only need to change the server collation, that is slightly easier. I have an article on my web site, Changing the Server and Database Collation in SQL Server, that can help you with both operations.

Although, if I understand Marcin's post correctly, it's only the database collation that must be SQL_Latin1_General_CP1_CI_AS. (I know SQL Server and its collations well, but I have no experience of Microsoft Configuration Manager.)