Snapshot replication and SQL Server's magic weapon to ensure data consistency

Generally, snapshot replication has the following features.

First, from the term snapshot, we can also see that the photo is a static process, that is, it only reflects the status at a certain time point of the database, rather than a change process of the database. The snapshot replication knowledge replicates data at a specified time in the database, instead of continuously monitoring data changes over a period of time. This is essentially different from another brother's "transaction replication. If snapshot replication is used as a full backup, transaction replication is a differential backup.

Second, snapshot replication requires a large amount of resources. Snapshot replication copies the entire database, regardless of whether it has changed. Undoubtedly, the replication time and transmission time will be relatively long, and the server and network resources will be relatively large. Therefore, the cost of copying snapshots is relatively high. However, in some specific cases, snapshot replication can improve the efficiency. For example, if you do not need to change the data frequently or maintain high synchronization data, you can use snapshot replication to coordinate data consistency between multiple databases, but make a reasonable choice.

Although snapshot replication is a relatively advanced technology, it does not work well in all application scenarios. If the snapshot replication technology is improperly used, the database administrator may be slowed down. Based on my experience, we recommend that you use snapshot replication in the following cases.

First, snapshot replication can be used when the data volume is large but not frequently changed. For example, I have a customer who has clothing stores all over the country, and the sales prices of each store are basically the same. Due to the impact of the economic crisis, in order to withdraw cash as much as possible at the end of the year, it was decided to lower the sales prices of each store between June 1 and June 3. Because their sales systems are all networked nationwide. The price reduction must be completed by the Headquarters system. In this case, the snapshot technology can be used to synchronize the price list with databases in various regions. After the price change is complete, copy the completed database snapshot. In general, the data in a table is mainly static data that is not frequently changed. When the data changes at a time, it will be better to release a new database snapshot to the server.

Second, many snapshot replication technologies are used in some decision support systems. Because of the Decision Support System, they only need to query data, but rarely make changes to the data. At the same time, database timeliness requirements are often not high. For example, a sales decision-making system may only need the sales data at the end of last month, instead of the sales data for the day. In this case, the snapshot replication technology is better than other data synchronization technologies, such as transaction replication technology. In general, in a certain period of time, if outdated data copies are allowed, that is, systems with low requirements for data timeliness, Snapshot technology can be considered.

Third, snapshot replication technology can be used when there are few data copies. Because snapshot replication is a copy of the entire database at a time, if the data volume is large, each copy and transmission will take a long time. At this time, it is reasonable to adopt transaction replication. In fact, this is similar to full backup and differential backup. When the database capacity is relatively small, full backup is more practical than differential backup. It is easy to maintain.

Fourth, you can use snapshot replication to back up SQL Server databases remotely. The SQL Server server itself does not have the remote backup function. It is often the first to back up locally, and then copy the backup file to another host. With snapshot replication, you can solve this problem. Create a distribution server and then copy the snapshot of the Publishing Server to the distribution server every night. In this case, the content of the distributor is consistent with that on the publisher. Back up the distribution server to complete the remote backup policy.

Therefore, if the enterprise application scenario meets the preceding four conditions, the effect of using snapshot replication is better. Otherwise, you need to consider using other data synchronization technologies such as transaction replication.

When using snapshot replication technology, I also have a few good reminders.

First, the snapshot replication technology can implement the functions of network databases to a certain extent. Up to now, SQL Server databases do not support horizontal database scaling. That is to say, we do not advocate using multiple servers to share the burden on the primary server. Microsoft has always stressed vertical expansion of servers, such as increasing the number of server CPUs and memory to improve server performance. Using snapshot replication technology can automate the data distribution process and enable automatic data synchronization between multiple servers to meet the needs of enterprises to varying degrees. When an enterprise expands, it can add additional database servers to reduce the workload of the master server and improve data usage efficiency. This enables the multi-server (Network Database) function to a certain extent. However, this function has restrictions. Please refer to the following notes.

Second, the snapshot replication technology is basically one-way. That is to say, data can be copied from the master server and then transmitted to other database servers. Other database servers can only passively receive snapshots from the master server. However, you cannot report the changes in the data on your server to the master server. This is essentially different from what we usually call a network server. If the network server is used, the data changed on each sub-server can be synchronized on each other's servers. Therefore, to use the snapshot server to implement the functions of the network server to reduce the load on the master server, there is usually a deployment technique. The subserver only accepts the query function. That is to say, some report generation and data query tasks are handed over to the subserver. The data update and other tasks are still handed over to the master server.

Third, force replication and forced subscription can be used for sudden data changes. If the data on the Publishing Server (the Publishing Server is the professional title of the master server in snapshot replication) is changed, no matter whether the time set by the database administrator is reached, you can force a snapshot copy to the Publishing Server. In addition, it is transmitted to the distributor and the updated data is forcibly used. This is forced subscription. Forced subscription is usually used in applications of this type that must be immediately sent to the subscription server when data changes occur. For example, a supermarket needs to adjust the prices of all commodities at on January 1, January 1. At this point, after the price adjustment is complete, you must use the forced subscription service to synchronize other prices to query data on the server. When the customer queries the price on the inquiry machine, it finds the adjusted price. This is a typical application of forced subscription. However, if the data changes frequently, this forced subscription may cause system or network paralysis. Database Administrators should pay attention to this point.

Fourth, we introduced Internet technology support from Versions later than 2005, including the new version 2008. Data can be published to the Internet through replication, improving the efficiency of data usage in the database. However, when copying data over the Internet, pay attention to several issues. First, ensure that the publishing server and the distribution server are on the same side of the network firewall rather than on both sides of the firewall. If they are responsible, data replication will not succeed. The second is to ensure that the publishing server and the distribution server have direct connections, while concurrency is only the Internet connection method. Third, it currently supports the TCP/IP protocol. Only when the protocol runs normally can the Protocol be replicated on the Internet. At the same time, you must pay attention to the Internet bandwidth. The bandwidth directly affects the efficiency of data replication.

In short, the emergence of snapshot replication provides a shortcut for data synchronization between SQL Server databases and is a magic weapon to ensure data consistency of SQL Server.