Native backup and restore with Amazon S3 integration on Amazon RDS Custom for SQL Server 2022

Amazon Relational Database Service (Amazon RDS) Custom for SQL Server is a managed database service for custom and packaged applications that require access to the underlying operating system (OS) and database (DB) environment. Amazon RDS Custom for SQL Server automates the setup, operation, and scaling of databases in the cloud while granting access to the database and underlying operating system. Using this, you can configure settings, install drivers, and enable native features to meet the dependent application’s requirement.

Prior to SQL Server 2022, Amazon RDS Custom for SQL Server users didn’t have direct support for backup and restore using Amazon Simple Storage Service (Amazon S3). Typically, users performed native backups on to the local disk and if required, uploaded the same backup file to an S3 bucket from the local disk. Now that Amazon RDS Custom for SQL Server supports SQL Server 2022, you can set up Amazon S3 integration and directly perform native backup and restore operations to and from the S3 bucket.

In this post, we explain how you can set up Amazon S3 integration on Amazon RDS Custom for SQL Server 2022.

Solution overview

Starting from SQL Server 2022 (16.x), Microsoft has included support for Amazon S3-compatible object storage. In this post, we demonstrate how to set up Amazon S3 integration on Amazon RDS Custom for SQL Server (2022) and then perform native backup and restore operations to an S3 bucket. For more details, refer to SQL Server backup to URL for S3-compatible object storage.

The following are the high-level steps:

Create an S3 bucket.
Create an AWS Identity and Access Management (IAM) policy with the required permissions to access the S3 bucket.
Create an IAM user and attach the IAM policy.
Create an RDS Custom for SQL Server (2022) instance.
Create SQL Server credentials to access the S3 bucket.
Perform native backup using the S3 bucket URL.
Perform native restore using the S3 bucket URL.

This solution involves the creation and utilization of new AWS resources. Therefore, it will incur costs on your account. Refer to AWS Pricing for more information.

Prerequisites

The following prerequisites are needed to implement this solution:

An AWS account with the required permissions to launch an RDS Custom SQL Server instance and use Amazon S3
Basic understanding of SQL Server backup and restore
Basic understanding of Amazon S3
The environment setup completed and ability to launch an RDS Custom for SQL Server instance

Create an S3 bucket

Create an S3 bucket to store backup files for backup and restore operations. For more details, refer to Creating a bucket.

In this example, we create a S3 bucket named cfs-s3-bucket-for-backups and then create a folder called ss-2022 in the us-west-2 Region.

Create an IAM policy

As part of our IAM policy, we define the S3 bucket name and grant the following required permissions for accessing the S3 bucket:

s3:ListBucket to allow the listing of the bucket’s contents
s3:PutObject to allow the backup files to be written to the bucket
s3:GetObject to allow the backup files to be read from the bucket

You may use the following code to create an IAM policy named cfs-iam-policy-for-backups using the AWS Command Line Interface (AWS CLI). It grants required permissions to the S3 bucket named cfs-s3-bucket-for-backups. You may replace the S3 bucket with an appropriate value:

aws iam create-policy \
    --policy-name cfs-iam-policy-for-backups \
    --policy-document '{
"Version": "2012-10-17",
"Statement": [
{
"Sid": "VisualEditor0",
"Effect": "Allow",
"Action": [
"s3:PutObject",
"s3:GetObject",
"s3:ListBucket"
],
"Resource": "arn:aws:s3:::cfs-s3-bucket-for-backups*"
}
]
}'

Create an IAM user and attach the IAM policy

Create an IAM user and attach the IAM policy that you created earlier. For instructions, refer to Creating an IAM user in your AWS account. It’s important to create an access key and note down the access key ID and secret access key—you’ll use them to create SQL Server credentials in a later step for S3 bucket access at the database level.

Create an RDS Custom for SQL Server (2022) instance

Before you create your first RDS Custom for SQL Server instance, ensure that all the prerequisites are configured successfully. Refer to Setting up your environment for Amazon RDS Custom for SQL Server for more information.

