Optimizing Snapshot Replication

In this section, we'll discuss optimizing snapshot replication by following best practices and tuning the snapshot and distributor agent parameters.

Snapshot Replication Best Practices

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.

Note

To find out more about recovery models, refer to https://docs.microsoft.com/en-us/sql/relational-databases/backup-restore/recovery-models-sql-server?view=sql-server-2017.

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:

• Change the isolation level to read-committed snapshot to avoid read-write blocking. You'll have to research and find out how the read-committed snapshot will not affect any other application or functionality of your environment.

• Another way to avoid read-write blocking is to selectively use the NoLock query hint. This is not a good practice; however, it's being used in many applications to fix read-write blocking.

• Schedule snapshot generation at off-peak hours when there is less workload on the server.

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.

Modifying Agent Parameters

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

Activity 1: Troubleshooting Snapshot Replication

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:

1. Use the existing snapshot replication that was configured in this lesson. You do not need to configure it again. If you didn't configure it, then follow the previous exercises to configure the snapshot replication.

2. Open a PowerShell console and run the following PowerShell script on the subscriber database:

   C:\Code\Lesson01\1_Activity1B.ps1 -SubscriberServer .\sql2014 -SubscriberDB WideWorldImporters -SQLUserName sa -SQLUserPassword sql@2014

   Modify the parameters as per your environment before running the script.

Generating a New Snapshot

Follow these steps to generate a new snapshot:

1. Open SQL Server Management Studio and connect to the publisher server in the Object Explorer.

2. Expand Replication | Local Publication. Right-click on the [WideWorldImporters]:WWI-Snapshot publication and select View Snapshot Agent Status from the context menu:

   

   Figure 1.79: The View Snapshot Agent Status option

3. In the View Snapshot Agent Status window, select Start to generate a new snapshot. This is another way to generate the snapshot:

   

   Figure 1.80: The View Snapshot Agent Status window

   Once the snapshot is generated, you'll get a success message, as shown in the preceding screenshot.

   You can further verify this by checking the snapshot folder or querying the MSsnapshot_history table in the distribution database.

Applying the Snapshot to the Subscriber

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

1. In the Object Explorer in SSMS, connect to the subscriber server. Expand Replication | Local Subscriptions.

2. Right-click on the [WideWorldImporters] – [WIN2012R2\SQL2016].[WideWorldImporters]:WWI-Snapshot subscription and select View Synchronization Status from the context menu.

   This is another way to run the distributor agent:

   

   Figure 1.81: The View Synchronization Status option

3. In the View Synchronization Status window, select Start to start the distributor agent. The agent will error out and the snapshot won't be applied to the subscriber:

   

   Figure 1.82: The View Synchronization Status window

4. To find out what the error is, click on View Job History in the View Synchronization Status window.

   This will open the SQL Server agent job history for the distributor job. You should see the following error in the agent history:

   

   Figure 1.83: The job history

   The distribution agent fails to drop the Sales.Orders and Sales.Orderlines tables because they are referenced by a view, vw_orders.

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.

Note

If you wish to apply the current snapshot, you will have to drop the view on the subscriber, apply the snapshot, and then create the view.

The solution for this activity can be found on page 438.