Running SQL Server Databases in the Amazon Cloud - RDS Backup and Restore (Part 3)

By: Sadequl Hussain   |   Updated: 2014-09-11   |   Comments (2)   |   Related: 1 | 2 | 3 | 4 | > Amazon AWS RDS

Problem

In the last installment of this series, we discussed some challenges associated with running a SQL Server instance in Amazon Web Service (AWS) RDS. In this tip, we will talk about how RDS SQL Server databases can be backed up and restored.

Solution

Amazon Web Service (AWS) Relational Database Service (RDS) offers two different ways to backup database instances. The first one is when you enable automated backup and the second one is when you take manual snapshots. As with any other AWS service, these methods are accessible via programmatic interfaces like command line tools and various programming language SDKs or via the AWS Management Console. In this tip we will discuss how to back up and restore using the console.

Automated SQL Server Backups on Amazon RDS

With RDS, DBAs don't have to worry about database backups and backup jobs. This can be set up only once during instance creation time or later and RDS will make sure all databases are backed up on a regular schedule. As we saw in a previous tip, DBAs can't take manual backups of individual databases; nor can they exclude databases from a backup plan. However, this also frees up the DBA, because:

• You don't have to manually configure jobs and maintenance plans for backups. You just specify whether the instance needs to be backed up and if so, at what time and how long the backups need to be available. 
• In traditional on-premises setup you have to make sure backup jobs are updated every time a new database is added to the system. When you are managing hundreds of instances across a production fleet, this can be a pain. You can always opt for "backup all user databases" in your maintenance plans, but what happens if some of your databases are offline or corrupted? The backup jobs will fail and you need to monitor them. With RDS, you can worry less.
• As a DBA, you also need to ensure there's enough disk space in your server's backup volume - whether it's across the network or in the local server or in a third-party appliance. This in turn will affect the retention period of the backups. Some servers may be able to keep seven days' worth of backups online while others may be forced to delete after one day. With RDS, that's not the case.  Your backups can be persisted up to thirty-five days and you don't have to worry about the disk space.

With RDS automated backups, only two options need to be specified. In the following image, I am configuring backup for a new instance:

The Backup Retention Period decides how long you want RDS to keep the automated backups. At the time of this writing (July 2014), this can go back to thirty five days.

You can either configure the time of the backup manually or let AWS decide it for you. The timing in RDS is specified in Coordinated Universal Time (UTC), so you may need to do a bit of calculation to convert the timescale of your choice to UTC.  In the image above, my database instance is in ap-southeast-2 (Sydney) region and I prefer it to be backed up at 2:00 AM AEST (Australian Eastern Standard Time). In UTC, that's 4:00 PM the previous day (16:00 hours). With manual configuration, you can also specify the backup duration. This can be anywhere between thirty minutes to three hours.

If you don't specify the backup window, RDS will choose an arbitrary thirty-minute window from an eight-hour block assigned to the RDS region. Every AWS region has a default eight-hour block of time for RDS and RDS can initiate backup any time during that block. The following list shows these blocks of time for different regions.

If you don't want to back up your databases, choose a retention period of zero days. RDS will warn you if you choose so, but will allow you to go ahead and create the instance.

With automated backups, RDS will perform a full backup of the databases once every day during the backup window. It will also perform transaction log backups of all databases. This allows the instance to be restored to any point in time. So, what happens if you change a database's recovery model to simple? Well, RDS will revert it back to full within five minutes of making this change. If you disabled automated backups, RDS will set the recovery models of all databases to simple. Again, if you choose to change them back to full, the changes will be reverted within five minutes.

If you want to stop the backup of an existing instance, you can modify the instance's property and choose a retention period of 0 days. When that happens, RDS will warn you, but let you save your configuration. RDS will then delete every automated backup of the instance. The same thing happens when you delete the instance: RDS will get rid of all previous backups. You have to modify an instance's property to enable backups again.

Restoring SQL Server Backups on Amazon RDS

Restoring a backup allows you to restore the whole instance to a point in time, typically within the last five minutes of current time. The following screenshots show how I am restoring an RDS instance.

I have used a custom restore time here. I could specify the time up to which RDS should apply its database transaction logs.

One thing to notice here is that the restore actually creates a new database instance. You can't overwrite an existing RDS instance with a restore. Also, most options can be changed here: you can change the VPC where the instance would be created, modify its size and so on, but you can't modify the master user name or password.

Once the instance has been restored, it's accessible from the RDS console. Note that it has a new endpoint and it also inherits the source server's backup properties.

Amazon RDS Snapshots

RDS snapshots are what the name implies: they are "snapshots" or point-in-time replicas of your database instance. You can create snapshots of RDS instances as many times as you want, whenever you want, and they will be saved persistently in durable storage in AWS. You can restore from a snapshot any time and RDS will create a fully functional instance from it. However, you need to be aware of a few things:

