Synchronize RDS SQL Server to SelectDB

Type

Description

Source database limitations

• Tables for synchronization must have a primary key or unique constraint. Otherwise, duplicate data may occur in the destination database.

• If a data synchronization task synchronizes more than 5,000 tables at the table level and requires object edits, such as mapping column names, split the tables into multiple tasks.

• A single data synchronization task supports a maximum of 10 databases. If you exceed this limit, you risk stability and performance issues. In this case, split the tables into multiple tasks.

• If you configure a task to synchronize specific objects instead of an entire database, you cannot synchronize objects that have the same table name but different schema names to the same destination database within that task.

• Data Transmission Service (DTS) uses the fn_log function to obtain logs from the source database. This function has performance bottlenecks. Do not clean up source database logs prematurely. Otherwise, the task may fail.

• Transaction logs:

  • Enable transaction logs, set the recovery model to Full, and make sure that a full physical backup has been completed.

  • For an incremental data synchronization task, DTS requires that transaction logs in the source database are retained for at least 24 hours. For a task that includes both full data synchronization and incremental data synchronization, transaction logs must be retained for at least 7 days. After the full data synchronization is complete, you can change the retention period to 24 hours or more. If DTS cannot obtain the transaction logs because the retention period is shorter than required, the task may fail. In extreme cases, this can lead to data inconsistency or loss. Issues that arise from a log retention period shorter than the DTS requirement are not covered by the service level agreement (SLA).

• To enable Change Data Capture (CDC) for tables in the source database, you must meet the following conditions. Otherwise, the precheck fails.

  • The value of the srvname field in the sys.sysservers view must be the same as the return value of the SERVERPROPERTY function.

  • If the source database is a self-managed SQL Server, the database owner must be sa. If the source database is an ApsaraDB RDS for SQL Server instance, the database owner must be sqlsa.

  • If the source database is Enterprise Edition, it must be SQL Server 2008 or later.

  • If the source database is Standard Edition, it must be SQL Server 2016 SP1 or later.

  • If the source database is SQL Server 2017 (Standard or Enterprise Edition), upgrade it to a later version.

• If the source database is a read-only instance, DDL operations are not synchronized.

• If the source database is an Azure SQL Database, a single synchronization instance can synchronize data from only one database.

• If the source database is an ApsaraDB RDS for SQL Server instance, disable the Transparent Data Encryption (TDE) feature to ensure the stability of the synchronization instance. For more information, see Disable TDE.

• In hybrid log parsing mode, you cannot perform multiple operations to add or drop columns in quick succession (less than 10 minutes apart). For example, running the following SQL statements consecutively will cause the task to fail.

  ALTER TABLE test_table DROP COLUMN Flag;
  ALTER TABLE test_table ADD Remark nvarchar(50) not null default('');

• If you use the sp_rename command to modify the names of objects, such as stored procedures, in the source database before the schema synchronization task runs, the task may produce unexpected results or fail.

  Note

  We recommend that you use the ALTER command to rename objects in the database.

• Do not run DDL operations that change database or table schemas during schema synchronization or full synchronization. Otherwise, the synchronization task fails.

  Note

  During full synchronization, DTS queries the source database. This creates metadata locks that may block DDL operations on the source database.

• If the source database is a Web Edition of ApsaraDB RDS for SQL Server, you must select Incremental Synchronization Based on Logs of Source Database (Heap tables are not supported) for SQL Server Incremental Synchronization Mode when you configure the task.

• We recommend that you keep the READ_COMMITTED_SNAPSHOT transaction processing mode parameter for the source database enabled during a full data synchronization task to prevent shared locks from affecting data writes. Otherwise, issues such as data inconsistency and instance failures may occur. Any resulting exceptions are not covered by the DTS SLA.

Other limitations

• DTS currently supports synchronizing data only to Unique engine tables in a SelectDB instance. Make sure that all unique keys in the target table also exist in the source table and are included in the synchronization object. Otherwise, data inconsistency may occur.

• Requirements for synchronization objects:

  • Schema initialization is supported for database, schema, and table objects.

  • DTS does not support synchronizing the following data types: CURSOR, ROWVERSION, SQL_VARIANT, HIERARCHYID, POLYGON, GEOMETRY, GEOGRAPHY, and user-defined types created by using the CREATE TYPE statement.

  • DTS does not support synchronizing the following object types: PROCEDURE, FUNCTION, TRIGGER, DATATYPE, SYNONYM, CATALOG, PLAN_GUIDE, or SEQUENCE.

