SQL Server Snapshot Folder Access – How to Guide

replicationsnapshotsql server

I have set up a Transaction Replication, with the distribution server as subscriber itself and implemented pull subscription, to avoid load on my publisher.

When I created publisher at my source server, I got snapshot agent error saying that it cannot access the default D:\Program Files\Microsoft SQL Server\MSSQL13.MSSQLSERVER\MSSQL folder location. So I changed and gave a file location at Subscriber E:\ drive and it worked.

My question here is, if it is pull subscription with distribution server as subscribe:

  • Where should the snapshot folder be?

Because I see access issue.

Please comment if I am not clear.

Best Answer

You could have used the default location by mentioning it an UNC path. Snapshot Agent job will need write and Distribution Agent job will need read privilege. Snapshot file location (directory) is a property of Publication, keep that in mind when you mention alternate location.

When configuring a Distributor through the Configure Distribution Wizard or the New Publication Wizard, the snapshot folder defaults to a local path: X:\Program Files\Microsoft SQL Server\\MSSQL\ReplData. If you are using a remote Distributor or pull subscriptions, you must specify a UNC network share (such as \\snapshot) rather than a local path.

Reference:

The Snapshot Agent must have write permissions for the directory you specify, and the Distribution Agent or Merge Agent must have read permissions. If pull subscriptions are used, you must specify a shared directory as a universal naming convention (UNC) path, such as \computername\snapshot.

Alternate snapshot locations enable you to store snapshot files in a location other than, or in addition to, the default location, which is typically located on the Distributor. Alternate locations can be on another server, on a network drive, or on removable media such as CD-ROMs or removable disks.

More about securing snapshot folder.

Secure the Snapshot Folder

Related Question