SQL Server replication function to avoid interference with defects

SQL Server replication is divided into three types. The following describes these three Replication technologies and their existing defects. You can select an appropriate replication method based on the specific situation during use, avoid interference with these defects.

1. Copying snapshots

A deletes all the data in the corresponding table in B at intervals, and inserts all the data in the corresponding table into B. This method is obviously not suitable for our applications.

2. Transaction Replication

For each transaction in A that meets the replication condition, A is applied to B at every certain time, and vice versa. SQL Server transmits data to three stored procedures (insert, delete, modify) for data modification. We can rewrite this stored procedure based on our own applications.

Defect: If the replication conditions from A to B are the same as those from B to A, A loop of "kicking the ball" will occur. When A series of transactions are applied to A, A will apply these transactions to B, and B will apply these transactions to ,...... This loop.

3. Merge and copy

At intervals of time, the replication will merge the data to be copied in database A and database B so that the data in database AB is exactly the same. The data is all the data in database AB. If the primary keys of data in A and B conflict, only one of the data is selected based on the priority.

Note: To distinguish the data source from where it comes from, SQL Server adds a field to each table to be copied. Pay attention to this during programming.

Defects:

SQL Server replicates data at a certain time. If another Server cannot be found (for example, due to a network failure or another SQL Server is not started), after n (the default value is 10) after the connection, its replication function will be down until someone starts the replication function. If data exists in the table to be copied before replication, It is troublesome to create a copy (SQL Server does not provide a solution to this problem, which must be done manually ). Therefore, tables to be copied cannot change the table structure frequently during use. (Wen/Zhang mengqiang)