Export a Microsoft SQL Server database to Amazon S3 by using AWS DMS - AWS Prescriptive Guidance
SummaryPrerequisites and limitationsArchitectureToolsEpicsRelated resourcesAdditional information

Export a Microsoft SQL Server database to Amazon S3 by using AWS DMS

Created by Sweta Krishna (AWS)

Environment: PoC or pilot

Source: Microsoft SQL Server

Target: Amazon S3

R Type: Replatform

Workload: Microsoft

Technologies: Migration; Databases

AWS services: AWS DMS; Amazon S3

Summary

Organizations often need to copy databases to Amazon Simple Storage Service (Amazon S3) for database migration, backup and restore, data archiving, and data analytics. This pattern describes how you can export a Microsoft SQL Server database to Amazon S3. The source database can be hosted on premises or on Amazon Elastic Compute Cloud (Amazon EC2) or Amazon Relational Database Service (Amazon RDS) for Microsoft SQL Server on the Amazon Web Services (AWS) Cloud.

The data is exported by using AWS Database Migration Service (AWS DMS). By default, AWS DMS writes full load and change data capture (CDC) data in comma-separated value (.csv) format. For more compact storage and faster query options, this pattern uses the Apache Parquet (.parquet) format option.

Prerequisites and limitations

Prerequisites

  • An active AWS account

  • An AWS Identity and Access Management (IAM) role for the account with write, delete, and tag access to the target S3 bucket, and AWS DMS (dms.amazonaws.com) added as a trusted entity to this IAM role

  • An on-premises Microsoft SQL Server database (or Microsoft SQL Server on an EC2 instance or an Amazon RDS for SQL Server database)

  • Network connectivity between the virtual private cloud (VPC) on AWS and the on-premises network provided by AWS Direct Connect or a virtual private network (VPN)

Limitations

  • A VPC-enabled (gateway VPC) S3 bucket isn't currently supported in AWS DMS versions earlier than 3.4.7.

  • Changes to the source table structure during full load are not supported.

  • AWS DMS full large binary object (LOB) mode is not supported.

Product versions

  • Microsoft SQL Server versions 2005 or later for the Enterprise, Standard, Workgroup, and Developer editions.

  • Support for Microsoft SQL Server version 2019 as a source is available in AWS DMS versions 3.3.2 and later.

Architecture

Source technology stack 

  • An on-premises Microsoft SQL Server database (or Microsoft SQL Server on an EC2 instance or an Amazon RDS for SQL Server database)  

Target technology stack  

  • AWS Direct Connect

  • AWS DMS

  • Amazon S3

Target architecture 

The data migrating from the source SQL Server database through Direct Connect into AWS DMS and then to storage in the S3 bucket.

Tools

  • AWS Database Migration Service (AWS DMS) helps you migrate data stores into the AWS Cloud or between combinations of cloud and on-premises setups.

  • AWS Direct Connect links your internal network to a Direct Connect location over a standard Ethernet fiber-optic cable. With this connection, you can create virtual interfaces directly to public AWS services while bypassing internet service providers in your network path.

  • Amazon Simple Storage Service (Amazon S3) is a cloud-based object storage service that helps you store, protect, and retrieve any amount of data.

Epics

TaskDescriptionSkills required

Validate the database version.

Validate the source database version and make sure that it’s supported by AWS DMS. For information about supported SQL Server database versions, see Using a Microsoft SQL Server database as a source for AWS DMS.

DBA

Create a VPC and security group.

In your AWS account, create a VPC and security group. For more information, see the Amazon VPC documentation.

System Administrator

Create a user for the AWS DMS task.

Create an AWS DMS user in the source database and grant it READ permissions. This user will be used by AWS DMS.

DBA

Test the DB connectivity.

Test the connectivity to the SQL Server DB instance from the AWS DMS user.

DBA

Create an S3 bucket.

Create the target S3 bucket. This bucket will hold the migrated table data.

Systems administrator

Create an IAM policy and role.

  1. To create an IAM policy with bucket permissions, use the code in the Additional information section.

  2. Create the role for AWS DMS, and attach the policy to the role.

Systems administrator
TaskDescriptionSkills required

Create an AWS DMS replication instance.

Sign in to the AWS Management Console, and open the AWS DMS console. In the navigation pane, choose Replication instances, Create replication instance. For instructions, see step 1 in the AWS DMS documentation.

DBA

Create source and target endpoints.

Create source and target endpoints. Test the connection from the replication instance to both source and target endpoints. For instructions, see step 2 in the AWS DMS documentation.

DBA

Create a replication task.

Create a replication task, and select full load or full load with change data capture (CDC) to migrate data from SQL Server to the S3 bucket. For instructions, see step 3 in the AWS DMS documentation.

DBA

Start the data replication.

Start the replication task, and monitor the logs for any errors.

DBA
TaskDescriptionSkills required

Validate the migrated data.

On the console, navigate to your target S3 bucket. Open the subfolder that has the same name as the source database. Confirm that the folder contains all the tables that were migrated from the source database.

DBA
TaskDescriptionSkills required

Shut down and delete temporary AWS resources.

Shut down temporary AWS resources that you created for the data migration, such as the AWS DMS replication instance, and delete them after you validate the export.

DBA

Related resources

Additional information

Use the following code to add an IAM policy with S3 bucket permissions for the AWS DMS role. Replace bucketname with the name of your bucket.

{
     "Version": "2012-10-17",
     "Statement": [
         {
             "Effect": "Allow",
             "Action": [
                 "s3:PutObject",
                 "s3:DeleteObject"
             ],
             "Resource": [
                 "arn:aws:s3:::bucketname*"
             ]
         },
         {
             "Effect": "Allow",
             "Action": [
                 "s3:ListBucket"
             ],
             "Resource": [
                 "arn:aws:s3:::bucketname*"
             ]
         }
     ]
 }