How to change SQL Server Instance Collation in an Always On Environment.

SUMMARY
You have successfully installed and configured a 2-Node Cluster with Always On which has been deployed into Production. Well you notice later that your 2 SQL Server Standalones participating in your AlwaysOn have different Server Collation.

Assuming:
Server A: Latin1_General_CI_AS – Case Insensitive
Server B: SQL_Latin1_General_CP1_CI_AS

ACTION
We are instructed as DBA’s to get this issue resolved to meet business requirements and standards and have both replicas have the same Server Collation.

STEPS:
Different Collation with 2 node cluster and AlwaysOn configured. No replication and no maintenance jobs on the server. Make sure to have the setup media for your SQL Server 2014 Enterprise Edition for step 8 below. Your current version “SQL Version Name – Microsoft SQL Server 2014 (SP2) (KB3171021) – 12.0.5000.0 (X64)” Machine Name – REBEL SQL Server Name – REBEL\MSSADMIN

collation1

STEP 1 : Create Backup of both VM’s in case we need to rollback changes
STEP 2: Create a full database backup and transaction log backup of all USER databases on the primary with the wrong Collation

collation2 collation3

STEP 3 : Run sp_configure to find the current server settings,for this you have to set ‘show advanced option’ to 1.
exec sp_configure ‘show advanced options’,1
reconfigure with override

Now run the sp_configure command to extract the server settings.
exec sp_configure

Copy the output into an excel sheet and save it in a safe location.You would need this after the collation has been changed.

STEP 4: Script out all the SQL Server Agent jobs on the server. – https://technet.microsoft.com/en-us/library/ms191450(v=sql.105).aspx

STEP 5: check whether any mail profiles are present and copy the details of each profile to a table on one of the user databases.
(use msdb’s sysmail_account,sysmail_profile,sysmail_profileaccount and sysmail_server to retrieve profile related information).

In order to create profile and account you can manipulate the code excerpt given below

BEGIN TRAN
SET @DESCRIPTION = ‘give description about the profile ‘;
SET @DESCRIPTION = @DESCRIPTION + @V_MAIL_PROFILE ;
— Create a Database Mail profile
EXECUTE msdb.dbo.sysmail_add_profile_sp
@profile_name = @V_MAIL_PROFILE,
@description = @DESCRIPTION;
SET @DESCRIPTION = ‘give description about the account ‘;
SET @DESCRIPTION = @DESCRIPTION + @V_MAIL_ACCOUNT ;
— Create a Database Mail account
EXECUTE msdb.dbo.sysmail_add_account_sp
@account_name = @V_MAIL_ACCOUNT,
@description = @DESCRIPTION,
@email_address = @EMAIL_ADDRESS,
@replyto_address = @EMAIL_ADDRESS,
@display_name = @DISPLAY_NAME,
@mailserver_name = @MAILSERVER_NAME;
— Add the account to the profile
EXECUTE msdb.dbo.sysmail_add_profileaccount_sp
@profile_name = @V_MAIL_PROFILE,
@account_name = @V_MAIL_ACCOUNT,
@sequence_number = @SEQUENCE_NUMBER;
— Grant access to the profile to the DBMailUsers role
EXECUTE msdb.dbo.sysmail_add_principalprofile_sp
@profile_name = @V_MAIL_PROFILE,
@principal_id = @PRINCIPAL_ID,
@is_default = @IS_DEFAULT;
COMMIT TRAN

STEP 6: Script out the logins on server and prepare a list that contains the permissions provided for each login.

https://social.msdn.microsoft.com/Forums/sqlserver/en-US/8df56c87-c507-4744-9503-c8d64437a742/script-to-script-out-users-roles-and-schema-leval-permistion-before-restore-into-the-test-server-?forum=transactsql

STEP 7: Delete Listener (On primary)

USE [master]
GO
/****** Object:  AvailabilityGroupListener [ListenerName]    Script Date: 11/1/2016 6:53:18 PM ******/
ALTER AVAILABILITY GROUP [ProdAG]
REMOVE LISTENER N’ListenerName’;
GO

collation3_4

STEP 8: drop availability group (On primary) Note – this will put AG databases in restoring state on secondary

USE [master]
GO
/****** Object:  AvailabilityGroup [TestAG]    Script Date: 11/1/2016 4:53:38 PM ******/
DROP AVAILABILITY GROUP [ProdAG];
GO

Delete AG

collation4_5 collation5

STEP 9: Stop SQL Server through configuration manager on both nodes. Keep in mind domain account used for SQL Server Instance name, example below uses CONTOSO\Administrator (Secondary Node 1st then primary Node)

collation6

collation7

STEP 10 : On server with incorrect COLLATION run the following command using your SQL Server media, use the domain account for SQL Server service(Example CONTOSO\ADMINISTRATOR). On my example below I mounted the media to E:\ drive

Setup /QUIET /ACTION=REBUILDDATABASE /INSTANCENAME=COLLATION2 /SQLSYSADMINACCOUNTS=CONTOSO\Administrator /SAPWD=** /SQLCOLLATION=SQL_Latin1_General_CP1_CI_AS

collation8 collation9

STEP 11 : Start SQL Server through configuration manager on bad Node à Connect to SQL Server Management Studio à Right click Instance name and check COLLATION

(Example below was changed from Latin1_General_CI_AS to SQL_Latin1_General_CP1_CI_AS)

collation10
collation11

