AWS Database Blog
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 contentss3:PutObject
to allow the backup files to be written to the buckets3: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:
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.
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
.
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.
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
.
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.