Migrate a Microsoft SQL Server database from Amazon EC2 to Amazon DocumentDB by using AWS DMS

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.

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.

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.

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.

Run the "use" command with the name of your database to create a database in your Amazon DocumentDB cluster.

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.”

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.

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.

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.

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.”

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.”

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.

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.”

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.