Changing System Databases Collations

Collations is a critical and interesting topic at the same time, collations simply refers to the behavior of SQL Server for sorting and comparing character data, which is sorted using rules that define the correct character sequence, case-sensitivity, accent marks, ..etc

SQL Server has three levels of collation, the first one on the instance level and which affects the system databases, the second level is the database one, and finally on row level.

The instance level collation “the one controls the system databases behavior” is defined during the installation of the database engine and also reflects any new created database and table's columns unless you explicitly specify a different collation, then it is important to carefully select your collation during the DB engine installation.

But what will happen in case of you get the need to change the instance collation after installing your engine? This post mainly targets this need and show how to apply such critical change.

Changing the instance collation or system databases collations should go throw the following steps:

1- Collecting all required information for the current installed database engine:

This simply include collecting information about the database instance configurations, databases and its locations and server\database security settings.

All this information can be done by running the following queries

SELECT * FROM sys.configurations;

SELECT

SERVERPROPERTY('ProductVersion ') AS ProductVersion,

SERVERPROPERTY('ProductLevel') AS ProductLevel,

SERVERPROPERTY('ResourceVersion') AS ResourceVersion,

SERVERPROPERTY('ResourceLastUpdateDateTime')

AS ResourceLastUpdateDateTime,

SERVERPROPERTY('Collation') AS Collation

select @@servername as 'sql_instance', db_name(database_id) as 'database_name', name,type_desc as 'file_type',SUM((size * 8)/1024)as 'file_size_MB',

convert(numeric(6,2),SUM((convert(numeric(17,4),(size * 8))/1024)/1024))as 'file_size_GB', substring(physical_name,1,1) as 'file_drive',

physical_name as 'file_location'

from sys.master_files

group by database_id,substring(physical_name,1,1),physical_name,type_desc,name

order by db_name(database_id) asc

go

Also it is recommended to store this information into a text file or excel sheet because you will need it during the following steps.

2- Getting logins server & DB level permissions:

We have to keep a copy of the server logins and its associated database users and the permissions\roles assigned to it, this information also required to be stored in order to revert it back “please note that all assigned roles\permissions will be removed during collation changing process”

We can use the scripts discussed before in previous two posts in order to collect this information

Getting Logins that granted server-level authorities

Getting Server Logins and its assigned DB Users

3- Backup all system databases

4- Checking system DBs template files " \MSSQL\Binn\Templates", these templates will be used for creating system databases again during the changing collation process “and that is the reason behind the need of adding all server logins again from scratch” 

5- Detaching all user databases using the below script

DECLARE @name VARCHAR(50) -- database name

DECLARE db_cursor CURSOR FOR

select name from sys.databases where name not in ('tempdb','master','msdb','model')

OPEN db_cursor 

FETCH NEXT FROM db_cursor INTO @name 

WHILE @@FETCH_STATUS = 0 

BEGIN 

print @name

exec(

'alter database '+@name+' SET  SINGLE_USER WITH ROLLBACK IMMEDIATE'+

' EXEC master.dbo.sp_detach_db @dbname = '+@name+' , @keepfulltextindexfile=N''true'''

)

       FETCH NEXT FROM db_cursor INTO @name 

END 

CLOSE db_cursor 

DEALLOCATE db_cursor

6- In case of cluster instance, then SQL instance must be brought offline from Failover Cluster Manager.

7- Navigate to SQL Server media to setup.exe file then execute it with the following parameters:

/ACTION=REBUILDDATABASE /QUIET /INSTANCENAME=SQL Instance Name /SQLSYSADMINACCOUNTS= Accounts that should be granted sysadmin /SAPWD=sa password /SQLCOLLATION= the required collation

Note: in the /SQLSYSADMINACCOUNTS section, only windows accounts "local & domain accounts" are accepted, SQL accounts is not accepted