Migrate on-premises SQL Server to RDS SQL Server

Prerequisites

You have created a target RDS for SQL Server instance with storage space greater than that of the source database. If the storage space is insufficient, increase the storage space of the instance in advance.

Precautions

Review these key precautions before migration. Ignoring them can lead to task failures or errors:

• Database count limit: A single migration task supports a maximum of 10 databases. Exceeding this limit introduces stability and performance risks.

• Table count limit: When a task includes incremental migration, the source database cannot contain more than 1,000 tables. Exceeding this limit can cause task delays or instability.

• Source database operation restrictions: During the schema migration and full migration phases, do not perform DDL operations on the database or table schema. Otherwise, the migration task will fail.

• Table schema requirements: Migrated tables must have a primary key or unique constraint, and all column names must be unique. Otherwise, duplicate data can occur in the target database.

• Foreign keys and triggers: For tasks that include incremental migration, you must disable all active triggers and foreign keys in the target database to prevent task failure or data loss.

• Database naming conventions: If the name of a database to be migrated does not comply with the naming conventions for RDS for SQL Server, you must manually create the database in RDS for SQL Server in advance. Otherwise, the task may not run correctly.

• Transaction log retention period: For incremental migration tasks, the transaction logs of the source database must be retained for more than 24 hours. For tasks that include both full and incremental migration, the transaction logs must be retained for at least 7 days. Otherwise, the task may fail or data inconsistency may occur.

Billing

Database account permissions

To complete the data migration, ensure the source and destination database accounts have the following permissions:

Prerequisites

To perform an incremental migration, prepare your self-managed SQL Server database before you configure the data migration task. Set the recovery model of the target database to full to ensure the transaction log records all changes. Then, create a full logical backup and an incremental log backup. These backups are required for the data migration.

1. Run the following command on your self-managed SQL Server database to change the database's recovery model to full.

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

   For example:

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

2. Create a logical backup of the database by running the following command. You can skip this step if you already have a recent logical backup.

   BACKUP DATABASE <database_to_migrate> TO DISK='<path_and_filename_for_backup_file>';
   GO

   For example:

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

3. Back up the database transaction log by running the following command.

   BACKUP LOG <database_to_migrate> to DISK='<path_and_filename_for_backup_file>' WITH init;
   GO

   For example:

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

Procedure

1. Go to the Data Transmission Service (DTS) console.

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

3. Click Create Task and configure the source and destination databases.

   Category	Setting	Description
   N/A	Task Name	Enter a descriptive name to help you identify the task later. The name does not have to be unique. You can also keep the system-generated name.
   Source database information	Select Existing Connection	If you previously saved the source database connection on the DTS Data Connection Management page, you can select it here to avoid re-entering the details.
   	Database Type	Select SQL Server.
   	Connection Type	Select Public IP Address. Note If you select a self-hosted database, you must also complete the required preparations.
   	Instance Region	Select the region where your self-hosted SQL Server database is located.
   	Hostname or IP address	Enter the access address of the self-hosted SQL Server database. In this example, enter a public address.
   	Port	Enter the service port of the self-hosted SQL Server database. The default port is 1433.
   	Database Account	Enter the database account for your self-hosted SQL Server. For more information about permission requirements, see Permission requirements for database accounts.
   	Database Password	Enter the password for the database account.
   	Encryption	If SSL encryption is not enabled for the source database, select Non-encrypted. If SSL encryption is enabled for the source database, select SSL-encrypted. DTS trusts the server certificate by default.
   Destination database information	Select Existing Connection	If you previously saved the destination database connection on the DTS Data Connection Management page, you can select it here to avoid re-entering the details.
   	Database Type	Select SQL Server.
   	Connection Type	Select Cloud instance.
   	Instance Region	Select the region of the destination RDS SQL Server instance.
   	Instance ID	Select the instance ID of the destination RDS SQL Server instance.
   	Database Account	Enter the database account for the destination RDS SQL Server instance. For more information about permission requirements, see Permission requirements for database accounts.
   	Database Password	Enter the password for the database account.
   	Encryption	If SSL encryption is not enabled for the destination database, select Non-encrypted. If SSL encryption is enabled for the destination database, select SSL-encrypted. DTS trusts the server certificate by default.

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

   Important

   Ensure you have added the IP address ranges of DTS servers to the source database's security settings to allow access from DTS.

5. Configure the task objects.

   1. On the Configure Objects page, configure the objects that you want to migrate.

      Setting	Description
      Migration Types	For a full migration, select Schema Migration and Full Data Migration. For a zero-downtime migration, select Schema Migration, Full Data Migration, and Incremental Data Migration. Note For more information, see Appendix 1: SQL operations supported by incremental migration and Appendix 2: Objects supported by schema migration. If you do not select Schema Migration, ensure the destination database already has the required databases and tables. If necessary, use the Selected Objects box to map schema, table, and column names. If you do not select Incremental Data Migration, do not write new data to the source instance during the data migration to ensure data consistency.
      Method to Migrate Triggers in Source Database	Select a trigger migration method. You can skip this setting if the migration does not involve triggers. For more information, see Configure how to synchronize or migrate triggers. Note This option is available only when both Migration Types and Schema Migration are selected for Incremental Data Migration.
      SQL Server Incremental Synchronization Mode	Log-based Parsing for Non-heap Tables and CDC-based Incremental Synchronization for Heap Tables (Hybrid Log-based Parsing) Limitations DTS incremental migration relies on the CDC component. Ensure that the CDC job in the source database is running correctly. Otherwise, the task will fail. By default, incremental CDC data is retained for 3 days. We recommend that you adjust the retention period by running the exec console.sys.sp_cdc_change_job @job_type = 'cleanup', @retention= <time>; command. The <time> parameter is in minutes. If a single table generates more than 10 million incremental SQL changes per day, we recommend that you set the value to 1440. The number of tables with CDC enabled in a single migration task cannot exceed 1,000. Otherwise, delays or instability may occur. The pre-check module enables CDC on the source database. During this process, a brief table lock occurs due to limitations within the SQL Server database kernel. DTS creates the dts_cdc_sync_ddl trigger, the dts_sync_progress heartbeat table, and the dts_cdc_ddl_history DDL storage table in the source database. It also enables database-level CDC and CDC for some tables. Executing SELECT INTO, TRUNCATE, and RENAME COLUMN statements on CDC-enabled tables in the source database is not supported. Do not manually delete the triggers created by DTS in the source database. Benefits Supports source database scenarios such as heap tables, tables without a primary key, compressed tables, and tables with computed columns. Offers a highly stable connection, retrieves complete DDL statements, and supports a wide range of DDL scenarios. Incremental Synchronization Based on Logs of Source Database (Heap tables are not supported) Limitations Migrated tables must have a clustered index that includes the primary key column. This mode does not support heap tables, tables without a primary key, compressed tables, or tables with computed columns. You can run the following SQL statements to check if your source database contains such tables: Check for heap tables in the source database: 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 a primary key: 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 that are not included in the clustered index: 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 in the source database: 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 that contain 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); Check for tables that contain sparse 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_sparse = 1); Benefits This method is non-intrusive to the source database. Polling and querying CDC instances for incremental synchronization Limitations The source database account must have permissions to enable CDC. Database-level CDC requires an account with the sysadmin role. Table-level CDC requires a high-privilege account. Enabling CDC is not supported for tables with clustered columnstore indexes. The pre-check module enables CDC on the source database. During this process, a brief table lock occurs due to limitations within the SQL Server database kernel. The number of source tables to be migrated cannot exceed 1,000. Otherwise, delays or instability may occur. By default, incremental CDC data is retained for 3 days. We recommend that you adjust the retention period by running the exec console.sys.sp_cdc_change_job @job_type = 'cleanup', @retention= <time>; command. The <time> parameter is in minutes. If a single table generates more than 10 million incremental SQL changes per day, we recommend that you set the value to 1440. Consecutive add and drop column operations (more than two DDL operations within one minute) are not supported. Otherwise, the task may fail. Modifying the source CDC instance is not supported. This can cause task failure or data loss. Benefits Supports full and incremental migration from sources like Amazon RDS for SQL Server, Azure SQL Database, Azure SQL Managed Instance, Azure SQL Server on Virtual Machines, and Google Cloud SQL for SQL Server. Uses the native SQL Server CDC component to capture incremental data, which ensures a more stable incremental migration and consumes less network bandwidth. Note This setting is available only if Migration Types is selected as a Incremental Data Migration.
      The maximum number of tables for which CDC is enabled that DTS supports.	Set a reasonable limit for the number of tables for which CDC can be enabled in this migration instance. The default value is 1000. Note This setting is not available when you select SQL Server Incremental Synchronization Mode for Incremental Synchronization Based on Logs of Source Database (Heap tables are not supported).
      Processing Mode of Conflicting Tables	Precheck and Report Errors: Checks if the destination database contains tables with the same names as those in the source. If no such tables exist, the task starts normally. Otherwise, the pre-check fails and the task does not start. Solution: If you cannot delete or rename the conflicting table in the destination database, use schema, table, and column name mapping to change the table's name in the destination. Ignore Errors and Proceed: Skips the check for tables with the same name in the destination database. Warning Selecting Ignore Errors and Proceed can lead to data inconsistency and pose risks to your business. For example: If the table structures are the same and the destination table already contains records with the same primary key values as the source: During full migration, DTS keeps the existing records in the destination table and does not migrate the corresponding records from the source. During incremental migration, new data from the source may overwrite data in the destination table, causing historical data loss. If the table structures are different, only partial data may be migrated, or the migration may fail. Use this option with caution.
      Source Objects	In the Source Objects box, click the objects to migrate, and then click to move them to the Selected Objects box. Note The granularity for selecting migration objects is schema, table, and column. If you select only tables or columns as migration objects, other objects such as views, triggers, and stored procedures are not migrated to the destination database.
      Selected Objects	To change the name of a single migration object in the target instance, right-click the object in the Selected Objects box. For more information, see Map individual schema, table, and column names. To change the names of multiple migration objects in the target instance, click Selected Objects in the upper-right corner of the Batch Edit box. For more information, see Map multiple schema, table, and column names. Note If you use object name mapping, the migration of other objects that depend on the renamed object may fail. To filter data by using a WHERE clause, right-click a table in the Selected Objects box and set the filter conditions in the dialog box that appears. To select SQL operations for migration at the database or table level, right-click an object in the Selected Objects box and choose the desired SQL operations in the dialog box.

   2. Click Next: Advanced Settings to configure advanced parameters.

      Setting	Description
      Dedicated Cluster for Task Scheduling	By default, DTS schedules tasks on a shared cluster. You do not need to select one. If you want more stable tasks, you can purchase a dedicated cluster to run DTS migration tasks.
      Retry Time for Failed Connections	After the migration task starts, if the connection to the source or destination database fails, DTS reports an error and immediately begins to retry the connection. The default retry duration is 720 minutes. You can customize the retry time to a value from 10 to 1440 minutes. We recommend that you set the duration to more than 30 minutes. If DTS reconnects to the source and destination databases within the specified duration, the migration task automatically resumes. Otherwise, the task fails. Note For multiple DTS instances that share the same source or destination, the network retry time is determined by the setting of the last created task. Because you are charged for the task during the connection retry period, we recommend that you customize the retry time based on your business needs, or release the DTS instance as soon as possible after the source and destination database instances are released.
      Retry Time for Other Issues	After the migration task starts, if a non-connectivity issue, such as a DDL or DML execution exception, occurs in the source or destination database, DTS reports an error and immediately begins to retry the operation. The default retry duration is 10 minutes. You can customize the retry time to a value from 1 to 1440 minutes. We recommend that you set the duration to more than 10 minutes. If the related operations succeed within the specified retry duration, the migration task automatically resumes. Otherwise, the task fails. Important The value of Retry Time for Other Issues must be less than the value of Retry Time for Failed Connections.
      Enable Throttling for Full Data Migration	During full migration, DTS consumes read and write resources on the source and destination databases, which may increase the database load. If required, you can enable throttling for the full migration task. You can set Queries per second (QPS) to the source database, RPS of Full Data Migration, and Data migration speed for full migration (MB/s) to reduce the load on the destination database. Note This configuration item is available only if you select Full Data Migration for Migration Types. You can also adjust the full migration speed after the migration instance is running.
      Enable Throttling for Incremental Data Migration	If required, you can also choose to set speed limits for the incremental migration task. You can set RPS of Incremental Data Migration and Data migration speed for incremental migration (MB/s) to reduce the load on the destination database. Note This configuration item is available only if you select Incremental Data Migration for Migration Types. You can also adjust the incremental migration speed after the migration instance is running.
      Environment Tag	You can select an environment tag to label the instance. This is not required for this example.
      Configure ETL	Choose whether to enable the extract, transform, and load (ETL) feature. For more information, see What is ETL? Valid values: Yes: Enables the ETL feature. Enter data processing statements in the code editor. For more information, see Configure ETL in a data migration or data synchronization task. No: Disables the ETL feature.
      Monitoring and Alerting	Select whether to set alerts and receive alert notifications based on your business needs. No: Does not set an alert. Yes: Configure alerts by setting an alert threshold and an alert contact. If a migration fails or the latency exceeds the threshold, the system sends an alert notification.

   3. Click Next: Data Validation to configure a data validation task.

      For more information about the data validation feature, see Configure data validation.

6. Save the task and run a precheck.

   • To view the parameters for configuring this instance when you call the API operation, move the pointer over the Next: Save Task Settings and Precheck button and click Preview OpenAPI parameters in the bubble that appears.

   • If you do not need to view or have finished viewing the API parameters, click Next: Save Task Settings and Precheck at the bottom of the page.

   Note

   • Before the migration task starts, DTS performs a precheck. The task starts only after it passes the precheck.

   • If the precheck fails, click View Details next to the failed check item, fix the issue based on the prompt, and then run the precheck again.

   • If a warning is reported during the precheck:

     • For check items that cannot be ignored, click View Details next to the failed item, fix the issue based on the prompt, and then run the precheck again.

     • For check items that can be ignored, you can click Confirm Alert Details, Ignore, OK, and Precheck Again to skip the alert item and run the precheck again. If you choose to ignore a warning, it may cause issues such as data inconsistency and pose risks to your business.

7. Purchase the instance.

   1. When the Success Rate is 100%, click Next: Purchase Instance.

   2. On the Purchase page, select the link specification for the data migration instance. For more information, see the following table.

      Category	Parameter	Description
      New Instance Class	Resource Group Settings	Select the resource group to which the instance belongs. The default value is default resource group. For more information, see What is Resource Management?
      	Instance Class	DTS provides migration specifications with different performance levels. The link specification affects the migration speed. You can select a specification based on your business scenario. For more information, see Data migration link specifications.

   3. After the configuration is complete, read and select Data Transmission Service (Pay-as-you-go) Service Terms.

   4. Click Buy and Start. In the OK dialog box that appears, click OK.

      You can view the progress of the migration task on the Data Migration Tasks list page.

      Note

      • If the migration task does not include incremental migration, it stops automatically after the full migration is complete. After the task stops, its Status changes to Completed.

      • If the migration task includes incremental migration, it does not stop automatically. The incremental migration task continues to run. While the incremental migration task is running, the Status of the task is Running.

Appendix 1: SQL operations for incremental migration

Appendix 2: Supported objects

• Supported objects: tables, views, table triggers, synonyms, SQL stored procedures, SQL functions, plan guides, user-defined types, rules, defaults, and sequences.

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