I am new to SQL Server replication. I was able to setup transactional replication successfully, but I feel like I am missing some important concepts regarding security and accounts. I have a few basic questions:
1 – Location of Snapshot.
Currently my distribution server and publication is on the same machine so my path is local and I guess it works. Besides distribution server does publisher also need access to "Snapshot" directory? Say what if my distribution server was on a different PC within LAN (I can use UNC) or maybe on a completely different remote location, then how would publisher access the snapshot?
2 – Publisher Security
What is the role of the Snapshot Agent here, what resources does it need access to? is it asking for the access to the physical "SnapShot" directory?
And what about Log Reader Agent, which resource does this user needs access to?
3 – Subscription Security
"Agent Process Account", what is the role of this user? what resources does this user needs access to at subscriber?
Best Answer
Firstly, it doesn’t matter how you configure your replication, the snapshot agent always runs on your distributor. It takes a snapshot of your publication and puts it in snapshot folder, then depends on your replication type, it will be send to subscriber by either distribution agent or merge agent. So publisher doesn’t need access to snapshot folder, actually no agent will be running on publisher.
Secondly, as the snapshot agent takes snapshot from publication and puts it in snapshot folder, it will need access to publisher and the folder. The log reader agent runs on distributor and reads transaction log on publisher then moves it to distribution database, so it will need access to both publication database and distributor database.
Thirdly, “Agent process account” here is the account setting for distribution agent. In transactional replication, distribution agent will apply the initial snapshot to subscriber and also takes transactions from distribution database to subscriber, so it will need access to both distribution and subscriber database, as well as snapshot folder.
If you need more information about replication, please refer to Book Online.
The only reason the snapshot directory need to be a share is when the subscribers are pull subscribers.Whatever account you are pulling with (the account the distirbution agent runs under on the subscriber, or the publisher login account) needs to be able to read the snapshot share and read files and list files and folders permissions on c:\snapshot.