Key Points of SQLSERVER Replication

The point of replication is frequently asked in the Forum: SQLSERVER replication has a problem !! SQLSERVER replication is not running !! Replication is blocked !! Then I finally came up with the following sentence: how to do it ??????????????? We know that the replication function is mainly used for read/write splitting. Of course, there are other

The point of replication is frequently asked in the Forum: SQLSERVER replication has a problem !! SQLSERVER replication is not running !! Replication is blocked !! Then I finally came up with the following sentence: how to do it ??????????????? We know that the replication function is mainly used for read/write splitting. Of course, there are other

Key Points of SQLSERVER Replication

In the forum, people often ask: SQLSERVER replication has a problem !! SQLSERVER replication is not running !! Replication is blocked !!

Then I finally came up with the following sentence: "How to do it ???????????????"

We know that the replication function is mainly used for read/write splitting. Of course, SQLSERVER replication is also used in other scenarios, but most of them are still used for "read/write splitting"

The advantages and disadvantages of SQLSERVER replication summarized in "SQLSERVER database large-scale application solution summary"

Article address:

Advantages and disadvantages:

(1) poor real-time data: the data is not synchronized to the self-read server in real time. After the data is written to the master server, it can be queried only after the next synchronization.

(2) Synchronization efficiency is poor when the data volume is large: when the data volume of a single table is too large, insertion and update may suffer from index, disk IO, and other problems, resulting in poor performance.

(3) connecting multiple (at least two) databases at the same time: at least two data databases must be connected. The actual read/write operations are completed in the program code, which may cause confusion.

(4) Reading features high performance, high reliability, and scalability: Read-Only server, no record filing space, because there is no write operation, it will greatly reduce disk I/O and other performance problems, greatly improve efficiency.

The read-only server can adopt Server Load balancer. The primary database is released to multiple read-only servers to achieve read operation scalability.

------------------------------------------------------ Gorgeous split line -----------------------------------------------------------

In fact, we can only synchronize some tables without synchronizing all the tables in the whole database to the subscription server, and SQLSERVER also supports only Synchronizing some columns in one table.

Some users synchronize all the tables in the database. No matter which tables are frequently accessed, which tables have a large data volume and which tables are not suitable for primary keys or indexes.

A condition for synchronization is that the table must have a primary key. If you want to synchronize all tables, not all tables must have a primary key ?? We all know that the primary key is added.

It is equivalent to adding a clustered index to the table, which definitely affects the performance of data modification. Therefore, you only need to publish the table to be synchronized/released.

There is no need to publish the entire database. There are also some stored procedures, functions, and views. You only need to synchronize frequently accessed or necessary resources.

-------------------------------------------------------- Gorgeous split line ---------------------------------------------------------

Prerequisites for replication:

(1) To improve execution efficiency, You can restrict all data obtained by the subscription server, or publish only the data that the subscriber really needs or the data that the subscriber has the right to obtain.

(2) Before snapshot replication, the website space should leave sufficient disk space for snapshot replication.

(3) Allocate sufficient log space before transaction replication, and leave sufficient disk space for the distribution database.

(4) create a primary key for each table

(5) Merge and copy data to move forward to the timestamp column. Because the data of the subscription server is also transmitted to the Publishing Server,

Data integrity should be ensured on each subscriber and table association references should be maintained.

(6) Add the not for replication setting to all IDENTITY attribute fields to ensure that SQLSERVER retains the start ID value on the row added by the REPLICATION agent,

However, the ID value is added to the rows added by other users. When a user adds a new row to a table, the flag value is usually increased. When the replication agent copies the new line

When inserting this row into the subscription server table, the ID value is not changed.

------------------------------------------------------------ Gorgeous split line -----------------------------------------------------

The following is an article I wrote about virtual hosts, Introduction to replication, and precautions for replication. If you want to improve replication performance, customize replication standards, and define replication

SQLSERVER Replication

1. Only publish necessary tables or fields

2. customized Performance Standards

3. Improve the Performance of regular Replication

There are several points of attention in it. I hope readers can read it carefully.

Hope the article can help you

If something is wrong, you are welcome to make a picture !!