How to maintain data consistency in SQL Server databases

Replication is a means of maintaining data consistency in SQL Server databases. Depending on the implementation strategy, there are three types of snapshot replication, transactional replication, merge replication, and so on. These three types of replication, each with its own characteristics, are applicable to different occasions. In general, when considering which type of replication is more appropriate, the main consideration is the time between performance and data synchronization

Replication is a means of maintaining data consistency in SQL Server databases. Depending on the implementation strategy, there are three types of snapshot replication, transactional replication, merge replication, and so on. These three types of replication, each with its own characteristics, are applicable to different occasions. Generally speaking, when considering which type of replication is more appropriate, the main consideration is the time interval between performance and data synchronization. So what are the scenarios where snapshot replication is more appropriate? I'll talk to you about this topic.

To take snapshot replication at the right time, the database administrator first needs to know the characteristics of snapshot replication. Snapshot replication is the forwarding of data to an instantaneous state at a given moment, rather than a solid update of the data. When synchronization occurs, the full snapshot is generated and sent to the Subscriber. Simply put, snapshot replication is a data synchronization operation at intervals. Instead of releasing the data from the publisher as soon as there is an update, this snapshot is replicated. Obviously the data synchronization of this snapshot replication is a little bit close. There is a time when there is data inconsistency between the Subscriber and the publisher. But this can improve the performance of subscribers and publishers to a large extent. It's like car transportation. Using snapshot replication can be a container full after delivery, rather than how much to send. After mastering the specific features of this database snapshot replication, the database administrator can consider under what circumstances, snapshot replication is more reasonable.

A system with fewer data changes.

The main drawback of snapshot replication compared to other replication is that the data in the database cannot be consistent with the publisher in a timely manner. For this reason, it is quite reasonable to take snapshot replication at this time if the content of the publisher is rarely changed. Snapshot replication at this time, not only the negative effects of data consistency delay will become less obvious, but also improve the performance of the Publisher and Subscriber. As in actual work, you will often encounter such a customer. If a company has offices or sales organizations all over the world, like KFC, the prices of products around the world are basically the same, not how to change. Even if the change, everywhere is also unified adjustment. Since the product price list changes less at this time, it is more appropriate to use snapshot replication between the database services at the corporate headquarters and subscribers around the world. In fact, there are a lot of similar situations. such as many garment enterprises, such as Li Ning, Nike, and so on, they not only produced their own, but also have their own sales offices everywhere. In terms of price is also unified. 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 during a period of time.

The point to add is that the data mentioned above does not change, referring to the continuity of the data changes. As in a year, the data changed every day or every hour is more average. snapshot replication is not appropriate at this time. However, if the data changes are concentrated within one time period. The contents of the database are not changed much at other times. Snapshot replication is possible at this time. As with some decision systems, it is often necessary to make a large number of changes when importing data at first. When the data is imported and the data is analyzed, the contents of the database are basically unchanged. In this case, the author thinks that it is still feasible to use snapshot replication as long as the update of the data is concentrated in a fixed time period.

Again, as in the case of KFC or the clothing business, if the marketing department maintains the price of a product, these prices are often updated several times at a fixed time. such as in the season when there will be some promotions. The database administrator can then perform a replicated data snapshot immediately after the data has been updated. Therefore, the standard is not the update amount of the data to determine whether it is suitable for snapshot replication by data update. As the analysis decision system mentioned above, its initial data update volume may be larger than some database systems for several years of data update. The author believes that the main is based on the frequency of data updates to judge. If the data is updated more frequently, it is not appropriate to take snapshot replication, even if the data is updated with little data, like a steady update. And those blowout-like data updates, all the updates are concentrated in a fixed moment, then snapshot replication is more reasonable.

Do you want to allow a copy of data that is obsolete to the publisher over a period of time?

Now many supermarkets have also been linked, such as the century Lian Hua and so on. In order to increase profits, increase market share, these supermarkets have launched a punch card, that is, consumers first of a certain amount of renminbi into the punch card. Then deduct the fee from the card after each consumption is completed. But a few days ago there were often news reports that a customer's consumption card was lost in a Lianhua supermarket. But people who pick up the card can still spend it in other Lianhua supermarket. To this end, consumers do not understand why the lost consumption card can still be consumed in other supermarkets? Who should bear the losses after the loss? In fact, this makes the supermarket in the inappropriate time to use the snapshot replication caused. With snapshot replication, data between Lianhua Supermarket databases cannot be consistent within a short period of time. If some businesses say they are not responsible for losses within the day of loss, this means that they may be making a snapshot copy every day after work. In general this will not be a problem. But like when the consumer card is stolen and so on, you will encounter similar problems.

Therefore, when considering the suitability of snapshot replication, it is also important to consider whether to allow a copy of the data that is obsolete relative to the publisher over a period of time. If not, then this snapshot replication is not allowed. If allowed, the database administrator will need to evaluate the longest period of time. If it is 24 hours, snapshot replication is required every 24 hours. However, it is important to note that snapshot replication is not necessary if the time interval is relatively short, such as allowing 10 minutes of data latency. transactional replication or merge replication may be more appropriate at this point.

Iv. copy a small amount of data.

Snapshot replication has a notable feature in comparison to other replication types, that is, when data synchronization occurs, a full snapshot is generated and transferred from the Publisher to the Subscriber. What is this concept? such as the Subscriber has 10G of data, and within a snapshot replication cycle, only 1 m of data has changed. When snapshot replication occurs, the database system transmits 10G of data to the Subscriber. At this point, the changed data is only 1 m, but need to transfer 10G of data traffic on the network, it will obviously produce more pressure on the network of the enterprise. Because the sequential cost of snapshot replication at the publisher is lower than the cost of transactional replication, a database system does not enable tracking incremental changes. But like this, if the amount of data to be copied is very large, and the usual updates are not much. When the database system is to generate and apply snapshots, it consumes a large amount of resources, including network resources and server resources. Therefore, when the data in the publisher is relatively long, it is not appropriate to use snapshot replication. Because at this point the network transmission will become its most important bottleneck resources. Conversely, if you can take a long transaction replication strategy, then the impact on the performance of the enterprise network will be much smaller, even negligible.

So when using snapshot replication, the database administrator must understand that snapshot replication transmits the entire database object. Thus, in the process of snapshot replication transmission, it will erode a large amount of network bandwidth, which can obviously reduce the performance of the enterprise network and even cause network congestion. Sometimes in order to ensure that snapshots can be accurately and quickly transmitted to other subscribers, but also have to use VPN technology to ensure the accuracy of transmission. Therefore, the author thinks that only the database of the publisher is not very large, it is suitable to use snapshot replication. Otherwise, the use of snapshot replication is not worth the candle.

From the above analysis, we can get a conclusion. When considering whether snapshot replication is appropriate, it is often not possible to use an indicator to judge. There are several factors to consider, such as the size of the database, the frequency of data updates, the time allowed for data latency, and so on. Finally, a balance is achieved between the consistency of data and the performance of the database. To be honest, it is difficult for most database administrators to make a choice. Because there is no fixed indicator can be used for reference. Snapshot replication should be used if the database capacity is less than how much. No database management Expert can make this conclusion. So in the grasp of the relevant factors affecting their choice, it is necessary to rely on the experience of database administrators. When you encounter a similar selection problem, often experience can help the administrator solve the problem quickly. The last thing to be reminded is that no matter what the final plan is, it's best to keep track of it for a while and see if your choice is reasonable.