SQL Server Migration to SQL Server
UDTS supports full and full+incremental migration tasks between SQL Server 2008 and later versions.
Function Limitations
- Supports single database migration, can migrate the entire database or specified tables, does not support the migration of stored procedures, triggers, views, etc.
- Full+incremental migration does not support DDL.
- During full+incremental migration, the source database and tables to be migrated need to have the cdc feature enabled.
-- Specify database name
use dbname
-- Enable cdc feature
exec sys.sp_cdc_enable_db
-- Check if cdc is enabled for dbname, return value 1 indicates enabled
select is_cdc_enabled from sys.databases where name = "dbname";
-- Enable cdc for dbo.tablename
exec sys.sp_cdc_enable_table @source_schema = 'dbo',@source_name = 'tablename',@role_name = null;
-- Check if cdc is enabled for dbo.tablename, return value 1 indicates enabled
select is_tracked_by_cdc from sys.tables where name = 'tablename'
-- Modify cdc data retention period, change to at least 1440 minutes (1 day) or more, recommended 14400 minutes (7 days).
EXECUTE sys.sp_cdc_change_job @job_type = N'cleanup', @retention = 14400;- The tables to be migrated must have a primary key, and it is recommended to use an int type as the primary key. If the primary key is of string type and the data volume is large, the migration speed will be affected.
- The auto-increment attribute of the primary key in the tables to be migrated cannot be migrated.
- The rowversion field in the tables to be migrated cannot be migrated.
Migration Content
| Migration Content | Description |
|---|---|
| Migration Structure | 1. Database, Table structure (index information currently only migrates the primary key) and data 2. Before starting migration, existing databases and tables in the target will not be cleared. If automatic table creation does not meet the requirements, customers can manually create the tables in the target database in advance. |
| Migration Scope | Only migrates databases and tables that can be checked when creating the task. Tables newly added during the task will not be automatically migrated temporarily. |
| DDL | Not supported |
| DML | insert/update/delete |
Fill in the Form
Data Source Form
| Parameter Name | Description |
|---|---|
| Address | Supports two methods: internal network address and dedicated line address. Internal network address needs to fill in VPC and subnet information; the dedicated line address supports both IP and domain name. If using a domain name, the user’s network requires external network access. |
| Port | SQL Server connection port |
| Username | SQL Server connection username |
| Password | Corresponding user password for the SQL Server database |
| Database Name | SQL Server database name, only supports single database migration |
| Table Name | SQL Server transmission table name, use commas to separate table names. Example: dbo.tablename1,dbo.tablename2, schema can be omitted when it is dbo, only fill in tablename, example: tablename1,tablename2 |
Transmission Target Form
| Parameter Name | Description |
|---|---|
| Address Type | Currently only supports internal network |
| Port | SQL Server connection port |
| Username | SQL Server connection username |
| Password | Corresponding user password for the SQL Server database |