Start/Stop SQL Server Replication Agent using TSQL
On a recently client engagement we ran into an issue with a nightly SQL Server job. The job was unable to generate data for a certain warehouse database. As it tuns out to be via process of elimination, I was able to figure out that there were some SQL distribution agents that were interfering with the nightly SQL job that caused the issue. I have stopped the distribution agents and ran the job manually to able to successfully populate the data, followed by re-starting the agents. The process of stopping and starting was done using the SQL Server Management studio.
Now that I knew how to populate the data, I had to automate the process of stopping and starting the distribution agents before and after running the nightly SQL job. Here are the stored procedures that can be used to achieve the same:
--STOP the Distribution Agent: sp_MSstopdistribution_agent @publisher, @publisher_db, @publication, @subscriber, @subscriber_db --START the Distribution Agent: sp_MSstartdistribution_agent @publisher, @publisher_db, @publication, @subscriber, @subscriber_db</pre>
- @publisher is the name of the Server that is publishing
- @publisher_db is the database of the publisher server
- @publication is the name of the table/object that is getting published
- @subscriber is the name of the subscriber server
- @subscriber_db is the name of the subscriber database
The above commands have to be executed in the publisher server against the 'distribution' database. Here is an example:
exec distribution.dbo.sp_MSstartdistribution_agent @publisher = 'PUBLISHERSERVER', @publisher_db = 'PUBLISHERDB', @publication = 'TABLE_EMPLOYEE', @subscriber = 'SUBSCRIBERSERVER', @subscriber_db = 'WAREHOUSEDB'