In this section, we'll discuss optimizing snapshot replication by following best practices and tuning the snapshot and distributor agent parameters.
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.
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:
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.
Open a PowerShell console and run the following PowerShell script on the
subscriberdatabase: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:
Open SQL Server Management Studio and connect to the
publisherserver in the Object Explorer.Expand Replication | Local Publication. Right-click on the
[WideWorldImporters]:WWI-Snapshotpublication and select View Snapshot Agent Status from the context menu:
Figure 1.79: The View Snapshot Agent Status option
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_historytable in thedistributiondatabase.
Applying the Snapshot to the Subscriber
You'll now apply the generated snapshot to the subscriber. To apply the snapshot, follow these steps:
In the Object Explorer in SSMS, connect to the
subscriberserver. Expand Replication | Local Subscriptions.Right-click on the
[WideWorldImporters] – [WIN2012R2\SQL2016].[WideWorldImporters]:WWI-Snapshotsubscription 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
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
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.OrdersandSales.Orderlinestables 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.