SQL Server Database Synchronization issues

We can maintain multiple copies of multiple data across the LAN and the Internet over different database servers through data replication, while ensuring data consistency between copies in a synchronous or asynchronous manner.

SQL Server provides the perfect built-in data replication capability, which is a very important part of the SQL Server Core engine.

SQL Server replication is divided into three different ways:

Snapshot replication: At a point in time, take a picture of the data in the publication database and copy it to the order server. Typically used to replicate static tables.

Transactional replication: The copied content is multiple statements and stored procedures, so the order database simply reads the data modification of the database and reflects the modification. A database with a higher frequency of general user updates.

Merge replication: Allows both the buyers and the publisher to modify the two directions and reflect the changes to the database on both sides. If the data is conflicting, the conflict will be handled according to certain rules.

Currently the unit is useful to a Web site, which is located in the United States, the Netherlands and Shenzhen respectively. On the Web site infrastructure, we are using 3DNS and BIGIP to implement intelligent domain name resolution and load balancing. However, because of the three databases (SQL Server), you need to ensure that the data for each database is consistent. We need to do data replication. According to the user's request we do is transactional replication, the first two versions of the site are normal, but in the last two weeks after updating the Web page version, found that transactional replication often error. Redo transactional replication still occurs after a short interval of time. Check the error log discovery prompt after:

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

Check the database table, and after the investigation, determine the problem of a table (xxx_article). And the programmer confirms that this table property has been changed to the ntext type, and that both this field and the table's clustered index fields are updated.

Therefore, it is suspected that because of the simultaneous modification of the two locations while doing transactional replication, the lookup of the relevant data did not lead to a definitive official description, but someone with other programs found a similar problem in the coding of the program, and the solution was to break into the two fields of update two times. The specific technical details could not be clearly stated. We cannot interfere with the process in transactional replication, so we will not do transactional replication to xxx_article. You can then do snapshot replication for xxx_article to avoid this problem. After testing and application, this problem has been solved.