#0258 – SQL Server – Installation – Choose the server collation carefully!


Recently, one of the project teams at the office had a customer-specified requirement to use a SQL Server running a server collation other than the English default (SQL_Latin1_General_CP1_CI_AS).

Please NOTE: Collation is a vast topic. The scope of this post is only present the means to choose the server collation at the time of installation or afterwards.

We have two major variants of the English language supported by Windows:

  • “English (United Kingdom)”, Windows LCID = 2057 (Hex: 0x809)
  • “English (United States)” , Windows LCID = 1033 (Hex: 0x409)

Each Windows LCID maps to a SQL Server side’s SQL LCID. Per the article “Collation Settings in Setup”, both 2057 and 1033 map to a single collation – 1033 (0x409), which is SQL_Latin1_General_CP1_CI_AS.

Unfortunately, the IT team who setup the servers for them had used the default values during the installation. When the issue was realized, they tried to change the server collation by changing the collation of the master database (because the collation of the SQL Server instance is that collation of the master database), which returned the following error:

Cannot alter the database ‘master’ because it is a system database.

image

Workaround (Reactive)

When their attempts failed, they called me and my instant reason was that because this is a customer-specified requirement, they would have to do the following:

  1. Backup and drop all user databases
  2. Rebuild the master database by specifying the new collation name in the SQLCOLLATION property of the SQL Server setup (command-line) as shown in the Books On Line here
  3. Re-create all user databases

Ideal solution (Proactive)

The ideal solution here would be to setup the SQL Server such that it uses the required collation during installation itself. The Server Configuration page (step #12) in the SQL Server setup allows the administrator to specify the collation that SQL Server would use:

image

image

By default, the SQL Server setup chooses a collation that matches to the operating system settings. However, the administrator can use the “Customize” button to choose a collation of their choice.

References/Further Reading:

Until we meet next time,

Be courteous. Drive responsibly.

Advertisements

Let me know what you think about this post by leaving your feedback here!

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