Data replication between Self-Managed SQL Server databases
NineData data replication supports full data synchronization between self-managed SQL Server data sources.
Prerequisite
Added source and target data sources to NineData. For how to add, see Adding Data Sources .
The versions of the source and target data sources are shown in the table below. Note that the source version must be less than or equal to the target data source version.
Source Target SQL Server 2022 SQL Server 2022 SQL Server 2019 SQL Server 2022, 2019 SQL Server 2017 SQL Server 2022, 2019, 2017 SQL Server 2016 SQL Server 2022, 2019, 2017, 2016 SQL Server 2014 SQL Server 2022, 2019, 2017, 2016, 2014 SQL Server 2012 SQL Server 2022, 2019, 2017, 2016, 2014, 2012 SQL Server 2008R2 SQL Server 2022, 2019, 2017, 2016, 2014, 2012, 2008R2 SQL Server 2008 SQL Server 2022, 2019, 2017, 2016, 2014, 2012, 2008R2, 2008 You must have read permissions on the source database and read and write permissions on the target data source.
Limits
- Before performing data synchronization, user need to evaluate the performance of the source data source and the target data source, and it is recommended to perform data synchronization during off-peak time. Otherwise, the full data initialization will occupy a certain amount of read and write resources of the source data source and the target data source, increasing database load.
- It is necessary to ensure that each table in the synchronization object has a primary key or unique constraint, and the column name is unique, otherwise the same data may be synchronized repeatedly.
Steps
View sync results
Log in to the NineData Console.
Click on Replication > Data Replication in the left navigation bar.
On the Replication page, click on the Task ID of the target synchronization task, the page details are as follows.
Number Function Description 1 Sync Delay The data synchronization delay between the source data source and the target data source. 0 seconds means there is no delay between the two ends, at which point you can choose to switch the business to the target data source for smooth migration. 2 Configure Alerts After configuring alerts, the system will notify you in the way you choose when the task fails. 3 More - Pause: Pause the task, only tasks with Running status are selectable.
- Duplicate: Create a new replication task with the same configuration as the current task.
- Terminate: End tasks that are unfinished or listening (i.e., in incremental synchronization). Once the task is terminated, it cannot be restarted, so please proceed with caution. If the synchronization objects contain triggers, a trigger replication option will pop up, please select as needed.
- Delete: Delete the task. Once the task is deleted, it cannot be recovered, so please proceed with caution.
4 Structure Replication (displayed in scenarios involving structure replication) Displays the progress and detailed information of structure replication. - Click on Log on the right side of the page: View the execution logs of structure replication.
- Click on
on the right side of the page: View the latest information. - Click on View DDL in the Actions column on the right side of the target object in the list: View SQL playback.
5 Full Replication (displayed in scenarios involving full replication) Displays the progress and detailed information of full replication. - Click on Monitor on the right side of the page: View various monitoring indicators during full replication. During full replication, you can also click on Flow Control Settings on the right side of the monitoring indicator page to limit the rate of writing to the target data source per second. The unit is rows/second.
- Click on Log on the right side of the page: View the execution logs of full replication.
- Click on on the right side of the page: View the latest information.
6 Incremental Replication (displayed in scenarios involving incremental replication) Displays various monitoring indicators of incremental replication. - Click on View Threads on the right side of the page: View the operations currently being executed by the current replication task, including:
- Thread ID: Replication tasks are executed in multiple threads, displaying the current thread number in progress.
- Execute SQL: Details of the SQL statement currently being executed by the current thread.
- Response Time: The response time of the current thread. If this value increases, it indicates that the current thread may be stuck for some reason.
- Event Time: The timestamp when the current thread was started.
- Status: The status of the current thread.
- Click on Flow Control Settings on the right side of the page: Limit the rate of writing to the target data source per second. The unit is rows/second.
- Click on Log on the right side of the page: View the execution logs of incremental replication.
- Click on on the right side of the page: View the latest information.
7 Modify Object Displays the modification records of synchronization objects. - Click on Modify Objects on the right side of the page to configure the synchronization objects.
- Click on on the right side of the page: View the latest information.
8 Data Comparison Displays the comparison results between the source data source and the target data source. If you have not enabled data comparison, please click on Enable Comparison on the page. - Click on Re-compare on the right side of the page: Re-initiate the comparison between the current source and target data sources.
- Click on Stop on the right side of the page: After the comparison task starts, you can click this button to stop the comparison task immediately.
- Click on Log on the right side of the page: View the execution logs of consistency comparison.
- Click on Monitor (displayed only in data comparison): View the trend chart of RPS (records per second) comparison. Click on Details to view earlier records.
- Click on
in the Actions column on the right side of the comparison list (displayed under the Data tab only in the case of inconsistency): View details of the comparison between the source and target sides. - Click on
in the Actions column on the right side of the comparison list (displayed only in the case of inconsistency): Generate change SQL. You can directly copy this SQL to the target data source to execute and modify the inconsistent content.
9 Expand Display detailed information of the current replication task. Common Options: - Export table configuration: Export the current task's database and table configuration, allowing for quick import when creating a new replication task. This helps rapidly establish multiple replication links with the same replication objects.
- Alert Rules: Configure the alarm strategy for the current task.
Appendix: Checklist of Pre-Check Items
| Check Item | Check Content |
|---|---|
| Source Database Permission Check | Check if the account permissions in the source database meet the requirements |
| Target Database Data Existence Check | Check if data exists for the objects to be replicated in the target database |
| Target Database Same Name Object Existence Check | Check if the objects to be replicated already exist in the target database |
| Source Data Source Connection Check | Check the status of the source data source gateway, instance accessibility, and accuracy of username and password |
| Target Data Source Connection Check | Check the status of the target data source gateway, instance accessibility, and accuracy of username and password |
| Target Database Permission Check | Check if the account permissions in the target database meet the requirements |
| Source Data Source and Target Data Source Version Check | Check if the versions of the source database and target database are compatible |
| SQL Server Agent Status Check | Check if the SQL Server instance agent is running |