Rebuilding server level collation setting

Scenario

You have just completed setting up a new development database server for a new project. After that, everyone in the team now starts working on implementing their design and starts to create their objects. When the time came for them to start adding test data, Chinese characters cannot be stored to the database. The system the development team are working on needs to store data in Chinese, Katakana and English characters at the same time.

Solution

The collation setting in your current setup must not be compatible with using Chinese or Katakana characters. Therefore you need to change the server level collation settings in your SQL Server. When doing an installation of a new instance of SQL Server you need to understand the code page, sorting, case sensitivity and accent sensitivity requirements of that application that is going to use the database.

Why?

SQL Server collation specifies the set of rules that will determine what types of characters will be stored in the database, for sorting and when you need to compare characters or strings. This is important because this is what specifies what code page to be used. Setting the wrong collation in your SQL Server may force you to re-install your SQL Server instance as this is a complex procedure.

How?

There is a way to change your server collation settings without the need to re-install your SQL Server. This article is a personal recount of how I was able to update our server collation settings when the above scenario happened to our development team by following the steps below.

  • Disconnect all current users. Let the development team that you need to at least 15-20 minutes to fix the issue. This was how long I had to fix our server when it happened.
  • Script all your logins, scheduled jobs, and other server objects you may have. This will serve as a backup that you will need to run later.  In my case, I had some legacy DTS packages so I had export them one by one and saved it somewhere.
  • Detach all your user database
  • Stop the SQL Server instance
  • Verify your SQL Server authentication mode. If you are using mixed mode authentication, you will need the ‘sa’ account password on hand.
  • Open the command window.
  • Issue the following command, see my screenshot below, please take note also of the directory path where you need to run the command.
Command Window

Command Window

If you are using mixed mode authentication in your security settings, you need to enter the ‘sa’ account password. The /instancename is your SQL Server instance name. The /SQLCollation parameter should contain the correct collation you need to set.

After the above command is successful, you will need to put all your system objects back because all the SQL Server system database are newly setup. So your earlier script for all your logins, scheduled jobs and other objects will come in handy now because you need to put them all back. Then attach your user database and verify everything is in order. If you have DTS packages which was the case for me, I needed to import them one by one again.

After that you can check your server collation setting and it should be the correct one now. And everyone can get back to work and you can get another cup of coffee.

About these ads

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

unfoldthecreativity

Traveller Observer

The SQL Pro

(Ayman El-Ghazali - www.thesqlpro.com)

Meels on Wheels

Meels for breakfast, lunch and dinner.

juliansherman

Just another WordPress.com site

Paul Turley's SQL Server BI Blog

from the world of Microsoft SQL Server BI and reporting

TroubleshootingSQL

Explaining the bits and bytes of SQL Server and Azure

Coding Tips

We will improve your knowledge

 Live, Learn, Share

Hemlock Time

Behind the scenes of becoming an APE (author, publisher, entrepreneur)

Sql And Me

My Experiments with SQLServer

Dimitrios Kalemis

I am exactly like Jesus Christ: an atheist and anarchist against society and bad people with influence and power (judges, social workers, politicians, priests, and teachers).

Clint Huijbers' Blog

Senior Certified Microsoft BI Consultant

Blog of Many Useless Wonders

Where Uselessness Abounds!

Stevespevack's Blog

IT Recruitment

SQL Problems and Solutions

Sharing the knowledge & experience of SQL, DBA & BI

Glenn Berry's SQL Server Performance

Semi-random musings about SQL Server performance

Algonquin Studios Blog

Ideas and opinions on software development, web design, customer service, sales and marketing...and food

SutoCom Solutions

Success & Satisfaction with the Cloud

Terli Srinivasa rao Blog

Sql server and Tableau practices

Follow

Get every new post delivered to your Inbox.

Join 48 other followers