February 24, 2020 at 3:02 pm
We are running a transactional replication with 2 DB publication. Configuration is below:
SVR_A = DISTRIBUTOR + PUBLISHER
SVR_B = SUBSCRIBER
Due to a system upgrade, I'm planning to re-initialize replication from scratch. So I have the steps on removing the existing replication.
[1] ON SVR_A - DROP SUBSCRIPTION
USE Published_DB;
EXEC sp_dropsubscription @publication = N'Published_DB_Publication',
@subscriber = N'all',
@destination_db = N'Published_DB',
@article = N'all';
[2] ON SVR_B - CLEANUP SUBSCRIPTION
USE Subscription_DB;
EXEC sp_subscription_cleanup @publisher = @SVR_A,
@publisher_db = 'Published_DB',
@publication = 'Published_DB_Publication';
[3] ON SVR_A - DROP PUBLICATION
USE Published_DB;
EXEC sp_droppublication @publication = N'Published_DB_Publication';
[4] ON SVR_A - REMOVE REPLICATION
EXEC sp_removedbreplication 'Published_DB_Publication';
[5] ON SVR_A - REMOVE SUBSCRIBER
EXEC sp_dropsubscriber @subscriber = 'SVR_B'
[6] ON SVR_A - REMOVE PUBLISHER AND DISTRIBUTOR
USE Master;
EXEC sp_dropdistpublisher @publisher='SVR_A';
EXEC sp_dropdistributiondb 'distribution';
EXEC sp_dropdistributor;
I tested this on a test environment and worked as desired. However, did I miss anything?
Thanks
February 24, 2020 at 5:31 pm
get rid of the sql agent jobs
they don't always disappear as you might expect
also - when you recreate everything your distributor is going to need passwords, get these in a safe place 🙂
MVDBA
February 28, 2020 at 3:34 pm
I added the last 2 lines on my STEP 3 below.
[3] ON SVR_A - DROP PUBLICATION
USE Published_DB;
EXEC sp_droppublication @publication = N'Published_DB_Publication';
USE master;
EXEC sp_replicationdboption @dbname = 'Published_DB', @optname = N'publish', @value = N'false';
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply