Synchronize data from SQL Server on ECS to AnalyticDB for PostgreSQL

Prerequisites

• The user-created SQL Server database must run version 2008, 2008 R2, 2012, 2014, 2016, 2017, or 2019.

  Note

  If your SQL Server database is deployed in an Always On availability group, you must use the synchronous-commit mode.

• Tables to synchronize from the user-created SQL Server database must have a primary key or a unique non-null index to ensure data idempotency.

• The destination AnalyticDB for PostgreSQL instance must have more available storage than the source SQL Server database uses.

Usage notes

• During full data initialization, DTS consumes read and write resources of the source and destination databases, which may increase the database load. When the databases have low performance, low specifications, or heavy workloads (for example, a large number of slow SQL statements, tables without primary keys, or deadlocks on the destination database), the database pressure may be further increased and services may become unavailable. Therefore, you must evaluate the performance of the source and destination databases before starting data synchronization, and it is recommended that you perform data synchronization during off-peak hours (for example, when the CPU load of both the source and destination databases is below 30%).

• Objects can be selected at the table level. DTS does not support synchronizing append-optimized (AO) tables. Column mappings can be modified. If you use column mapping without synchronizing the entire table, or the source and destination table schemas are inconsistent, data in columns that exist only in the source table is lost.

• Avoid frequent backups on the source database. Retain logs for at least three days to prevent retrieval failures after log truncation.

• DTS adds a heartbeat table to the user-created SQL Server database to track synchronization latency. The table name follows the format <table_name>_dts_mysql_heartbeat.

Billing

Limitations

• DTS does not support schema and table structure synchronization for 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, system tables, or aggregate functions.

• DTS does not synchronize data of the following types: TIMESTAMP, CURSOR, ROWVERSION, HIERARCHYID, SQL_VARIANT, SPATIAL GEOMETRY, SPATIAL GEOGRAPHY, and TABLE.

• DTS does not synchronize tables that contain computed columns.

Supported SQL operations

• DML operations: INSERT, UPDATE, DELETE

• DDL operation: ADD COLUMN

  Note

  DTS does not migrate transactional DDL operations.

Account permissions

Preparations

Configure log settings and create a clustered index on the source SQL Server database before configuring the synchronization task.

1. In the user-created SQL Server database, run the following command to change the recovery model of the source database to FULL. You can also modify this setting by using an SSMS client. For more information, see View or Change the Recovery Model of a Database.

   use master;
   GO
   ALTER DATABASE <database_name> SET RECOVERY FULL WITH ROLLBACK IMMEDIATE;
   GO

   Parameters:

   <database_name>: The name of the source database.

   Example:

   use master;
   GO
   ALTER DATABASE mytestdata SET RECOVERY FULL WITH ROLLBACK IMMEDIATE;
   GO

2. Back up the source database by running the following command. You can skip this step if you have already performed a logical backup.

   BACKUP DATABASE <database_name> TO DISK='<physical_backup_device_name>';
   GO

   Parameters:

   • <database_name>: The name of the source database.

   • <physical_backup_device_name>: The path and filename for the backup file.

   Example:

   BACKUP DATABASE mytestdata TO DISK='D:\backup\dbdata.bak';
   GO

3. Back up the logs of the source database by running the following command.

   BACKUP LOG <database_name> to DISK='<physical_backup_device_name>' WITH init;
   GO

   Parameters:

   • <database_name>: The name of the source database.

   • <physical_backup_device_name>: The path and filename for the backup file.

   Example:

   BACKUP LOG mytestdata TO DISK='D:\backup\dblog.bak' WITH init;
   GO

4. Create a clustered index for the tables to be synchronized. For more information, see Create Clustered Indexes.

Procedure

1. Purchase a data synchronization instance. For more information, see Purchase procedure.

   Note

   On the buy page, set the SQL Server parameter to SQL Server, the Destination Instance parameter to AnalyticDB for PostgreSQL, and the One-way Synchronization parameter to One-way Synchronization.

2. Log on to the DTS console.

3. In the left-side navigation pane, click Data Synchronization.

4. At the top of the Synchronization Tasks page, select the region where the destination instance is located.

5. Find your data synchronization task and click Configure Synchronization Channel.

6. Configure the source and destination instances.

   Category	Parameter	Description
   N/A	Synchronization Task Name	DTS automatically generates a task name. Specify a descriptive name for easy identification. The name does not need to be unique.
   Source Instance Details	Instance Type	Select the deployment location of the source database. This example uses User-Created Database in ECS Instance. Note If you select a different instance type, additional preparations are required. For more information, see Preparation overview.
   	Instance Region	The region selected when you purchased the data synchronization instance. This setting cannot be changed.
   	ECS Instance ID	Select the ECS instance that hosts the source database.
   	Database Type	The database type, fixed as SQL Server.
   	Port Number	Service port of the user-created database. Default: 1433.
   	Database Account	Enter the database account for the source database. For permission requirements, see Account permissions.
   	Database Password	Enter the password for the database account.
   	Connection Mode	Select Non-encrypted or SSL-encrypted as needed.
   Destination Instance Details	Instance Type	Select AnalyticDB for PostgreSQL.
   	Instance Region	The region selected when you purchased the data synchronization instance. This setting cannot be changed.
   	Instance ID	Select the destination AnalyticDB for PostgreSQL instance.
   	Database Name	Enter the name of the destination database.
   	Database Account	Enter the database account for the AnalyticDB for PostgreSQL instance. For permission requirements, see Account permissions.
   	Database Password	Enter the password for the database account.