You can use the following command to create an RDS Custom for SQL Server instance (using SQL Server 2022) with the AWS CLI. Replace <db-instance-name>, <key-id>, <custom-iam-profile>, <security-group-id>, and <db-subnet-group> with appropriate values.

aws rds create-db-instance \
 --db-instance-identifier <db-instance-name> \
 --engine custom-sqlserver-ee \
 --kms-key-id <key-id> \
 --engine-version 16.00.4085.2.v1 \
 --master-username admin \
 --master-user-password ********* \
 --db-instance-class db.m5.2xlarge \
 --allocated-storage 200 \
 --storage-type gp3 \
--region us-west-2 \
 --custom-iam-instance-profile <custom-iam-profile> \
--vpc-security-group-ids <security-group-id> \
 -db-subnet-group <db-subnet-group>

Create SQL Server credentials to access the S3 bucket

After the instance creation is successful, log in to the database using SSMS and the primary user.

The following example creates SQL Server credentials for authentication with the object storage endpoint. Use the access key ID and secret key ID from the previous step. The following example uses a folder named ss-2022 under the S3 bucket cfs-s3-bucket-for-backups.

CREATE CREDENTIAL   [s3://<endpoint>:<port>/<bucket>]
WITH
        IDENTITY    = 'S3 Access Key',
        SECRET      = '<AccessKeyID>:<SecretKeyID>';

Example:
CREATE CREDENTIAL [s3://cfs-s3-bucket-for-backups.s3.us-west-2.amazonaws.com/ss-2022]
WITH IDENTITY = 'S3 Access Key',
SECRET = 'ABCDEFGHXXXXX:CKLKq6/123456789XXXX';

Perform native backup using the S3 bucket URL

The following command creates a sample database named test1 and saves a full backup called test-full-1.bak to the S3 bucket cfs-s3-bucket-for-backups under folder ss-2022. You can replace the database name and URL with appropriate values.

-- Create database test1
create database test1;

use test1;

-- create table t1
create table t1 (id int);
insert into t1 values (10)
go 100

-- create table t2
create table t2 (id int);
insert into t2 values (10)
go 100

-- backup database test1
BACKUP DATABASE test1
TO URL = 's3://cfs-s3-bucket-for-backups.s3.us-west-2.amazonaws.com/ss-2022/test-full-1.bak' WITH FORMAT, MAXTRANSFERSIZE = 20971520, COMPRESSION;

Perform native restore using the S3 bucket URL

The following command uses a backup file test-full-1.bak from the S3 bucket and restores it on the RDS Custom for SQL Server instance with database name test2.

RESTORE DATABASE test2
FROM URL ='s3://cfs-s3-bucket-for-backups.s3.us-west-2.amazonaws.com/ss-2022/test-full-1.bak'
WITH move 'test1' to 'D:\RDSDBData\test2.mdf',
move 'test1_log' to 'D:\RDSDBData\test2_log.ldf';

Conclusion

In this post, we provided a step-by-step guide to implement Amazon S3 integration on Amazon RDS Custom for SQL Server, which included creating an S3 bucket, IAM user, and IAM policy. We also demonstrated how to create Amazon S3 credentials on SQL Server and then successfully perform native backup and restore operations. Leave a comment if you have any questions.

To learn more about where or when to use RDS Custom, check out Working with RDS Custom for SQL Server.

About the authors

Srikanth Katakam is a Senior Database Engineer at Amazon Web Services. He works on the Amazon RDS team, focusing on commercial database engines, Amazon RDS Custom, and SQL Server. He enjoys working on technical challenges in Amazon RDS and is passionate about learning from and sharing knowledge with his teammates and AWS customers.

Minesh Chande is Senior Database Specialist Solutions Architect at Amazon Web Services. He helps customers across different industry verticals design, migrate, and optimize their SQL Server workloads to a managed database platform like Amazon RDS and Amazon RDS Custom.