“The Only Thing That Is Constant Is Change”

Changing Collation for SQL Server 2005 Clustered Default Instance.

Posted by database-wiki on March 6, 2011


We would like to change collation of a instance similar to one on a different environment. In our case, it is set the default collation for SQL server 2005 clustered instance.

Action Plan:

1. Create a database, Create a table with right column collation as that dev server table column collation. (do this only when the column collation is different on the tables

Compared to development or production, you can use select name, collation from syscolumns where [id]=object_id(‘Mytable’))

INTO database2.dbo.tblTest
FROM database1.dbo.tblTest

2. Please take a backup of the system databases (master, msdb, model, tempdb, mssqlresurce) using sql server as well as file system backup. Run script in the article http://support.microsoft.com/kb/918992 to generate the logins from the master and script out the job, any packages that’s there. Please save them to a text file as it is.

3. Changing the collation for a default instance (mssqlserver) in clustered environment. (tested) (sql server build is 9.00.3042 which is sp2)


start /wait setup.exe /qb VS=sqlvirt155143 INSTANCENAME=MSSQLSERVER REINSTALL=SQL_Engine REBUILDDATABASE=1 SAPWD=”MyPass” SQLCOLLATION=Latin1_General_CI_AI ADMINPASSWORD=”balajimalab!” SQLACCOUNT=DC155143\Administrator SQLPASSWORD=”balajimalab!” AGTACCOUNT=DC155143\Administrator AGTPASSWORD=”balajimalab!” SKUUPGRADE=1

 3.1 Initial collation of the sql serve is below:

3.2 Installation started.

3.3 Setup progress.

3.4 Running this process will rebuild the system databases.

3.5 Setup completes.

3.6 New server collation.

4. After which run thoses logins and jobs scripts which was scripted in step 2.

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 )

Twitter picture

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

Facebook photo

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

Google+ photo

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

Connecting to %s

%d bloggers like this: