Sitecore Databases in Amazon RDS
Posted by Bhavik Patel on 9/1/2016
UPDATE: As of July 2016, Amazon RDS fully supports backup and restoration of MS SQL Server backup files. See https://aws.amazon.com/blogs/aws/amazon-rds-for-sql-server-support-for-native-backuprestore-to-amazon-s3/ for more information about that.
Considering this, getting your Sitecore databases installed in Amazon RDS has never been easier! Follow the steps below:
Add the SQL_SERVER_BACKUP_RESTORE option to your RDS instance. See Amazon’s article above on how to do that.
If you’re starting a brand new Sitecore install, attach the databases to a local environment (e.g. your local machine) and create SQL Server backups of the databases. If you already have backups, you’re ready for the next step.
Upload your backup files to an Amazon S3 bucket (the one that you selected when you completed step 1).
Use the stored procedures specified in Amazon’s article above to restore your databases from the S3 bucket (i.e. specifically, rds_restore_database).
What a welcome new feature of Amazon Web Services!
Old blog post is below, for reference.
The truth is, it isn’t terribly difficult to get your Sitecore databases up and running in RDS. In fact, there are many benefits of using RDS that you don’t get when you use an explicit SQL Server AWS instance. For example, you get multi-availability zone standby instances, automated backups, automatic software patches, and more. If you have a volume licensing agreement with Microsoft, you can even BYOL (bring your own license).
Okay, back to the task at hand… If you need to get your Sitecore databases in Amazon RDS, here’s how you can get it done.
First, you’ll have to restore/attach the Sitecore databases you want to move to RDS somewhere locally. You can do that on a local server, or even better, your local machine.
Using SQL Server Management Studio (SSMS), connect to the local instance where the Sitecore databases were restored. After this, use the “Generate and Publish Scripts” wizard to script each of the Sitecore databases (core, master, web, analytics). Note that scripting the database does not include data, only structure (database objects), so we’ll deal with that in a later step.
Now, use SSMS to connect to the RDS instance in AWS. Use the scripts generated from step 2 to create the databases in RDS (core, master, web, analytics). If you use the generated scripts verbatim, you may encounter some errors because RDS SQL Server permissions are slightly tightened. Just verify that the errors you receive don’t affect any of database objects that are being created for Sitecore (in my case, none did).
Now we’re ready to transfer the data. You can do this through SSMS also, using the “Import and Export Wizard”.
Use “SQL Server Native Client” for both the source and destination, and provide the appropriate connection parameters (let’s start with the core database). After that, choose to “Copy data from one or more tables or views”. When prompted, select all tables, and not views, because the views have already been defined when we created the databases in RDS via script. Also important to note is that before you begin the export process, you will need to ignore all IDENTITY and TIMESTAMP columns in all the tables. Use the “Edit Mappings” dialog to do that (I’ve included some queries at the end of this post that will help you identify those columns and tables). Once all of the above is done, you’re free to complete the wizard and start the export process. If you followed the steps above correctly, it should go through without a hitch.
And that’s it really. Repeat step 4 for all the remaining databases (master, web, analytics), and you should have the Sitecore databases ready to go in Amazon RDS.
Here’s the two queries that will help you identify the IDENTITY and TIMESTAMP columns:
SELECT COLUMN_NAME, TABLE_NAME FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA = 'dbo' AND COLUMNPROPERTY(object_id(TABLE_NAME), COLUMN_NAME, 'IsIdentity') = 1
SELECT COLUMN_NAME, TABLE_NAME FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA = 'dbo' AND DATA_TYPE = 'timestamp'