While configuring a publisher for replication, we execute sp_startpublication_snapshot SP and provide the publication name as a parameter to the SP. We do not specify the distribution server's login/password. So, I am confused if the snapshot agent runs on the publisher or distributor. Even if it runs on distributor then how can we trigger it from publisher (sp_startpublication_snapshot has to run at publisher as per MSDN tutorials)
SQL Server – Determining the Server for Snapshot Agent
replicationsql server
Related Question
- SQL Server – Replicating Source Server to Destination Server Using Distribution Server
- SQL Server Replication – Will Commands Build Up if Distribution Agent Stops?
- SQL Server – Transactional Replication and Security Setup
- SQL Server Replication – Unable to Start Agent Job Due to Incorrect Username or Password
- SQL Server – Fix Snapshot Agent Exception in Replication Setup
- SQL Server – Snapshot Replication Scripts Not Executing
Best Answer
The Snapshot Agent (snapshot.exe) runs on the Distributor.
From Books Online:
Replication Agents Overview
However, the stored procedure sp_startpublication_snapshot is executed at the Publisher on the publication database and is used to start the Snapshot Agent job that generates the initial snapshot for a publication.
When you configured replication and created the publication, you specified the accounts in which the Snapshot Agent will run under at the Distributor and the Publisher. This account information is saved. This is covered in Snapshot Agent Security.
Even though you execute sp_startpublication_snapshot from the Publisher, it already has all of the necessary information to connect to the Distributor and start the Snapshot Agent.