Achieve point-in-time recovery for all databases in Amazon RDS Custom for SQL Server

Amazon Relational Database Service (Amazon RDS) Custom for SQL Server is a managed database service that automates set up, operation, backups, high availability, and scaling of databases while granting administrator access to the database and underlying operating system (OS). The database administrator can use this access to enable native features such as SQL Common Language Runtime (CLR), configure OS settings, and install drivers to migrate legacy, custom, and packaged applications. RDS Custom for SQL Server has two licensing models: License Included and Bring Your Own Media (BYOM). With BYOM you bring your SQL Server licenses in accordance to Microsoft terms.

This post shows how to achieve point-in-time recovery for all databases hosted in RDS Custom for SQL Server.

Amazon RDS Custom for SQL Server allows up to 5,000 databases per instance. However, the number of databases that can be restored to a specific point in time using point-in-time recovery (PITR) depends on the instance class type (these numbers were set to maintain optimal performance). See Restoring an RDS Custom for SQL Server instance to a point in time for more information.

Instance class type	Maximum number of PITR eligible databases
db.*.large	100
db..xlarge to db..2xlarge	150
db..4xlarge to db..8xlarge	300
db..12xlarge to db..16xlarge	600
db..24xlarge, db..32xlarge	1000

SQL Server system databases (master, msdb, and model) don’t count against the number of databases that can be restored to a specific point in time (PITR).

Note: When restoring a database instance to a specific point in time, the databases that aren’t part of the RDS Custom managed PITR databases will be restored to the point of the last automated snapshot.

In this post, we show how to use native backup and restore commands to achieve PITR for databases that aren’t eligible because of the instance type limitation. We present two solutions: one applicable to all versions of RDS Custom for SQL Server and the other for RDS Custom for SQL Server version 2022.

Solution overview

The following diagram outlines a high-level architecture of two solutions: one using Amazon FSx for Windows File Server and another using Amazon Simple Storage Service (Amazon S3). In the following example, we’re using an RDS Custom SQL Server version 2022 instance type db.m5.xlarge with 200 user databases (for example: db001 – db200). This instance type allows up to 150 databases for RDS Custom PITR.

The high-level workflow is:

Create and populate the table dbo.rds_pitr_blocked_databases. This table is used to explicitly exclude a database from RDS Custom PITR.
Backup and restore databases excluded from managed PITR:
1. To and from the Amazon FSx file share.
2. To and from Amazon S3 (works with SQL Server 2022 or later).

Prerequisites

We assume that you have the following prerequisites:

Background knowledge of SQL Server backup and restore.
Familiarity with setting up, launching, and connecting to RDS for Custom SQL Server instances.
Amazon FSx for Windows File Server and a share have been created. See Creating Your File System.
An existing Microsoft Active Directory. RDS Custom SQL Server works in either an AWS managed directory or self-managed directory. Connectivity between your Active Directory and Amazon Virtual Private Cloud (Amazon VPC) should also be established. In this solution, RDS Custom is using AWS Managed Microsoft AD and the Amazon FSx for Windows file server share is attached to the AWS managed directory (used for Solution 1).
An S3 bucket configured to store the necessary database backup files. See Creating a bucket (used for Solution 2).
Background knowledge of AWS services such as Amazon Elastic Compute Cloud (Amazon EC2), the AWS Command Line Interface (AWS CLI).

Because this solution involves setting up and using AWS resources, it will incur costs in your account. See AWS Pricing for more information. We strongly recommend that you set this up in a non-production instance and run end-to-end validations before you implement this solution in a production environment.

Create and populate msdb.dbo.rds_pitr_blocked_databases

The first step is to define which databases you don’t want self-managed for PITR. Because the instance type is db.m5.xlarge, it will manage up to 150 databases for PITR. In our example, we have databases db001 to db200 and want to exclude databases db150–db200. To create and populate a table to exclude those databases:

Create the table rds_pitr_blocked_databases in the msdb database.

CREATE TABLE msdb.dbo.rds_pitr_blocked_databases (
database_id INT NOT NULL,
database_name SYSNAME NOT NULL,
db_entry_updated_date datetime NOT NULL DEFAULT GETDATE(),
db_entry_updated_by SYSNAME NOT NULL DEFAULT CURRENT_USER,
PRIMARY KEY (database_id)
);

Populate the table with the databases to be excluded from self-managed PITR. The following code excludes databases db150–db200. They’re being excluded because they’re outside the number of databases supported by PITR in RDS Custom when using the instance type db.m5.xlarge. See the Number of databases eligible for PITR per instance class type.
```
INSERT INTO msdb.dbo.rds_pitr_blocked_databases (database_id, database_name)
VALUES (DB_ID('db150'), 'db150'),
(DB_ID('db151'), 'db151'),
...
(DB_ID('db199'), 'db199'),
(DB_ID('db200'), 'db200');
```
To achieve PITR for the databases not eligible for RDS Custom managed PITR (the databases specified in msdb.dbo.rds_pitr_blocked_databases) you must manage their full, differential, and transaction log backups.

