How Snapshot Replication Works in MS SQL Server

This article explains the snapshot process by going into the details of its working.

Snapshot replication in SQL Server comes handy when you wish to share your database exactly the way it is right now, not focusing on any of the updates that would be made in it. Snapshot replication feature of SQL Server helps users to share data the way it appears in the current moment to the subscribers. It is important to note that although the snapshot replication process can be used in isolation, it is mostly used for providing just the database objects or the initial data which is used for the purpose of transaction and mail merge. If you wish to use snapshot replication in isolation, you should make use of it in the following situations.

• If there are infrequent changes taking place in the data.
• If you need to replicate small volumes of data only.
• If you can afford to have data copies that are outdated on the publisher end.
• Large changes in the database are made frequently.

The continuous overhead that the publisher needs to take up is much lower in snapshot replication as compared to transactional replication, as it does not take into account any of the incremental changes. In case the dataset you are replicating is large, you will be needing a large amount of resource to ensure that the snapshot is generated and applied properly. To make sure that it is done efficiently, you should keep in mind the size of complete data along with the frequency over which the changes are being made, and if using snapshot replication is the appropriate choice.

Working of Snapshot Replication

Snapshot is used in SQL Server for all types of replication for initializing subscribers. However, in SQL Server, the snapshot agent is used for generating snapshot but not for delivering. The agent used for delivering the snapshot differs on the basis of the type of replication that is in use. The Distribution Agent is used for delivering the files in-case of snapshot and transactional replication. Merge replication makes use of SQL Server Merge Agent. 

Snapshot agent makes use of Distributor for running, it is also run at by the Distribution Agent as well as the Merge Agent for push subscriptions, in-case of pull subscription, they run at Subscribers.

You can opt for applying and generating applications immediately as well as after the subscription process is complete. The snapshot file that contains the schema and the data from published tables, along with database objects is created by the Snapshot Agent and then stored in the snapshot folder for use by the publisher. These files can also be used for the purpose of tracking records in the distribution database. The default snapshot folder is specified when the Distributor has been configured, however, an alternate location can be specified for a publication even when you have a default, and even when you don’t.

Apart from the working of the snapshot that has been described above, there is also another snapshot process that is divided into two parts and is used when making use of merge publication along with certain parameterized filters.

Apart from investing time in optimizing SQL Server, DBAs should also give a key consideration to data safety and accessibility. Investing in a sql fix tool can go a long way in preventing data loss situations.

Author Introduction:

Victor Simon is a data recovery expert in DataNumen, Inc., which is the world leader in data recovery technologies, including access recovery and sql recovery software products. For more information visit www.datanumen.com