As changing the database collation is quite easy, you may find that changing the server collation is not. When you read the article at MSDN: Set or Change the Server Collationit seems that you need at least complex test environment before you can apply those advice.
When you need to change server collation
Usually when you host localized software database it is enough to set appropriate collation for each database. The default server collation is being set during SQL Server setup, and by default it is set to the current regional settings of Windows Server. This has quite important implications during future use of SQL Server instance, as there is software that requires Latin1_General (English) server collation. One of the software that requires SQL_Latin1_General_CP1_CI_AS collation is Microsoft System Center Operation Manager.
How to change SQL Server 2008 R2 collation?
The article at MSDN: Set or Change the Server Collation tries to cover steps required to change you SQL Server’s collation. Most of the blogs you can find on the Internet simply copy and pastes this article without any further investigation of the process. The article instructs that in order to change server collaction one need to rebuild master dabatase.
Rebuild Master database. Do what??!?!
Master database contains information about logins and attached user databases. When the rebuild process is going to be complete you will find that your SQL Server misses that information. As a result all the databases will be detached, and you will find all databases files intact. So, before we start this process we should store somewhere information about logins and databases.
The first scripts enumerates through all the logins (principles) and prints T-SQL script that can be used to recreate that logins when the rebuild is complete. It recreates SQL Logins and Windows Users and Groups and database roles membership. It neither recovers Certificate logins nor server roles.
SKRYPT
This script will generate T-SQL script that you could use later to re attach your databases back to the SQL server:
01.DECLARE@dbid int
02.DECLARE@fileid int
03.DECLARE@DBName nvarchar(64)
04.DECLARE@Cmd nvarchar(max)
05.DECLARE@Path nvarchar(500)
06.
07.SET@Cmd = N'USE [Master]
08.GO
09.'
10.-- skip master, model and msdb
11.SET@dbid = 4
12.
13.WHILE EXISTS(SELECTTOP1 dbid, nameFROMsys.sysdatabases
14. WHEREdbid > @dbid ORDERBYdbid)
15.BEGIN
16. SELECTTOP1 @DBName = name, @dbid = dbid
17. FROMsys.sysdatabases
18. WHEREdbid > @dbid ORDERBYdbid
19.
20. IF @DBName <> N'tempdb'
21. BEGIN
22. SET@Cmd = @Cmd + N'CREATE DATABASE ['+ @DBName + N'] ON
23.'
24. SET@fileid = 0
25. WHILE EXISTS(SELECTTOP1 file_id
26. FROMsys.master_files
27. WHEREdatabase_id = @dbid
28. ANDfile_id >= @fileid
29. ORDERBYfile_id)
30. BEGIN
31. SELECTTOP1
32. @fileid = file_id,
33. @Path = physical_name
34. FROMsys.master_files
35. WHEREdatabase_id = @dbid
36. ANDfile_id >= @fileid
37. ORDERBYfile_id
38.
39. SET@Cmd = @Cmd + N'( FILENAME = N'''+ @Path + N''' )'
40. IF @fileid < (SELECTMAX(file_id)
41. FROMsys.master_files
42. WHEREdatabase_id = @dbid)
43. BEGIN
44. SET@Cmd = @Cmd + N',
45.'
46. END
47. SET@fileid = @fileid + 1
48. END
49. SET@Cmd = @Cmd + N' FOR ATTACH
50.GO
51.';
52. END
53. SET@dbid = @dbid + 1
54.END
55.SELECT@Cmd
When you run this script it will generate another T-SQL script, that you should save to a file. It should look like this:
01.USE [Master]
02.GO
03.CREATEDATABASE[CMS] ON
04.( FILENAME = N'E:\MSSQL10\MSSQL\DATA\CMS.mdf'),
05.( FILENAME = N'E:\MSSQL10\MSSQL\DATA\CMS.ldf')FORATTACH
06.GO
07.CREATEDATABASE[Tfs_TestConfiguration] ON
08.( FILENAME = N'E:\MSSQL10\MSSQL\DATA\Tfs_TestConfiguration.mdf'),
09.( FILENAME = N'E:\MSSQL10\MSSQL\DATA\Tfs_TestConfiguration_log.LDF')FORATTACH
10.GO
11.CREATEDATABASE[NORTHWIND] ON
12.( FILENAME = N'E:\MSSQL10\MSSQL\DATA\Northwind.mdf'),
13.( FILENAME = N'E:\MSSQL10\MSSQL\DATA\Northwind2.ndf'),
14.( FILENAME = N'E:\MSSQL10\MSSQL\DATA\Northwind_log.ldf')FORATTACH
15.GO
16.CREATEDATABASE[WSS_Content] ON
17.( FILENAME = N'E:\MSSQL10\MSSQL\DATA\WSS_Content.mdf'),
18.( FILENAME = N'E:\MSSQL10\MSSQL\DATA\WSS_Content_log.LDF')FORATTACH
19.GO
I do advice to make a back-up of all your databases as well.
Changing SQL Server 2008/2008 R2 Server Collation
Now when we have script that would recover our users and reattach databases we can move on. You will need SQL Server installation media, as the only way to change the collation is to run setup locally:
E:\Setup.exe /QUIET /ACTION=REBUILDDATABASE /INSTANCENAME=InstanceName /SQLSYSADMINACCOUNTS=accounts /SAPWD=StrongPassword /SQLCOLLATION=CollationName
Where:
- E:\ - is SQL media path
- INSTANCENAME – is the name of the instance. The default instance name is MSSQLSERVER.
- SQLSYSADMINACCOUNTS – is the account which will have sysadmin privileges. If you want to use Windows account for that purpose type: SQLSYSADMINACCOUNTS=DOMAIN\Account or if you want to use your current account, type: SQLSYSADMINACCOUNTS=%username%
- SAPWD – is your current password to the SA login to you SQL server instance.
- SQLCOLLACTION – is the name of new server collation. For complete list of collations look here: Windows Collation Name. More info on collation: Collation and Unicode Support
The process should take about a minute or so and as a result the Summary.txt file will be created in the C:\Program Files\Microsoft SQL Server\100\Setup Bootstrap\Log\Summary.txt. You will find there detailed information about the whole process and possible errors.
Final information
Please note that this article covers only simple scenario, without recreating database mirroring sessions and/or cluster configuration. In such scenarios the whole process will get much more complex and risky.