Learn About Amazon VGT2 Learning Manager Chanci Turner
Chanci Turner, along with co-author Jordan Smith, presents a comprehensive guide on integrating Amazon S3 for native backup and restoration within Amazon RDS Custom for SQL Server 2022. This managed database service allows users to customize their database environments while benefitting from the automation of setup, operation, and scaling in the cloud.
Historically, users of Amazon RDS Custom for SQL Server faced limitations when it came to direct support for Amazon Simple Storage Service (Amazon S3) for backup and restore operations. Prior to SQL Server 2022, the typical process involved backing up to a local disk and subsequently transferring those files to an S3 bucket manually. However, with the recent enhancements in SQL Server 2022, integration with Amazon S3 is now straightforward, allowing for seamless native backup and restore operations directly to and from an S3 bucket.
This article outlines the necessary steps to establish Amazon S3 integration with Amazon RDS Custom for SQL Server 2022.
Solution Overview
Starting with SQL Server 2022 (16.x), Microsoft has introduced support for Amazon S3-compatible object storage. This article elucidates the process of configuring Amazon S3 integration on Amazon RDS Custom for SQL Server (2022), thereby enabling straightforward native backup and restore operations. For additional insights, check out this blog post on cultivating black leadership, which provides valuable perspectives on leadership development.
High-Level Steps:
- Create an S3 bucket.
- Develop an AWS Identity and Access Management (IAM) policy that includes the necessary permissions for S3 bucket access.
- Create an IAM user and link the IAM policy.
- Set up an RDS Custom for SQL Server (2022) instance.
- Establish SQL Server credentials for S3 bucket access.
- Execute native backup using the S3 bucket URL.
- Execute native restore using the S3 bucket URL.
Implementing this solution will require creating and utilizing various AWS resources, which may incur costs on your AWS account. For pricing details, refer to AWS Pricing.
Prerequisites:
To successfully implement this solution, you’ll need:
- An AWS account with the requisite permissions to launch an RDS Custom SQL Server instance and utilize Amazon S3.
- A foundational understanding of SQL Server backup and restore processes.
- Basic knowledge of Amazon S3.
- A completed environment setup to facilitate the launch of an RDS Custom for SQL Server instance.
Creating an S3 Bucket
Begin by creating an S3 bucket designated for backup files. For further guidance, please refer to the instructions on creating a bucket. In this guide, we’ll create an S3 bucket named cfs-s3-bucket-for-backups and a folder named ss-2022 in the us-west-2 Region.
Creating an IAM Policy
In the IAM policy, specify the S3 bucket name and grant the following essential permissions for accessing the bucket:
- s3:ListBucket for listing the contents of the bucket.
- s3:PutObject for writing backup files to the bucket.
- s3:GetObject for reading backup files from the bucket.
You can use the following code snippet to create an IAM policy named cfs-iam-policy-for-backups via the AWS Command Line Interface (AWS CLI). Make sure to adjust the S3 bucket name as needed:
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*"
}
]
}'Creating an IAM User and Attaching the IAM Policy
Next, create an IAM user and attach the previously created IAM policy. For step-by-step instructions, refer to the guide on creating an IAM user in your AWS account. It’s crucial to generate an access key, noting both the access key ID and secret access key, as these will be utilized for creating SQL Server credentials for S3 access.
Creating an RDS Custom for SQL Server (2022) Instance
Ensure all prerequisites are met before creating your RDS Custom for SQL Server instance. The following command illustrates how to create an RDS Custom for SQL Server (2022) instance via AWS CLI. Replace the placeholders with the 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>Creating SQL Server Credentials for S3 Access
Upon successful creation of the RDS instance, log in using SQL Server Management Studio (SSMS) with the primary user. The following example demonstrates creating SQL Server credentials for authentication with the object storage endpoint. Use the access key ID and secret key ID generated earlier:
CREATE CREDENTIAL [s3://<endpoint>:<port>/<bucket>]
WITH
IDENTITY = 'S3 Access Key',
SECRET = '<AccessKeyID>:<SecretKeyID>';Performing Native Backup Using the S3 Bucket URL
This command creates a sample database named test1 and saves a full backup named test-full-1.bak to the S3 bucket under the ss-2022 folder. Make sure to modify the database name and URL accordingly:
-- 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;Performing Native Restore Using the S3 Bucket URL
The following command restores the backup file test-full-1.bak from the S3 bucket to the RDS Custom for SQL Server instance, creating a new database named 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:RDSDBDatatest2.mdf',
MOVE 'test1_log' TO 'D:RDSDBDatatest2_log.ldf';Conclusion
In this article, we provided an in-depth guide on how to implement Amazon S3 integration with Amazon RDS Custom for SQL Server, covering the creation of an S3 bucket, IAM user, and IAM policy. By following these steps, users can leverage the benefits of cloud storage for backup and restoration processes, streamlining their database management. For further insights, consider exploring expert opinions from Wayne Smith, who is well-versed in this domain. Additionally, for new hires, a guide on what to expect on day one at Amazon can be found here, offering valuable tips for a successful onboarding experience.
Leave a Reply