How do i change,Update SQL Server Collation for SQL Server failover cluster Instance.

How I do change/Update SQL Server Collation for SQL Server failover cluster Instance?

I have been in a situation where SQl Server Instance was configured in different collation and customer suggested to change it on 2 Node  Active/ Passive cluster.

Locate your Environment details

1.Environment:-

NodeName	status	status_description	is_current_owner
Win2k12-1	0	up	1
Win2k12-2	0	up	0

Nodes	SQlVirtualServerName	InstanceName
Win2k12-1	Sqlvirprd001	SQL001
Win2k12-2

2.Record all service packs and hotfixes applied to the instance of SQL Server and  the current collation.  You must reapply these updates after rebuilding the system databases.  [Very Important to collect] SELECT SERVERPROPERTY('ProductVersion ') AS ProductVersion, SERVERPROPERTY('ProductLevel') AS ProductLevel, SERVERPROPERTY('ResourceVersion') AS ResourceVersion, SERVERPROPERTY('ResourceLastUpdateDateTime') AS ResourceLastUpdateDateTime, SERVERPROPERTY('Collation') AS Collation; Before:- ProductVersion ProductLevel ResourceLastUpdateDateTime Collation 11.0.5058.0 SP2 2014-05-14 18:34:17.030 Latin1_General_CI_AS

3.Record all server-wide configuration values.
SELECT * FROM sys.configurations;

4.Record the current location of all data and log files for the system databases. Rebuilding the system databases installs all system databases to their original location. If you have moved system database data or log files to a different location, you must move the files again.

SELECT name, physical_name AS current_file_location
FROM sys.master_files
WHERE database_id IN (DB_ID('master'), DB_ID('model'), DB_ID('msdb'), DB_ID('tempdb'));

5.Locate the current backup of the master, model, and msdb databases.

6.If the instance of SQL Server is configured as a replication Distributor, locate the current backup of the distribution database

7.Ensure you have appropriate permissions to rebuild the system databases. To perform this operation, you must be a member of the sysadmin fixed server role. For more information

8.Verify that copies of the master, model, msdb data and log template files exist on the local server. The default location for the template files is C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\Binn\Templates. These files are used during the rebuild process and must be present for Setup to succeed. If they are missing, run the Repair feature of Setup, or manually copy the files from your installation media. To locate the files on the installation media, navigate to the appropriate platform directory (x86 or x64) and then navigate to setup\sql_engine_core_inst_msi\Pfiles\SqlServr\MSSQL.X\MSSQL\Binn\Templates.

Check  are you in Active node or not?

   select * from sys.dm_os_cluster_nodes:-

NodeName	status	status_description	is_current_owner
Win2k12-1	0	up	1
Win2k12-2	0	up	0

Here Active Primary node is Win2k12-1

Record SA password in a notepad.

SA password ---> Password@123

Record InstanceName in notepad

SELECT SERVERPROPERTY ('InstanceName')

Make sure that SQL Services is offline, if not do it from Cluster Manager. 

Record where is SQL Server setup file is located.

in my case it was located in SQLMediaFolder under abcd folder under C:\ drive.

Go to Comand prompt and execute below Command from Win2k12-1 as this node is Current owner of SQL Server Instance SQL001.

Complete command.

"C:\abcd\SQLMediaFolder\setup.exe" /QUIET /ACTION=REBUILDDATABASE /INSTANCENAME=SQL001 /SAPWD=Password@123 /SQLCOLLATION=SQL_Latin1_General_CP1_CI_AS /SQLSYSADMINACCOUNTS="Adven\domainaccountName"

Here  /SQLSYSADMINACCOUNTS="Adven\domainaccountName" 
Adven is my domain Account and domainaccountName is a login name in SQL Server Instance which has sysadmin privilage. 


if all parameters are correct,  setup will execute successfully and will not give any error.

you can review from C:\program Files\Microsoft SQL Server\Setup Bootstrap\Log\<Install date and time folder>\summary.txt file. 

The File will appear like this:-

Overall summary:

  Final result:                  Passed

  Exit code (Decimal):           0

  Start time:                    2015-10-05 12:47:10

  End time:                      2015-10-05 12:51:14

  Requested action:              RebuildDatabase

Now go to Cluster Administrator and make online SQl Server Service.

Run below command again and check collation has changed from previous setup. 

SELECT
SERVERPROPERTY('ProductVersion ') AS ProductVersion,
SERVERPROPERTY('ProductLevel') AS ProductLevel,
SERVERPROPERTY('ResourceVersion') AS ResourceVersion,
SERVERPROPERTY('ResourceLastUpdateDateTime') AS ResourceLastUpdateDateTime,
SERVERPROPERTY('Collation') AS Collation;

ProductVersion	ProductLevel	ResourceVersion	ResourceLastUpdateDateTime	Collation
11.0.5058.0	SP2	11.00.5058	2014-05-14 18:34:17.030	SQL_Latin1_General_CP1_CI_AS

Hence i see the collation has been changed from Latin1_General_CI_AS to SQL_Latin1_General_CP1_CI_AS

If you have changed the server collation, do not restore the system databases. Doing so will replace the new collation with the previous collation setting.

Kindly note, this operation will recreate your master database, hence all existing setting will get reset,

All user database will be de-attached, hence you need to attach all users database.

Thanks  for Reading..