Editor: Please review my edits.

This article is dedicated to the Senior DBA Nick Mcnair,who showed me the command to alter server level collation.nRecently I hit a problem were the user database had a different collation from that of the server level collation. In this article we wil look at how to change the server level collation for a clustered SQL Server. When one changes the server collation he/she is rebuilding the system databases. This means you would lose references to user databases, jobs, SSIS packages, etc. In order to avoid the loss of information and reduce downtime, it is mandatory that a step by step procedure is followed.

On a side note the database collation can be changed using the command given below

ALTER DATABASE <database_name> COLLATE <collation>;
ALTER DATABASE adventureworks COLLATE SQL_Latin1_General_Cp850_BIN;

It is worth remembering that character fields such as varchar,char,text etc will have the old collation even after the above command is executed.

I have listed below some steps that I had to follow in order to change the server level collation.The steps given below aren't an exhaustive list, but they worked for me.

1) 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.

2) Go through each of the user databases and check whether any user defined assemblies exist. Make a note of all the databases that have user defined assemblies.

3) Script out all the jobs on the server.

4) Check the database maintenance plan and make sure that you have noted down the schedules for each step in the maintenance plan.

5) Export all the SSIS packages to the file system.

6) 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 excert 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

7) :Script out the logins on server and prepare a list that contains the permissions provided for each login. You are lucky if all users belong to some windows group rather than individual Windows account/SQL accounts for each user.

8) Stop the replication agent if you have replication set up. Use the table sysarticles to get the list of objects that are being replicated only if you chose to use GUI to create replication.Scripting replication makes life easier,incase you wish to use scripts then refer to the link: http://technet.microsoft.com/en-us/library/ms151213.aspx

9) Detach all user defined databases. The sp_detach_db command is listed below.

  sp_detach_db  @dbname=  'AdTestDb' 
               ,@skipchecks= 'false' -- update statistics
               ,@keepfulltextindexfile ='true' --true if you don't want 
                                  --full text search index to be dropped

For more details refer the web link: http://technet.microsoft.com/en-us/library/ms188031.aspx

10) Take the SQL service offline from cluster manager (applicable for clustered environment).If it is a standalone system then shutdown the instance.

Now we can run this command from a command prompt

Setup /QUIET /ACTION=REBUILDDATABASE /INSTANCENAME=InstanceName 
/SQLSYSADMINACCOUNTS=accounts /[ SAPWD= StrongPassword ] 
/SQLCOLLATION=CollationName

For more details about this command see this link: http://msdn.microsoft.com/en-us/library/ms179254.aspx

Once the command has been executed successfully restart the SQL Services from cluster manager. Check whether the collation has been reset to the required collation by checking server property

11) Attach all the user databases.I was for some reason unable to attach the user database using windows account (SQL Server 2008).Use the SQL login to attach the user databases if you come across some errors(probably because this stored proc is deprecated).

EXEC sp_attach_db @dbname = N'AdTestDb'
                 ,@filename1 = N'D:\Program Files\Microsoft SQL Server\MSSQL\Data\AdTestDb.mdf' 
                 ,@filename2 = N'L:\Program Files\Microsoft SQL Server\MSSQL\Data\AdTestDb.ldf'

For more details refer the web link: http://msdn.microsoft.com/en-us/library/aa259611(SQL.80).aspx

12) All the databases that had user defined assemblies should have 'trustworthy' set to true.

ALTER DATABASE <DBNAME> SET TRUSTWORTHY ON;

13) Change the server settings using the results obtained in step1.

sp_configure 'name','runvalue'
reconfigure with overrride

14)Recreate the linked servers and logins.check for orphaned users using the command

sp_change_user_login --check orphaned users

For more details refer to this link: http://msdn.microsoft.com/en-us/library/ms175475.aspx

15) Create all the mail profiles(tailor the script given in step6 and use it).

16) Import the SSIS packages to the SQL Server from the file system.

17) Recreate all the jobs.

18) Create the maintenance plan using schedules noted in step4.

19) Setup replication based on the output obtained in step8.

Conclusion

Before you change the collation of production server,do perform a dry run in the development environment and test the jobs,SSIS packages,application etc.If all looks fine then rollout the change into production.The above list isn't exhaustive by any means but will act as a good starting point.