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;
SERVERPROPERTY('ProductVersion ') AS ProductVersion,
SERVERPROPERTY('ProductLevel') AS ProductLevel,
SERVERPROPERTY('ResourceVersion') AS ResourceVersion,
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'
group by database_id,substring(physical_name,1,1),physical_name,type_desc,name
order by db_name(database_id) asc
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
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')
FETCH NEXT FROM db_cursor INTO @name
WHILE @@FETCH_STATUS = 0
'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
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