RDS SQL Server has two new exciting backup and restore enhancements

Amazon Relational Database Service (Amazon RDS) is the primary mechanism for running relational databases in the AWS Cloud. Amazon RDS for SQL Server supports running SQL Server versions from SQL Server 2008 R2 to SQL Server 2017.

The RDS for SQL Server team has recently released two key improvements around backup and restore for native SQL Server backups. These backups, documented in the Amazon RDS user guide, are full database backups of your SQL Server databases as a single .bak file in an S3 bucket. After you have granted the RDS SQL Server permissions to see the S3 bucket, you can back up RDS SQL Server databases to the S3 bucket. You can also restore backups from that S3 bucket to your RDS SQL Server.

Database Backup Uniqueness

A common technique to create user databases with standard schemas or table data is to create the needed schema and data, and then create a SQL Server backup. Then, you restore the backup and rename it repeatedly. This works great for use cases such as hosting multiple customers on a single system. RDS for SQL Server used to detect these as duplicates of the same database and prevent you from restoring databases created this way. With the recent improvements to our native backup and restore system, RDS no longer prevents you from restoring these backups to RDS for SQL Server systems.

Synchronous Native Restore

Another recent improvement involves multi-AZ high availability (HA) deployments of Amazon RDS for SQL Server. When you select the high availability option for your RDS for SQL Server installation, RDS creates a database mirror copy of your database in a second Availability Zone. It can do this for up to 30 databases that are supported in RDS for SQL Server. Previously, when you used a native restore to RDS for SQL Server in an HA configuration, we would restore the database on the primary SQL Server instance. Then we would break and re-establish the mirroring session. This also broke the mirroring sessions for all other databases on the same system, and we would then immediately begin re-mirroring all user databases on that server.

With the new improvements, we simultaneously restore the full backup to both the primary and the mirror partner server, and then sync the two databases up quickly. Additionally, we no longer break and re-mirror the other databases on the same instance of SQL Server. The result is a much-reduced window where the secondary server is not completely in-sync with the primary mirror server. The overall availability of the system is improved. This only works for databases with the FULL recovery model, which we recommend for production deployments of SQL Server.

In these screenshots, you can see that the restore process will break the mirror during the restore, and now immediately show synchronized again after the restore is complete:

Task In-Progress with mirroring state NULL

Task Success with mirroring state Synchronized

With these two improvements, we continue to innovate the service offering and native backup and restore capabilities of RDS for SQL Server.

About the Author

Richard Waymire is a principal database specialist solutions architect at Amazon Web Services. He works with AWS customers to provide guidance and technical assistance on database projects, helping them improve the value of their solutions when using AWS.