Export Amazon RDS for SQL Server tables to an S3 bucket by using AWS DMS - AWS Prescriptive Guidance
SummaryPrerequisites and limitationsArchitectureToolsEpicsRelated resourcesAdditional information

Export Amazon RDS for SQL Server tables to an S3 bucket by using AWS DMS

Created by Subhani Shaik (AWS)

Environment: PoC or pilot

Source: RDS

Target: S3

R Type: N/A

Workload: Microsoft

Technologies: Databases; CloudNative

AWS services: AWS DMS; Amazon RDS; Amazon S3; AWS Secrets Manager; AWS Identity and Access Management

Summary

Amazon Relational Database Service (Amazon RDS) for SQL Server doesn’t support loading data onto other DB engine linked servers on the Amazon Web Services (AWS) Cloud. Instead, you can use AWS Database Migration Service (AWS DMS) to export Amazon RDS for SQL Server tables to an Amazon Simple Storage Service (Amazon S3) bucket, where the data is available to other DB engines.

AWS DMS helps you migrate databases to AWS quickly and securely. The source database remains fully operational during the migration, minimizing downtime to applications that rely on the database. AWS DMS can migrate your data to and from the most widely used commercial and open-source databases.

This pattern uses AWS Secrets Manager while configuring the AWS DMS endpoints. Secrets Manager helps you protect secrets needed to access your applications, services, and IT resources. You can use the service to rotate, manage, and retrieve database credentials, API keys, and other secrets throughout their lifecycle. Users and applications retrieve secrets with a call to Secrets Manager, reducing the need to hardcode sensitive information. Secrets Manager offers secret rotation with built-in integration for Amazon RDS, Amazon Redshift, and Amazon DocumentDB. Also, the service is extensible to other types of secrets, including API keys and OAuth tokens. With Secrets Manager, you can control access to secrets by using fine-grained permissions and audit secret rotation centrally for resources in the AWS Cloud, third-party services, and on premises.

Prerequisites and limitations

Prerequisites

  • An active AWS account

  • An S3 bucket

  • A virtual private cloud (VPC)

  • A DB subnet

  • Amazon RDS for SQL Server

  • An AWS Identity and Access Management (IAM) role with access (list, get, and put objects) to the S3 bucket on behalf of the Amazon RDS instance.

  • Secrets Manager to store the RDS instance credentials.

Architecture

Technology stack

  • Amazon RDS for SQL Server

  • AWS DMS

  • Amazon S3

  • AWS Secrets Manager

Target architecture

The following diagram shows the architecture for importing data from the Amazon RDS instance to the S3 bucket with the help of AWS DMS.

Description follows the diagram.

  1. The AWS DMS migration task connecting to the source Amazon RDS instance through the source endpoint

  2. Copying data from the source Amazon RDS instance

  3. The AWS DMS migration task connecting to the target S3 bucket through the target endpoint

  4. Exporting copied data to the S3 bucket in comma-separated values (CSV) format

Tools

AWS services

Other services

Epics

TaskDescriptionSkills required

Create the Amazon RDS for SQL Server instance.

  1. Open the AWS Management Console, choose RDS, and use the Standard create option to create an Amazon RDS instance with the required edition, such as  SQL Server Express Edition, SQL Server Standard Edition, or SQL Server Enterprise Edition. For the version, choose 2016 or later.

  2. Under Templates, choose Dev/Test.

DBA, DevOps engineer

Set up credentials for the instance.

  1. Enter a name for the instance.

  2. Provide a username and password for the Amazon RDS instance.

DBA, DevOps engineer

Configure the instance class, storage, auto scaling, and availability.

  1. Select the DB instance class from the list: Standard, Memory Optimized, and Burstable classes. Choose the DB instance type that allocates the computational, network, and memory capacity required by the workloads planned for this DB instance. For more information, see the AWS documentation.

  2. Select the Storage Type from the list: General Purpose SSD, Provisioned IOPS SSD, or Magnetic. Allocate the default storage size as required.

  3. Choose Enable storage autoscaling to increase the Amazon RDS storage based on your capacity planning.

  4. A Multi-AZ deployment with a replication instance is supported by AWS DMS. In the event of an outage in the Availability Zone, internal hardware, or network, AWS DMS will create a standby instance and provide high availability (HA) through automatic failover to the standby replicas. Depending on the size of your import, select the appropriate option.

DBA, DevOps engineer

Specify the VPC, subnet group, public access, and security group.

