Data Transmission Service:Migrate SQL Server databases to Alibaba Cloud

Solution

Usage notes

References

Physical cloud migration

Use Object Storage Service (OSS) to manually migrate data

1. Set the backup_type parameter of the source database to FULL.

2. Back up full data and upload the backup data to an OSS bucket.

3. Back up and upload incremental logs on a scheduled basis.

4. Stop writing data to the source database. After the last incremental log is successfully replayed, switch the workloads to the new database.

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

Use Database Backup (DBS) and Data Transmission Service (DTS) to migrate data with a few clicks

1. Deploy a physical protocol gateway.

2. Use DTS to migrate data to the cloud. The system calls an API operation of OSS to upload the backup data to an OSS bucket.

3. Stop writing data to the source database. After the last incremental log is successfully replayed, switch the workloads to the new database.

Migrate data from a self-managed SQL Server database to an ApsaraDB RDS for SQL Server instance by using a physical gateway

Logical cloud migration

Use DTS to migrate data in log parsing mode

1. Use DTS to migrate data to the cloud.

   Note

   Set the SQL Server Incremental Synchronization Mode parameter to Incremental Synchronization Based on Logs of Source Database (Heap tables are not supported).

2. Stop writing data to the source database. After the last incremental log is successfully replayed, switch the workloads to the new database.

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

Use DTS to migrate data in hybrid log parsing mode

1. Use DTS to migrate data to the cloud.

   Note

   Set the SQL Server Incremental Synchronization Mode parameter to Log parsing incremental synchronization for non-heap tables and CDC incremental synchronization for heap tables (Hybrid Log parsing).

2. Stop writing data to the source database. After the last incremental log is successfully replayed, switch the workloads to the new database.

Other

Use SQL Server Management Studio (SSMS) to migrate data

1. Stop writing data to the source database.

2. Use SSMS to export the data of the source database.

3. Use SSMS to import the exported data to the destination database.

4. After data consistency is verified, switch the workloads to the new database.

Use SSMS to migrate data to the cloud

Solution

Benefit

Limit

Physical cloud migration

Use OSS to manually migrate data

• The backup and recovery features provided by SQL Server support a large number of scenarios.

• The migration speed is fast.

• You can manually change the name of the backup file based on the format of the log file name of the source database.

• You must manually back up and upload logs, which is cumbersome.

• You can migrate only one database at a time.

• You must stop writing data to the source database and wait until the last incremental log is successfully replayed before you switch the workloads to the new database. Therefore, service may be interrupted for several minutes.

• The version of the destination database cannot be earlier than that of the source database.

• The name mapping feature for databases, tables, and columns is not supported.

Use DBS and DTS to migrate data with a few clicks

• The migration operations are performed in the DTS console. The configurations and operations are simple and convenient.

• You can migrate multiple databases at a time.

• The backup and recovery features provided by SQL Server support a large number of scenarios.

• The migration speed is fast.

• The extension of the backup log file name must be bak.

• You must stop writing data to the source database and wait until the last incremental log is successfully replayed before you switch the workloads to the new database. Therefore, service may be interrupted for several minutes.

• You must install AliyunDBSAgent on the server of the source database.

• The version of the destination database cannot be earlier than that of the source database.

• The name mapping feature for databases, tables, and columns is not supported.

Logical cloud migration

Use DTS to migrate data in log parsing mode

• The migration operations are performed in the DTS console. The configurations and operations are simple and convenient.

• You can migrate data between databases of different versions.

• The name mapping feature for databases, tables, and columns is supported.

• When you migrate incremental data by using DTS, you do not need to stop writing data to the source database. Service continuity is ensured.

• You can migrate data from a third-party cloud to Alibaba Cloud.

  For example, you can migrate full data from an Amazon RDS for SQL Server database to Alibaba Cloud and migrate full and incremental data from an Azure SQL Server database in NoSQL mode to Alibaba Cloud.

• Only some DDL statements can be migrated. If more than 100 DDL statements are executed per hour, the migration speed is affected.

• If the speed of log write in the source database exceeds 10 MB/s, 30 GB/hour, or 500 GB/day, the task may be delayed or even fail.

• If the frequency of log backup in the source database exceeds one time per hour, DTS may fail to obtain local backup logs. We recommend that you retain backup logs for three days on local disks.

• DTS creates a trigger and DDL storage table in the source database to capture DDL changes.

• You must disable FOREIGN KEY constraints during incremental data migration. Otherwise, the task may fail.

• Heap tables, tables without primary keys, compressed tables, and tables that contain computed columns cannot be migrated.

  Note

  For information about how to check whether an SQL Server database contains these tables, see the How do I view information about heap tables, tables without primary keys, compressed tables, and tables that contain computed columns in an SQL Server database? section of the "FAQ" topic.

• Tables without PRIMARY KEY or UNIQUE constraints may contain duplicate data. If you want to retain tables without PRIMARY KEY or UNIQUE constraints, we recommend that you do not use this solution.

• DTS uses the fn_log function to pull and parse logs. This function is not stable. If unexpected operations occur, the task may fail.

• You can migrate no more than 10 databases in a single data migration task. Otherwise, stability and performance issues may occur.

Use DTS to migrate data in hybrid log parsing mode

• The migration operations are performed in the DTS console. The configurations and operations are simple and convenient.

• You can migrate data between databases of different versions.

• The name mapping feature for databases, tables, and columns is supported.

• Heap tables, tables without primary keys, and compressed tables can be migrated.

• You can migrate data from a third-party cloud to Alibaba Cloud.

  For example, you can migrate full data from an Amazon RDS for SQL Server database to Alibaba Cloud and migrate full and incremental data from an Azure SQL Server database in NoSQL mode to Alibaba Cloud.

• The source SQL Server database must be SQL Server 2008 or later of Enterprise Edition, or SQL Server 2016 SP1 or later of Standard Edition, excluding SQL Server 2017.

• Only some DDL statements can be migrated. If more than 100 DDL statements are executed per hour, the migration speed is affected.

• If the speed of log write in the source database exceeds 10 MB/s, 30 GB/hour, or 500 GB/day, the task may be delayed or even fail.

• If the frequency of log backup in the source database exceeds one time per hour, DTS may fail to obtain local backup logs. We recommend that you retain backup logs for three days on local disks.

• DTS enables change data capture (CDC) for databases and some tables and creates a trigger and DDL storage table in the source database to capture DDL changes.

• You must disable FOREIGN KEY constraints during incremental data migration. Otherwise, the task may fail.

• Tables that contain computed columns cannot be migrated.

  Note

  For information about how to check whether an SQL Server database contains these tables, see the How do I view information about heap tables, tables without primary keys, compressed tables, and tables that contain computed columns in an SQL Server database? section of the "FAQ" topic.

• Tables without PRIMARY KEY or UNIQUE constraints may contain duplicate data. If you want to retain tables without PRIMARY KEY or UNIQUE constraints, we recommend that you do not use this solution.

• DTS uses the fn_log function to pull and parse logs. This function is not stable. If unexpected operations occur, the task may fail.

• You can migrate no more than 10 databases in a single data migration task. Otherwise, stability and performance issues may occur.

Other

Use SSMS to migrate data

The operations are simple, the process is stable, and the risk of data inconsistency is low.

• You must stop writing data to the source database. Otherwise, data inconsistency may occur.

• You must manually perform the migration by using SSMS.

Data source

Whether incremental data migration is supported

Supported solution

Recommended solution

Self-managed SQL Server database

Yes

• Use OSS to manually perform physical cloud migration

• Use DBS and DTS to perform physical cloud migration with a few clicks

• Use DTS to perform logical cloud migration

Use DBS and DTS to perform physical cloud migration with a few clicks. For more information, see Migrate data from a self-managed SQL Server database to an ApsaraDB RDS for SQL Server instance by using a physical gateway.

Azure SQL Database

No

• Use SSMS to perform cloud migration

• Use DTS to perform logical cloud migration for full data migration

  Note

  Incremental data cannot be migrated because the Database Console Commands (DBCC) commands are not available during incremental data migration.

Use SSMS to perform cloud migration. For more information, see Use SSMS to migrate data to the cloud.

Azure SQL Managed Instance

Yes

• Use DTS to perform logical cloud migration

• Use SSMS to perform cloud migration

Use DTS to perform logical cloud migration. For more information, see Migrate data from a self-managed SQL Server database to an ApsaraDB RDS for SQL Server instance.

Azure SQL Server (logical server)

Amazon RDS for SQL Server

No

• Use SSMS to perform cloud migration

• Use DTS to perform logical cloud migration for full data migration

  Note

  Incremental data cannot be migrated because the required sysadmin role of Amazon RDS for SQL Server databases is unavailable.

Use DTS to perform logical cloud migration. For more information, see Migrate data from a self-managed SQL Server database to an ApsaraDB RDS for SQL Server instance.

Huawei Cloud RDS for SQL Server

No

• Use SSMS to perform cloud migration

• Use DTS to perform logical cloud migration for full data migration

• Use OSS to manually perform physical cloud migration for full data migration

Use OSS to manually perform physical cloud migration for full data migration. For more information, see Migrate the full backup data of a self-managed SQL Server instance to an ApsaraDB RDS instance.

TencentDB for SQL Server database

Yes

• Use SSMS to perform cloud migration

• Use DTS to perform logical cloud migration

• Use OSS to manually perform physical cloud migration

Use DTS to perform logical cloud migration. For more information, see Migrate data from a self-managed SQL Server database to an ApsaraDB RDS for SQL Server instance.