Archive for April, 2016

Amazon RDS DMS

April 3, 2016

This is a follow up to my previous write-up. This one is specific to the Database Management Service that Amazon AWS introduced recently. Finally, I’m able to setup the database in RDS and host the webapp with some work around and I’m going to lay it out as to what is involved in doing so. Thanks to the AWS forums and engineers.

The issue I had in migrating data is that my database had all the objects in “dbo” schema and had no other schema. This seems to be a bug and when I posted on the forum, the engineer responded with a workaround. The solution it to use a custom mapping rather than default mapping with the following json
{
“TableMappings”: [
{
“Type”: “Include”,
“SourceSchema”: “dbo”,
“SourceTable”: “%”
}
]
}

I’m giving this for completeness sake. I would advise in not following the wizard approach as things take time  and is not clearly mentioned and results in unnecessary delay and confusion. Instead, create the source and destination endpoints, a replication instance (this is the server /machine that runs the program / task of exporting and importing data by connecting to the source and target end points) and a task. The step of creating a replication instance takes time, please wait and let it do its job.

Once you created all these, start the task. In this process, all tables along with the data are copied from the source to destination along with any primary key or clustered indexes your database tables have. That’s all good news, but that’s all it does. It does not bring in any of the constraints (foreign keys, default values, triggers), views, stored procedures, functions or other non-clustered indexes. You have to script them out separately and apply after the data is migrated. If you have any identity columns you are in for some serious work. You have to create a new identity column, turn identity off, update the data and turn identity on. However, if your tables are not that big, you can goto the design mode and turn the identity property (after enabling that from tools->options).

These  issues could have been easily resolved, if we can restore from .bak file. If Amazon can offer restore using a .bak file from S3, it will be an icing on the cake.

One of the basic rule for all the apps we write for cloud is that it should be independent of the local file system. So, Amazon should have a tool to start the restore process using a .bak file from S3. Once initiated (though the RDS claims no access to file system, a file system of some sort exists as it shows c:\MSSSQL\Data\ when you create databases), it should initiate a copy from S3 to the local file system and then do a restore.

Once we have this as part of DMS or a sub-tool similar to the “MigrationTool” they have currently that helps in migrating data from disparate databases.

PS: If you are done with your data migration, please delete the replication instance otherwise it will continue to cost money.

Amazon RDS (MSSQL Server)

April 3, 2016

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.