edit-icon download-icon

Migrate from on-premises SQL Server to ApsaraDB RDS for SQL Server

Last Updated: May 07, 2018

This section introduces how to configure a data migration task from on-premises SQL Server to AparaDB RDS for SQL Server.

Migration types

For data migrations from on-premises SQL Server to ApsaraDB RDS for SQL Server, DTS supports the following migration types: migrate object structures, migrate existing data, and replicate data changes. The features and restrictions of these migration types are as follows:

  • Migrate object structures

    This migrates structure definitions of migrated objects to the target instance. Currently DTS supports the following objects: tables, views, triggers, synonyms, stored procedures, functions, PlanGUID, user defined data types, rules and defaults.

  • Migrate existing data

    This migrates all existing data in the source instance to the target instance. If you migrate the existing data without choosing data change replication, the data changes made to the source instance during the migration will not be synchronized to the target instance.

  • Replicate data changes

    If you also choose to replicate data changes, the data changes made to the source instance during the migration will be synchronized to the target instance.

Migration restrictions

  • Currently the following SQL Server versions support migrations of structures and existing data: SQL Server 2005, 2008, 2008 R2, 2012, and 2016; the following SQL Server versions support migrations of data changes: SQL Server 2008, 2008 R2, 2012, and 2014.
  • If the migrated objects use the object name mapping function, the objects dependent on these objects may fail to be migrated.
  • The sql_variant data type is not supported.
  • Structure migrations does not support migrations of assemblies, database-level stored procedures, Service Broker, full-text indexes, full-text catalogs, distributed functions, distributed functions, CLR scalar-valued functions, CLR table-valued functions, inner tables, aggregate functions, and systems.
  • If you choose all of the three migration types, do not perform DDL operations after the structure migration and before the data change replication; otherwise the migration may fail.
  • Restrictions of data change replication:
    • Does not support migrations of all DDL statements. For details, see the following DDL operations that can be migrated by data change replication section.
    • Does not support Update statements that update only large object data types.
    • Does not support tables containing computed columns.
    • Supports only tables that use the clustered index as the primary key.
    • Each migration task with data change replication supports the migration of only one database. To replicate data changes for multiple databases, create multiple migration tasks.

DDL operations that can be migrated by data change replication

  • CREATE TABLE (Functions, partitions, and default values are not supported.)
  • ALTER TABLE … ADD COLUMN (Default values are not supported.)
  • ALTER TABLE … DROP COLUMN
  • ALTER TABLE … ALTER COLUMN (Default values are not supported.)
  • CREATE INDEX (Index properties are not supported.)
  • SP_RENAME table_name
  • SP_RENAME column_name

Migration permissions

When DTS is used for data migrations from on-premises SQL Server to ApsaraDB RDS for SQL Server, the migration accounts of the source and target instances must have the following permissions:

Instance Object structure migration Existing data migration Data change replication
On-premises SQL Server Select Select sysadmin
Target ApsaraDB RDS for SQL Server instance Read/Write Permission Read/Write Permission Read/Write Permission

Migration process

To improve the success rate of migration from on-premises SQL Server to ApsaraDB RDS for SQL Server, DTS uses the following migration process:

  1. Migrate the structure objects: tables, views, synonyms, user-defined data types, rules, defaults, and PlanGuid.
  2. Migrate existing data.
  3. Migrating the structure objects: stored procedures, functions, triggers, and foreign keys.
  4. Replicate data changes.

For a migration task that does not include data change replication, if the migration progress is Structure migration 100%, data migration 100% and the migration status is Migrating, do not end the task manually because the task is performing the preceding step 3. Otherwise, migration data loss may occur.

Migration procedure

The following describes the procedure of configuring a migration task from on-premises SQL Server to ApsaraDB RDS for SQL Server.

Create a database on the RDS instance

During a data migration, if the database to be migrated does not exist in the target RDS instance, DTS automatically creates one in the target RDS instance. However, in either of the following cases, you need to manually create the database before configuring a migration task:

  • The database name does not comply with the RDS defined specification (that is, the name has a maximum of 64 characters, starts with a letter, ends with a letter or digit, and consists of lowercase letters, digits, underscores and hyphens).
  • The database name on the source instance is different from that on the target instance.

Create migration accounts

When configuring a migration task, you need to provide the migration accounts of the source and target instances. The accounts must have the permissions listed in the preceding Migration permissions section.

  • If the on-premises SQL Server instance does not have a migration account with required permissions, create one by referring to Create a database user .

  • If the target RDS for SQL Server instance does not have a migration account with required permissions, create one by referring to Create an account.

Other preparations

To perform the migration without stopping services, you need to set the log format of the source database to full by running the following two commands in the source database:

  1. alter database database_name set recovery_model_desc=’full’
    database_name is the name of the database to be migrated.
  2. BACKUP LOG database_name to DISK= backup_place WITH init
    database_name is the name of the database to be migrated, and backup_place is the place storing backup files.

Configure a migration task

After the databases and migration accounts are created, configure a migration task as follows:

  1. Log on to Alibaba Cloud DTS console and click Create Migration Task at the upper right corner.
  2. Configure the connection information of the on-premises SQL Server and the target RDS instance.

    • Task name

      By default, DTS generates a task name automatically. The task name is not required to be unique. You can modify it as needed.

    • Source database

      • Instance type: Select On-permises databases
      • Instance region: Select the region closest to the source instance.
      • Database engine: Select SQL Server
      • Host name or IP address: Enter the public network IP address of the on-premises SQL Server instance.
      • Port: Enter the listening port number of the on-premises SQL Server instance.
      • Database account: Enter the username of the migration account of the on-premises SQL Server instance.
      • Database password: Enter the password of the migration account of the on-premises SQL Server instance.
    • Target database

      • Instance type: Select RDS instance
      • Instance region: Select the region where the target instance is located.
      • RDS instance ID: Enter the ID of the target RDS instance. The target RDS instance can be in a classic network or a VPC network.
      • Database account: Enter the username of the migration account of the target RDS instance.
      • Database password: Enter the password of the migration account of the target RDS instance.
  3. Configure the migration types and objects to be migrated.

    • Migration types

      To perform a migration without stopping services , select all the three migration types.

      To migrate only the existing data, select migrate object structure and migrate existing data.

    • Objects to be migrated

      The objects are databases, tables, and columns. By default, after an object is migrated, the object name in the source instance is the same as that in the target instance. If you want them to be different, use the Object name mapping function.

  4. Pre-check

    A pre-check is performed before the migration starts.

    If the pre-check fails, click Failed next to the failure items, solve the problem, and perform the pre-check again.

    Precheck failure

  5. Start the migration task

    If the pre-check succeeds, start the migration task. You can view the migration status and progress in the task list.

    Data change replication is a process of dynamic synchronization. When the data change replication has no latency, verify the data on the target database. If the data is correct, disable the migration task and switch services to the target database.

Thank you! We've received your feedback.