• If you select Incremental Synchronization Based on Logs of Source Database (Heap tables are not supported) for SQL Server Incremental Synchronization Mode in the Configure Objects step, the synchronized tables must have a clustered index that includes the primary key columns. Heap tables, tables without primary keys, compressed tables, tables with computed columns, and tables with sparse columns are not supported. These limitations do not apply in hybrid log parsing mode.

  Note

  For information about how to check for these table types, see How to view information about heap tables, tables without primary keys, compressed tables, tables with computed columns, and tables with sparse columns in SQL Server.

• If you select Log-based Parsing for Non-heap Tables and CDC-based Incremental Synchronization for Heap Tables (Hybrid Log-based Parsing) for SQL Server Incremental Synchronization Mode in the Configure Objects step, the following limitations also apply:

  • Incremental data synchronization in DTS depends on the CDC component. Ensure that the CDC jobs in the source database are running correctly. Otherwise, the DTS task fails.

  • By default, the CDC component stores incremental data for 3 days. You can use the exec console.sys.sp_cdc_change_job @job_type = 'cleanup', @retention= <time>; command to adjust the retention period as needed.

    Note

    • <time> specifies the time in minutes.

    • If a single table in the source database generates more than 10 million incremental change SQL statements per day on average, we recommend that you set <time> to 1440.

  • The DTS precheck for an incremental data synchronization task enables CDC on the source database and tables. Due to limitations in the SQL Server database engine, this process can cause brief locking on the source database.

  • In a single data synchronization task, enable CDC on no more than 1,000 tables. Exceeding this limit may cause task latency or instability.

• If you select Polling and querying CDC instances for incremental synchronization for SQL Server Incremental Synchronization Mode in the Configure Objects step, the following limitations also apply:

  • The source database account used by the DTS instance must have permissions to enable CDC. An account with the sysadmin role is required to enable database-level CDC. A high-privilege account is required to enable table-level CDC.

    Note

    • The highest-privilege account (server administrator) provided in the Azure SQL Database console meets this requirement. For databases using the vCore-based purchasing model, all service tiers support CDC. For databases using the DTU-based purchasing model, the service tier must be S3 or higher to support CDC.

    • The high-privilege account for Amazon RDS for SQL Server meets this requirement and supports enabling database-level CDC for stored procedures.

    • CDC is not supported for tables with clustered columnstore indexes.

    • The DTS precheck for an incremental data synchronization task enables CDC on the source database and tables. Due to limitations in the SQL Server database engine, this process can cause brief locking on the source database.

  • DTS polls the CDC instance for each table in the source database to obtain incremental data. Therefore, synchronize no more than 1,000 tables. Otherwise, the task may experience latency or instability.

  • Incremental data stored by the CDC component is retained for 3 days by default. We recommend that you run the exec console.sys.sp_cdc_change_job @job_type = 'cleanup', @retention= <time>; command to set a proper retention period.

  Note

  • <time> specifies the time in minutes.

  • If the average daily number of incremental change SQL statements for a single table in the source database exceeds 10 million, we recommend setting <time> to 1440.

  • Performing consecutive DDL operations to add or drop columns (more than two such operations within one minute) is not supported and may cause the task to fail.

  • Modifying the CDC instance in the source database is not supported and may cause task failure or data loss.

• Complex DDL operations are not supported for DDL synchronization.

• To ensure accurate latency monitoring for incremental data synchronization, DTS creates specific objects in the source database based on the selected mode. In log parsing mode, DTS creates the dts_cdc_sync_ddl trigger, the dts_sync_progress heartbeat table, and the dts_cdc_ddl_history DDL history table. In hybrid mode, DTS creates the dts_cdc_sync_ddl trigger, the dts_sync_progress heartbeat table, and the dts_cdc_ddl_history DDL history table, and also enables CDC at the database level and for some tables. The rate of data changes on tables with CDC enabled should not exceed 1,000 RPS.

• When you set parameters in the Selected Objects section, you can currently set only the bucket_count (number of buckets) parameter.

  Note

  The value of bucket_count must be a positive integer. The default value is auto.

• During data synchronization, do not create a new cluster in the target SelectDB instance. Otherwise, the task fails. You can try to restart the synchronization instance to recover the task.

• SelectDB supports only database and table names that start with a letter. If the name of a database or table to be synchronized does not start with a letter, you must use the mapping feature to change it.

• If the name of a synchronization object (database, table, or column) contains Chinese characters, you must use the mapping feature to change it (for example, to an English name). Otherwise, the task may fail.

• DTS does not support DDL operations that modify multiple columns at once or perform consecutive modifications on the same table.

• During data synchronization, do not add BE (Backend) nodes to the SelectDB database. Otherwise, the task fails. You can try to restart the synchronization instance to recover the task.