7. In the lower-right corner of the page, click Set Whitelist and Next.

   If the source or destination database is an Alibaba Cloud database instance, such as an ApsaraDB RDS for MySQL or ApsaraDB for MongoDB instance, DTS automatically adds the CIDR blocks of DTS servers to the IP address whitelist of the instance. If the source or destination database is a self-managed database hosted on an Elastic Compute Service (ECS) instance, DTS automatically adds the CIDR blocks of DTS servers to the security group rules of the ECS instance, and you must make sure that the ECS instance can access the database. If the self-managed database is hosted on multiple ECS instances, you must manually add the CIDR blocks of DTS servers to the security group rules of each ECS instance. If the source or destination database is a self-managed database that is deployed in a data center or provided by a third-party cloud service provider, you must manually add the CIDR blocks of DTS servers to the IP address whitelist of the database to allow DTS to access the database. For more information, see Whitelist DTS server IP addresses.

   Warning

   Adding the public IP address blocks of the DTS service, either automatically or manually, may pose security risks. Using this product, you acknowledge that you understand and accept the potential security risks and that you must implement basic security measures. These measures include, but are not limited to, strengthening password security, limiting the ports open to each CIDR block, using authentication for internal API calls, and regularly checking and restricting unnecessary CIDR blocks. Alternatively, you can connect through a private network using a leased line, VPN Gateway, or Smart Access Gateway.

8. Configure the synchronization policy and objects.

   Setting	Description
   Synchronization types	By default, Initial Schema Synchronization, Full Data Synchronization, and Incremental Data Synchronization are selected. After the precheck completes, DTS synchronizes the schemas and existing data of the selected objects to the destination database as a baseline for incremental synchronization.
   Processing mode for existing destination tables	Precheck and Report Errors: DTS checks for destination tables with the same names as the source tables. The precheck passes if no matching tables are found. If a matching table exists, the precheck fails and the task does not start. Note If the destination database already contains a table with the same name that cannot be deleted or renamed, you can rename the destination table. For more information, see Rename a synchronization object in the destination instance. Ignore Errors and Proceed: Skips the check for identically named tables in the destination database. Warning Selecting Ignore Errors and Proceed may cause data inconsistency. For example: If the schemas match and a destination record has the same primary key as a source record, the destination record is preserved and the source record is not synchronized. If the schemas differ, initial data synchronization may fail or synchronize only a subset of columns.
   Table merging	Yes: In OLTP scenarios, sharding is often used to improve response times. AnalyticDB for PostgreSQL can store massive amounts of data in a single table, which simplifies queries. You can use table merging to synchronize multiple source tables with the same schema into a single AnalyticDB for PostgreSQL table. Note After selecting multiple source tables, use the object name mapping feature to map them to a single table in AnalyticDB for PostgreSQL. For more information, see Rename a synchronization object in the destination instance. You must add a __dts_data_source column of the TEXT type to the destination table to track data origin. DTS writes values in the format <DTS data synchronization instance ID>:<source database name>.<source schema name>.<source table name>, such as dts********:dtstestdata.testschema.customer1. Table merging is a task-level feature and cannot be applied to individual tables. If you need to merge some tables but not others, you must create two separate synchronization tasks. No: This is the default option.
   Operation types	Select the operation types to synchronize based on your business needs. All operation types are selected by default.
   Synchronization objects	In the Source Objects box, click the objects that you want to synchronize, and then click the icon to move them to the Selected Objects box. Because this is a heterogeneous synchronization scenario, only tables can be synchronized. Other objects such as views, triggers, and stored procedures are not supported. Note By default, synchronized object names remain unchanged. To use different names in the destination instance, see Rename a synchronization object in the destination instance. If you set Merge Tables to Yes, you must use the object name mapping feature to map the selected source tables to a single table name in AnalyticDB for PostgreSQL.
   Add quotation marks to object names	Specifies whether to quote destination object names. If you select Yes and any of the following conditions are met, DTS adds quotation marks to destination object names during schema synchronization and incremental data synchronization: The source database environment is case-sensitive and uses mixed-case names. The source table name does not start with a letter, or contains characters other than letters, numbers, and supported special characters. Note Only underscores (_), pound signs (#), and dollar signs ($) are supported as special characters. The schema, table, or column name to be synchronized is a keyword, reserved word, or invalid character in the destination database. Note If you add quotation marks, you must use the quoted names when querying the objects after synchronization.
   Edit mapped names	Change the names of synchronized objects in the destination instance. For more information, see Map databases, tables, and columns.
   Connection retry duration	If DTS cannot connect to the source or destination instance, it retries for 720 minutes (12 hours) by default. You can also specify a custom retry duration. If DTS reconnects to the source or destination instance within the specified duration, the synchronization task automatically resumes. Otherwise, the task fails. Note You are billed for task run time during connection retries. Customize the retry duration based on your business needs, or release the DTS instance as soon as the source and destination instances are released.

9. Configure the table type, primary key columns, and distribution key for the destination tables in AnalyticDB for PostgreSQL.

   Note

   For more information about primary key columns and the distribution key, see and .

10. After you complete the preceding configurations, click Precheck and Start in the lower-right corner of the page.

    Note

    • Before the synchronization task is officially started, a precheck is performed. The synchronization task can only be started after the precheck passes.

    • If the precheck fails, click the icon next to a specific check item to view the failure details.

      • Follow the prompt to fix the issue and run the precheck again.

      • If you do not need to fix the warning check items, you can also select Ignore, Ignore Warning Items and Rerun Precheck to skip the warning check items and run the precheck again.

11. After the Precheck dialog box shows that Precheck Passed, close the Precheck dialog box. The synchronization task starts.

12. Wait for the task to complete initialization and enter the Synchronizing state.

    You can view the status of the data synchronization task on the Data Synchronization page.