Automating restoration of production databases into test using AWS RDS

Jeremy Gunzburg — Tue, 08 May 2018 02:49:29 +0000

I have recently been working on some automation to regularly restore all of our live databases from AWS RDS into copies for use as a test environment. This includes some data scrubbing to remove sensitive information, real email addresses, etc, but allows testing against a production- like environment.

We have MS-SQL and Aurora MySql databases, and I have made use of the AWS APIs for this task. In my case I used the .Net APIs and built a console app to call them. While the APIs are well documented and quite easy to use, I have still found getting the right combination of calls has taken some time.

Firstly, the API call to restore a DB from a snapshot will, quite rightly, refuse to override an existing instance. However in this case we want to override the existing environment each time we restore. So I had to build that into my app.

First, call DescribeDBInstances with the name of the target instance. If an instance is found, then call DeleteDBInstance to delete it. Be aware that this call throws a DBInstanceNotFoundException if the instance does not exist, so you need to catch this and move on, as this is a perfectly valid scenario.

One thing to note with all of these calls if that the API will trigger a task in the cloud, but not wait for it to complete. So your DB instance will exist with status "Deleting" for a while. I did not find a way to subscribe to events which will tell you when this has completed, so I implemented some simple polling, calling DescribeDBInstances every 2 minutes until the instance no longer exists.

Once you are sure the target instance does not exist, you can call RestoreDBInstanceFromDBSnapshot to do the restore. This assumes you have a snapshot available, and the best bet is to have regular snapshots generated from the live DB automatically. You'll need to find the latest snapshot for the source DB, using DescribeDBSnapshots. Return all available by DB instance name, and then sort descending by date to find the latest.

Once again, you'll need to wait for the restore to complete, so again, I did some polling using DescribeDBInstances to wait until the target instance existed, with a status of "available".

Once you have a newly restored database, you may need to apply security groups, and you will probably want to change the master user password, so test and live don't have the same credentials. This can all be achieved using ModifyDBInstance. One gotcha I found is to ensure that "ApplyImmediately" is set to true, if you need to subsequently connect to the DB to complete further tasks. Otherwise AWS will wait for a maintenance window to apply the changes, and you don't want that.

Next, you can apply some data scrubbing or whatever you want to do to turn your prod data into test ready data. For this, you can simply connect to the database using whatever method you prefer, and run your scripts.

Now, this is all well and good, but what if you have databases in multiple regions? What if you have a UK database, which you want to replicate as a test environment, but all your testing happens in Australia, so you'd rather your test DB sits in Aus for better latency . Well, you can't restore a Snapshot across regions, but you can copy snapshots across regions. Simply call the CopyDBSnapshot command, connecting to the target region, passing the "DBSnapshotArn" of the snapshot in the source region that you wish to copy.
Then, once it is copied, (Check status using DescribeDBSnapshots in the target region), you can proceed as above.

So that covers single instances in MS-SQL, but what about Aurora DB clusters? It's a bit more complicated and a bit less intuitive.

First, call RestoreDBClusterFromSnapshot. The main gotcha here is that you need to specify the engine, even though the documentation implies you can leave it out. In my case, Engine = "aurora-mysql".

As above, you can poll for availability (call DescribeDBClusters) then call ModifyDBCluster to apply security groups and password changes.

Here is where it's a bit unintuitive. If you were to do this via the web dashboard, the cluster and an instance within would be restored, however the API does not do the second step. So you end up with an empty cluster, which seems useless, but the cluster contains all the info you need to spin up instances within it.
So you now need to call CreateDbInstance.

Pass in the following parameters:
DBClusterIdentifier = (target cluster),
DBInstanceIdentifier = (target instance name),
Engine = "aurora-mysql",

PubliclyAccessible = true (Note this defaults to false, even if the original you restored from was public. This caught me out and I wasted some time wondering why I couldn't connect).

So there you go. Everything you need to know to set up a restore of production AWS databases into a test environment. Simply schedule your app to run using whichever build server or scheduling tool you like, and away you go. You can restore a new environment, every night, at the start of a sprint, or whenever you wish.

Exporting scripts from SQL Server for use with DBUp

Jeremy Gunzburg — Wed, 18 Apr 2018 00:21:50 +0000

Recently I have started a new job, and one of my first tasks is to help improve the database development and deployment process. They are moving away from having all changes managed in the dev database, and pushing changes using SQL Compare. Thankfully they have already started this process, with database delta scripts being written and deployed using DbUp. This can work quite well and is a technique I have used for managing and deploying databases previously.

The next step to improving the process is having all stored procedures, functions and views scripted out of the database and living as part of the codebase. Any changes made should be made to the script, which now has a history in source control, and all of these scripts should be applied to the database with each deployment.

SQL Server Management Studio does provide tools to assist with this, but I found it took me a while to get all the right settings to export things the way I wanted, in a way that would allow DbUp to repeatedly apply these changes without error.

Right click on the database, select tasks, Generate scripts
Select which ones you want (procs, functions, views, etc)
Save to file, single file per object, and specify the target folder. I have a separate folder for stored procs, views, and functions, and I export each separately.

Ensure ANSI text is selected so git can diff the files easily. This is what tripped me up the first time.

Click Advanced.
Set Script USE DATABASE = false

For stored procs:
Check for object existence = true
Script CREATE

This creates CREATE + ALTER scripts for all procs. (You don't need to script drop). This has the advantage of not dropping existing procs, in case you have custom permissions created on the procs.

For views and functions, we want to do something similar, but scripting CREATE with checking for existence generates the create as a string, which is ugly, harder to maintain, and only creates it once, rather than altering the view / function once it is created. I believe this is due to some of the intricacies of how views and functions are handled by SQL Server, whereas you can easily create a dummy stored proc and immediately alter it.
Here is an example:

This is obviously not that useful. But we can't always drop and create, as this won't work if the view / function does not exist (initial run), and we can't always create, as this is not re-runnable.
SO: Do 2 generates. One for only the DROP, checking for existence.
Second for the CREATE, not checking for existence. Untick overwrite, and in advanced, set Append to file = true.

This gives you a nice set of files, one per view or function, that includes a conditional DROP if exists, and then a create, so is re-runnable.

For a good overview of how to integrate these newly exported scripts with your projects and have DbUp run them on every execution, go here: http://wengier.com/reviewable-sprocs/

DEV Community: Jeremy Gunzburg

Automating restoration of production databases into test using AWS RDS

Exporting scripts from SQL Server for use with DBUp