• Before you start a data synchronization task, evaluate the performance of the source and destination databases. Also, run the task during off-peak hours. Full data initialization consumes read and write resources on both the source and destination databases, which can increase the database load.

• Full data initialization performs concurrent INSERT operations, which can cause fragmentation in the tables of the destination database. As a result, the table space in the destination instance may be larger than in the source instance after initialization.

• During DTS synchronization, do not allow any data writes to the target database from sources other than DTS. This can cause data inconsistency between the source and target databases.

• Do not rebuild indexes during synchronization. This can cause task failure or even data loss.

  Note

  You cannot perform DDL operations related to the primary key on a table for which CDC is enabled.

• If the number of tables with CDC enabled in a single data synchronization task exceeds the The maximum number of tables for which CDC is enabled that DTS supports., the precheck fails.

• If you need to write more than 64 KB of data to a single field in a CDC-enabled table, you must use the exec sp_configure 'max text repl size', -1; command to adjust the configuration of the source database in advance.

  Note

  By default, the maximum size of a single field that a CDC job can process is 64 KB.

• If a DDL write to the target database fails, the DTS task continues to run. You need to check the task log for the failed DDL statements. For more information about how to view task logs, see View Task Logs.

• If you use the Modify Objects to be Synchronized feature, you cannot remove databases from the task.

• Multiple synchronization instances that use the same SQL Server database as a source have independent incremental data capture modules.

• During incremental synchronization, DTS uses a batch-and-flush strategy, writing data at most every 5 seconds by default. In normal cases, synchronization latency is usually within 10 seconds. You can adjust this by modifying the selectdb.reservoir.timeout.milliseconds parameter. The value can range from 1,000 to 10,000 milliseconds.

• If a task fails, DTS support staff will attempt to restore it within eight hours. During restoration, they may restart the task or adjust its parameters.

  Note

  Only DTS task parameters are modified—not database parameters. Parameters that may be adjusted include those listed in Modify instance parameters.

• SQL Server is a commercial, closed-source database. Due to known or unknown characteristics of its log format, issues can arise during incremental change data capture (CDC) and parsing by DTS. Before using DTS for incremental synchronization or migration from a SQL Server source in a production environment, perform comprehensive proof-of-concept (POC) testing. Your tests should cover all business change scenarios, schema modifications, and peak-load stress tests. Ensuring that your production business logic is identical to what was tested in the POC phase is critical for the stable and efficient operation of DTS.

• During incremental synchronization, partial transaction rollbacks on the source are not supported. Rollback operations may be lost.

Special case

When the source instance is an ApsaraDB RDS for SQL Server, DTS creates an rdsdt_dtsacct account in the source instance for data synchronization. Do not delete this account or change its password while the task is running, or the task may fail. For more information, see System account descriptions.

Synchronization type

Pricing

Schema synchronization and full data synchronization

Free of charge.

Incremental data synchronization

Charged. For more information, see Billing overview.

Operation type

SQL statement

DML

INSERT, UPDATE, DELETE

DDL

ALTER TABLE supports only ADD COLUMN and DROP COLUMN.

Database

Permissions

Actions

Source RDS SQL Server

Owner permissions on the source objects

Create standard, high-privilege, and global read-only accounts and Modify the permissions of an account

Target SelectDB

cluster access permissions (Usage_priv) and database permissions (Select_priv, Load_priv, Alter_priv, Create_priv, and Drop_priv)

Manage cluster permissions and Manage basic permissions

Category

SQL Server type

SelectDB type

Numeric

BIT

INT

TINYINT

SMALLINT

SMALLINT

SMALLINT

INT

INT

BIGINT

BIGINT

• DECIMAL

• NUMERIC

DECIMAL

FLOAT

DOUBLE

REAL

DOUBLE

MONEY

STRING

SMALLMONEY

STRING

Date and Time

DATE

DATEV2

DATETIME

DATETIMEV2

DATETIME2

DATETIMEV2

SMALLDATETIME

DATETIMEV2

TIME

STRING

DATETIMEOFFSET

DATETIMEV2

TIMESTAMP

STRING

String

• CHAR

• VARCHAR

VARCHAR(4*N)

• NCHAR

• NVARCHAR

VARCHAR(4*N)

• VARCHAR(MAX)

• NVARCHAR(MAX)

• TEXT

• NTEXT

STRING

• BINARY

• VARBINARY

• IMAGE

STRING

XML

STRING

UNIQUEIDENTIFIER

STRING

Other

GEOGRAPHY

STRING

GEOMETRY

STRING

• SQL_VARIANT

• HIERARCHYID

• SYSNAME

STRING