Migrate a Microsoft SQL Server database from Amazon EC2 to Amazon DocumentDB by using AWS DMS
Created by Uma Maheswara Rao Nooka (AWS)
Source: Microsoft SQL Server on Amazon EC2 | Target: Amazon DocumentDB | R Type: Re-architect |
Environment: PoC or pilot | Technologies: Cloud-native; Databases; Migration | Workload: Microsoft |
AWS services: Amazon EC2; Amazon DocumentDB |
Summary
This pattern describes how to use AWS Database Migration Service (AWS DMS) to migrate a Microsoft SQL Server database hosted on an Amazon Elastic Compute Cloud (Amazon EC2) instance to an Amazon DocumentDB (with MongoDB compatibility) database.
The AWS DMS replication task reads the table structure of the SQL Server database, creates the corresponding collection in Amazon DocumentDB, and performs a full-load migration.
You can also use this pattern to migrate an on-premises SQL Server or an Amazon Relational Database Service (Amazon RDS) for SQL Server DB instance to Amazon DocumentDB. For more information, see the guide Migrating Microsoft SQL Server databases to the AWS Cloud on the AWS Prescriptive Guidance website.
Prerequisites and limitations
Prerequisites
An active AWS account.
An existing SQL Server database on an EC2 instance.
Fixed database (db_owner) role assigned to AWS DMS in the SQL Server database. For more information, see Database-level roles
in the SQL Server documentation. Familiarity with using the
mongodump,mongorestore,mongoexport, andmongoimportutilities to move data in and out of an Amazon DocumentDB cluster.Microsoft SQL Server Management Studio
, installed and configured.
Limitations
The cluster size limit in Amazon DocumentDB is 64 TB. For more information, see Cluster limits in the Amazon DocumentDB documentation.
AWS DMS doesn't support the merging of multiple source tables into a single Amazon DocumentDB collection.
If AWS DMS processes any changes from a source table without a primary key, it will ignore large object (LOB) columns in the source table.
Architecture
Source technology stack
Amazon EC2
Target architecture
Target technology stack
Amazon DocumentDB
Tools
AWS DMS – AWS Database Migration Service (AWS DMS) helps you migrate databases easily and securely.
Amazon DocumentDB – Amazon DocumentDB (with MongoDB compatibility) is a fast, reliable, and fully managed database service.
Amazon EC2 – Amazon Elastic Compute Cloud (Amazon EC2) provides scalable computing capacity in the AWS Cloud.
Microsoft SQL Server
– SQL Server is a relational database management system. SQL Server Management Studio (SSMS)
– SSMS is a tool for managing SQL Server, including accessing, configuring, and administering SQL Server components.
Epics
| Task | Description | Skills required |
|---|---|---|
| Create a VPC. | Sign in to the AWS Management Console and open the Amazon VPC console. Create a virtual private cloud (VPC) with an IPv4 CIDR block range. | System administrator |
| Create security groups and network ACLs. | On the Amazon VPC console, create security groups and network access control lists (network ACLs) for your VPC, according to your requirements. You can also use the default settings for these configurations. For more information about this and other stories, see the “Related resources” section. | System administrator |
| Task | Description | Skills required |
|---|---|---|
| Create an Amazon DocumentDB cluster. | Open the Amazon DocumentDB console and choose “Clusters.” Choose “Create,” and create an Amazon DocumentDB cluster with one instance. Important: Make sure you configure this cluster with your VPC’s security groups. | System administrator |
| Install the mongo shell. | The mongo shell is a command-line utility that you use to connect to and query your Amazon DocumentDB cluster. To install it, run the “/etc/yum.repos.d/mongodb-org-3.6.repo” command to create the repository file. Run the “sudo yum install -y mongodb-org-shell” command to install the mongo shell. To encrypt data in transit, download the public key for Amazon DocumentDB, and then connect to your Amazon DocumentDB instance. For more information about these steps, see the “Related resources” section. | System administrator |
| Create a database in the Amazon DocumentDB cluster. | Run the "use" command with the name of your database to create a database in your Amazon DocumentDB cluster. | System administrator |
| Task | Description | Skills required |
|---|---|---|
| Create the AWS DMS replication instance. | Open the AWS DMS console and choose “Create replication instance.” Enter a name and description for your replication task. Choose the instance class, engine version, storage, VPC, Multi-AZ, and make it publicly accessible. Choose the “Advanced” tab to set the network and encryption settings. Specify the maintenance settings, and then choose “Create replication instance.” | System administrator |
| Configure the SQL Server database. | Log in to Microsoft SQL Server and add an inbound rule for communication between the source endpoint and the AWS DMS replication instance. Use the replication instance’s private IP address as the source. Important: The replication instance and target endpoint should be on the same VPC. Use an alternative source in the security group if the VPCs are different for the source and replication instances. | System administrator |
| Task | Description | Skills required |
|---|---|---|
| Create the source and target database endpoints. | Open the AWS DMS console and choose “Connect source and target database endpoints.” Specify the connection information for the source and target databases. If required, choose the “Advanced” tab to set values for “Extra connection attributes.” Download and use the certificate bundle in your endpoint configuration. | System administrator |
| Test the endpoint connection. | Choose “Run test” to test the connection. Troubleshoot any error messages by verifying the security group settings and the connections to the AWS DMS replication instance from both the source and target database instances. | System administrator |
| Task | Description | Skills required |
|---|---|---|
| Create the AWS DMS migration task. | On the AWS DMS console, choose “Tasks,” “Create task.” Specify the task options, including the source and destination endpoint names, and replication instance names. Under “Migration type” choose “Migrate existing data,” and “Replicate data changes only.” Choose “Start task.” | System administrator |
| Run the AWS DMS migration task. | Under "Task settings,” specify the settings for the table preparation mode, such as “Do nothing,” “Drop tables on target,” “Truncate,” and “Include LOB columns in replication.” Set a maximum LOB size that AWS DMS will accept and choose “Enable logging.” Leave the “Advanced settings” at their default values and choose “Create task.” | System administrator |
| Monitor the migration. | On the AWS DMS console, choose “Tasks” and choose your migration task. Choose “Task monitoring” to monitor your task. The task stops when the full-load migration is complete and cached changes are applied. | System administrator |
| Task | Description | Skills required |
|---|---|---|
| Connect to the Amazon DocumentDB cluster by using the mongo shell. | Open the Amazon DocumentDB console, choose your cluster under “Clusters.” In the “Connectivity and Security” tab, choose “Connect to this cluster with the mongo shell.” | System administrator |
| Verify the results of your migration. | Run the "use" command with the name of your database and then run the “show collections” command. Run the “db. .count();” command with the name of your database. If the results match your source database, then your migration was successful. | System administrator |
Related resources
Create and configure a VPC
Create and configure the Amazon DocumentDB cluster
Create and configure the AWS DMS replication instance
Create and test the source and target endpoints in AWS DMS
Migrate data
Other resources
