Working with SQL collation on Amazon Web Services instances

So when deploying a Amazon Machine Image (AMI) to a new instance in Amazon Web Services EC2 where SQL Server is installed as part of the AMI, you inherit the SQL server collation for that particular install. This is one issue I have experienced where the SQL Server collation of the AMI has been ‘SQL_Latin1_General_CI_AS’ and I wanted to change this to be ‘Latin1_General_CI_AS’.

Now it is possible to achieve this by rebuilding the master database and specifying the SQL collation you wish to use. First of all there is a number of steps that are required to be performed, these being:

  • Make sure you have all the information or scripts needed to re-create your user databases and all the objects in them.
  • Export all your data
  • Drop all the user databases.

Once those steps are completed (if required), you are required to rebuild master database and specify the SQL collation using the setup command. As the install media is not available from the AMI, I had to upload the media to an S3 bucket which I access as a mapped network drive using TntDrive (http://tntdrive.com/download.php).

Setup /QUIET /ACTION=REBUILDDATABASE /INSTANCENAME=InstanceName /SQLSYSADMINACCOUNTS=accounts /[ SAPWD= StrongPassword ] /SQLCOLLATION=CollationName

So for example in my case, I ran the below for the default named instance, where the security group ‘DOMAIN\DBA’ where to be added to the sysadmin fixed server role and the SQL collation was to be ‘Latin1_General_CI_AS’.

Setup /QUIET /ACTION=REBUILDDATABASE /INSTANCENAME=MSSQLSERVER /SQLSYSADMINACCOUNTS=DOMAIN\DBA  /SAPWD= ******** /SQLCOLLATION=Latin1_General_CI_AS

On running the above I was then presented with the following error message in the setup log:

The folder ‘<install media>’ does not contain a valid media info file ‘mediainfo.xml’.
Setup closed with exit code: 0x84C4001.

I then had to change my regional settings on the server from ‘English (United States)’ to ‘English (United Kingdom)’ and the run the above command to set the SQL collation, this time the the rebuild completed succesfully. I then restarted the SQL service and confirmed the collation had changed as required.

In my situation, I had no user databases present so I did not have to perform the additional steps, however depending on the state of the SQL server prior to changing the collation these tasks may be required to be completed:

  • Create all the databases and all the objects in them.
  • Import all your data.

For more information on rebuilding system databases, please refer to http://msdn.microsoft.com/en-us/library/dd207003.aspx.

Advertisements

6 thoughts on “Working with SQL collation on Amazon Web Services instances

  1. Hi Dean!

    This was brilliant, saved our bacon.

    Though we wish we’d noticed before we’d loaded all our data.
    Damn Amazon assuming US rather than real English.

    Alan, Hew, ADQ, Iain

    Like

  2. Dean

    You’re a legend.
    Just had a number of people whom you may remember (….) on a monster skype call whilst we tried to sort this out. Seems to be working so far…

    Hew

    Like

  3. Normally I’d do that with C:\Program Files\Microsoft SQL Server\100\Setup Bootstrap\Release\setup.exe, but I was getting stumped with the following error:
    Error result: -2068643838
    Result facility code: 1203
    Result error code: 2

    If you’re working with SQL Server 2008 R2 on Amazon then use the setup.exe from this folder instead:
    C:\Program Files\Microsoft SQL Server\100\Setup Bootstrap\SQLServer2008R2\

    Like

  4. Dean,

    SQL AWS related but off topic. Do you happen to know if with SQL 2012 AlwaysOn can you cluster across different regions (WEST and EAST)?

    Like

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