ApsaraDB RDS:Migrate data from a self-managed SQL Server database to an ApsaraDB RDS for SQL Server instance

Billing

Prepare the source database for incremental migration

Skip this section if you are doing full migration only.

If migrating multiple databases, repeat steps 1–3 for each database.

1. Set the recovery model to FULL:

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

   Example:

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

2. Perform a full backup (skip if you have already done this):

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

   Example:

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

3. Back up the transaction log:

   BACKUP LOG <database_name> TO DISK='<backup_file_path>' WITH init;
   GO

   Example:

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

Create and configure the migration task

1. Go to the DTS console.

2. In the left-side navigation pane, click Data Migration, then select the region at the top.

3. Click Create Task. Configure the source and destination database connection information.

   Source database:

   Parameter	Description
   Task Name	Enter a descriptive name for easy identification. Uniqueness is not required.
   Select Existing Connection	If you have saved this source database on the DTS Data Connection Management page, select it here to skip manual entry.
   Database Type	Select SQL Server.
   Access Method	Select Public IP Address. See Preparation overview for additional steps required for self-managed databases.
   Instance Region	Select the region of the self-managed SQL Server database.
   Hostname Or IP Address	Enter the public IP address of the self-managed SQL Server database.
   Port Number	Enter the service port. Default is 1433.
   Database Account	Enter the source database account. See Required permissions.
   Database Password	Enter the password for the database account.
   Encryption	Select Non-encrypted if SSL is not enabled. Select SSL-encrypted if SSL is enabled — DTS trusts the server certificate by default.

   Destination database:

   Parameter	Description
   Select Existing Connection	If you have saved this destination database on the DTS Data Connection Management page, select it here to skip manual entry.
   Database Type	Select SQL Server.
   Access Method	Select Alibaba Cloud Instance.
   Instance Region	Select the region of the destination ApsaraDB RDS for SQL Server instance.
   Instance ID	Select the destination instance ID.
   Database Account	Enter the destination database account. See Required permissions.
   Database Password	Enter the password for the database account.
   Encryption	Select Non-encrypted if SSL is not enabled. Select SSL-encrypted if SSL is enabled — DTS trusts the server certificate by default.

4. Click Test Connectivity and Proceed. In the CIDR Blocks of DTS Servers dialog box, click Test Connectivity.

   Important

   Add the IP address ranges of DTS servers to the source database security settings before testing. Otherwise the connectivity test fails.