Select the VPC, DB subnet groups, and VPC security group as required to create the Amazon RDS instance. Follow the best practices, for example:

  • Do not enable public access to the RDS DB instance.

  • Do not use the CIDR 0.0.0.0/0 in the security groups.

  • Use only the required IP address and port details to access the RDS instance.

DBA, DevOps engineer

Configure monitoring, backup, and maintenance.

  1. Specify the backup options that you want. By default, automated backups are enabled with a retention period of 7 days.

  2. Choose the appropriate auto minor version upgrade and Maintenance window settings to apply the pending modifications or maintenance to the database by Amazon RDS.

  3. Choose Create database.

DBA, DevOps engineer
TaskDescriptionSkills required

Create a table and load the example data.

In the new database, create a table. Use the example code in the Additional information section to load data into the table.

DBA, DevOps engineer
TaskDescriptionSkills required

Create the secret.

  1. On the console, choose Secrets Manager, and choose Store a new secret.

  2. Enter a username and password for the Amazon RDS for SQL Server database.

This secret will be used for the AWS DMS source endpoint.

DBA, DevOps engineer
TaskDescriptionSkills required

Create an IAM role for access to Amazon RDS.

  1. On the console, choose IAM, and create an IAM role that gives an S3 bucket read/write access to Amazon RDS.

  2. Under Feature, select S3 Integration.

DBA, DevOps engineer
TaskDescriptionSkills required

Create the S3 bucket.

To save the data from Amazon RDS for SQL Server, on the console, choose S3, and then choose Create bucket. Make sure that the S3 bucket is not publicly available.

DBA, DevOps engineer
TaskDescriptionSkills required

Create an IAM role for AWS DMS to access Amazon S3.

Create an IAM role that allows AWS DMS to list, get, and put objects from the S3 bucket.

DBA, DevOps engineer
TaskDescriptionSkills required

Create the AWS DMS source endpoint.

  1. On the console, choose Database Migration Service, and choose Endpoints. Create the Source endpoint, selecting the Select RDS DB instance check box.

  2. For the Source engine, select Microsoft SQL Server.

  3. Under Access to endpoint database, choose AWS Secrets Manager, and enter the secret and IAM role that you created earlier, and the database name.

  4. Test the source endpoint.

DBA, DevOps engineer

Create the AWS DMS target endpoint.

Create the Target endpoint, selecting Amazon S3 as the Target engine.

Provide the S3 bucket name and folder name for the IAM role that you created previously.

DBA, DevOps engineer

Create the AWS DMS replication instance.

In the same VPC, subnet, and security group, create the AWS DMS replication instance. For more information about choosing an instance class, see the AWS documentation.

DBA, DevOps engineer

Create the AWS DMS migration task.

To export the data from Amazon RDS for SQL Server to the S3 bucket, create a database migration task. For the migration type, choose Migrate existing data. Select the AWS DMS endpoints and replication instance that you created.

DBA, DevOps engineer
TaskDescriptionSkills required

Run the database migration task.

To export the SQL Server table data, start the database migration task. The task will export the data from Amazon RDS for SQL Server to the S3 bucket in CSV format.

DBA, DevOps engineer
TaskDescriptionSkills required

Delete the resources.

To avoid incurring extra costs, use the console to delete the resources in the following order:

  1. Migration task

  2. Replication instance

  3. Endpoints

  4. S3 bucket

  5. Database instance

DBA, DevOps engineer

Related resources

Additional information

To create the database and table, and to load the example data, use the following code.

--Step1: Database creation in RDS SQL Server
CREATE DATABASE [Test_DB]
 ON  PRIMARY
( NAME = N'Test_DB', FILENAME = N'D:\rdsdbdata\DATA\Test_DB.mdf' , SIZE = 5120KB , FILEGROWTH = 10%)
 LOG ON
( NAME = N'Test_DB_log', FILENAME = N'D:\rdsdbdata\DATA\Test_DB_log.ldf' , SIZE = 1024KB , FILEGROWTH = 10%)
GO

--Step2: Create Table
USE Test_DB
GO
Create Table Test_Table(ID int, Company Varchar(30), Location Varchar(20))

--Step3: Load sample data.
USE Test_DB
GO
Insert into Test_Table values(1,'AnyCompany','India')
Insert into Test_Table values(2,'AnyCompany','USA')
Insert into Test_Table values(3,'AnyCompany','UK')
Insert into Test_Table values(4,'AnyCompany','Hyderabad')
Insert into Test_Table values(5,'AnyCompany','Banglore')