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. Here are some examples of replication use cases:

• 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.
• 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 read-only mode to provide data to users and applications
• 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 and About Databases contains detailed tutorials explaining each step:

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.