Migrate to Amazon SQL Server RDS

Question

I have been attempting to move from a regular SQL Server on a Win2008 Server to the SQL Server on Amazon AWS RDS.

I thought an simple backup and restore would work. Though AWS RDS doesn't seem to have access to a file system so the sql scripts all seem to need a local file system on the source and destination server. I attempted a script following

exec sp_addlinkedserver @server='test.xxxx.us-east-1.rds.amazonaws.com'

-- Verify that the servers were linked (lists linked servers)
exec sp_linkedservers

EXEC ('RESTORE DATABASE [orchard] FROM DISK = ''C:\Temp\orchard.bak'' WITH FILE = 1,  NOUNLOAD, STATS = 10')
AT [test.xxxx.us-east-1.rds.amazonaws.com]

Any Suggestions would be helpful.

Answer 1

download the free 'SQL Azure Migration Wizard' from CodePlex -- I did a short blog/screencast about this. Be sure to set the 'TO' setting in the wizard to the AWS DNS name and then use 'SQL Server 2008' and not 'SQL Azure'

Answer 2

The official word I got for AWS support on migration of SQL databases using .bak files is that it is not supported. So no more quick restore from .bak files. They offered the official help for migration of existing databases here:

Official AWS database migration guide

And the also gave me an unofficial wink at the Azure database migration tool. Just use it to generate a script of your schema and or data and execute it against your RDS instance. Its a good tool. You will have to import the .bak into a non-RDS SQL server first to do this.

SQL Azure migration tool

Answer 3

You will probably find that the Data-tier Applications BACPAC format will provide you with the most convenient solution. You can use Export to produce a file that contains both the database schema and data. Import will create a new database that is populated with data based on that file.

In contrast to the Backup and Restore operations, Export and Import do not require access to the database server's file system.

You can work with BACPAC files using SQL Server Management Studio or via the API in .Net, Powershell, MSBuild etc.

Note that there are issues using this method to Export and then Import from and to Amazon RDS. As a new database is created on RDS, the following two objects are created within it.

• A User with membership in the db_owner role.
• The rds_deny_backups_trigger Trigger

During the import, there will be a conflict between the objects included in the BACPAC file and the ones that are added automatically by RDS. These objects are both present in the BACPAC file and automatically created by RDS as the new database is created.

If you have a non-RDS instance of SQL Server handy, then you can Import the BACPAC to that instance, drop the objects above and then export the database to create a new BACPAC file. This one will not have any conflicts when you restore it to an RDS instance.

Otherwise, it is possible to work around this issue using the following steps.

1. Edit the model.xml file within the BACPAC file (BACPACs are just zip files).
2. Remove elements with the following values in their Type attributes that are related to the objects listed above (those that are automatically added by RDS).
   • SqlRoleMembership
   • SqlPermissionStatement
   • SqlLogin
   • SqlUser
   • SqlDatabaseDdlTrigger
3. Generate a checksum for the modified version of the model.xml file using one of the ComputeHash methods on the SHA256 class.
4. Use the BitConverter.ToString() method to convert the hash to a hexadecimal string (you will need to remove the separators).
5. Replace the existing hash in the Checksum element in the origin.xml file (also contained within the BACPAC file) with the new one.
6. Create a new BACPAC file by zipping the contents of the original with both the model.xml and origin.xml files replaced with the new versions. Do NOT use System.IO.Compression.ZipFile for this purpose as there seems to be some conflict with the zip file that is produced - the data is not included in the import. I used 7Zip without any problems.
7. Import the new BACPAC file and you should not have any conflicts with the objects that are automatically generated by RDS.

Note: There is another, related problem with importing a BacPac to RDS using SQL Server Management Studio which I explain here.

Answer 4

I wrote up some step-by-step instructions on how to restore a .bak file to RDS using the SQL Azure Migration Tool based on Lynn's screencast. This is a much simpler method than the official instructions, and it worked well for several databases I migrated.

Answer 5

Use the export wizard in sql server management studio on your source database. Right click on the database > tasks > export data. There is a wizard that walks you through sending the whole database to a remote sql server.

Answer 6

There is a tool designed by AWS that will answer most, if not all, of your compatibility questions - the Schema Conversion Tool for SQL Server: https://docs.aws.amazon.com/SchemaConversionTool/latest/userguide/CHAP_Source.SQLServer.html

Because not all sql server database objects are supported by RDS, and even varies across sql server versions, the Assessment report will be well worth your time as well: https://docs.aws.amazon.com/SchemaConversionTool/latest/userguide/CHAP_AssessmentReport.html

Lastly, definitely leverage Database Migration Service: https://aws.amazon.com/dms/

Answer 7

The following article discussing how to Copy Database With Data – Generate T-SQL For Inserting Data From One Table to Another Table is what I needed.

http://blog.sqlauthority.com/2009/07/29/sql-server-2008-copy-database-with-data-generate-t-sql-for-inserting-data-from-one-table-to-another-table/