Shulou(Shulou.com)05/31 Report--

This article is to share with you about how to maintain data consistency in the SQLServer database. The editor thinks it is very practical, so I share it with you to learn. I hope you can get something after reading this article.

First, in a system with relatively few data changes.

The main disadvantage of snapshot replication compared with other replications is that the data in the database can not be consistent with the publisher in time. For this reason, if the content in the publisher rarely changes, it is obviously reasonable to use snapshot replication at this time. If snapshot replication is adopted at this time, not only the negative effect of data consistency delay will be less and less obvious, but also the performance of publishers and subscribers can be improved. Such as in practical work, we often encounter such customers. If an enterprise has offices or sales organizations everywhere, just like KFC, the prices of products are basically the same and will not change. Even if it is changed, it will be adjusted uniformly in all localities. Since there are few changes in the product price list at this time, it is more appropriate to use snapshot replication between the database service of the enterprise headquarters and the local subscribers. In fact, there are many similar situations. For example, many clothing enterprises, such as Li Ning, Nike and so on, not only produce by themselves, but also have their own sales offices in various places. It is also uniform in terms of price. In this case, snapshot replication can often improve the performance of database replication without affecting its use.

Second, there will be a large number of changes in data within a certain period of time.

One point that needs to be added is that the data mentioned above does not change much, referring to continuous changes in the data. For example, in a year, the data is changed more evenly every day or every hour. Snapshot replication is not appropriate at this time. But if the changes to the data are concentrated in one period of time. The contents of the database will not change much at other times. It is feasible to use snapshot replication at this time. For example, some decision-making systems often need to make a lot of changes when they import data at the beginning. When the data is imported and analyzed, the content in the database remains basically the same. In this case, the author believes that as long as the update of the data is concentrated in a fixed period of time, snapshot replication is still feasible. As in the case of the KFC or clothing company above, if the marketing department maintains the price of a product, and these prices are often updated several times at a fixed time. For example, there will be some promotions at the end of the season. At this point, the database administrator can perform a snapshot of the replicated data immediately after the data has been updated. Therefore, to judge whether it is suitable for snapshot replication by data update, the standard is not the amount of data update. Like the above-mentioned analysis and decision-making system, the initial data update amount may be larger than that of some database systems for several years. The author believes that the judgment is mainly based on the frequency of data updates. If the data is updated frequently, then snapshot replication is not suitable for snapshot replication, even if there is not much data to update, such as a long stream of updates. While those blowout-like data updates, all the updates are concentrated in a fixed time, so it is more reasonable to use snapshot replication at this time.

Third, is it allowed to have a copy of data that is out of date relative to the publisher for a period of time?

Now many supermarkets have been linked, such as Century Lianhua and so on. In order to increase profits and increase market share, these supermarkets have launched credit cards, that is, consumers first put a certain amount of RMB into the credit cards. Then deduct the fee from the card after each consumption is completed. But a few days ago, there were frequent news reports that a customer's consumption card had been reported missing in a Lianhua supermarket. But people who pick up this card can still spend money in other Lianhua supermarkets. For this reason, consumers do not understand why lost consumption cards can still be consumed in other supermarkets. Who should bear the loss after reporting the loss? In fact, this makes supermarkets use snapshot replication at an inappropriate time. Due to the use of snapshot replication, the data between the databases of Lianhua supermarket can not be consistent in a short period of time. If some merchants say that they will not bear the loss within the day of reporting the loss, it means that they may make a snapshot copy after work every day. Normally, this will not be a problem. But for example, if the consumption card is stolen and so on, you will encounter similar problems. Therefore, when considering the suitability of snapshot replication, you also need to consider whether to allow a copy of data that is outdated to the publisher for a period of time. If not, then this snapshot replication is not allowed. If allowed, the database administrator needs to assess the maximum amount of time. If it is 24 hours, then snapshot replication needs to take place every 24 hours. It is important to note, however, that if the time interval is short, such as allowing only ten minutes of data delay, then snapshot replication is not necessary. Transactional replication or then and merge replication may be more appropriate at this time.

Fourth, copy a small amount of data.

Snapshot replication has another remarkable feature compared with other types of replication, that is, when data synchronization occurs, a complete snapshot is generated and delivered from the publisher to the subscriber. What kind of concept is this? For example, there is 10G of data in the subscriber, but only 1m of data has changed in a snapshot replication cycle. If snapshot replication occurs at this time, the database system will transfer 10G of data to the subscriber. At this time, the changed data is only 1m, but it needs to transmit 10G data traffic on the network, which will obviously exert great pressure on the enterprise network. Because the continuous overhead of snapshot replication at the publisher is lower than that of transactional replication, the database system does not enable tracking incremental changes at one time. But in this case, if the amount of data to be copied is very large, and there are not many updates at ordinary times. At this time, if the database system wants to generate and apply snapshots, it will consume a lot of resources, including network resources and server resources. Therefore, when there is a lot of data in the publisher, snapshot replication is not appropriate. Because at this time, network transmission will become its most important bottleneck resource. On the contrary, if the transaction replication strategy with long flow can be adopted, the impact on enterprise network performance will be much smaller or even negligible. So when using snapshot replication, the database administrator must understand that snapshot replication will transfer the entire database object. Thus, in the process of snapshot replication transmission, it will erode a large amount of network bandwidth, thus significantly reduce the performance of the enterprise network, and even lead to network congestion. Sometimes in order to ensure that snapshots can be accurately and quickly delivered to other subscribers, technologies such as VPN have to be used to ensure the accuracy of transmission. For this reason, the author thinks that snapshot replication is suitable only when the database of the publisher is not very large. Otherwise, the loss of snapshot replication outweighs the gain.

The above is how to maintain data consistency in the SQLServer database. The editor believes that there are some knowledge points that we may see or use in our daily work. I hope you can learn more from this article. For more details, please follow the industry information channel.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.