Ranga Babu
DMS Homepage

AWS RDS SQL Server Migration using AWS Database migration service

April 30, 2019 by

In this article, we will review how to migrate database from on-premise SQL Server instance to AWS RDS SQL Server instance using AWS Database migration service. There are different ways to migrate the database to the AWS RDS instance:

  • AWS Database Migration Service (DMS)
  • Native backups
  • BCP utility
  • Import and Export wizard

AWS Database Migration Service

With AWS Database migration service, you can migrate your on-premise SQL Server database to AWS RDS instance with minimal downtime.

Source as on-premise: AWS Database migration service supports 2005, 2008, 2008R2, 2012, 2014, and 2016 versions. Web and express editions are not supported.

Source as RDS instance: AWS Database migration service supports 2008R2, 2012, 2014, and 2016 versions. Enterprise and standard versions are supported.

Step by step to set up database migration service between on-premise instance and AWS RDS SQL Server instance.

Creating replication instance

To use data migration service, we need to create a replication instance which will be used to run migration tasks.

Login to AWS management console, Click on Services and type database migration service. Click on Database migration service.

Database migration service

In the database migration service home page, click on Replication Instances.

DMS Homepage

Click on Create replication instance.

Database migration service - Create replication instance

On Create replication instance page, enter the name and description of the replication instance. Select the instance class.

Replication instance configuration

Enable multi-AZ if you need high availability and fault-tolerant replicating environment. Enable Publicly accessible if you want to access the replication instance from outside of your VPC network.

Configure other options like maintenance and security as per your need and click on Create at the bottom of the page.

Once the replication instance is created we need to create source(on-premise SQL Server) and destination endpoints(AWS RDS SQL Server).

Creating endpoints

An endpoint has connection information of source and target database systems. AWS DMS uses the information in these endpoints to connect to the source database and migrate data from source to target.

Navigate to Database migration service console, Click on Endpoints and click on Create Endpoint.

Create Endpoint

On Create endpoint page, select the endpoint type as source endpoint and enter the endpoint identifier.

Endpoint configuration

Now select the source engine as SQL Server and enter the connection details like server name, port number, username, password, and the database name.

Choose SSL mode if you want to encrypt the connection for the endpoint.

AWS RDS SQL Server migration - Endpoint Configration

Click on Create endpoint at the bottom of the page.

Alternatively, you can test the endpoint connection by selecting the VPC and replicating instance. Once you click on Run test endpoint is created with details provided above and an attempt is made to connect to source endpoint. Upon successful connection, the status is reported as successful.

AWS Database migration service - Test endpoint

After creating source endpoint, we need to create target endpoint.

Navigate to Database migration service console, Click on Endpoints and click on Create Endpoint. On Create endpoint page, select the endpoint type as target endpoint and enable Select RDS DB Instance option. Select the AWS RDS SQL Server instance to which you want to migrate the on-premise database.

AWS Database migration service - Target endpoint

RDS instance connection details are auto-populated. enter the password and database name and test your target endpoint connection which will automatically create an endpoint.

Creating Migration Tasks

Migration task will perform all your migration work. To create a migration task, we need to create a replication instance, source, and target endpoints first.

Navigate to DMS Page and click on database migration tasks and click on Create task.

AWS Database migration service - create task

On Create database migration task page enter the task identifier. Select the replication instance, source and target endpoints you created earlier. Select the migration type as per your need and disable “Start task on create”. Below are the different migration types available to migrate the on-premise database to AWS RDS SQL Server instance.

  • Migrate existing data
  • Migrate existing data and replicate ongoing changes
  • Replicate data changes only

Migrate existing data – If you select migrate existing data, the task will perform one-time migration from source to target endpoint.

Migrate existing data and replicate ongoing changes – this option will load initial data from source to target endpoint and migrate the incremental data changes. To use this option the distribution must be set up on source SQL Server database instance and the database must be in full recovery model.

AWS DMS uses transactional replication in the backend to migrate ongoing changes on tables with a primary key. For tables which do not have a primary key, Change Data Capture is used to migrate ongoing changes to target (AWS RDS SQL Server).

Replicate data changes only – This option will not migrate initial data but replicate incremental changes.

AWS Database migration service - Migration type

In table mapping section, you can specify the selection and transformations rules. Select Guided UI option and click on Add new selection rule.

Adding migration rules

Here we can specify which schema can be part of the migration and which one to be ignored during migration.

In this case, I want to migrate the existing data and on-going changes of SalesTaxRate table only. So, I created a rule by specifying schema, table name and Include action.

You can also filter data that is being replicated from a source endpoint to target endpoint(AWS RDS SQL server) by creating a column filter.

Click on Add column filter. Enter the column name, condition, and value. This filter will replicate only rows which match the condition i.e. where name is equal to “Canadian GST”.

Migration filter

You can also specify transformation rules if you want to rename destination table or change the table schema. To create transformation rule, under transformation rules, Click on Add new transformation rule. Select the target and schema and specify the action.

Migration transformation rule

Specify the advanced setting as per your need or set them to default values and click on Create task.

Once the migration task is created you can run an assessment test to identify if the migration task has any unsupported data types. AWS DMS scan your source endpoint for the datatypes and compares with pre-defined datatypes supported by AWS DMS. To create an assessment report, Navigate to Database migration tasks and select the task for which you want to run the assessment test. Click on Actions drop-down and select Assess.

AWS RDS SQL Server Migration - Task assessment test

Once the assessment is complete, click on the migration task to see the assessment result.

Assessment test result

Navigate to the AWS DMS console click on Database migration task, select the task and click Actions drop down and select the Restart/Resume to start the task. The status of the task will be changed to Starting.

Once the initial load is complete the status will be changed to “Load complete, replication ongoing”.

Login to AWS RDS SQL Server instance using SQL Server management studio. Navigate to tables and you can see the table on target is created with “DBO” schema as mentioned in the transformation rules.

Target Instance - AWS RDS SQL Server

Query the table to see if the data is loaded as per the filter mentioned in the migration task.

You can see only rows with values “Canadian GST” are migrated to target instance.

Destination table

To see the statistics of migration task, click on migration task and scroll down to Table Statistics section which shows few stats like the number of inserted rows, the initially loaded rows and total rows in the table.

Migration statistics

Observations from migration

  • Identity property of the column is not copied to the target database
  • Foreign keys are not created on the target database. We must create them manually
  • Constraints are not created on the target database. We must create them manually
  • Indexes are not copied to the target database
  • Stored procedures, views, functions, and triggers must be manually created on the target database
  • Tables with primary key – NVARCHAR(MAX) and VARCHAR(MAX) columns are replicated to the target database
  • Tables without a primary key – NVARCHAR(MAX) and VARCHAR(MAX) columns are not replicated to the target database
  • Tables without a primary key- rows exceeding the size of 8000 bytes are not replicated

Ranga Babu

Ranga Babu

SQL Server DBA, Developer with a good experience in SQL server administration, development, performance tuning, monitoring, high availability and disaster recovery technologies
Ranga Babu
440 Views