Snapshot Replication in Microsoft SQL Server

SQL Server's snapshot replication technology allows you to automatically transfer information between multiple SQL Server databases. This technology is a great way to improve the performance and/or reliability of your databases. 

There are many ways that you might use snapshot replication in your SQL Server databases. For example, you may use this technology for geographically distributing data to databases located at remote sites.

This improves performance for end users by placing the data in a network location close to them and simultaneously reduces the load on intersite network connections.

You may also use snapshot replication for distributing data across multiple servers for load-balancing purposes. One common deployment strategy is to have a master database that is used for all update queries and then several subordinate databases that receive snapshots and are used in ​a read-only mode to provide data to users and applications.

Finally, you may use snapshot replication to update data on a backup server to be brought online in the event the primary server fails.

When you use snapshot replication, you copy the entire database from the Publisher SQL Server to the Subscriber SQL Server(s) on a one-time or recurring basis. When the Subscriber receives an update, it overwrites its entire copy of the data with the information received from the Publisher.

This can take quite a long time with large datasets and it is imperative that you carefully consider the frequency and timing of snapshot distribution. For example, you would not want to transfer snapshots between servers in the middle of a busy data on a highly congested network. It would be much more prudent to transfer the information in the middle of the night when users are at home and bandwidth is plentiful.



Initiating snapshot replication is a three-step process:

  1. Create the distributor
  2. Create the publication
  3. Subscribe to the publication

You may repeat the final step of creating a subscriber as many times as necessary to create all of the subscribers you would like.

Snapshot replication is a powerful tool that allows you to transfer data between SQL Server installations in your enterprise. The tutorials linked above will help you get started moving data in a matter of hours.

Format
mla apa chicago
Your Citation
Chapple, Mike. "Snapshot Replication in Microsoft SQL Server." ThoughtCo, Apr. 3, 2017, thoughtco.com/snapshot-replication-in-microsoft-sql-server-1019829. Chapple, Mike. (2017, April 3). Snapshot Replication in Microsoft SQL Server. Retrieved from https://www.thoughtco.com/snapshot-replication-in-microsoft-sql-server-1019829 Chapple, Mike. "Snapshot Replication in Microsoft SQL Server." ThoughtCo. https://www.thoughtco.com/snapshot-replication-in-microsoft-sql-server-1019829 (accessed October 24, 2017).