3 min to read
Restoring MSSQL DBs with Lambda
This is not another post about Databases, AWS and most importantly, how we got to have full restores from live Dbs to dev and UAT envs on RDS in the middle of the night while sleeping on your computer and dreaming about it, just like the picture above!
Prologue
There was a time when Developers from all around the world (the company where I work is a multinational so there are developers from all over the world) used to ask me if we could have the databases from dev and uat being restored from live backups on daily basis.
Let me explain what we are doing before I can tell you how we are doing this post, we are using terraform to deploy the new releases from the websites every day, the deployment itself creates the new Windows Servers, Load Balancer, DNS Records and also provision the sites and deploys the all the artifacts using msdeploy and Powershell, after 5 to 10 minutes we have a fully working site running on AWS, provisioned and deployed by a single click on Bamboo (our CI/CD Server). Pretty awesome, right? It actually gets better, these sites need also a MSSQL Database, which we have already on RDS. That being said, we need to put the live data into this RDS database so the websites can have recent information.
The Problem
The developers had their immutable infrastructure and they were happy with them, the only problem was that they were asking from time to time to refresh the data from the database, which they call “the terraform database ”. So, no big deal, right? I was just asking to the Ops team to provide me a recent .bak which they dumped into S3 and then I was able to just run the following commands on the RDS Database
exec msdb.dbo.rds_restore_database
@restore_db_name='database_name',
@s3_arn_to_restore_from='arn:aws:s3:::bucket_name/file_name_and_extension';Everyone was happy but me, because I just wanted to sleep without anyone asking me stuff, and because they were around 6 different sites and therefore 6 databases, so the actual commands I was running needed to be changed each time and also the database that I needed to connect.
The Solution
Introducing AWS Lambda, with this bad boy I was now able to execute a C# function that connected to the Terraform Database (A regular MSSSQL RDS) and executed the commands to restore the new .bak file dumped into S3 by the scheduled task of the Production MSSQL server automatically! I thought I was dreaming but I realized I was not!
