1

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.

Improve this question
2
  • Are you replicating schema changes (DDL) ?
    – Spörri
    Commented Sep 22, 2015 at 16:29
  • You should be able to make DDL changes as described in the answer. Make sure your vendor is not doing anything drastic - dropping PK, etc - replication wont allow that. Also, since you are using SQL Server 2014 Enterprise Edition, you can think of using AlwaysON and you can leverage your read requests to secondary server. Just putting it as food for thoughts.
    – Kin Shah
    Commented Sep 23, 2015 at 1:23

1 Answer 1

Reset to default
1

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

Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.