Data Synchronization Solution

This article will talk about the database data synchronization scheme. The scheme is not provided here for the local area network or the visible synchronization between the two database IP addresses, in this case, the database itself provides many outstanding performance solutions, which can be solved by reading the help documentation.

The cases described in this article are:

There are two or more database servers A and B in different networks. The database IP address is invisible and the port is invisible.

Table t1 in Table A -----> one-way synchronization to table t1 in Table B

Table t2 in Table A <-----> bidirectional synchronization to table t2 in Table B

That is, how should one-way synchronization and two-way synchronization of the AB database server be implemented?

 

The network speed must be considered in the internet. Therefore, the amount of data transmitted should be as small as possible.

Two-way synchronization is only used for two one-way synchronization. We will illustrate the synchronization scheme by taking the t1 form on server A and the t1 table synchronized to server B as an example.

Let's take a look at the three solutions and then compare them.

Solution 1:

 

• Data (DateTable type) of Table t1 of Site A is directly transmitted to table t1 of site B;

• End B is modified to insert Table t1. The so-called Modified insert means that if insert does not exist, it will be updated;
  (Equivalent to the insert into t1 () values () on duplicate key update syntax in mysql)

• Complete. (Simple)

 

Comments:This is the most direct, most crude, and safest solution. However, if many table data are synchronized, this solution will certainly not work. However, when the data volume is small, such as less than 100 records, this solution also highlights its advantages: security and simplicity. Therefore, this solution is useful.

 

Solution 2:

 

• Add Table at one end of the same step source (for example, table t1 of Site A in the case)
  Is_sync
  Is_del
  Two tinyint or bite fields;

• When used as an Insert or Update operation, set is_sync to 0 and wait for synchronization;

• When used as a Delete operation, set is_del to 1, and is_sync to 0, instead of physical deletion;

• Query all is_sync = 0 data on end A and pass it to end B;

• After receiving the data, end B adds, deletes, and modifies the existing data in Table B and returns the successful result to end;

• After receiving the result of successful operation B, the side sets is_sync = 1 for is_sync = 0 and is_del = 1 for the data returned successfully. In addition, if is_del = 1 is returned, the data is physically deleted.
  

 

Comments:This is a common solution, but I think it is still unwise. Although it achieves fine-grained synchronization, it adds many additional operations, for example, the is_sync value must be changed during the add, delete, and modify operations. If the project has completed the final stage, all data layers have been implemented, and the changes will be relatively large if synchronization is required, therefore, this solution improves synchronization accuracy and reduces the amount of data transmitted, but it has a great impact on the original code addition, deletion, and modification.

A similar solution is to add tables with the same structure (called tables to be synchronized), and use triggers to add, delete, modify, and delete records to the tables to be synchronized, then, according to the above process, the synchronization scheme also has a lot of additional content, such as the new trigger and the table to be synchronized, which also has a negative impact on the existing system to increase the burden and complexity.

 

Solution 3:

In solution 3, we will first introduce the Rowversion field type, which is TIMESTAMP type in mysql.

Long ago, MSDN said: "Each database has a counter.RowversionWhen you insert or update a column table, this counter value increases. This counter is the row version of the database. This can track the relative time in the database, rather than the actual time associated with the clock. One table can have only oneRowversionColumn. Each modification or insertion containsRowversionWhen the column is runningRowversionInsert the incremental database row version value in the column ."

The specific description of the Rowversion type is not detailed. You can search for it on msdn. In short, this type of field will change the database records automatically without manual operations. With this feature, a third synchronization scheme is designed.

• Add the following three fields to the table t1 in the same step source end (Table t1 in Table A) (taking the mssql field type as an example)

  Row_version rowversion, -- current version

  Sync_version binary (8), -- synchronized version

  Is_del bit default 0, -- whether to delete

• When table t1 in Table A is added or modified, the value of the row_version field is automatically changed to the current time, but the value of sync_version is not changed, is_del needs to be set to 1 without physical deletion;

• Query all row_version! = Sync_version data, transmitted to side B;

• B performs physical addition, deletion, and modification after receiving the data, and returns the successful result to;

• After receiving the result returned by B, A sets the value of sync_version and row_version to the same. If is_del = 1 is found, the system deletes the result physically;
  

 

 Comments:I think this solution is the best solution for synchronizing large tables. It only needs to be changed to logical deletion when performing the delete operation. Other operations do not need to take synchronization into account and have the least impact on existing systems, it is also accurate synchronization and does not occupy the network resources. This solution is the main character of this article and also a solution I strongly recommend.

The TIMESTAMP type can be used in mysql. As long as the mysql table contains the TIMESTAMP field (and there is no default value ), when the table is updated or increased, the value of the first TIMESTAMP type field is changed to the current time. This can also be applied to the above three solutions.

 

In actual use, we need to select the synchronization scheme based on the table size. If the table is small, the first scheme is the best. If the table is large, the third scheme is the best.

 

As this is a solution article, we simply talk about the solution to a specific technical implementation level.

In this case, A needs to transmit table data to B. How can this be achieved?

In practice, both networks usually do not open ports other than website 80 for Internet calls, in addition, a program needs to scan the database to check whether data needs to be synchronized at regular intervals. Based on these situations, it is easy to think of win Service + web Service.

I recommend using win service + remoting. According to the test, remoting is 7-8 times faster than web Service. However, both of them have a limit on the size of the transfer package. Therefore, it is best to transmit data in batches in 100 or 1000 records at a time to avoid synchronization failure because the data packet size exceeds the limit.

 

If you are familiar with. Net Remoting (or Webservice) and WinService technologies, you must know how winService communicates with remoting. This is an article about solutions, so the basic technical knowledge is not detailed. I hope this article will help you. For better solutions, please leave a message.