Note: Manually backing up more databases can have an impact on the system depending on its load. Test thoroughly before implementing in production.

Solution 1 – Backup and restore databases excluded from managed PITR to and from an Amazon FSx file share

In this solution backups are performed to an Amazon FSx share. For RDS Custom for SQL Server to be able to access an Amazon FSx file share, an RDS Custom EC2 instance must be connected to the Active Directory that you joined your Amazon FSx file system to. To attach to the Active Directory, see Attach RDS Custom for SQL Server to a domain in Migrate a multi-TB SQL Server database to Amazon RDS Custom for SQL Server using Amazon FSx for Windows File Server.

After your RDS Custom for SQL Server has access to an Amazon FSx share, create full, differential, and transaction log backup jobs for the non-self-managed PITR databases. The following are examples of backup statements to an Amazon FSx share.

--Full database backup to FSx share
BACKUP DATABASE db150
TO DISK = '\\<FSx DNS name>\share\Backup\db150\db150_20240215_000000_FULL.bak';

--Transaction log backup to FSx share
BACKUP LOG db150
TO DISK = '\\<FSx DNS name>\share\Backup\db150\db150_20240215_000015_LOG.trn';

--------
--<create a job that takes periodic transaction log backups and meets your recovery point objective to FSx share. In this example we backup every 15 minutes>
--------

--Transaction log backup to FSx share
BACKUP LOG db150
TO DISK = '\\<FSx DNS name>\share\Backup\db150\db150_20240215_074500_LOG.trn';

--Differential database backup to FSx share
BACKUP DATABASE db150
TO DISK = '\\<FSx DNS name>\share\Backup\db150\db150_20240215_080000_DIF.diff'
WITH DIFFERENTIAL;

--Transaction log backup to FSx share
BACKUP LOG db150
TO DISK = '\\<FSx DNS name>\share\Backup\db150\db150_20240215_081500_LOG.trn';

You can restore those databases to any point in time using native restore commands. The restore can be done on the same instance or on any instance that is connected to the Amazon FSx file share and has access to it. The following is an example of restoring a database from an FSx file share to the same instance (Note: database files must be restored to the D:\rdsdbdata directory):

--Full database backup restore from FSx share
----Database files must be restored to D:\rdsdbdata\Data
RESTORE DATABASE db150_Restored
FROM DISK = '\\<FSx DNS name>\share\Backup\db150\db150_20240215_000000_FULL.bak'
WITH MOVE 'db150'   TO 'D:\rdsdbdata\DATA\db150_Restored.mdf', --Database files restored to D:\rdsdbdata\Data
     MOVE 'db150_log' TO 'D:\rdsdbdata\DATA\db150_Restored_log.ldf',
     NORECOVERY
GO

--Differential database backup restore from FSx share
RESTORE DATABASE db150_Restored
FROM DISK = '\\<FSx DNS name>\share\Backup\db150\db150_20240215_080000_DIF.diff'
WITH NORECOVERY
GO

--Transaction log backup restore from FSx share
RESTORE LOG db150_Restored
FROM DISK = '\\<FSx DNS name>\share\Backup\db150\db150_20240215_081500_LOG.trn'
WITH NORECOVERY
GO

--Recover database
RESTORE DATABASE db150_Restored
WITH RECOVERY
GO

Solution 2 – Backup and restore databases excluded from managed PITR to and from Amazon S3

This solution is only applicable to RDS Custom for SQL Server hosting a database instance version 2022 or later. In this solution backups are performed to an S3 bucket.

For RDS Custom for SQL Server to have S3 bucket access, follow the steps in Native backup and restore with Amazon S3 integration on Amazon RDS Custom for SQL Server 2022.

After your RDS Custom for SQL Server and S3 are integrated, create full, differential and transaction log backup jobs for the non-self-managed PITR databases. The following are examples of backup statements to an S3 bucket.

