SQL Server replication features clever choice to avoid bugs

SQL Server has powerful replication capabilities, as well as synchronization between databases, in addition to replicating data and database objects from one database to the exact distribution of another. SQL Server replication is divided into three kinds, the following describes the three replication technologies and some of the shortcomings, you can use the specific circumstances to choose the appropriate replication methods to avoid these defects interference.

1. Snapshot replication

A you remove all the data from the corresponding table in B at intervals, and then insert all of your corresponding tables into B. This approach is clearly not suitable for our application and is not considered.

2. Transactional replication

Every time a transaction that satisfies a replication condition in a is applied to B, and vice versa. SQL Server modifies the data by passing it to three stored procedures (INSERT, delete, modify), and we can overwrite the stored procedure according to our own application.

Defect: If the copy condition from A to B is the same as the copy from B to A, the "buck" loop phenomenon is generated. A series of transactions applied to A,a will apply these transactions to the b,b after receiving the series of transactions, and applying it to a, ... So loop.

3. Merge replication

Every once in a while, replication will merge the data that needs to be replicated in the database a,b so that the data in AB is exactly the same, and the data is all the data in the AB database. If there is a conflict between the data primary keys in A and B, only one of the data is selected based on priority.

Note: To differentiate where the data comes from, SQL Server adds a field to each table that needs to be replicated, which should be noted when programming.

There are defects:

SQL Server replicates at certain intervals, if no other server is found (for example, because of a network failure or another SQL Server is not started), after N (the default is 10), its replication functionality will be down until someone starts the replication feature. In tables that need to be replicated, if there is data before replication, it can be cumbersome to build replication (SQL Server does not provide a workaround for this type of problem and needs to be done manually). Therefore, tables that need to be replicated cannot change the table structure frequently during use. (Text/Zhangmengiang)