Chanci Turner

Amazon S3 Integration for Native Backup and Restore on Amazon RDS Custom for SQL Server 2022

—

by

The Story of Chanci Turner
in

Amazon S3 Integration for Native Backup and Restore on Amazon RDS Custom for SQL Server 2022Learn About Amazon VGT2 Learning Manager Chanci Turner

Amazon Relational Database Service (Amazon RDS) Custom for SQL Server offers a managed database solution tailored for both custom and packaged applications that demand access to the underlying operating system (OS) and database (DB) environment. With Amazon RDS Custom for SQL Server, users can automate the setup, management, and scaling of databases in the cloud while retaining control over the database and OS. This setup allows for the configuration of settings, installation of drivers, and activation of native features to fulfill application requirements.

Before the advent of SQL Server 2022, users of Amazon RDS Custom for SQL Server lacked direct support for backup and restore operations using Amazon Simple Storage Service (Amazon S3). Typically, users would conduct native backups to local disk storage and then upload the files to an S3 bucket if necessary. With the introduction of SQL Server 2022, Amazon RDS Custom now supports integration with Amazon S3, enabling users to directly perform native backup and restore operations to and from S3 buckets.

In this article, we will guide you through the setup of Amazon S3 integration on 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 blog will illustrate how to integrate Amazon S3 with Amazon RDS Custom for SQL Server (2022) and execute native backup and restore operations utilizing an S3 bucket. For further details, consult the SQL Server backup to URL for S3-compatible object storage.

The high-level steps include:

  1. Creating an S3 bucket.
  2. Establishing an AWS Identity and Access Management (IAM) policy with the necessary permissions to access the S3 bucket.
  3. Setting up an IAM user and linking the IAM policy.
  4. Launching an RDS Custom for SQL Server (2022) instance.
  5. Creating SQL Server credentials for S3 bucket access.
  6. Executing a native backup using the S3 bucket URL.
  7. Performing a native restore using the S3 bucket URL.

This process requires the creation and use of new AWS resources, which could incur costs on your account. Check AWS Pricing for more details.

Prerequisites

To implement this solution, you will need:

  • An AWS account with the permissions necessary to launch an RDS Custom SQL Server instance and utilize Amazon S3.
  • Basic knowledge of SQL Server backup and restore processes.
  • A fundamental understanding of Amazon S3.
  • A properly set up environment to launch an RDS Custom for SQL Server instance.

Create an S3 Bucket

Begin by creating an S3 bucket to store backup files for your operations. For detailed instructions, see Creating a bucket. For this example, we will create an S3 bucket named cfs-s3-bucket-for-backups and a folder called ss-2022 in the us-west-2 Region.

Create an IAM Policy

Define the S3 bucket name in your IAM policy and grant the following permissions for S3 bucket access:

  • s3:ListBucket to allow listing bucket contents.
  • s3:PutObject to permit writing backup files to the bucket.
  • s3:GetObject to allow reading backup files from the bucket.

You can use the following command to create an IAM policy named cfs-iam-policy-for-backups via the AWS Command Line Interface (AWS CLI). This policy grants the necessary permissions to the S3 bucket named cfs-s3-bucket-for-backups. Be sure to replace the bucket name with your own 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

Next, create an IAM user and attach the policy you created earlier. For detailed instructions, refer to Creating an IAM user in your AWS account. Remember to create an access key; note down the access key ID and secret access key, which you will use to create SQL Server credentials for S3 bucket access later.

Create an RDS Custom for SQL Server (2022) Instance

Ensure all prerequisites are successfully configured before creating your RDS Custom for SQL Server instance. For further information, refer to Setting up your environment for Amazon RDS Custom for SQL Server. 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 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>

Create SQL Server Credentials for S3 Bucket Access

After successfully creating the instance, log in to the database using SQL Server Management Studio (SSMS) with 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. This example uses the ss-2022 folder under the cfs-s3-bucket-for-backups S3 bucket:

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 the ss-2022 folder. You can adjust the database name and URL as necessary:

-- 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 restores the test-full-1.bak backup file from the S3 bucket to the RDS Custom for SQL Server instance as 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 a comprehensive guide for setting up Amazon S3 integration on Amazon RDS Custom for SQL Server, including the steps for creating an S3 bucket, IAM user, and IAM policy. By implementing these steps, you can efficiently manage your database backup and restore processes. If you’re looking for ways to manage your career transitions, check out this insightful blog post on how to save to quit your job. For more information on employee-generated videos, take a look at this authoritative source on the topic. Finally, if you’re interested in a career opportunity, consider this excellent resource for a Learning Trainer position.

Related Topics:

Chanci Turner