SQL Server Migration to MySQL
UDTS supports full and full+incremental migration tasks from SQL Server 2008 onwards to MySQL 5.5 and later versions.
Functional Limitations
- Supports single database migration, capable of migrating the entire database or specified tables. It does not support migrating stored procedures, triggers, or views.
- Full+incremental migration does not support DDL.
- During full+incremental migration, the source database and the 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 whether cdc is enabled for dbname database, a return value of 1 indicates it is enabled
select is_cdc_enabled from sys.databases where name = "dbname";
-- Enable cdc for dbo.tablename table
exec sys.sp_cdc_enable_table @source_schema = 'dbo',@source_name = 'tablename',@role_name = null;
-- Check whether cdc is enabled for dbo.tablename table, a return value of 1 indicates it is enabled
select is_tracked_by_cdc from sys.tables where name = 'tablename'
-- Modify cdc data retention time, at least change to 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 or unique index, and it is recommended to use type int 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.
- When the target database is MySQL, the schema name of the source database is not migrated. If there are tables with the same name in different schemas in the source database, only one of them will be migrated.
Migration Content
| Migration Content | Description |
|---|---|
| Migration Structure | 1. Database, Table structure, and data 2. Libraries and tables already existing in the target database will not be cleared before the migration begins. If automatic table creation does not meet requirements, customers can manually create tables in the target database in advance. |
| Migration Scope | Only migrates the libraries and tables detectable when the task is created. New tables added during the task run will not be automatically migrated temporarily. |
| DDL | Not supported |
| DML | insert/update/delete |
Fill Out the Form
Source Data Form
| Parameter Name | Description |
|---|---|
| Address | Supports two types: intranet and dedicated line addresses. The intranet address requires filling in VPC and subnet information; the dedicated line address supports both IP and domain name. If using a domain name, the user’s network must have an external network outlet. |
| Port | SQL Server connection port |
| Username | SQL Server connection username |
| Password | Password corresponding to the SQL Server database user |
| Database Name | SQL Server database name, only single database migration is supported |
| Table Name | SQL Server transfer table name. Use a comma to separate table names. Example: dbo.tablename1,dbo.tablename2. If the schema is dbo, it can be omitted, only filling in the table name. Example: tablename1,tablename2 |
Target MySQL Transfer Form
| Parameter Name | Description |
|---|---|
| Address Type | Currently only intranet is supported for the target |
| Port | MySQL connection port |
| Username | MySQL connection username |
| Password | Password corresponding to the MySQL database user |