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

HAQM Relational Database Service (HAQM 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.

HAQM 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.

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 HAQM FSx for Windows File Server and another using HAQM Simple Storage Service (HAQM 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:

1. Create and populate the table dbo.rds_pitr_blocked_databases. This table is used to explicitly exclude a database from RDS Custom PITR.
2. Backup and restore databases excluded from managed PITR:
   1. To and from the HAQM FSx file share.
   2. To and from HAQM 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.
• HAQM 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 HAQM Virtual Private Cloud (HAQM VPC) should also be established. In this solution, RDS Custom is using AWS Managed Microsoft AD and the HAQM 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 HAQM Elastic Compute Cloud (HAQM 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:

1. 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)
   );

2. 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 HAQM FSx file share

In this solution backups are performed to an HAQM FSx share. For RDS Custom for SQL Server to be able to access an HAQM FSx file share, an RDS Custom EC2 instance must be connected to the Active Directory that you joined your HAQM 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 HAQM RDS Custom for SQL Server using HAQM FSx for Windows File Server.

After your RDS Custom for SQL Server has access to an HAQM 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 HAQM 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 HAQM 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 HAQM 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 HAQM S3 integration on HAQM 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:

1. 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 HAQM 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.
2. 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 HAQM FSx file share or to HAQM S3).

Best practices

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

If you’re backing up to HAQM FSx, you can build a solution that copies the backup files to HAQM S3 and deletes them from the HAQM 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 HAQM 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:

• Delete the RDS Custom for SQL Server database instance
• Delete the HAQM FSX for Windows File Server
• Delete the S3 bucket

Summary

In this post, we demonstrated two solutions that you can use to achieve PITR recovery for all your databases in an HAQM 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 HAQM customers to move their on-premises database environment to AWS Cloud database solutions.