June 21, 2011 at 1:42 pm
We periodically refresh development and test environments that have transactional replication set up. In the past, various methods have been used to remove replication before restoring backups of production data. They include:
1) Manually removing publications and associated jobs (i.e. log reader, distribution and snapshot).
2) Run EXEC sp_dboption 'MyDatabase_DevTest','published','false'. This seems to remove the option, the publications and the jobs. When this is used, it does not appear that data in the distribution database for the publication and subscriptions is removed. This proc is to be deprecated.
3) Run sp_removedbreplication. This seems to remove the option, the publications and the jobs. When this is used, it does not appear that data in the distribution database for the publication and subscriptions is removed.
4) Run sp_replicationdboption. I haven't had a chance to test this, but it is what is actually called by sp_dboption for replication option changes.
Is there a defined process for removing replication for a published database before restoring it that removes the published option, the publications, the jobs and the data in the distribution database? I spent quite a bit of time with Books Online this morning looking for a definitive answer to this question. When we used option #2 for an environment a couple of months ago, everything worked fine when we set up replication from scripts taken before the environment refresh, so maybe removing the data in the distribution database isn't really an issue. I did notice this morning, as we began the refresh of a different environment, that there are still transactions in MSrepl_transactions even though we ran sp_repldone on the database that was previously published. We're trying to determine the correct steps for doing this.
June 23, 2011 at 12:58 am
I don't know if I've understood your problem completely so let me know if I've gone off on a tangent.
We do the following in our environments:
- Backup and restore the publisher DB on our dev/test environments. If we have replication setup in these environments we first drop the existing replication setup before doing this. When we drop the existing replication setup we take care of removing everything - all publications/subscriptions are removed, distribution DB is dropped and distributor is removed.
- Run sp_removedbreplication to get rid of all the replication related settings on the restored publisher DB
- Configure replication in our dev/test environments using the restored publisher DB as the publisher.
The detailed steps we use to remove the replication setup are:
a) Run sp_droppullsubscription on each of the subscribers for each of the subscriptions (for pull subscriptions - this is not needed for a push subscription)
b) Run sp_dropsubscription on the publisher for each of the subscribers and each of the publications
c) Run sp_droppublication on the publisher for each of the publications
d) Run sp_replicationdboption on the publisher to set the publish option to false
e) Remove the distributor setup - run sp_dropdistributor, sp_dropdistributiondb and sp_dropdistpublisher on the distributor
In our environments we have some extra checks to take care of removing the replication setup for only one subscriber (we have multiple subscribers and need to be able to drop and setup replication for one or all subscribers). This method has worked well for most of the time for us - there have some instances where we had network issues during the deployment which caused some weird problems but otherwise this approach works fine for us.
I can share our deployment scripts if you want - though they'll probably be pretty confusing (we're trying to move our deployments to powershell sometime in the near future and hopefully things will be easier then ;-))
June 23, 2011 at 2:04 pm
It's really a matter of determining conclusively what works to "tear down" replication before refreshing a development or QA environment with the least effort. I believe that we've determined that sp_replicationdboption removes the distribution and snapshot jobs and the data in the distribution database associated with publications, though I'm not the person who tested that earlier today. We have used sp_dboption in the past but we're trying to move away from that because it could go away in the next version.
June 24, 2011 at 12:12 am
I believe that we've determined that sp_replicationdboption removes the distribution and snapshot jobs and the data in the distribution database associated with publications, though I'm not the person who tested that earlier today.
From http://msdn.microsoft.com/en-us/library/ms188734(v=SQL.90).aspx:
This stored procedure is executed at the Publisher on the publication database or at the Subscriber on the subscription database. The procedure removes all replication objects from the database in which it is executed, but it does not remove objects from other databases, such as the distribution database.
We tend to use sp_removedbreplication to remove replication related objects from the publisher and subscriber when other methods don't work - the distributor and distribution DB have to be removed by sp_dropdistributor and sp_dropdistributiondb as sp_removedbreplication does not take care of this.
November 16, 2018 at 1:10 pm
winash - Thursday, June 23, 2011 12:58 AMI don't know if I've understood your problem completely so let me know if I've gone off on a tangent.We do the following in our environments:- Backup and restore the publisher DB on our dev/test environments. If we have replication setup in these environments we first drop the existing replication setup before doing this. When we drop the existing replication setup we take care of removing everything - all publications/subscriptions are removed, distribution DB is dropped and distributor is removed.- Run sp_removedbreplication to get rid of all the replication related settings on the restored publisher DB- Configure replication in our dev/test environments using the restored publisher DB as the publisher.The detailed steps we use to remove the replication setup are:a) Run sp_droppullsubscription on each of the subscribers for each of the subscriptions (for pull subscriptions - this is not needed for a push subscription)b) Run sp_dropsubscription on the publisher for each of the subscribers and each of the publications c) Run sp_droppublication on the publisher for each of the publicationsd) Run sp_replicationdboption on the publisher to set the publish option to falsee) Remove the distributor setup - run sp_dropdistributor, sp_dropdistributiondb and sp_dropdistpublisher on the distributorIn our environments we have some extra checks to take care of removing the replication setup for only one subscriber (we have multiple subscribers and need to be able to drop and setup replication for one or all subscribers). This method has worked well for most of the time for us - there have some instances where we had network issues during the deployment which caused some weird problems but otherwise this approach works fine for us.I can share our deployment scripts if you want - though they'll probably be pretty confusing (we're trying to move our deployments to powershell sometime in the near future and hopefully things will be easier then ;-))
Hello Winash,
Did you ever get a chance to build the PowerShell scripts for automating disabling and enabling Replications?
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply