August 25, 2003 at 2:30 pm
In working with replication my coworkers and I have found some interesting facts when deleting Subscriptions from Publications. Given the following scenario how/where and what would you suspect the issue is.
We have a Publisher Db and Distributor Database. There are also 2 subscribers. One is stationary to the network and the other is remote to the network. One morning we decided to make an update to a table that was listed in a subscription. The update was to simply update a users password when all of a sudden we noticed that after running UPDATE Employee SET Password = ENCRYPT('myPass') we forgot to set the where clause. I’m sure we have all been there, right??. After kicking our selves and the ass’s for making such a foolish mistake we then deiced to simply restore the single table by restoring the DB on another server and then inserting the data back into our employee table.
The first step was to delete the subscription from the publication.
The second was to delete the current records in the Employee DB and Insert into from our backed up table.
The problem became when we noticed that we were unable to perform the INSERT INTO due to the fact that the Db still recognized the table as being part of a Merge Replication. There were also some identity conflicts, which forced us to run some stored procedure to fix those issues.
We couldn't figure it out because by clearly looking at the Distributor and Publisher screen in Enterprise manager there was no indication that any subscriptions were running or even existed. However we did notice that on our Stationary Subscriber it still indicated that there was a subscription. We soon realized that if it existed there then it must also exists on the remote subscriber which was hard to identify due to the fact the users was not on the network yet. Because of the apparent existance of the subscription there was a lock on our table Employees prohibiting us from doing almost everything including restoring our table. Unfortunately it is impossible to delete just a subscription on your subscriber machines. DUMB
Anyway we continued to be perplexed by this situation and unfortunately the table was crucial to our day-day-operations and it supported one major intranet application. So on top of users screaming down our backs we continued to try and test some theories. We finally were able to pin point the problem.
When you want to delete a Subscription from your publication you must always do it from the Publisher server and NOT the Distributor server. Try it and you will see that in a merge replication process by deleting any subscription from the Distributor the Subscription can still be seen on any subscriber machines and you would not be able to rename, delete, InsertInto, pretty much nothing until you delete it from the Source which in this case was the Publisher Db.
Thought people would like to know about this since its not documented anywhere that I know of. Anyway if you have any comments please leave them.
Edited by - kbrady on 08/25/2003 2:33:03 PM
Edited by - kbrady on 08/25/2003 2:36:16 PM
August 25, 2003 at 3:16 pm
For me it is always better to use the stored procedures to do anything with replication.
The sp sp_dropMergeSubscription must be run on the publisher DB on the publisher.
If you trace the drop subscriber operation made by EM, you will notice that it uses this sp.
And I don't understand how could you delete the subscription from the distributor. I asume that you try to do it from the Replication Monitor, but you can't delete a subscription from there. So from EM you should do it in Replication/Publications/YouPub, but doing this you are standing in the Publisher.
August 26, 2003 at 7:07 am
I believe what happened is that you can view the various publications within the your Distributor machine locate underneath Replication Monitor. What occurred was that the actual publications could be deleted from here; however the subscriptions would not be deleted from your subscriber’s machines? So Replication would be gone but the locks on the tables set up in a publication would still remain.
But I guess I would have to ask: Why would Microsoft set this up this way? If I have 50 laptops that are all part of a Publication that and I wanted to physically drop those subscriptions so that I can restore data then if those laptops are not on the network then it would still place a lock on those files unless I completely delete the publication. And Im still not sure if by doing that, that it would fix the whole lock issue.
Edited by - netguy on 08/26/2003 07:06:58 AM
September 3, 2003 at 5:15 pm
While your replication problems are interesting I'm stumped as to why you didn't just use your subscription table to update the publisher using a simple update query against the subscriber table. You would then have had your data back within minutes.
BTW: That is precisely why any update on the publisher that I do is ALWAYS in a non-commited transaction that does a select, action, select so that I can view the results before commiting the action. If you get in the habit of doing such you will seldom run into the problem you had. 🙂
As far as using the SP to handle the dropping of the subscriber you will also have to make sure you run the appropriate SP on the subscriber as well. Otherwise the publisher thinks the subscription is gone but the subscriber will still think it is a valid subscriber.
Gary Johnson
Microsoft Natural Language Group
DBA, Sr. DB Engineer
Gary Johnson
Microsoft Natural Language Group
DBA, Sr. DB Engineer
This posting is provided "AS IS" with no warranties, and confers no rights. The opinions expressed in this post are my own and may not reflect that of my employer.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply