Deans Blog

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.