SQL Server 2012 Replication – How to Script and Manage During Deployment

replicationsql-server-2012

The scenario that I'm in is:

We have a database that is replicated but its objects (tables, etc.) are updated via Entity Framework. Whenever a schema change is made to the database by the SEs, we need to turn off replication, allow EF to make the changes to any replicated articles, and then turn replication back on. Currently this is done manually but we want to automate it for Octopus Deploy.

What is the process to script turning off replication and then turning it back on?

Best Answer

--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>

Example:
exec distribution.dbo.sp_MSstartdistribution_agent @publisher  =         'PUBLISHERSERVER',
@publisher_db   = 'PUBLISHERDB',
@publication    = 'TABLE_EMPLOYEE',
@subscriber     = 'SUBSCRIBERSERVER',
@subscriber_db  = 'WAREHOUSEDB'

All code above credited to this article: https://cavemansblog.wordpress.com/2012/03/12/startstop-sql-server-replication-agent/

Related Question