• AWS Database Migration Service
Best options to migrate SQL Server database from on premise to RDS. You can choose the option based on your business use case.
- Backup/Restore: Backup and Restore is very simple option to migrate the on premise database but it requires the downtime.
- AWS Database Migration Service (DMS): allows you to migrate the database with little to no downtime. Please note DMS only migrates the tables, primary keys and data. Secondary objects are not migrated like stored procedure, views, functions, triggers etc. This has to be done manually.
Let’s start by creating the sample database on EC2 SQL Server. WideWorldImporters is the new sample database for SQL Server (starting 2016). AdventureWorks sample database doesn’t include all the features support by the new version. You can download the sample database backup file from here:
Steps to restore sample database
- In Object Explorer, connect to an instance of the SQL Server Database Engine and then expand that instance.
- Right-click Databases and select Restore Database...
- On the General page, use the Source section to specify the source and location of the backup sets to restore. Select the Device and Click the browse (...) button to open the Select backup devices dialog box. Backup Media Type : File and select the backup file by browsing the path from the tree
Configure the Database Migration Service
An AWS DMS replication instance performs the actual data migration between source and target. The replication instance also caches the transaction logs during the migration. The amount of CPU and memory capacity a replication instance has influences the overall time that is required for the migration.
Step # 1 – Create Replication Subnet Group
To use the Database Migration Services, you need to create a replication subnet group or you can use the default replication group. If you have more than one VPC then you need to select the VPC you want to use. Also, Database Migration Services requires at least two availability zone subnets.
If you have only one availability zone subnet then you will get this error “Replication subnet group doesn’t meet availability zones coverage requirement please add valid subnet to cover at latest 2 availability zones”. The CIDR block and IP address range will be the same as the main subnet group. You do not need to define the CIDR block.
- Sign in to the AWS Management Console, and open the AWS DMS console at https://console.aws.amazon.com/dms/.
- On the DMS dashboard page, click on Subnet groups. On the Subnet page, click on Create Subnet.
- On the Create replication subnet group dialog box, give an Identifier name, description, if there is more than one VPC then select the correct one from the from drop down menu. Select the available subnet and click Add to add the subnet group and then click Create.
Note: if you have only one availability zone subnet then you will get this error “Replication subnet group doesn’t meet availability zones coverage requirement please add valid subnet to cover at latest 2 availability zones”.
Step # 2 – Create Replication Instance
With DMS migration, DMS will create a replication instance that will run on an EC2 instance server. You will be using this replication instance to migrate your database, to create a replication instance you need to provide some information and AWS will create the DMS replication instance. To create the Replication Instance click on the Create migration button or from the menus you can go to Replication Instances and click on Create Replication Instance.
- Go to Replication Instances and click on Create Replication Instance.
- On the Create Replication Instance dialog box, give the instance name, description, select instance class, replication instance version, VPC, if you want to use multi-AZ for the replication instance then you can change multi-AZ to yes, and if you want to replication instance to be publicly accessible then you need to check the option.
- Name: Select a name for your replication instance. If you are using multiple replication servers or sharing an account, choose a name that helps you quickly differentiate between the different servers.
- Description: Type a brief description.
- Instance class: Select the type of replication server to create. Each size and type of instance class has increasing CPU, memory, and I/O capacity. Generally, t2 instances are for lower load tasks, and the c4 instances are for higher load and more tasks.
- VPC: Choose the virtual private cloud (VPC) in which your replication instance will launch. If possible, select the same VPC in which either your source or target database resides (or both).
- Multi-AZ: If you choose Yes, AWS DMS creates a second replication server in a different Availability Zone for failover if there is a problem with the primary replication server.
- Publicly accessible: If either your source or target database resides outside of the VPC in which your replication server resides, you must make your replication server policy publicly accessible.
For the Advanced section, specify the following information.
- Allocated storage (GB): Amount of storage on the replication server for the AWS DMS task logs, including historical tasks logs. AWS DMS also uses disk storage to cache certain data while it replicates it from the source database to the target. Additionally, more storage generally enables better IOPS on the server.
- Replication Subnet Group: If you are running in a Multi-AZ configuration, you need at least two subnet groups.
- Availability zone: Generally, performance is better if you locate your primary replication server in the same Availability Zone as your target database.
- VPC Security Group(s): Security groups enable you to control ingress and egress to your VPC. AWS DMS lets you associate one or more security groups with the VPC in which your replication server is launched.
- KMS master key: With AWS DMS, all data is encrypted at rest using a KMS encryption key. By default, AWS DMS creates a new encryption key for your replication server. However, you might choose to use an existing key.
On Create Replication Instance dialog box, under Maintenance you can check Auto minor version update, so whenever there is a minor update AWS will release it automatically to the replication instance, you can specify the maintenance window for the replication instance and click Create Replication Instance.
On the Replication Instances page the replication instance will appear with an available status to show that it is up and running.
Step # 3 – Create Endpoints
AWS Database Migration Services (DMS) will access your source and target data using endpoints. The source endpoint allows AWS DMS to read data from a database (on premises or EC2 in the cloud), or from a non-database source such as Amazon S3. A target endpoint allows AWS DMS to write data to a database or to a non-database target.It is recommended that you test the connection to verify that your endpoint is valid and successfully connects to the source and target before creating the migration task in DMS AWS.
On the Endpoints page, click on Create endpoint.
On the Create endpoint dialog box, select the source endpoint type, give the endpoint an identifier name, select source database engine from drop down list. AWS provides a wide range of source database engine selections; here I have selected sqlserver as we are migrating from an EC2 SQL Server database to an Amazon RDS SQL Server database.
On the Create endpoint dialog box, under Test endpoint connection select the VPC and replication instance and click Run test to make sure the connection works.
On the Create endpoint dialog box, under Test endpoint connection once the connection test is successful click Save to save the settings.
On the Create endpoint dialog box, select target endpoint type, give the endpoint identifier name, select the target database engine from the drop down list.
Note: Please create the database on target if doesn’t exist. You can use SSMS or create database
The Create endpoint dialog box, under Test endpoint connection select the VPC and replication instance and click Run test. Once connection tested successfully, click on save to save the endpoint.
Step # 4 – Create Task
When you want to migrate the data or database from on-premises or EC2 SQL Server you need to create a task. A task defines the source and target endpoints, migration type, etc.
Click on Tasks and then Create task.
On the Create task page, give the task name, select the replication instance, source/target endpoints, migration type “migrate existing data” and check the on Start task on create. There are three options under Migration Type: - Migrate existing data - This option will migrate only existing tables, primary keys and data it won’t capture ongoing changes. - Migrate existing data and replicate ongoing - This option will migrate existing tables, primary keys and data and replicate ongoing changes. - Replicate data changes only - This option migrates only data changes.
On the Create Task page, under Task Settings “Target table preparation mode Do Nothing” and checked the “Enable Logging”
- Target table preparation mode
- Do nothing will not do anything
- Drop tables on target will drop the tables and the data
- Truncate will truncate the target table data.
- Stop task after full load completes
- Don’t stop will not stop anything
- Select Stop Before Applying Cached Changes
- Stop After Applying Cached Changes.
- Include LOB columns – If you have large object columns then you need to select this option.
- Enable logging – This option will generate the log to monitor in Cloudwatch.
On the Create Task page, under Table mappings you need to select the schema you need to migrate any associated tables. You can use wildcards like % to migrate all tables within a given schema. You can also add column filters if you want to use a column level filter. Click on Add selection rule
On the Create task page after you configured all required parameters click on Create task.
If you chose Enable logging during setup, you can monitor your task. You can then view the Amazon CloudWatch metrics. To monitor a data migration task in progress
- On the navigation pane, choose Tasks.
- Choose your migration task.
- Choose the Task monitoring tab, and monitor the task in progress on that tab.
When the full load is complete and cached changes are applied, the task stops on its own.
Note: If you chose to use AWS DMS to replicate changes in future, in the AWS DMS console, start the AWS DMS task by choosing Start/Resume for the task. Important replication instance metrics to monitor include the following: