This topic describes how to migrate data from a self-managed SQL Server database to an ApsaraDB RDS for SQL Server instance by using Data Transmission Service (DTS).
Prerequisites
- The version of the self-managed SQL Server database is supported by DTS. For more information, see Overview of data migration scenarios.
- The destination ApsaraDB RDS for SQL Server instance is created. For more information, see Create an ApsaraDB RDS for SQL Server instance.
- The available storage space of the ApsaraDB RDS for SQL Server instance is larger than the total size of the data in the self-managed SQL Server database.
- If the source database is in the following conditions, we recommend that you migrate data by using the backup feature of ApsaraDB RDS for SQL Server. For more information, see Migrate data from a self-managed database to ApsaraDB RDS for SQL Server.
Limits
Category | Description |
---|---|
Limits on the source database |
|
Other limits |
|
Special cases | If the destination instance is an ApsaraDB RDS for SQL Server instance, take note
of the following limits:
DTS automatically creates a destination database in the ApsaraDB RDS for SQL Server instance. However, if the name of the source database is invalid, you must create a database in the ApsaraDB RDS for SQL Server instance before you configure the data migration task. For more information, see Create a database on an ApsaraDB RDS for SQL Server instance. |
Migration types
- Schema migration
DTS migrates the schemas of the required objects from the source database to the destination database.
- DTS supports schema migration for the following types of objects: table, view, trigger, synonym, SQL stored procedure, SQL function, plan guide, user-defined type, rule, default, and sequence.
- DTS does not migrate the schemas of 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, systems, or aggregate functions.
- Full data migration
DTS migrates historical data of the required objects from the source database to the destination database.
- Incremental data migration
After full data migration is completed, DTS migrates incremental data from the source database to the destination database. Incremental data migration allows you to ensure service continuity when you migrate data between self-managed databases.
SQL operations that can be migrated
Operation type | SQL statements |
---|---|
DML | INSERT, UPDATE, and DELETE
Note If an UPDATE operation updates only the large fields, DTS does not migrate the operation.
|
DDL |
Note For source instances in mixed log-based parsing mode, all common DDL operations can
be migrated.
|
Permissions required for database accounts
Database | Schema migration | Full data migration | Incremental data migration |
---|---|---|---|
Self-managed SQL Server database | The SELECT permission | The SELECT permission | The permissions of the sysadmin role |
ApsaraDB RDS for SQL Server instance | The read and write permissions |
For more information about how to create and authorize a database account, see the following topics:
- Self-managed SQL Server databases: CREATE USER
- ApsaraDB RDS for SQL Server instance: Create an account for an RDS SQL Server instancy and Modify the permissions of a standard account on an ApsaraDB RDS for SQL Server instance
Data migration process
To prevent data migration failures caused by dependencies among objects, DTS migrates the schemas and data from the source SQL Server database in the following order:- Migrate the schemas of tables, views, synonyms, user-defined types, rules, defaults, and plan guides.
- Perform full data migration.
- Migrate the schemas of SQL stored procedures, SQL functions, triggers, and foreign keys.
- Perform incremental data migration.
Before you begin
- Run the following command in the self-managed SQL Server database to change the recovery
model to full:
Parameters:use master; GO ALTER DATABASE <database_name> SET RECOVERY FULL WITH ROLLBACK IMMEDIATE; GO
<database_name>: the name of the source database.
Example:use master; GO ALTER DATABASE mytestdata SET RECOVERY FULL WITH ROLLBACK IMMEDIATE; GO
- Run the following command to create a logical backup for the source database. Skip
this step if you have already created a logical backup.
Parameters:BACKUP DATABASE <database_name> TO DISK='<physical_backup_device_name>'; GO
- <database_name>: the name of the source database.
- <physical_backup_device_name>: the storage path and file name of the backup file.
BACKUP DATABASE mytestdata TO DISK='D:\backup\dbdata.bak'; GO
- Run the following command to back up the log entries of the source database:
Parameters:BACKUP LOG <database_name> to DISK='<physical_backup_device_name>' WITH init; GO
- <database_name>: the name of the source database.
- <physical_backup_device_name>: the storage path and file name of the backup file.
BACKUP LOG mytestdata TO DISK='D:\backup\dblog.bak' WITH init; GO