Synchronize data from SQL Server on ECS to AnalyticDB for PostgreSQL

Prerequisites

• The user-created SQL Server database must be one of the following versions: 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.

• The tables to be synchronized in the user-created SQL Server database must have a primary key or a unique non-null index. This ensures data idempotency during synchronization.

• The destination AnalyticDB for PostgreSQL instance must have more available storage space than is used by the source SQL Server database.

Usage notes

• During full data initialization, DTS consumes read and write resources from the source and destination databases, increasing their load. If your databases have poor performance, low specifications, or high business traffic (for example, the source database has many slow SQL queries or tables without primary keys, or the destination database experiences deadlocks), the database load may increase and even cause service interruptions. Before you synchronize data, evaluate the performance of the source and destination instances. We recommend synchronizing data during off-peak hours, such as when the CPU utilization of both instances is below 30%.

• You can select objects at the table level for synchronization. DTS does not support synchronizing append-optimized (AO) tables. You can modify column mappings. If you use column mapping and do not synchronize the entire table, or if the source and destination table schemas are inconsistent, data in the columns that exist in the source table but not in the destination table will be lost.

• To ensure the data synchronization task runs smoothly, do not perform frequent backups on the source database. Retain logs for at least three days to prevent retrieval failures after log truncation.

• To ensure that the displayed data synchronization latency is accurate, DTS adds a heartbeat table to the user-created SQL Server database. The table name is in 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

Before you configure the data synchronization task, configure log settings and create a clustered index on the source SQL Server database.

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 in 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 your destination instance is located.

5. Find the data synchronization instance that you purchased 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. We recommend that you provide 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 topic uses User-Created Database in ECS Instance as an example. Note If you select another instance type for the user-created database, you must complete additional preparations. For more information, see Preparation overview.
   	Instance Region	The region of the source instance that you selected when purchasing the data synchronization instance. This setting cannot be changed.
   	ECS Instance ID	Select the ID of the ECS instance where the source database is located.
   	Database Type	Fixed as SQL Server.
   	Port Number	Enter the service port number of the user-created database. The default value is 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 of the destination instance that you selected when purchasing the data synchronization instance. This setting cannot be changed.
   	Instance ID	Select the ID of the destination AnalyticDB for PostgreSQL instance.
   	Database Name	Enter the name of the database where the destination table is located.
   	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 DTS server IP whitelist.

   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 is complete, DTS synchronizes the schema and existing data of the selected objects to the destination database. This serves as the baseline for subsequent incremental data synchronization.
   Processing mode for existing destination tables	Precheck and Report Errors: DTS checks for tables in the destination database with the same names as the source tables. The precheck passes if no such tables are found. If a table with a matching name exists, the precheck fails, and the task does not start. Note If a table with the same name exists in the destination database and cannot be deleted or renamed, you can change the name of the destination table. For more information, see Rename a synchronization object in the destination instance. Ignore Errors and Proceed: Skips the check for tables with the same name in the destination database. Warning If you select Ignore Errors and Proceed, data inconsistency may occur, posing risks to your business. For example: If the table schemas are identical and a record in the destination database has the same primary key value as a source record, the existing destination record is preserved, and the corresponding source record is not synchronized. If the table schemas are different, the initial data synchronization might fail, synchronize only a subset of columns, or fail entirely.
   Table merging	Yes: In OLTP scenarios, sharding is often used to improve table response times. In AnalyticDB for PostgreSQL, a single table can store massive amounts of data, making single-table queries more convenient. In such cases, you can use the table merging feature of DTS to synchronize multiple source tables with the same schema into a single table in AnalyticDB for PostgreSQL. Note After selecting multiple source tables, you must use the object name mapping feature to map them to a single table name in AnalyticDB for PostgreSQL. For more information, see Rename a synchronization object in the destination instance. You must add a column named __dts_data_source of the TEXT type to the destination table in AnalyticDB for PostgreSQL to store the data's origin. DTS writes values in the format of <DTS data synchronization instance ID>:<source database name>.<source schema name>.<source table name> to distinguish the data's origin, 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, the names of the synchronized objects remain unchanged. If you need to use different names for the objects in the destination instance, use the object name mapping feature. For more information, 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	Select whether to add quotation marks to the destination object names. If you select Yes, and any of the following conditions are met, DTS adds single or double quotation marks to the destination object names during schema and incremental data synchronization: The source database environment is case-sensitive and uses mixed-case names. The source table name either does not start with a letter or contains characters other than letters, numbers, and the 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 choose to add quotation marks, you must use the quoted object names when you query them after synchronization is complete.
   Edit mapped names	To change the name of a synchronization object in the destination instance, use the object name mapping feature. For more information, see Object name mapping.
   Connection retry duration	If DTS cannot connect to the source or destination database, it retries the connection for 720 minutes (12 hours) by default. You can also specify a custom retry duration. If DTS reconnects to the databases within the specified duration, the data synchronization task automatically resumes. Otherwise, the task fails. Note You are charged for the DTS instance during the connection retry period. We recommend that you specify a custom retry duration based on your business requirements or release the DTS instance promptly after the source and destination database 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 Table constraints and Table distribution key.

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

    Note

    • DTS performs a precheck before the synchronization task starts. The task can begin only after it passes this check.

    • If the precheck fails, click the icon next to the failed item to view details.

      • Fix the issues based on the cause and run the precheck again.

      • If you do not need to fix an alert item, you can click Ignore or Ignore and Re-precheck to skip the alert item and run the precheck again.

11. After Precheck Passed is displayed in the Precheck dialog box, close the Precheck dialog box, and the synchronization job will start.

12. Wait for the synchronization channel to initialize. The task will then enter the Synchronizing state.

    On the Data Synchronization page, you can view the status of the data synchronization task.