Let's look at a few of the best practices you should consider when working with snapshot replication.

Minimizing Logging at Subscriber

Snapshot replication uses bcp to bulk insert data from the publisher to the subscriber database. It's therefore advised to switch to a bulk-logged or simple recovery model to minimize logging and optimize bulk insert performance.

Minimizing Locking

As we discussed earlier, snapshot generation applies exclusive locks on tables until the snapshot is generated. This stops any other applications from accessing the tables, resulting in blocking. You can look at the following options to minimize blocking:

Replicating Only Required Articles

Understand the business requirements and replicate only what is required. Replicating all articles in a large database will take time and resources for snapshot generation.

Using Pull Subscription

Consider using pull subscription. In pull subscription, the distribution agent is on the subscriber and not on the distributor. This reduces workload on the distributor. Moreover, if the publisher is acting as its own distributor, its workload is reduced.

Compressing the Snapshot Folder

As discussed earlier, there is an option to compress snapshot files in the .cab format. This reduces the size of the snapshot files and speeds up network transfer. However, it takes time to compress the files by the snapshot agent and decompress by the distribution agent.

The following table discusses snapshot and distribution agent parameters that can be modified so as to optimize snapshot replication:

Figure 1.78: The Agent Parameters table

In this activity, we'll troubleshoot snapshot replication.

You have been asked to set up snapshot replication for the WideWorldImporters database. The subscriber database will be used to run daily reports. You configured the replication so that the initial snapshot is applied successfully. You schedule it to occur daily at 12:00 AM, as directed by the business. However, the next day you are informed that the data isn't synced.

In this activity, you'll find and fix the issue.

Setup Steps

To simulate the error, follow these steps:

Generating a New Snapshot

Follow these steps to generate a new snapshot:

Applying the Snapshot to the Subscriber

You'll now apply the generated snapshot to the subscriber. To apply the snapshot, follow these steps:

An easy solution to this problem is to drop the view at the subscriber database. However, the business tells you that the view can't be dropped as it's being used by the daily report.

Another solution to this problem is to modify the publication properties to include pre- and post-snapshot scripts so that you can delete and create the view, respectively.