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.
In the database migration service home page, click on Replication Instances.
Click on Create replication instance.
On Create replication instance page, enter the name and description of the replication instance. Select the instance class.
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.
On Create endpoint page, select the endpoint type as source endpoint and enter the endpoint identifier.
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.
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.
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.
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.
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.
In table mapping section, you can specify the selection and transformations rules. Select Guided UI option and click on Add new selection rule.
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”.
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.
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.
Once the assessment is complete, click on the migration task to see the assessment 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.
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.
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.
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
- Geo Replication on Transparent Data Encryption (TDE) enabled Azure SQL databases - October 24, 2019
- Overview of the Collate SQL command - October 22, 2019
- Recover a lost SA password - September 20, 2019