Removing Push Subscriptions when Publisher unavailable

Posted on 2005-04-15
Last Modified: 2012-06-27
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.
Question by:andrewbleakley
    LVL 5

    Expert Comment

    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
    LVL 11

    Author Comment

    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 ?
    LVL 5

    Accepted Solution

    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
    LVL 11

    Author Comment

    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.
    LVL 5

    Expert Comment

    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.

    Featured Post

    How to run any project with ease

    Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
    - Combine task lists, docs, spreadsheets, and chat in one
    - View and edit from mobile/offline
    - Cut down on emails

    Join & Write a Comment

    This is my first ever Article on EE or elsewhere; therefore, please bear with me if I have some discrepancies in my writing. I read many articles and questions related to "how to pass values to SSIS packages at run-time?"  Hence, this common ques…
    The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
    This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
    Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.

    734 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    17 Experts available now in Live!

    Get 1:1 Help Now