AWS RDS Cloud formation template for SQL Server

Question

I'm pretty new to using AWS stuff. I wanted to take a snapshot of the current SQL Server instance and create another instance with the same snapshot (so that all the existing databases and data gets migrated) and more storage capacity using AWS Cloud Formation.

I saw something on Amazon like https://s3-us-west-2.amazonaws.com/cloudformation-templates-us-west-2/RDS_MySQL_With_Read_Replica.template

but couldn't tailor it to my needs. I don't want all that EC2 instance and extra things. Just my existing snapshot ID and the new SQL Server RDS instance details which will be cloned with the Snapshot ID

That CloudFormation template creates a MySQL database. You can change it to MSSQL and remove any sections of the template that you don't need, but, you don't need a CloudFormation template to do this. Just create a snapshot using the console and a new database from it. The steps are in the AWS documentation at docs.aws.amazon.com/AmazonRDS/latest/UserGuide/… — jzonthemtn
Thank you ! I need a CloudFormation template, otherwise Its not possible as i wanted my new DB storage size to be more than what the snapshot Db storage size is. — hakuna

shenku shenku · Accepted Answer · 2017-03-09T03:45:18

Take a snapshot of your existing database, either manually, using the CLI or PowerShell, taking note of the DBSnapshotIdentifier.

Using PowerShell it looks like this:

New-RDSDBSnapshot -DBSnapshotIdentifier "NameOfYourNewSnapshot" -DBInstanceIdentifier "YourExistingDbIdentifier"

Okay, now you have a snapshot, you need to change your CloudFormation template to use the DBSnapshotIdentifier.

Change your existing template to create a SqlServer database and specify a new property, DBSnapshotIdentifier:

"MyDB" : {
 "Type" : "AWS::RDS::DBInstance",
 "Properties" : {
     "DBSecurityGroups" : [
        {"Ref" : "MyDbSecurityByEC2SecurityGroup"}, {"Ref" : "MyDbSecurityByCIDRIPGroup"} ],
     "AllocatedStorage" : "20",
     "DBInstanceClass" : "db.t2.micro",
     "Engine" : "sqlserver-ex",
     "MasterUsername" : "MyName",
     "MasterUserPassword" : "MyPassword",
     "DBSnapshotIdentifier" : "NameOfYourNewSnapshot"
 }
}

That should be it, when you run your stack it will drop and re-create your database from your snapshot, so be sure to cater for the down time.

Docs:

http://docs.aws.amazon.com/powershell/latest/reference/Index.html
http://docs.aws.amazon.com/AWSCloudFormation/latest/UserGuide/aws-properties-rds-database-instance.html
http://docs.aws.amazon.com/AWSCloudFormation/latest/UserGuide/aws-properties-rds-database-instance.html#cfn-rds-dbinstance-dbsnapshotidentifier

AWS RDS Cloud formation template for SQL Server

1 Answers