SQL Server replication capabilities to avoid bug interruptions

SQL Server has powerful replication capabilities, in addition to synchronizing data and database objects from one database to another and accurately distributing them. SQL Server replication is divided into three kinds, the following describes the three kinds of replication technology and some of the deficiencies, we 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 The server replicates at a certain time, and if no other server is found (for example, because of a network failure or another SQL server does not start), its replication functionality will be down after the N (default 10) Secondary connection, 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)