Amazon RDS (MSSQL Server)

The concept of RDS sounded very attractive and started reviewing its usability as it can take care of so many chores (backup, availability, clustering…). As an icing on the top, Amazon announced Database Migration Service (DMS), a week ago. I could not resist any longer, so took a dive into RDS and I ended up nibbling in its frontiers.

To avoid any security or network issue, I setup two test servers, one in ec2 -Windows 2012 / MS-SQLServer2014 and an RDS instance with the same version of MS SQLserver. I created the test database by restoring from a .bak file to the ec2 SQLserver (as you can’t restore from a backup file directly to RDS) and then wanted to use the DMS. It was pretty basic and the setup was easy to follow. Setup the source DB and target DB connection, tested it, created the replication instance (this is a ec2 server) and the migration task. The task ran successfully, no error logged. However, to my surprise, it didn’t do anything! No matter what I tried, it didn’t do it. I’m still working on getting this up.

In the meantime, I got the data into RDS by the following method (hold your breadth)  – not an easy way especially if you have large amounts of data, that’s where I think DMS will be a killer. DMS offers not only to migrate data but also to replicate data on an ongoing basis. This will let you maintain a parallel world of your production data – how cool is that? (if only it works!:-()

First, script out your views, stored procedures, functions, keys, constraints, indexes etc all in separate scripts. Secondly, use the import / export data and migrate all your current data into your RDS instance (pretty fast – i migrated a database 50 gb under 15 minutes – though I was running a m4.large instance for the source DB and used 10000 IOPS for RDS). Then you use the script you generated to create rest of the objects. This is a painful process, if you still have the habit of using Identity columns, you need to drop and re-create the columns! In addition to being not able to restore a database from a backup, you can’t backup “a specific” database.  The entire instance (sql instance) along with all databases will be backed up. if you are a small shop, you are starting from scratch or you don’t have much data or you want to use it for analytical purposes, RDS will be helpful. However, my sense so far is that RDS is not ready for large installations to be used for production purposes.

I’ll follow up with a write-up after a successful DMS usage.

PS: If you are using an older version of SQLServer (2005 for example), you can use Amazon AWS ec2 / SQLserver 2014 to test your applications without buying the license which is a lot.


Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your 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

%d bloggers like this: