Migrate SQL Server databases to Alibaba Cloud

Readiness checklist

Before selecting a solution:

1. Check version compatibility. Run the following query on both the source and destination instances to verify that the destination compatibility level is equal to or higher than the source:

      SELECT name, compatibility_level FROM sys.databases;

2. Check network access. Confirm that the source and destination databases accept connections from DTS servers or other migration tools.

3. Assess source database characteristics. Identify heap tables, tables without primary keys, compressed tables, and tables with computed columns. These determine available DTS modes. FAQ on checking table types.

4. Check source edition and version. Some migration modes require specific SQL Server editions and versions (details in the capability matrix below).

Physical backup via Object Storage Service (OSS) (manual)

Upload full and incremental backups to OSS manually, then restore them on the destination instance.

Procedure:

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

2. Create a full backup and upload it to an OSS bucket.

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

4. Stop writes to the source database. After the last incremental log replays successfully, switch workloads to the destination.

If the source database runs SQL Server 2008 R2, upgrade the database version before performing this operation.

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

Physical backup via Data Disaster Recovery and DTS

Use a physical gateway and DTS to automate backup upload and restore.

Procedure:

1. Deploy a physical gateway.

2. Use DTS to migrate data. The system automatically uploads backup data to OSS.

3. Stop writes to the source database. After the last incremental log replays successfully, switch workloads to the destination.

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

DTS logical migration: log parsing mode

DTS reads and parses the transaction logs of the source database to capture changes.

Procedure:

1. Create a DTS data migration task. Set the SQL Server Incremental Synchronization Mode parameter to Incremental Synchronization Based on Logs of Source Database (Heap tables are not supported).

2. Stop writes to the source database. After the last incremental log replays successfully, switch workloads to the destination.

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

DTS logical migration: hybrid log parsing mode

Combines log-based parsing for non-heap tables with Change Data Capture (CDC) for heap tables, expanding the range of migrateable table types.

Procedure:

1. Create a DTS data migration task. Set the SQL Server Incremental Synchronization Mode parameter to Log-based Parsing for Non-heap Tables and CDC-based Incremental Synchronization for Heap Tables (Hybrid Log-based Parsing).

2. Stop writes to the source database. After the last incremental log replays successfully, switch workloads to the destination.

DTS logical migration: CDC instance polling mode

Uses SQL Server's native CDC component to capture incremental changes from change tables rather than transaction logs. This provides more stable migration with lower bandwidth, unaffected by log truncation.

Procedure:

1. Create a DTS data migration task. Set the SQL Server Incremental Synchronization Mode parameter to Polling and querying CDC instances for incremental synchronization.

2. Stop writes to the source database. After the last incremental log replays successfully, switch workloads to the destination.

SSMS migration

Export and import data manually using SSMS.

Procedure:

1. Stop writes to the source database.

2. Export data from the source database using SSMS.

3. Import the exported data into the destination database.

4. Verify data consistency, then switch workloads to the destination.

Reference: Use SSMS to migrate data to the cloud

OSS manual (physical backup)

• Requires manual backup and upload of logs.

• Migrates only one database at a time.

• Service interruption of several minutes during the final switchover (stop writes, wait for last incremental log replay).

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

• Name mapping for databases, tables, and columns is not supported.

Data Disaster Recovery + DTS (physical backup)

• The backup log file extension must be bak.

• Service interruption of several minutes during the final switchover.

• AliyunDBSAgent must be installed on the source database server.

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

• Name mapping for databases, tables, and columns is not supported.

Log parsing mode (logical)

• Only some DDL statements can be migrated. More than 100 DDL statements per hour affects migration speed.

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

• If log backup frequency exceeds once per hour, DTS may fail to obtain local backup logs. Retain backup logs for three days on local disks.

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

• Disable FOREIGN KEY constraints during incremental data migration. Otherwise, the task may fail.

• Heap tables, tables without primary keys, compressed tables, and tables with computed columns cannot be migrated. FAQ on checking table types.

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

• DTS uses the fn_log function to pull and parse logs. This function is not stable. Unexpected operations may cause the task to fail.

• Maximum 10 databases per data migration task. Exceeding this limit may cause stability and performance issues.

Hybrid log parsing mode (logical)

• Source edition requirement: SQL Server 2008 or later (Enterprise Edition), or SQL Server 2016 SP1 or later (Standard Edition). SQL Server 2017 is not supported.

• Only some DDL statements can be migrated. More than 100 DDL statements per hour affects migration speed.

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

• If log backup frequency exceeds once per hour, DTS may fail to obtain local backup logs. Retain backup logs for three days on local disks.

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

• Disable FOREIGN KEY constraints during incremental data migration. Otherwise, the task may fail.

• Tables with computed columns cannot be migrated. FAQ on checking table types.

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

• DTS uses the fn_log function to pull and parse logs. This function is not stable. Unexpected operations may cause the task to fail.

• Maximum 10 databases per data migration task. Exceeding this limit may cause stability and performance issues.

CDC instance polling mode (logical)

• Source edition requirement for Azure VMs: SQL Server 2008 or later (Enterprise Edition), or SQL Server 2016 SP1 or later (Standard Edition). SQL Server 2017 is not supported.

• Account permissions: The DTS access account must have permission to enable database-level and table-level CDC. Database-level CDC requires an account with the sysadmin role. Table-level CDC requires a privileged account.

  • Azure SQL Database: A server administrator account has the required permissions. CDC can be enabled for all databases purchased under the vCore model. For the DTU model, databases must have a service tier of S3 or higher.

  • Amazon RDS for SQL Server: A privileged account has the required permissions. CDC can be enabled for stored procedures at the database level.

  • CDC cannot be enabled for clustered columnstore indexes.

  • The DTS pre-module enables CDC in the source database. This process causes table locks lasting a few seconds due to SQL Server limitations.

• Maximum 1,000 tables per task. Exceeding this limit may cause delays or instability.

• Maximum 10 databases per data migration task. Exceeding this limit may cause stability and performance issues.

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

• Incremental data migration has a latency of approximately 10 seconds.

• Do not add or remove columns via DDL more than twice within a minute. Otherwise, the data migration task may fail.

• Do not modify the CDC instances of the source database during migration. Otherwise, the task may fail or data loss may occur.

• Migrating multiple tables across multiple databases in a single task may cause stability and performance issues.

SSMS migration

• Requires stopping writes to the source database before migration. Otherwise, data inconsistency may occur.

• All migration steps must be performed manually through SSMS.

Self-managed SQL Server

This approach combines physical backup speed with DTS console convenience, and supports multi-database migration in a single task. 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

Both offer guided workflows with minimal manual steps. Migrate data from a SQL Server database on Microsoft Azure to ApsaraDB RDS for SQL Server.

Azure SQL Managed Instance and SQL Server on Azure Virtual Machines

Amazon RDS for SQL Server

Migrate data from an Amazon RDS for SQL Server instance to an ApsaraDB RDS for SQL Server instance.

Huawei Cloud RDS for SQL Server

Migrate the full backup data of a self-managed SQL Server instance to an ApsaraDB RDS instance that runs SQL Server 2008 R2 with cloud disks or runs SQL Server 2012 or later.

• If the source database runs SQL Server 2008 R2, upgrade the database version before performing this operation.

• For instructions on obtaining backup data, see Creating a Manual Backup and Downloading a Backup File.

TencentDB for SQL Server

With incremental support:

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

Without incremental support: Use SSMS migration.

Google Cloud SQL for SQL Server

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

Verify data integrity

After migration, verify that all data transferred correctly to the destination instance.

-- Source database
SELECT TOP 10 OrderID, OrderDate, CustomerID, TotalAmount
FROM Orders
ORDER BY OrderDate DESC;

-- Destination database
SELECT TOP 10 OrderID, OrderDate, CustomerID, TotalAmount
FROM Orders
ORDER BY OrderDate DESC;

Update database statistics

Query performance on the destination instance may decrease due to changes in data distribution. Update all statistics in the migrated databases to restore performance. Update database statistics.