Removing Push Subscriptions when Publisher unavailable

Is there anyway to remove push subscriptions from SQL Server (2000) when the publisher is unavailable - we are extending a lients network and need to know "what if". We have tested and tried a second publisher - but we can not sign off this solution until to know how we will handle every possibility. Help appreciated we are running out of time.
LVL 11
Who is Participating?
obahatConnect With a Mentor Commented:
What I would do is the following:
If any workstations crash, bring them back to life. On the publisher (or more correctly, the distributor) - try to synch with the push subscription once the publisher server is alive again, through the Enterprise Manager GUI (the usual techniques): Check the distributor, see which machines crashed, try to bring replication to life by synchronizing, manage the subscribers and subscriptions through the EM GUI.
If that does not work (and it may not.. it depends on how you have the replication set up.. how the communication works, etc), only then I would consider removing and readding the subscribers from the publication.

Some tips I can think of:
1. Make sure that you script out the publication frequently (on the publisher), so that you can drop the publication and recreate in a case of need. Read the script. See what the actions that are taken on each server are.
2. Try to add/remove subscriptions on the publisher first, and that does not work, see if you can nuke the publication and recreate it.
3. If nothing else works - only then try to manually remove. You can easily cleanup the sys tables (you would need to clean some or all of the following table:
On the publisher:
sysmerge<> (see books on line [BOL]).
On the distributor:
Look at the tables WHERE NAME LIKE 'MS%merg%'

The best solution is: Script out your publication, understand each line in the code and see what are the actions that are being taken are, and try to use the same code in the generated script, if/once errors occur. I apologize for not being to be of more help, but further actions depend on the volume of data, specific settings and configurations, etc.

For the long run, you may wish to look into alternative methods of synchoronizing the data, since replication is not a reliable one. You can use backup/restore techniques (i.e., have each workstation backup the DB, and restore it on the central, and then aggregate the data). If you visit SQL Farms ( around August, we will have a tool available for download, that can be used as an alternative solution for replication.

Hope this helps.

SQL Farms Solutions
There are may ways to achieve the desired removal:
1. You can always remove any subscription from a publication by manually writing into the system replication tables.
(e.g., unlist the subscriber server from sysservers, cleanup the subscriptions on that subscribers, etc.). One word of caution is that you must know where to clean and how (there are several objects you would have to clean, i.e., sysservers, syssubscriptions). [I assume here that you are talking about a transaction publication].
2. Are you using a local or remote distributor? You can always remove the subscriber from the distributor,
and not have to worry about the subscriber until a later time.

I am not really sure what you're asking, and for which purpose. We have done tons of work with replication.
If you can, please be more specific and re-post.

Hope this helps for now,

SQL Farms Solutions
andrewbleakleyAuthor Commented:
We have a main office and a branch office. at each location each workstation works on a local copy of a database and each evening all the workstations synchronise with the server in there rspective office. the branch office server then synchronises (merge replication) with the main office server. given our location (the pacific islands) there is the very real possibility that the server in the branch office will somehow cease functioning. if that happens then we will be left with 10 workstations subscribing to a now non-existant server in the branch office. the data is quite critical and time is a factor (it must all occur over night ready for the next days processing). should the branch (or main office) servers fail we need to be able to unsubscribe the workstations at that location and subscribe (push) to the server at the other location. how can we achieve this ?
andrewbleakleyAuthor Commented:
Thanks that has given us what we need to complete this documentation. Give me a second and I will accept it - even though you probably broke all the rules with that advertisement near the end. Will check out the site regardless of you cheeky sales pitch. Thanks heaps mate.
Ouch. You are correct. I was not aware of this rule. I was really not trying to prompote or advertise anything, just provide technical assistance using the best of my knowledge. I apologize for doing so.
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.