--Create Credential
CREATE CREDENTIAL [s3://<S3 bucket name>.s3.<region>.amazonaws.com/<your folder>]
WITH IDENTITY = 'S3 Access Key',
SECRET = '<Access-Key-ID>:<Secret-Key-ID>';

--Full database backup to S3 bucket
BACKUP DATABASE db150
TO URL = 's3://<S3 bucket name>.s3.<region>.amazonaws.com/<your folder>/db150/db150_20240215_000000_FULL.bak' WITH FORMAT, MAXTRANSFERSIZE = 20971520, COMPRESSION;

--Transaction log backup to S3 bucket
BACKUP LOG db150
TO URL = 's3://<S3 bucket name>.s3.<region>.amazonaws.com/<your folder>/db150/db150_20240215_000015_LOG.trn'
WITH FORMAT, MAXTRANSFERSIZE = 20971520, COMPRESSION;

--------
--<create a job that takes periodic transaction log backups and meets your recovery point objective to S3 bucket. In this example we backup every 15 minutes>
--------

--Transaction log backup to S3 bucket
BACKUP LOG db150
TO URL = 's3://<S3 bucket name>.s3.<region>.amazonaws.com/<your folder>/db150/db150_20240215_074500_LOG.trn'
WITH FORMAT, MAXTRANSFERSIZE = 20971520, COMPRESSION;

--Differential database backup to S3 bucket
BACKUP DATABASE db150
TO URL = 's3://<S3 bucket name>.s3.<region>.amazonaws.com/<your folder>/db150/db150_20240215_080000_DIF.diff'
WITH DIFFERENTIAL, FORMAT, MAXTRANSFERSIZE = 20971520, COMPRESSION;

--Transaction log backup to S3 bucket
BACKUP LOG db150
TO URL = 's3://<S3 bucket name>.s3.<region>.amazonaws.com/<your folder>/db150/db150_20240215_081500_LOG.trn'
WITH FORMAT, MAXTRANSFERSIZE = 20971520, COMPRESSION;

You can restore those databases to any point in time using native restore commands. The restore can be done on the same instance or on any instance that has access to the S3 bucket. The following is an example of restoring a database from an S3 bucket to the same instance.

Note: database files must be restored to the D:\rdsdbdata directory.

--Full database backup restore from S3 bucket
----Database files must be restored to D:\rdsdbdata\Data
RESTORE DATABASE db150_Restored
FROM URL = 's3://<S3 bucket name>.s3.<region>.amazonaws.com/<your folder>/db150/db150_20240215_000000_FULL.bak'
WITH MOVE 'db150'   TO 'D:\rdsdbdata\DATA\db150_Restored.mdf', --Database files restored to D:\rdsdbdata\Data
     MOVE 'db150_log' TO 'D:\rdsdbdata\DATA\db150_Restored_log.ldf',
     NORECOVERY
GO

--Differential database backup restore from S3 bucket
RESTORE DATABASE db150_Restored
FROM URL = 's3://<S3 bucket name>.s3.<region>.amazonaws.com/<your folder>/db150/db150_20240215_080000_DIF.diff'
WITH NORECOVERY
GO

--Transaction log backup restore from S3 bucket
RESTORE LOG db150_Restored
FROM URL = 's3://<S3 bucket name>.s3.<region>.amazonaws.com/<your folder>/db150/db150_20240215_081500_LOG.trn'
WITH NORECOVERY
GO

--Recover database
RESTORE DATABASE db150_Restored
WITH RECOVERY
GO

Steps to restore all databases to a point in time

To restore all your databases (non-managed and self-managed PITR database) to a specific point in time:

Restore your RDS Custom for SQL Server database instance to a point in time. This can be done using the AWS Management Console, the AWS CLI, or the Amazon RDS API. See PITR restore using the AWS Management Console, the AWS CLI, or the RDS API in Restoring an RDS Custom for SQL Server instance to a point in time. On completion of this step, all self-managed databases will be restored to the specified point in time. The non-managed databases—those specified in the msdb.dbo.rds_pitr_blocked_databases table—will be restored to the time of the last automated backup.
Restore the non-managed PITR databases specified in the msdb.dbo.rds_pitr_blocked_databases table. First, drop the existing databases specified on the table using the DROP DATABASE command and then restore the database as per the solution in this post (backup\restore to an Amazon FSx file share or to Amazon S3).

Best practices

To keep storage costs down, it’s a best practice to create an Amazon S3 lifecycle policy to manage deletion (expiration) of your backups as well as transitioning the backups to different Amazon S3 storage classes. For information about how to achieve this, see Managing object lifecycle.

If you’re backing up to Amazon FSx, you can build a solution that copies the backup files to Amazon S3 and deletes them from the Amazon FSx share. You can use a combination of AWS CLI commands, AWS Lambda functions, and AWS DataSync. Building this solution is out of scope for this post.

If the RDS Custom instance needs to be persisted in the Active directory, then you can persist the member join by following the steps in Automate the configuration of Amazon RDS Custom for SQL Server using AWS Systems Manager.

Clean up

If you don’t need your environment anymore, follow these steps to clean it and avoid incurring costs:

Summary

In this post, we demonstrated two solutions that you can use to achieve PITR recovery for all your databases in an Amazon RDS Custom for SQL Server instance.

If you have any questions or suggestions, leave a comment.

About the Authors

Jose Amado-Blanco is a Sr. Consultant on Database Migration with over 25 years of experience working with AWS Professional Services. He helps customers on their journey to migrate and modernize their database solutions from on-premises to AWS.

Priya Nair is a Sr. Database consultant at AWS. She has over 18 years of experience working with different database technologies. She works as a database migration specialist to help Amazon customers to move their on-premises database environment to AWS Cloud database solutions.

AWS Database Blog