Scenario

You have a SQL staging environment with a different collation than the SQL production environment. When you publish objects from staging to production you can have different results.

For example you have a case insensitive collation (SQL_Latin1_General_CP1_CI_AS) in staging and a case sensitive collation (SQL_Latin1_General_CP1_CS_AS) in production. Running this script in the 2 environments gives a different result:

DECLARE @strA VARCHAR(10)
DECLARE @strB VARCHAR(10)
SET @strA = 'abcdefghij'
SET @strB = 'ABCDEFGHIJ'
IF @strA = @strB 
 PRINT 'Case Insensitive Collation'
ELSE
 PRINT 'Case Sensitive Collation'

This inconsistent situation can have serious consequences when deploying scripts from staging to production. An equal environment is a requisite to prevent this kind of problems.

In this example the staging collation is Latin1_General_CI_AS and the production collation is SQL_Latin1_General_CP1_CI_AS. We will change the staging collation to SQL_Latin1_General_CP1_CI_AS.

To learn more about SQL Server 2008 collation please visit http://msdn.microsoft.com/en-us/library/ms144260.aspx

ToDo's

First we have to run some scripts to minimize manual actions. The next step is rebuilding the master database to change the server to the correct collation. After that we have to configure all the databases, logins and settings.

Run this statement that creates a script to detach all the user databases. Save the result of this script as detachdbs.sql

SELECT 'EXEC sp_detach_db ''' + name + ''', ''false''' FROM sysdatabases where name not in ('master','tempdb','model','msdb','northwind')

Now we have to make a script to attach all the user databases, therefore run following script and save the result to attachdbs.sql

DECLARE @dbname varchar(50)
DECLARE @dbname_prev varchar(50)
DECLARE @file varchar(150)
SET @dbname_prev = ' '
DECLARE cAttach CURSOR
READ_ONLY
FOR SELECT a.name, b.filename FROM sysdatabases a inner join sysaltfiles b on a.dbid = b.dbid where a.name not in ('master','tempdb','model','msdb','northwind', 'pubs') order by 1
OPEN cAttach
FETCH NEXT FROM cAttach INTO @dbname, @file
WHILE (@@fetch_status = 0)
BEGIN
IF (@dbname_prev <> @dbname) 
BEGIN
IF (@dbname_prev <> ' ')  PRINT 'FOR ATTACH ';
PRINT '';
PRINT 'CREATE DATABASE [' + @dbname + '] ON';
PRINT '( FILENAME =  ''' + rtrim(@file) + ''') ';
END
ELSE PRINT ', ( FILENAME =  ''' + rtrim(@file) + ''') ';
SET @dbname_prev = @dbname
FETCH NEXT FROM cAttach INTO @dbname, @file
END
CLOSE cAttach
DEALLOCATE cAttach
PRINT 'FOR ATTACH ';
GO

The next step is making a script to create all SQL Logins.

The master database is the repository of the logins. After rebuilding the master database the changes made after installation are lost. Use this article to script the logins http://support.microsoft.com/kb/918992. Save the result as Logins.sql

EXEC dbo.sp_help_revlogin 

Also the server role credentials located in de master database are lost after rebuilding de master database. Run this script and save it as LoginRoles.sql

select 'EXEC master..sp_addsrvrolemember @loginame = N''' + [name] + ''', @rolename = N''sysadmin''' as script from syslogins
where sysadmin = 1
union
select 'EXEC master..sp_addsrvrolemember @loginame = N''' + [name] + ''', @rolename = N''securityadmin''' as script from syslogins
where securityadmin = 1
union
select 'EXEC master..sp_addsrvrolemember @loginame = N''' + [name] + ''', @rolename = N''serveradmin''' as script from syslogins
where serveradmin = 1
union
select 'EXEC master..sp_addsrvrolemember @loginame = N''' + [name] + ''', @rolename = N''setupadmin''' as script from syslogins
where setupadmin = 1
union
select 'EXEC master..sp_addsrvrolemember @loginame = N''' + [name] + ''', @rolename = N''processadmin''' as script from syslogins
where processadmin = 1
union
select 'EXEC master..sp_addsrvrolemember @loginame = N''' + [name] + ''', @rolename = N''diskadmin''' as script from syslogins
where diskadmin = 1
union
select 'EXEC master..sp_addsrvrolemember @loginame = N''' + [name] + ''', @rolename = N''dbcreator''' as script from syslogins
where dbcreator = 1
union
select 'EXEC master..sp_addsrvrolemember @loginame = N''' + [name] + ''', @rolename = N''bulkadmin''' as script from syslogins
where bulkadmin = 1 

To make sure you don't lose anything you can export and script all the objects in the msdn database. So export SSIS packages, scripts jobs, linked servers, and anything else you can think of.

The preparation is done so we can start with the real stuff. To detach all the user databases you have to make sure everyone is disconnected. You also need to stop the application services that connect to the staging environment.

Run the prepared script detachdbs.sql to detach the databases.

Now that all the user databases are detached we can rebuild the master database to change the server collation. First make sure you bring the SQL Server service offline. Locate the Setup Bootstrap folder, which is usually C:\Program Files\Microsoft SQL Server\100\Setup Bootstrap\Release. You need to run this command, but be sure you use the correct instance name, your domain-account and a new sa password.

Setup /QUIET /ACTION=REBUILDDATABASE /INSTANCENAME={INSTANCE NAME} /SQLSYSADMINACCOUNTS={DOMAIN}\{WINDOWS ACCOUNT} /SAPWD={SA PASSWORD} /SQLCOLLATION=SQL_Latin1_General_CP1_CI_AS

After a successful installation the collation of the SQL Instance is changed to SQL_Latin1_General_CP1_CI_AS. Please check it before you continue. (Right-click on the instance in the Object Explorer => Properties => 'General' page => Server Collation)

Now we can run the prepared scripts:

• Attach all the user databases with attachdbs.sql
• Create SQL Logins with Logins.sql
• Create server roles for the logins with LoginRoles.sql

You also need to reconfigure your SQL Instance because all configurations are set to default after rebuilding the master database. To learn more how to configure your SQL instance please visit http://technet.microsoft.com/en-us/library/ms189631.aspx

The last step is changing the collation of the user databases. The collation of the attached user databases didn't change so you have to run the result of this script to change it.

Note: You need an exclusive lock on the database to do this

select  'ALTER DATABASE [' + [name] + '] COLLATE SQL_Latin1_General_CP1_CI_AS' from sys.databases
 where collation_name <> 'SQL_Latin1_General_CP1_CI_AS'

I hope this is a useful article for some people. Please let me know your comments!

Kevin Sermijn