3

When I use the GUI to make any changes to transactional replication on SQL server, I live in fear of the "the changes you have made require a reinitialization of all articles" dialog.

I have had this happen even when doing something simple like adding or removing individual tables.

How do I avoid this?

Are there replication settings that impact when a full reinitialization is required? I found this blog post that claims that publication properties @immediate_sync and @allow_anonymous should be set to 0, I've done that but I've still sometimes had it prompt to reinitialize the whole thing.

Should I be scripting the add/remove instead of using the GUI?

Also, we frequently remove a table from replication but need to add it back in later, are there scripts out there to preserve the replication settings so this process is easier?

1
0

I've done that but I've still sometimes had it prompt to reinitialize the whole thing.

The snapshot will only contain the new table that has been added.

Also, we frequently remove a table from replication but need to add it back in later, are there scripts out there to preserve the replication settings so this process is easier?

Script out replication - right click - script to file. Change the parameters @immediate_sync and @allow_anonymous should be set to 0.

Run the script post completion of your work.

Also, check my answer for Transactional replication altering tables and adding stored procedures that will help when you are adding columns to existing replication.

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.