SQL Server replication, cluster

There are four types of database replication.

They are:

Each item has its own advantages and disadvantages. I mainly use snapshot publishing and transaction Publishing. as for why not to use other products, all of them are synchronized with each other. the auto-increment primary key conflict may occur, and some people on the Internet say it may become an endless loop. of course I have never tried.

Snapshot publishing:
The publishing server sends a snapshot of published data to the subscription server at a scheduled interval.

Principle: take photos of the current database and send the photos to restore the data.

Advantage: Snapshot publishing can effectively ensure data integrity and control the interval between snapshot generation.

Disadvantage: Because the snapshot mechanism is to take a photo of the current database and then send the photos to restore the data, there are three problems.

1. because it is a recovery process, the entire table will be deleted first and then restored. so there will be a very small period of time when the database cannot find the table, because it is deleted. this time is determined by the size of the current synchronization table.

2. Because snapshots take photos of the entire data table, old data may exist in tables with large data volumes and cannot be transmitted.

3. Due to the large data size of each synchronization, there will be imtimeliness.

Applicable: tables that change frequently and have a small amount of data.

 

Transactional publishing:
After the subscription server receives the initial snapshot of published data, the publisher streams transactions to the subscription server.

Principle: records the changes in table data and synchronizes the changes to the subscription server table.

Advantage: data is synchronized in a timely manner and can be synchronized in several seconds. No matter how large the table data is, it's okay.

Disadvantages:

1. The subscription server table cannot be changed. Otherwise, synchronization may fail.

2. Only the changed data is synchronized in the future, so the loss of old data cannot be recovered, and the integrity is lacking.

3. As changes to the publishing table are synchronized immediately, it is often frequent to connect to the subscription server.

Applicable: data tables that are not constantly changing.

 

Transactional publishing with updatable subscriptions:
After the SQL Server subscriber receives the initial snapshot of published data, the publisher streams transactions to the subscriber. Transactions from the subscription server are applied to the Publishing Server.

Merge and publish:
After the subscription server receives the initial snapshot of published data, the publisher and subscription server can independently Update published data. Changes are periodically merged. Microsoft SQL Server mobile edition can only subscribe to combined Publishing.