SQL Server replication principles and considerations

A,B bidirectional replication as a model with two replication servers

SQL Server replication is divided into three categories:

1. Snapshot replication

A to delete all the data from the corresponding table in B at intervals, and then

Insert all 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 when it receives the series of transactions and applies 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.

SQL Server replication flaw:

SQL Server replicates at certain intervals, if you cannot find another SQL Server (for example, because of a network failure, or if another SQL Server is not started), after the N (default 10) Secondary connection, 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.