Full data migration from a self-managed SQL Server to ApsaraDB RDS for SQL Server

Prerequisites

• The source self-managed SQL Server database must be version 2005, 2008, 2008 R2, 2012, 2014, 2016, 2017, or 2019.
  Note

  • SQL Server Cluster and Always On availability group configurations are not supported as migration sources. A primary/secondary switchover causes LSN inconsistency, which prevents DTS from resuming the task and causes it to fail.
  • When migrating across database versions, verify version compatibility first.
• The destination RDS for SQL Server instance must have more available storage than the source database.

Usage notes

• A full data migration consumes read and write resources on both the source and destination databases, increasing their server load. This added load can degrade performance or cause service unavailability, especially if your databases have poor performance, low specifications, or are already busy (for example, with many slow SQL queries, tables without primary keys, or deadlocks in the destination database). Before you start the migration, evaluate the performance of both databases and perform the migration during off-peak hours, for example, when CPU utilization is below 30%.

• If a source table to be migrated lacks a primary key or unique constraint, duplicate data may occur in the destination database.

• DTS automatically creates the destination database in the RDS for SQL Server instance. If the source database name does not comply with RDS naming conventions, create the destination database manually before configuring the task.

  Note

  Naming conventions and creation steps are described in Create a database.

• DTS automatically retries failed tasks. Stop or release the task before you switch workloads to the destination instance. Otherwise, auto-retry may overwrite destination data with source data.

Limitations

• Data of type sql_variant cannot be migrated.
• Schema migration is not supported for assemblies, service broker, full-text indexes, full-text catalogs, distributed schemas, distributed functions, CLR stored procedures, CLR scalar-valued functions, CLR table-valued functions, internal tables, aggregate functions, or system objects.

Migration types

• Schema migration

  DTS migrates schema definitions of selected objects to the destination instance. Supported objects: tables, views, table triggers, synonyms, SQL stored procedures, SQL functions, plan guides, user-defined types, rules, defaults, and sequences.

• Full data migration

  DTS migrates all existing data from selected objects in the source database to the destination RDS for SQL Server instance.

Billing

Database account permissions

To create database accounts and grant the required permissions:

• Self-managed SQL Server: CREATE USER.

• RDS for SQL Server: Create an account.

Migration order

DTS migrates schemas and data in this order to manage dependencies:

1. Migrate the schemas of tables, views, synonyms, user-defined types, rules, defaults, and plan guides.
2. Perform a full data migration.
3. Migrate the schemas of SQL stored procedures, SQL functions, triggers, and foreign keys.

Procedure

1. Log on to the Data Transmission Service (DTS) console.

   Note

   If you are automatically redirected to the Data Management (DMS) console, you can click the icon in the lower-right corner and then click to return to the classic DTS console.

2. In the navigation pane on the left, click Data Migration.

3. At the top of the Migration Tasks page, select the region where the destination instance is located.

4. In the upper-right corner of the page, click Create Data Migration Task.

5. Configure the source and destination databases.

   Category	Parameter	Description
   N/A	Task name	DTS auto-generates a task name. Use a descriptive name for easy identification. Uniqueness is not required.
   Source Database	Instance type	Select the instance type that matches your source database deployment. This topic uses User-Created Database with Public IP Address as an example. Note If your self-managed database is a different instance type, you must perform additional preparations. For more information, see Preparations.
   	Instance region	Instance Region is not required when you select User-Created Database with Public IP Address. Note If your self-managed SQL Server database uses a whitelist, click Get DTS IP CIDR Blocks next to Instance Region to get the DTS server IP addresses. Add them to the database whitelist.
   	Database type	Select SQL Server.
   	Hostname or IP address	Enter the endpoint of the source SQL Server database. In this example, enter a public IP address.
   	Port	Enter the service port of the source SQL Server database. Default: 1433. Note In this example, the service port must be accessible over the public network.
   	Database account	Enter the account for the source SQL Server database. For permissions, see Database account permissions.
   	Database password	Enter the password for the database account. Note After you enter the source database information, you can click Test Connectivity next to Database Password to verify that the information is correct. If the information is correct, the message Passed is displayed. If the message Failed is displayed, click Diagnose next to the Failed message and adjust the source database information based on the prompts.
   Destination Database	Instance type	Select RDS Instance.
   	Instance region	Select the region of the destination RDS instance.
   	RDS instance ID	Select the ID of the destination RDS instance.
   	Database account	Enter the account for the destination RDS instance. For permissions, see Database account permissions.
   	Database password	Enter the password for the database account. Note After you enter the destination database information, you can click Test Connectivity after Database Password to verify that the entered information is correct. If the information is correct, a Passed message is displayed. If a Failed message is displayed, click Diagnose after Failed and adjust the destination database information based on the prompts.

