Microsoft SQL Server: Snapshot Replication

New publication wizard in Microsoft SQL Server

Mike Chapple

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.

Snapshot Replication for Distributing Data

You may also use snapshot replication for distributing data across multiple servers for load-balancing purposes. One common deployment strategy is to have a primary 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.

Format
mla apa chicago
Your Citation
Chapple, Mike. "Microsoft SQL Server: Snapshot Replication." ThoughtCo, Nov. 18, 2021, thoughtco.com/snapshot-replication-in-microsoft-sql-server-1019829. Chapple, Mike. (2021, November 18). Microsoft SQL Server: Snapshot Replication. Retrieved from https://www.thoughtco.com/snapshot-replication-in-microsoft-sql-server-1019829 Chapple, Mike. "Microsoft SQL Server: Snapshot Replication." ThoughtCo. https://www.thoughtco.com/snapshot-replication-in-microsoft-sql-server-1019829 (accessed March 29, 2024).