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?

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

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/

| improve this answer | |

If you open the properties of the publication, then select the Subscription Options section, you will see a property named Replicate Schema Changes. Set that to true and any schema change will be replicated and display in the Distributor to Subscriber History in Replication Monitor as DDL change. No need to stop and restart any agents. I have found that you need to use alter and cannot use sp_rename to rename a column.

Of course, test in a non-production environment first.

| improve this answer | |

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.