How to maintain data consistency in SQL Server databases

Depending on the implementation policy, there are three main types: snapshot replication, transaction replication, and merge replication. The three replication types have their own characteristics and are applicable to different scenarios. Generally, when considering which replication type is suitable, we mainly consider the time interval between performance and data synchronization. Under what circumstances is snapshot replication applicable? I will discuss this topic with you.
In order to adopt snapshot replication at the right time, the database administrator must first know the characteristics of snapshot replication. Snapshot Replication refers to the transmission of data in the instantaneous state at a specific time point without solid data updates. When synchronization occurs, a complete snapshot is generated and sent to the subscription server. Simply put, snapshot replication synchronizes data at intervals. Instead of starting the snapshot copy when the data on the Publishing Server is updated. Apparently, the data synchronization of such snapshot replication is slightly inferior. Data inconsistency may occur between the subscription server and the Publishing Server for a period of time. However, this can greatly improve the performance of the subscription server and the Publishing Server. This is like car transportation. If snapshot replication is used, a container can be loaded and then delivered, instead of the quantity to be sent. After understanding the specific features of this database, the database administrator can consider under what circumstances snapshot replication is more reasonable.

1. Systems with few data changes.

The major drawback of snapshot replication compared with other copies is that the data in the database cannot be consistent with that on the distribution server in a timely manner. Therefore, if the content on the Publishing Server is rarely changed, it is reasonable to use snapshot replication. When snapshot replication is used, not only will the negative effects of Data Consistency delay become less and less obvious, but it can also improve the performance of the Publishing Server and the subscription server. Such customers often encounter such problems in actual work. For example, if an enterprise has offices or sales offices in different regions, like KFC, the prices of products in different regions are basically the same and will not be changed. Even if the changes are made, all regions are uniformly adjusted. At this time, because the product price list is relatively less changed, it is more appropriate to copy snapshots between the database service of the enterprise headquarters and the subscription servers in various regions. In fact, there are many similar situations. For example, many garment enterprises, such as Li Ning and Nike, have their own sales offices in different regions. The price is also consistent. In this case, using snapshot replication can improve the performance of Database Replication without affecting its usage.

2. A large amount of data changes may occur during a certain period of time.

It should be noted that the data mentioned above does not change much. It refers to the continuous change of data. For example, the data changed every day or every hour in a year is average. At this time, snapshot replication is not suitable. However, if data changes are concentrated within a period of time. The contents of the database won't be changed much in other periods of time. At this time, it is feasible to use snapshot replication. For example, some decision-making systems often need to make a lot of changes when importing data at the beginning. After the data is imported and analyzed, the content in the database remains unchanged. In this case, the author believes that as long as data updates are concentrated in a fixed period of time, using snapshot replication is still feasible.

In the above case, if the marketing department maintains the price of a product and these prices are often updated several times at a fixed time. For example, some promotions will be made during the seasonal change. At this time, the database administrator can immediately execute the copied data snapshot after the data is updated. Therefore, to determine whether snapshot replication is suitable for data update, the standard is not the data update volume. As mentioned above, the initial data update volume of the analysis and decision-making system may be larger than the data update volume of some database systems in several years. I believe that it is mainly determined based on the frequency of data updates. If the data is updated frequently, snapshot replication is not suitable even if there is not much data to be updated, such as updates with a long stream. However, when all the data updates are concentrated at a fixed time, it is reasonable to use snapshot replication.

3. Can I have outdated data copies of the Publishing Server within a period of time?

Now many supermarkets have chain stores, such as century Lianhua. To increase profits and increase market share, these supermarkets have launched a balance card, that is, consumers first put a certain amount of RMB into the balance card. After each consumption is completed, the fee is deducted from the card. But a few days ago, there were frequent news reports that a customer's consumption card was reported to be lost in a Lianhua supermarket. However, the person who finds the card can still consume it in other Lianhua supermarkets. For this reason, the consumer cannot understand why the lost consumer card can still be consumed in other supermarkets? Who should bear the loss after the loss? In fact, this caused supermarkets to adopt snapshot replication when they are not appropriate. Because snapshot replication is used, data in different Lianhua supermarkets cannot be consistent within a short period of time. For example, some merchants say they will not bear the loss within the day of the Report, which means they may copy snapshots once a day after work. Generally, this is not a problem. However, similar problems may occur when a consumer card is stolen.

Therefore, when considering whether snapshot replication is suitable, you also need to consider whether to allow outdated data copies that are used by the publishing server for a period of time. If not, this snapshot copy is not allowed. If this is allowed, the database administrator needs to evaluate the maximum duration. If it is 24 hours, you need to copy snapshots every 24 hours. However, it should be noted that, if the interval is relatively short, for example, to allow 10 minutes of Data delay, it is unnecessary to use snapshot replication. In this case, transaction replication or merge replication may be more appropriate.

4. Copy a small amount of data.

Compared with other replication types, snapshot replication also has a significant feature, that is, when data synchronization occurs, it will generate a complete snapshot and send it from the Publishing Server to the subscription server. What is this concept? For example, the subscription server has 10 Gb of data, and only 1 MB of data has been changed during the snapshot replication period. In this case, the database system transfers 10 Gb of data to the subscription server. At this time, the changed data is only 1 MB, but 10 Gb of data traffic needs to be transferred on the network, which obviously puts a lot of pressure on the enterprise's network. Because the continuous overhead of snapshot replication on the Publishing Server is lower than the overhead of transaction replication, tracking incremental changes will not be enabled for one database. However, in this case, if the data volume to be copied is very large, there are not many updates at ordinary times. At this time, the database system needs to generate and apply snapshots, which will consume a large amount of resources, including network resources and server resources. Therefore, when there is a large amount of data in the publishing server, it is not appropriate to use snapshot replication. At this time, network transmission will become its most significant bottleneck resource. On the contrary, if you can adopt a fine-grained transaction replication policy, the impact on the enterprise's network performance will be much smaller, or even negligible.

Therefore, when using snapshot replication, the database administrator must understand that snapshot replication will transmit the entire database object. In this way, a large amount of network bandwidth is eroded during snapshot replication and transmission, which significantly reduces the network performance of enterprises and even causes network congestion. Sometimes, in order to ensure that snapshots can be transmitted to other subscription servers accurately and quickly, VPN and other technologies have to be used to ensure the transmission accuracy. For this reason, I believe that snapshot replication is applicable only when the database of the publishing server is not very large. Otherwise, using snapshot replication is not worth the candle.

from the above analysis, we can draw a conclusion. When considering whether snapshot replication is appropriate, it is often impossible to use an indicator to determine. Multiple factors need to be considered, such as the size of the database, the frequency of data updates, and the time allowed for data delay. Finally, a balance is achieved between data consistency and database performance. To be honest, it is indeed difficult for most database administrators to make a choice. Because there are no fixed indicators for reference. If the database capacity is less than a certain amount, snapshot replication is used. No database management expert can come to this conclusion. Therefore, in addition to the factors that affect the selection, you must rely on the experience of the database administrator. When you encounter similar multiple-choice questions, experience can often help the administrator solve the problem quickly. The last thing to note is that no matter what solution is finally adopted, it is best to keep track for a period of time to see if your choice is reasonable.