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.
6 thoughts on “Working with SQL collation on Amazon Web Services instances”
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
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…
Hope you guys are all well, glad it helped. Luckily, we hadn’t any user databases before we noticed the issue!
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\
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)?
You will documentation to support WSFC and SQL Server Availably Groups across different subnets and availability zones at https://s3.amazonaws.com/quickstart-reference/microsoft/sql/latest/doc/Microsoft_WSFC_and_SQL_AlwaysOn_Quick_Start.pdf.