Migrate incremental data from a self-managed SQL Server to RDS for SQL Server

Prerequisites

• The source self-managed SQL Server database runs one of the following versions: 2008, 2008 R2, 2012, 2014, 2016, 2017, or 2019.

  Note

  • SQL Server Cluster and Always On availability group (AOAG) configurations are not supported as the migration source. A primary/secondary switchover causes log sequence number inconsistency, which DTS detects as a log discontinuity and fails the task.

  • If you are migrating across different SQL Server versions, verify compatibility in advance.

• Tables to be migrated must have primary keys or UNIQUE NOT NULL indexes.

• The destination RDS for SQL Server instance must have more available storage than the source database occupies.

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%.

• Do not perform frequent backups on the source database during incremental migration. Retain transaction logs for at least three days to prevent truncation.

• DTS adds a heartbeat table to the source database for latency reporting. The table is named in the sourcetable_dts_sqlserver_heartbeat format.

• 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.

• The migration task fails if the destination database contains enabled triggers or foreign keys.

• DTS automatically recovers failed migration tasks. Before switching workloads to the destination instance, stop or release the task to prevent automatic recovery from overwriting destination data.

Limitations

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

• DTS does not migrate data of the sql_variant type.

• DTS does not migrate tables that contain computed columns.

Migration types

• Schema migration

  DTS migrates schema definitions to the destination database. 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 database.

• Incremental data migration

  After the full data migration, DTS synchronizes data changes from the self-managed SQL Server database to the destination database.

SQL operations for incremental migration

• INSERT, UPDATE, and DELETE

  Note

  UPDATE statements that only modify large object (LOB) columns are not supported.

• CREATE TABLE

  Note

  Tables with partitions or function calls in their definitions are not supported.

• ALTER TABLE operations, limited to ADD COLUMN, DROP COLUMN, and RENAME COLUMN

• DROP TABLE

• RENAME TABLE, TRUNCATE TABLE, and CREATE INDEX

Billing

Database account permissions

To create accounts and grant permissions:

• Self-managed SQL Server: CREATE USER.

• RDS for SQL Server: Create an account.

Incremental data migration process

DTS migrates SQL Server schemas and data in the following order to handle object dependencies:

1. Migrates the schemas of tables, views, synonyms, user-defined types, rules, defaults, and plan guides.

2. Performs full data migration.

3. Migrates the schemas of SQL stored procedures, SQL functions, triggers, and foreign keys.

4. Performs incremental data migration.

   Note

   Do not perform DDL operations on migrated objects in the source database before incremental migration starts. Otherwise, the task may fail.

Before you begin

Configure log settings and create a clustered index on your self-managed SQL Server database before configuring the migration task.

1. Run the following command to change the recovery model to Full.

   use master;
   GO
   ALTER DATABASE <database_name> SET RECOVERY FULL WITH ROLLBACK IMMEDIATE;
   GO

   Parameter:

   <database_name>: The name of the database to be migrated.

   Example:

   use master;
   GO
   ALTER DATABASE mytestdata SET RECOVERY FULL WITH ROLLBACK IMMEDIATE;
   GO

2. Run the following command to create a logical backup. Skip this step if a logical backup already exists.

   BACKUP DATABASE <database_name> TO DISK='<physical_backup_device_name>';
   GO

   Parameters:

   • <database_name>: The name of the database to be migrated.

   • <physical_backup_device_name>: The storage path and filename of the backup file.

   Example:

   BACKUP DATABASE mytestdata TO DISK='D:\backup\dbdata.bak';
   GO

3. Run the following command to back up the transaction log of the database.

   BACKUP LOG <database_name> to DISK='<physical_backup_device_name>' WITH init;
   GO

   Parameters:

   • <database_name>: The name of the database to be migrated.

   • <physical_backup_device_name>: The storage path and filename of the backup file.

   Example:

   BACKUP LOG mytestdata TO DISK='D:\backup\dblog.bak' WITH init;
   GO

4. Create a clustered index for each table to migrate. Create Clustered Indexes.

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.

   Section	Parameter	Description
   N/A	Task Name	DTS generates a task name automatically. Specify a descriptive name for easy identification. Uniqueness is not required.
   Source Database	Instance Type	Select the deployment type of the source database. This example uses User-Created Database with Public IP Address. Note If your self-managed database is a different instance type, you must perform additional preparations. For more information, see Preparations.
   	Instance Region	If the instance type is Self-managed database with a public IP address, you do not need to set Instance Region. Note If your self-managed SQL Server uses a whitelist, click Get DTS IP Segment for the Instance Region setting to obtain DTS server IP addresses, and add them to your SQL Server whitelist.
   	Database Type	Select SQL Server.
   	Hostname or IP Address	Enter the endpoint of the self-managed SQL Server database. This example uses a public IP address.
   	Port	Enter the service port of the self-managed SQL Server database. Default: 1433. Note The port must be accessible over the public network in this example.
   	Database Account	Enter the database account of the self-managed SQL Server database. Required permissions are listed in 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 database account of the destination RDS instance. Required permissions are listed in 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. Click Authorize Whitelist and Go to Next Step.

   If the source or destination is an Alibaba Cloud database instance, such as an ApsaraDB RDS for MySQL instance or an ApsaraDB for MongoDB instance, DTS automatically adds the IP addresses of DTS 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 the IP addresses to the security rules of the ECS instance. You must also ensure that the self-managed database does not block access from the ECS instance. If the database is deployed in a cluster across multiple ECS instances, you must manually add the IP addresses of DTS servers to the security rules of each ECS instance. If the source or destination is a self-managed database in a data center or a database from another cloud provider, you must manually add the IP addresses of DTS servers to allow access. For a list of DTS server IP addresses, see IP address segments of DTS servers.

   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 to migrate.

   Parameter	Description
   Migration Types	For full data migration only, select Schema Migration and Full Data Migration. For minimal downtime, select Schema Migration, Full Data Migration, and Incremental Data Migration. This example uses all three. Note If Incremental Data Migration is not selected, do not write new data to the source database during migration to ensure data consistency.
   Migration Objects	In the Migration Objects box, select the objects to migrate and click the icon to move them to the Selected Objects box. Note Objects can be selected at the database, table, or column level. By default, object names in the destination match the source. To rename objects, use Object name mapping. Renaming objects may cause dependent objects to fail migration.
   Edit Mapped Object Name	Rename migrated objects in the destination instance using Object name mapping.
   Retry duration upon a network failure	Default: 12 hours. The task automatically resumes if the connection is restored within this period. Otherwise, it fails. Note You are charged for the DTS instance during the retry period. Set a reasonable duration based on your needs, and release the DTS instance after the source and destination instances 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. The migration task starts.

    • Full data migration

      Do not manually stop the migration task. This can cause data loss. Wait for the task to finish. The task stops automatically.

    • Incremental data migration

      The migration task does not stop automatically. You must stop it manually.

      Note

      Stop the task manually at a suitable time, such as during off-peak hours or when you are ready to switch your business to the destination instance.

      1. Wait until the task status changes to Incremental Data Migration and shows Undelayed. Then, stop writing data to the source database for a few minutes. The status of Incremental Data Migration may show a latency.

      2. Wait until the status of Incremental Data Migration changes back to Undelayed. Then, stop the migration task manually. The progress of schema migration and full data migration both show 100%. To pause the migration task, select the task and click Pause in the batch operations bar at the bottom.

12. Switch your workloads to the destination ApsaraDB RDS for SQL Server instance.

What to do next

For security, delete the migration accounts from both databases after migration completes.