STEP 12: Attach each database localtestdb –> Click OK –> Click OK

For my test environment via T-SQL or GUI –

USE [master]
GO
CREATE DATABASE [AdventureWorks2014] ON
( FILENAME = N’C:\Program Files\Microsoft SQL Server\MSSQL12.COLLATION2\MSSQL\DATA\AdventureWorks2014_Data.mdf’ ),
( FILENAME = N’C:\Program Files\Microsoft SQL Server\MSSQL12.COLLATION2\MSSQL\DATA\AdventureWorks2014_Log.ldf’ )
FOR ATTACH
GO

collation12.png

STEP 13: Check under security, and just run the command for the logins that are not already here. In my example I only had to add the following account “testnode1”

USE master

CREATE LOGIN [testnode1] WITH PASSWORD=0x0200b9e84f64f02a91f31214e48daefd72c3438068f8c94aa68a7f45b27ee487292a8244b9fc92d852ad14797039b6e042b2b1b4f070466241d337e055568fadf62795babd88 HASHED, SID=0xc373b220e1279a4089db7b9fe656f248, DEFAULT_DATABASE=[master], DEFAULT_LANGUAGE=[us_english], CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF

CREATE LOGIN [Testnode2localuser] WITH PASSWORD=0x0200633f51b19f09128261def02a1e737dfc3e650460e61473f3b803e24b1ca24118f7bdc100e5c6d92420f28ca93f2d5fc0c28ab4bc65dac304f6793891bfb07557b76d0e88 HASHED, SID=0x112a26acd6ba8a4f977f1ca3ba036eea, DEFAULT_DATABASE=[master], DEFAULT_LANGUAGE=[us_english], CHECK_EXPIRATION=ON, CHECK_POLICY=ON

Note reset SA password
collation13
STEP 14: Add roles, for my test environment I executed the following
EXEC sp_addsrvrolemember [CONTOSO\administrator], [sysadmin]
EXEC sp_addsrvrolemember [NT SERVICE\SQLWriter], [sysadmin]
EXEC sp_addsrvrolemember [NT SERVICE\Winmgmt], [sysadmin]
EXEC sp_addsrvrolemember [NT SERVICE\MSSQL$COLLATION2], [sysadmin]
EXEC sp_addsrvrolemember [NT SERVICE\SQLAgent$COLLATION2], [sysadmin]

STEP 15: Use results of STEP 2 to configure your SQL Server 2014 – https://msdn.microsoft.com/en-us/library/ms178067(v=sql.120).aspx

Example A

The following example sets the max server memory option to 4 GB:
sp_configure ‘show advanced options’, 1;  GO  RECONFIGURE;  GO  sp_configure ‘max server memory’, 4096;  GO  RECONFIGURE;  GO

STEP 16: Results of STEP 3, use to recreate all SQL Server Agent Jobs

STEP 17: Recreate mail profiles from STEP 4

STEP 18: Check and if necessary Upgrade to SQL Server 2014 SP2
(I did not have to upgrade after rebuilding my system databases with correct collation, doublecheck with select @@version) – https://www.microsoft.com/en-us/download/details.aspx?id=53168

STEP 19A: Start SQL Server on the other node which has the correct COLLATION through configuration manager and open up SQL Server management studio (see below all my AlwaysOn databases are all in restoring state)

collation14

collation15

STEP 19B: When I did not shut down both servers and I only changed the COLLATION on primary. 2nd server became primary, the databases did not come back up with restoring state. In this scenario I had to restore databases with NO RECOVERY and Add replica to the availability group

Restored DB from full backup with NO RECOVERY

collation16  collation17

Restored transaction log backup with NO RECOVERY

collation18  collation19

I then went to primary server and right clicked secondary –> Connect

collation20    collation21

After connecting it no longer showed red icon next to the name but a green arrow like below

collation22

On the secondary server databases still showed Restoring state

collation23

Under Availability Databases –> Right click database –> Join to Availability Group (will have to be done for each database). Click OK on the Join Database to AG Dialog Box

collation24  collation25

Replicas successfully added and show in Synchronized state

collation26

STEP 20: On primary Node –> Right Click Availability Groups –> New Availability Group Wizard –> Click Next

collation27

Input name for availability group –> click Next

collation28

Select databases to include in Availability group –> Click Next

collation29

Click Add Replica

collation30

Select Server name for 2nd Node which will be part of the Availability group –> click connect

collation31

Select options applicable to your environment by selecting Replicas tab options, Endpoints tab options, Backup Preferences tab options. With Listener options choose “Do not create an availability group listener now” we will do this step last –> click Next

collation32

Select Join only –> Click Next

collation33

Click Next

collation34

Verify choices and click Finish

collation35

Successfully recreated availability group –> Click Close
collation36.png

Checked on secondary node and replicas changed from restoring state to Synchronized state

collation37

STEP 21:  Create a Listener (Last Step after Availability Group is up and running)

https://msdn.microsoft.com/en-us/library/hh213080(v=sql.120).aspx

–Via GUI –> Click AlwaysOn High Availability –> Availability Groups –> ProdAG(primary) –> Availability Group Listeners –> Right click –>Add listener        –>Click OK –> Click OK

collation38.png

Listener has been added

collation39

2 thoughts on “How to change SQL Server Instance Collation in an Always On Environment.

Add yours

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

w

Connecting to %s

Create a website or blog at WordPress.com

Up ↑

%d bloggers like this: