SQL Server database synchronization problems

We can maintain multiple copies of multiple data on different database servers through data replication across the LAN and the Internet, and ensure data consistency between copies in synchronous or asynchronous mode. SQLServer provides comprehensive built-in data replication capabilities, which are an important part of the SQLServer Core Engine. SQLServer replication is divided

We can maintain multiple copies of multiple data on different database servers through data replication across the LAN and the Internet, and ensure data consistency between copies in synchronous or asynchronous mode. SQL Server provides comprehensive built-in data replication capabilities, which are an important part of the core SQL Server engine. SQL Server replication is divided

We can maintain multiple copies of multiple data on different database servers through data replication across the LAN and the Internet, and ensure data consistency between copies in synchronous or asynchronous mode.

SQL Server provides comprehensive built-in data replication capabilities, which are an important part of the core SQL Server engine.

There are three ways for SQL Server replication:

Snapshot copy: take photos of data in the published database at a certain time point and copy it to the ordering server. It is generally used to copy static tables.

Transaction replication: the copied content contains multiple statements and stored procedures. In this way, the ordering database only reads the data modification method of the database and reflects the modification. Generally, databases with high update frequency are used.

Merge replication: allows two-way modification by the orderer and publisher, and reflects the modification to the databases of both parties. If there is a data conflict, the conflict will be handled according to certain rules.

Currently, the Organization has a web site with databases located in the United States, the Netherlands, and Shenzhen. In terms of Website infrastructure, we use 3DNS and BigIP for smart domain name resolution and load balancing. However, because the three regions have their own databases (SQL Server), Data Consistency of each database must be ensured. We need to copy data. According to the user's requirements, we are doing transaction replication. The first two versions of the website are normal, but after updating the web page version in the last two weeks, we found that transaction replication often encountered errors. Re-transaction replication still appears after a short interval. After checking the error log, the following error occurs:

The query processor cocould not produce a query plan from the optimizer because a query cannot update a text, ntext, or image column and a clustering key at the same

Check the database table. After troubleshooting, identify the problematic table (XXX_article ). After confirmation with the programmer, the table property is changed to the ntext type, and the field and the clustered index field of the table are updated at the same time.

Therefore, it is suspected that the two locations will be modified at the same time during transaction replication, and the relevant data cannot be found in a clear official description, however, some people in other programs found similar problems in program code. The solution is to divide the problem into two columns: update. The specific technical details are not clearly described. We cannot interfere with the transaction replication process, so we will not perform transaction replication on xxx_article. Then, copy the snapshot of xxx_article to avoid this problem. After testing and application, this problem has been solved.