Migrating SQL Server 2008 to Amazon RDS: A Complete Guide

Are you a user of SQL Server 2008 or SQL Server 2008 R2? Are you planning to migrate to Amazon RDS as soon as Microsoft ends support for these SQL Server versions? Read this post, as you will learn two different ways to migrate from SQL Server 2008 to Amazon RDS.Microsoft will be stopping extended support for SQL Server 2008 and SQL Server 2008 R2 in July 2019. This means that the users of these two versions of the SQL Server will no longer receive any security patches from Microsoft. As a result, the SQL Servers will become vulnerable to system glitches and bugs. Since there is only one year left before the extended support ends, most of the SQL Server 2008 users have started preparing for migration.Amazon RDS Cloud is the favorite choice for many users who are thinking about migration. But the point is, people do not know how to perform the migration process. This is why this post will share with readers two ways to migrate SQL Server to Amazon RDS. One way is to use the Amazon Database Migration Service (DMS), and another one is to use SysTools SQL Server to Amazon RDS Migrator.

How to Migrate SQL Server 2008 to Amazon RDS

There are several ways to do this migration, but here, we will learn about two common methods. The first of the methods is the use of DMS.

Using Amazon Database Migration Service (DMS)

The process to export SQL Server using the Amazon Database Migration Service (DMS) is a long one. To perform it correctly, follow the guidelines.

1. Create Replication Subnet Group

In order to use this service, creation of a replication subnet group is mandatory. Otherwise, users can utilize the default replication group for this purpose. Apart from these, at minimum, two availability zone subnets are needed.(a) Go to the top left side and click on Services > > Database Migration Services. Now, go to the DMS dashboard page to click on the Subnet groups option. Then, go to Subnet page and click Create Subnet.(b) A dialog box named Create replication subnet group will appear. Enter Identifier name and description. In the case of multiple VPC, choose the right one from the drop-down menu. Now, select the available subnet > > click Add > > click Create.

2. Create Replication Instance

Now, provide some data, and AWS will create a DMS replication instance that will be used during migration.

1. First, click on Create migration. Or you can go to Menu > > Replication Instances > > Create Replication Instance.
2. When the Create Replication Instance dialog box appears, users have to provide all the information that is present in the box, including the instance name, description, replication instance version, VPC, etc.
3. In the same dialog box, enter information under the Advanced option; specify allocated storage and other criteria.
4. Under Maintenance on the Create Replication Instance dialog box, check the Auto minor version update option to get all updates automatically to the replication instance. Users can also mention the maintenance window for the replication instance and then click on Create Replication Instance.
5. At this point, the replication instance will be seen on the Replication Instances page. Its available status will indicate that it is running.

3. Creating Endpoints

Endpoints are essential, as DMS will use these endpoints to read and write data. Before beginning the migration, users must verify their endpoints.

1. Click on Endpoints from the left panel and select Create endpoints.
2. A dialog box will pop up, and in it you have to select Source. Select SQL Server from the drop-down menu of “Source engine.”
3. On the same page, enter server name, user name, password, and other required information.
4. Also select the KMS master key under the Advanced section.
5. Under the Test endpoint connection section, select VPC and replication instance. Click Run Test to check connection.
6. If the test is successful, click Save.
7. Now, the source endpoint will appear with available status. You can Modify or Delete it if needed.
8. From the Create endpoint dialog box, select Target as the endpoint type. From the drop-down menu, select SQL Server as the Target engine.
9. Also enter server name, SSL mode, port number, etc.
10. Under the Advanced section of the dialog box, select KMS master key.
11. Under Test endpoint connection, choose the VPC and replication instance, and then click on Run test button.
12. Upon successful testing, click on Save.
13. The target endpoint will also appear.

4. Creating a Task

To proceed with the migration, users need to create a task.

1. Start with selecting Tasks>> Create task.
2. On the Create Task page, enter the task name. Select the replication instance and source/target endpoints. Select between three kinds of migration types and check Start task on create box.
3. Changes made during the load get cached under Task Settings. Users can make required configurations on this page.
4. Select the schema that you need to export any associated tables. To transfer all tables within a particular schema, use the % wildcard.
5. Under the Table Mapping section, select the options needed to get results. Finally, click on Add selection rule.
6. Under the same section, you can customize the JSON.
7. Click on the Create task button once you are done configuring all parameters on the Create task page.
8. Now, the task will be visible as created on the Tasks page. Once you select Start task on create, it will appear to become 100% completed.
9. Users can see the total number of rows and complete load rows from the table statistics tab on the same page.
10. The task can be started, resumed, stopped, modified, and deleted from the Tasks page.

5. Verify Data Replication

Connect to Amazon RDS SQL to view data and tables loaded in the server. Here, you can review the SQL Server 2008 data that has been successfully migrated to the Amazon RDS SQL Server.

Limitations of Using Amazon DMS

There are some disadvantages to using the Database Migration Service:

1. Only data, tables, and primary keys can be migrated from SQL Server 2008 with the help of this method.
2. All secondary objects have to be migrated manually. These include views, functions, store procedures, triggers, etc.
3. To conduct this migration, the source SQL Server has to be the default instance.

Using SysTools SQL Server to Amazon RDS Migrator

To avoid these Amazon DMS limitations, there is a convenient alternative: the SysTools SQL Server to Amazon RDS Migrator Software. This tool can be used for smooth and fast SQL Server migrations. Through this process, both primary and secondary objects can be exported. Therefore, users do not have to migrate anything manually. If needed, users can selectively export data using this application.

Final Words

As it has become compulsory for SQL Server 2008 users to migrate, many are going to Amazon RDS. This export process can be done in various ways. Here, we have discussed two techniques for the SQL Server to Amazon RDS export process. Since the migration process involves use of the Amazon Database Management System, users should go for the tool mentioned in the post. The application can migrate SQL Server 2008 data at a fast speed.