Migrating Data from a Self-Managed SQL Server Database on an ECS to an RDS for SQL Server DB Instance

Scenarios

• You have created a Microsoft SQL Server database on an ECS.
• The self-managed SQL Server database version on the ECS cannot be later than the version of the RDS for SQL Server DB instance.
• You have installed the SQL Server Management Studio (SSMS).

Procedure

1. Create an ECS.
   

   The ECS and the RDS DB instance must be in the same region and VPC.

2. Install Microsoft SQL Server 2008, 2012, or 2014 on the ECS.
   

   The Microsoft SQL Server installed on the ECS must be Standard or Enterprise Edition. It is recommended that the Microsoft SQL Server version be the same as the RDS DB instance version.

3. Upload a local .bak file to the ECS and use Microsoft SQL Server to restore the local file to the RDS DB instance.
4. Use the script generation tool provided by Microsoft SQL Server to generate a database structure script.
   1. Right-click the database whose schema script needs to be generated and choose Tasks > Generate Scripts.
   2. On the Choose Objects page, choose database objects to script, as shown in Figure 1. Then, click Next.
      Figure 1 Choosing objects
      
   3. On the Set Scripting Options page, specify a directory for saving the script.
      

      You are advised to save the script locally and generate an SQL script for execution.

      Figure 2 Specifying a directory for saving the script
      
   4. Click Advanced. In the displayed Advanced Scripting Options dialog box, specify scripting options for triggers, indexes, unique keys, the primary key, and server version. Then, click OK.
      Figure 3 Specifying advanced scripting options
      
      

      Generate Script for Dependent Objects indicates the script data type option.

   5. Click Next to generate the script.

5. Use the SSMS client to connect to the RDS DB instance and open the generated SQL script.
   

   You need to create an empty database, and then use the script to create structures in the database.

6. Use the import and export function provided by Microsoft SQL Server to migrate data.
   1. Right-click the database where data is to be imported and choose Tasks > Import Data.
   2. Click Next.
   3. On the Choose a Data Source page, select a data source and click Next.
   4. On the Choose a Destination page, select a destination database and click Next.
      • Destination: Select SQL Server Native Client (depending on your destination database type).
      • Server name: Enter the IP address and port number of the destination DB instance.
      • Authentication: Select Use SQL Server Authentication. Then, set User name to rdsuser, and Password to the password of rdsuser.
      • Database: Select the destination database where data is to be imported.
   5. Select Copy data from one or more tables or views and click Next.
   6. On the Select Source Tables and Views page, select the tables and views that you want to copy. Then, click Edit Mappings. In the displayed dialog box, select Enable identity insert and edit mappings based on your requirements.
   7. Click Next.
   8. Select Run immediately and click Next.
   9. Click Finish to import data. You can view the progress. About 4,000 rows can be processed per second.