6. After configuration, click Set Whitelist and Next.

   If the source or destination is an Alibaba Cloud database instance, such as ApsaraDB for MySQL or ApsaraDB for MongoDB, DTS automatically adds the IP addresses of its servers in the corresponding region to the instance's whitelist. If the source or destination is a self-managed database on an ECS instance, DTS automatically adds its IP addresses to the security rules of the ECS instance. You must also ensure that the self-managed database does not restrict access from the ECS instance. If the database is a cluster deployed across multiple ECS instances, you must manually add the DTS IP addresses to the security rules for each ECS instance. If the source or destination is a database in an on-premises data center or on another cloud, you must manually add the DTS IP addresses for the corresponding region to allow access from DTS servers. For a list of DTS server IP addresses, see DTS server IP addresses.

   Warning

   Adding public IP addresses of DTS servers, whether automatically or manually, may introduce security risks. By using this product, you acknowledge and accept these potential risks. You are responsible for implementing basic security measures, such as using strong passwords, restricting open ports, using authentication for internal APIs, regularly reviewing and limiting unnecessary network segments, or connecting by using private connections such as Express Connect, VPN Gateway, or Smart Access Gateway.

7. Select the migration types and objects.

   Parameter	Description
   Migration type	For a one-time full data migration, select both Schema Migration and Full Data Migration. For a migration with minimal downtime, select Schema Migration, Full Data Migration, and Incremental Data Migration. For full data migration, select only Schema Migration and Full Data Migration. Note Do not write new data to the source database during migration to ensure data consistency.
   Migration objects	In the Available box, select the objects to migrate, and then click the icon to move them to the Selected Objects box. Note You can select objects at the database, table, or column level. By default, migrated object names remain unchanged. To rename objects, use Object name mapping. Using object name mapping may cause dependent objects to fail migration.
   Object name mapping	To rename migrated objects, use Object name mapping.
   Retry duration upon failure	Default: 12 hours. You can specify a custom duration. If DTS reconnects within this period, the task resumes automatically. Otherwise, the task fails. Note The DTS instance incurs charges during retries. Set a duration based on your needs, or release the instance after the source and destination databases are released.

8. After you complete these settings, click Pre-check and Start in the lower-right corner of the page.

   Note

   • DTS performs a precheck before the task starts. The task can start only after it passes the precheck.

   • If the precheck fails, click the icon next to the failed item to view details.

     • Fix the issues based on the details, and then run the precheck again.

     • For warnings that do not require a fix, you can select Ignore or Ignore and Rerun Precheck to run the precheck again.

9. After the task passes the precheck, click Next.

10. On the Confirm Order page, select a Instance Class and select the Data Transmission Service (Pay-As-You-Go) Terms of Service checkbox.

11. Click Buy and Start to start the migration task.
    Note Do not manually stop the task. Wait for it to complete to avoid data loss.
12. Switch your business to the ApsaraDB RDS for SQL Server instance.

What to do next

Delete the migration accounts from both databases after migration. These read-write accounts pose a security risk if left active.