• RDS snapshots are not accessible directly: in other words, you can't connect Management Studio or other query tools to a snapshot. They have to be restored first.
• An RDS snapshot represents a static state of the database instance at a particular point in time. This means you can't use it in the same way you would restore other database backups. You can't roll forward or backward in time when you restore: there is no option of applying transaction logs.
• Automated backups happen on a regular schedule "automatically", you don't have to do anything. Snapshots on the other hand have to be initiated by the user manually. However, you can automate this by running a scheduled job from an EC2 instance. The jobs in the instance can be calling scripts that would invoke the snapshot process.
• Snapshots will remain even after you terminate or delete the RDS instance. This means you can go back to your final snapshot any time in future and restore from there. That's not the case with automated backups. When you delete an  RDS instance, all backups are deleted with it.
• When you restore a snapshot, RDS won't overwrite any existing instance. It will create a completely separate instance with a different endpoint (unless you have deleted the original instance). So if you need only certain databases to be restored, you need to restore the whole snapshot first and then use a manual process to copy the data across. Also, if you want the restored snapshot to start serving live database traffic, the application connection strings might need to change for the new endpoint.

In the image below, I am taking a snapshot of the database instance:

I can provide a name for the snapshot before it's generated:

Once the snapshot has been created, it's found under the "Snapshots" section of the navigation menu:

The "Copy Snapshot" option allows you to copy the snapshot to a different region. That's useful if you are building a multi-region application in AWS and you want to start from an existing "baseline". In the following image, I am copying the RDS snapshot located in the Oregon Region (us-west-2) to Singapore (ap-southeast-1)

Think before you copy snapshots though. Copying the snapshot of a 1 terabyte database instance over the Internet can be an expensive affair both in terms of time and money. You can think about using Amazon CloudFormation for such operations. We will talk about CloudFormation in a later tip.

Restoring Amazon RDS Snapshots

Restoring from an RDS snapshot is fairly simple as well:

Although you can choose to modify a number of options here, the main thing to watch is the DB Instance Identifier field. You can't restore on top of an existing instance. If you try to do so, you will get an error message like this:

DB Instance already exists (Service: AmazonRDS; Status Code: 400; Error Code: DBInstanceAlreadyExists; Request ID: bc4bfe6c-05d4-11e4-bb0b-cfef0cdb5bae)

If the instance doesn't exist (deleted previously), you can safely use its name.

Amazon RDS Use Cases

I can think of at least two scenarios where snapshots would be useful:

• You are applying a database code update and want some sort of rollback mechanism. Just like you would run a manual backup of the on-premises database server before making the change, you would want to create a snapshot of your RDS instance before applying the patch.
• The RDS instance has served the application's life and it's time to decommission it. Just before deleting the instance, RDS gives you a final chance to create a snapshot:

SQL Server Backup Costs on Amazon RDS

Like most other things in the cloud, RDS backup also comes with a price. Backup storage is a billable item and how you are charged will depend on how long you keep your backups and how often you create snapshots. Amazon provides free backup space of up to 100% of the instance's provisioned storage. Beyond that, charges will start to accrue. What this means is that if you have rolled out a 500 GB-month database instance, Amazon will give you a 500 GB-month backup space free of charge. That space can be used for automated backups as well database snapshots.

This doesn't apply to "idle" instances though: an RDS instance that gets backed up regularly, but has no user or app connecting to it will cost you money. Think about a staging system. You could be backing up a staging system before it goes live. Post roll-out the instance could be running idle with no one connecting to it - everything would have been migrated to the production system by then - and automated backups of the system would be unnecessarily incurring charges.

The main thing to be mindful here is the retention period. It's easy to get carried away and go for the maximum, but ask yourself, do you really need that many backups? Also, delete snapshots once their purpose is over.

We will talk about how to monitor RDS instance usage in a later tip.

Conclusion

In this tip we have tried to look at two different ways of backing up SQL Server RDS instances and restoring them. As with any backup plan, you still need to perform periodic test restores and run integrity checks against the databases. You will also need to formulate a retention policy for your backups. The retention period should create a balance between the company's RPO and the projected budget for RDS service. DBAs need to be mindful about creating a final snapshot before instances are decommissioned.

Next Steps

• Make an audit of your company's production database instances in RDS. Are they being backed up?
• What's the backup retention period for production database instances? Are they reflecting corporate policy?
• Check the development, test or QA systems in RDS. Are they being backed up? If so, ask the team: do we really need to back up non-production systems?
• Try to perform regular test restores from backups and snapshots. Check database integrity in the newly created instance.
• Check the dates of existing database snapshots. When were they taken? Ask the team: are these snapshots still needed?
• Check out these related tips:
  • Running SQL Server Databases in the Amazon Cloud (Part 1)
  • Running SQL Server Databases in the Amazon Cloud - RDS Limitations (Part 2)

About the author

Sadequl Hussain has been working with SQL Server since version 6.5 and his life as a DBA has seen him managing mission critical systems.

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips