SCCM Prerequisite Required SQL Server Collation

SCCM Prerequisite Required SQL Server Collation The SQL server collation acts as the default collation for all system databases that are installed with the instance of SQL Server and also any newly created user databases. Collations specify the rules for how strings of character data are sorted and compared, based on the norms of particular languages and locales. The Server collation is specified during SQL Server installation. This post will help anyone who is looking to fix the required SQL server collation prerequisite check that is seen while running prerequisite check during SCCM installation.

SCCM Prerequisite Required SQL Server Collation

When you install SCCM, during the prerequisite check step, it verifies that the instance for SQL Server and the Configuration Manager site database is configured to use the SQL_Latin1_General_CP1_CI_AS collation, unless you are using a Chinese operating system and require GB18030 support. We should specify SQL Server Collation when installing the SQL Server. The collation should be set to SQL_Latin1_General_CP1_CI_AS.

But sometimes during the SQL installation, you miss the step for specifying SQL Server Collation. If SQL Server is installed with a different collation for example Latin1_General_CI_AS it won’t work. During the SCCM prerequisite check, you get the below message.

Configuration Manager requires that you configure your SQL Server instance and Configuration Manager site database (if already present) to use the SQL_Latin1_General_CP1_CI_AS collation, unless you are using a Chinese operating system and require GB18030 support. For information about changing your SQL Server instance and database collations, see http://go.microsoft.com/fwlink/p/?LinkID=234541. For information about enabling GB18030 support, see http://go.microsoft.com/fwlink/p/?LinkId=234542.

Few things you need to keep in mind here –

1. Instead of changing the default collation of an instance of SQL Server, you can specify a default collation for each new database you create. This is recommended by Microsoft.
2. /SQLCOLLATION parameter should be supplied in the command. If you don’t supply this parameter, then SQL Server will rebuild the system databases with the collation you selected when you installed SQL Server.
3. Use a dedicated instance of SQL Server for each site.
4. While changing the SQL Collation, use an account which has admin rights on SQL server.

To change the SQL Collation, you need to rebuild the master database specifying the new collation in the SQLCOLLATION property of the setup command. Launch the command prompt (run as administrator). Navigate to your SQL setup folder or DVD where the setup file is located and execute the following command.

Setup.exe /QUIET /ACTION=REBUILDDATABASE  /SQLCOLLATION=SQL_Latin1_General_CP1_CI_AS /INSTANCENAME=MSSQLSERVER /SQLSYSADMINACCOUNTS=Account Name

Command Info

/q – performs silent installation.

/Action=RebuildDatabase – This option rebuilds the system databases to change the collation name.

/INSTANCENAME – Provide the name of the instance for which the collation has to be changed. If you have a Default Instance then it is “MSSQLSERVER”. If you have setup Named Instance then use “Named Instance Name”

/SQLCollation – Provide the new collation name of SQL Server.

/SQLSYSADMINACCOUNTS – Provide account name which has admin rights on SQL server.

When setup has completed rebuilding the system databases, it will return to the command prompt with no messages. So when you execute the above command there is no message that shows about the collation being changed. To verify or view a collation setting for a database, open the SQL Server Management Studio, connect to an instance of Database Engine and then expand that instance. Expand Databases, right-click the database and select Properties. On the General page, check the collation info under Maintenance option.

Once you change the SQL Collation, run the prerequisite check again and the SQL Server Collation message shouldn’t be seen again.