1

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.

  • 1
    You mentioned it is working when you are using `E:\' drive on subscriber. Then you said you are getting error. Are you looking for best practice advise? – SqlWorldWide May 3 '18 at 16:58
1

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

  • As per your comment,default location of snapshot folder is in Distributor(in my case distributor is at subscriber),so it will be D:\Program Files\Microsoft SQL Server\MSSQL13.MSSQLSERVER\MSSQL in Distributor.So when i create publisher,the snapshot agent should have access to snapshot folder in distributor.Am i correct? – sparktech May 3 '18 at 19:29
  • Modified my answer based on your comment. – SqlWorldWide May 4 '18 at 12:20

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.