Is it possible to temporarily remove Transactional Replication from a publisher database in order to allow a release to be executed against the database?
If I stopped the log reader agent would I be able to perform DDL/DML changes against the publisher database without error?
The product vendor has a bespoke tool which issues the T-SQL commands against the database for the release, which will include dropping and recreating constraints, keys, indexes etc. I need to make sure releases can happen without error caused by replication – the database needs to allow the changes.
Publisher and Distribution db live on the same server. Both publisher and subscriber are SQL 2014 Enterprise. I am replicating the entire database.
Help is appreciated.
Thanks
Peter
Update: Yes I am replicating schema changes
Update: Is this the answer? https://stackoverflow.com/questions/15673829/resume-replication-after-restoring-publication-database
Update: I've tested our vendor's release on a published database and found that dropping stored procedures is the first issue we see.
Best Answer
This seems to be the best method: Script the Publication -> Drop Subscriptions -> Drop Publication -> Re-create them
I was hoping there would be a 'trick' with replication where I could leave subscribers in place and just script out the publisher and recreate it. I don't see a clear way to restart replication without doing a full rebuild/reinitialise.
What I have noted is that this method does work pretty well once you have established replication for a database, worked out all the issues, scripted it all out; running those scripts does simplify the process.
Hope this helps someone