5. Configure the objects to migrate. To verify whether the source database has tables incompatible with source log parsing mode, run the following SQL statements: Check for heap tables:

   1. Click Next Step: Data Verification to optionally configure a data verification task. See Configure a data verification task.

   Parameter	Description
   Migration Types	Select the types based on your approach (see Choose a migration approach). For supported operations, see Appendix 1 and Appendix 2. If you do not select Schema Migration, make sure the destination database already contains the required tables and schemas.
   Method to Migrate Triggers in Source Database	Select how to handle triggers. Available only when both Schema Migration and Incremental Data Migration are selected. See Configure the method for synchronizing or migrating triggers.
   SQL Server Incremental Synchronization Mode	Available only when Incremental Data Migration is selected. Choose one of the three modes described below.

   Incremental synchronization mode options:

   Mode	Limits	Benefits
   Hybrid log-based parsing (log-based for non-heap tables, CDC-based for heap tables)	CDC job must run properly; CDC data retained 3 days by default (adjust with exec console.sys.sp_cdc_change_job @job_type = 'cleanup', @retention= <time>; where <time> is in minutes; set to 1440 if a single table exceeds 10 million incremental operations per day); max 1,000 CDC-enabled tables; SELECT INTO, TRUNCATE, and RENAME COLUMN not supported on CDC tables	Supports heap tables, tables without primary keys, compressed tables, and computed columns; high link stability; broad DDL scenario support
   Source log parsing (heap tables not supported)	Tables must have clustered indexes containing primary key columns; does not support heap tables, tables without primary keys, compressed tables, or computed columns	Does not intrude on the source database
   CDC-based polling	Source account requires sysadmin role for database-level CDC or a privileged account for table-level CDC; clustered columnstore indexes not supported; max 1,000 tables; consecutive column add/delete operations not supported	Supports Amazon RDS for SQL Server, Azure SQL Database, Azure SQL Managed Instance, and Google Cloud SQL for SQL Server as sources; uses native CDC for efficient incremental data capture

   Parameter	Description
   The maximum number of tables for which CDC is enabled that DTS supports	Set the maximum number of CDC-enabled tables for this migration instance. Default: 1,000. Not available when using source log parsing mode.
   Processing Mode of Conflicting Tables	Precheck and Report Errors: DTS checks for tables with the same name in the destination. If found, the precheck fails and migration does not start. Rename conflicting destination tables or use object name mapping to resolve. Ignore Errors and Proceed: DTS skips the same-name check. During full migration, existing records in the destination are kept and conflicting source records are skipped. During incremental migration, destination records may be overwritten. Use with caution.
   Source Objects	Select objects and click the right-arrow icon to move them to Selected Objects. You can select columns, tables, or schemas. Selecting tables or columns excludes views, triggers, and stored procedures.
   Selected Objects	Right-click an object to rename it (see Map the name of a single object), set WHERE conditions for data filtering, or select specific SQL operations. Click Batch Edit to rename multiple objects at once (see Map multiple object names at a time). Object name mapping may cause dependent objects to fail migration.

   Parameter	Description
   Dedicated Cluster for Task Scheduling	By default, DTS uses a shared cluster. For higher stability, purchase a dedicated cluster. See What is a DTS dedicated cluster.
   Retry Time for Failed Connections	How long DTS retries after a connection failure. Valid range: 10–1,440 minutes. Default: 720 minutes. Set to more than 30 minutes. If multiple tasks share the same source or destination database, the most recently configured value takes precedence. During retries, you are charged for the DTS instance.
   Retry Time for Other Issues	How long DTS retries after DDL or DML failures. Valid range: 1–1,440 minutes. Default: 10 minutes. Set to more than 10 minutes. Must be less than Retry Time for Failed Connections.
   Enable Throttling for Full Data Migration	Throttle full migration read/write to reduce database load. Configure Queries per second (QPS) to the source database, RPS of Full Data Migration, and Data migration speed for full migration (MB/s). Available only when Full Data Migration is selected.
   Enable Throttling for Incremental Data Migration	Throttle incremental migration. Configure RPS of Incremental Data Migration and Data migration speed for incremental migration (MB/s). Available only when Incremental Data Migration is selected.
   Environment Tag	Tag the DTS instance for identification. Optional.
   Configure ETL	Enable the extract, transform, and load (ETL) feature to transform data during migration. See What is ETL? and Configure ETL.
   Monitoring and Alerting	Configure alerts for task failure or latency exceeding a threshold. See Configure monitoring and alerting.

   SELECT s.name AS schema_name, t.name AS table_name FROM sys.schemas s INNER JOIN sys.tables t ON s.schema_id = t.schema_id AND t.type = 'U' AND s.name NOT IN ('cdc', 'sys') AND t.name NOT IN ('systranschemas') AND t.object_id IN (SELECT object_id FROM sys.indexes WHERE index_id = 0);

   Check for tables without primary keys:

   SELECT s.name AS schema_name, t.name AS table_name FROM sys.schemas s INNER JOIN sys.tables t ON s.schema_id = t.schema_id AND t.type = 'U' AND s.name NOT IN ('cdc', 'sys') AND t.name NOT IN ('systranschemas') AND t.object_id NOT IN (SELECT parent_object_id FROM sys.objects WHERE type = 'PK');

   Check for primary key columns not in clustered index columns:

   SELECT s.name schema_name, t.name table_name FROM sys.schemas s INNER JOIN sys.tables t ON s.schema_id = t.schema_id WHERE t.type = 'U' AND s.name NOT IN('cdc', 'sys') AND t.name NOT IN('systranschemas') AND t.object_id IN ( SELECT pk_colums_counter.object_id AS object_id FROM (select pk_colums.object_id, sum(pk_colums.column_id) column_id_counter from (select sic.object_id object_id, sic.column_id FROM sys.index_columns sic, sys.indexes sis WHERE sic.object_id = sis.object_id AND sic.index_id = sis.index_id AND sis.is_primary_key = 'true') pk_colums group by object_id) pk_colums_counter inner JOIN ( select cluster_colums.object_id, sum(cluster_colums.column_id) column_id_counter from (SELECT sic.object_id object_id, sic.column_id FROM sys.index_columns sic, sys.indexes sis WHERE sic.object_id = sis.object_id AND sic.index_id = sis.index_id AND sis.index_id = 1) cluster_colums group by object_id ) cluster_colums_counter ON pk_colums_counter.object_id = cluster_colums_counter.object_id and pk_colums_counter.column_id_counter != cluster_colums_counter.column_id_counter);

   Check for compressed tables:

   SELECT s.name AS schema_name, t.name AS table_name FROM sys.objects t, sys.schemas s, sys.partitions p WHERE s.schema_id = t.schema_id AND t.type = 'U' AND s.name NOT IN ('cdc', 'sys') AND t.name NOT IN ('systranschemas') AND t.object_id = p.object_id AND p.data_compression != 0;

   Check for tables with computed columns:

   SELECT s.name AS schema_name, t.name AS table_name FROM sys.schemas s INNER JOIN sys.tables t ON s.schema_id = t.schema_id AND t.type = 'U' AND s.name NOT IN ('cdc', 'sys') AND t.name NOT IN ('systranschemas') AND t.object_id IN (SELECT object_id FROM sys.columns WHERE is_computed = 1);

   Continue configuring objects:

6. Run the precheck. Click Next: Save Task Settings and Precheck. DTS runs a precheck before migration starts. If any items fail:

   • Click View Details next to the failed item, troubleshoot the issue, then click Precheck Again.

   • If an alert item is non-critical, click Confirm Alert Details > Ignore > OK > Precheck Again. Ignoring alerts may cause data inconsistency.

   To preview API parameters, hover over Next: Save Task Settings and Precheck and click Preview OpenAPI parameters before proceeding.

7. Purchase the migration instance.

   1. Wait for Success Rate to reach 100%, then click Next: Purchase Instance.

   2. On the Purchase Instance page, configure the instance class:

      Parameter	Description
      Resource Group	The resource group for the migration instance. Default: default resource group. See What is Resource Management?
      Instance Class	Select a class based on required migration speed. See Instance classes of data migration instances.

   3. Accept the Data Transmission Service (Pay-as-you-go) Service Terms by selecting the check box.

   4. Click Buy and Start, then click OK in the confirmation dialog.

Monitor the migration progress on the Data Migration page.

• Full-only migration tasks stop automatically when complete. Status shows Completed.

• Incremental migration tasks run continuously and do not stop automatically. Status shows Running.

Switch traffic to the destination instance

After verifying that data in the destination matches the source:

1. Stop writes to the source database.

2. Wait for the incremental migration to catch up (latency approaches zero).

3. Stop or release the DTS migration task to prevent auto-recovery from overwriting destination data.

4. Update application connection strings to point to the destination ApsaraDB RDS for SQL Server instance.

It is recommended to enable READ_COMMITTED_SNAPSHOT on the source database during full data migration to prevent shared locks from affecting write operations. Otherwise, it may lead to data inconsistency, instance running failure, and other abnormal situations. Failures caused by not enabling this are not covered by the DTS SLA.

Storage space for tables in the destination may be larger than in the source after full migration completes, due to fragmentation from concurrent INSERT operations.

Appendix 1: SQL operations supported for incremental migration

Appendix 2: Objects supported for schema migration

Supported object types: table, view, trigger, synonym, SQL stored procedure, SQL function, plan guide, user-defined type, rule, default, and sequence.

Not supported: assemblies, service brokers, full-text indexes, full-text catalogs, distributed schemas, distributed functions, CLR stored procedures, CLR scalar-valued functions, CLR table-valued functions, internal tables, system tables, and aggregate functions.