locked
Can the publication be deleted on this situation? RRS feed

  • Question

  • Hi,

    We are using SQL Server 2005 SP4 and merge & transactional replication with DB mirroring. The publisher & principle server is A, and the mirror server is B.

    Recently A had been disconnected for one day, and we then did a manual fail over to B, and let it be the new publisher. It succeeded, and the publication had been moved to the mirror server. When the publisher was back, we then tried to resume the mirroring session, but it failed.

    Later we decided to issue the command "ALTER DATABASE DB_NAME SET PARTNER OFF" to remove mirroring, it did work, but the mirroring DB on A became inaccessible, and the originally functioning merge replication was failed. It kept popping out messages like "cannot connect to the publisher A", i.e. the system identified the original publisher A as the publisher, but the real publisher for now should be B. 

    Finally we decided to restore the DB from B to A, and the DB on A was alive again. we used restore "WITH RECOVERY" and "preserver replication settings".

    However, the problem is that there are publications not only on A, but also on B! And this makes the DB on B could not be deleted. Could we just remove the publication on B? Will this action affect the publication on A? We are afraid of this.

    Could someone offer some help? We appreciate this very much. Thanks.

     



    Tuesday, November 15, 2011 2:42 AM

Answers

  • Yes, you can delete the pub on b. Doing so should not effect the pub on a

     

    Use the procedures bellow or the UI. If you use the UI, when asked to delete repl settings on the sub (a in this case) uncheck the check box and delete.

     

    EXEC sp_dropsubscription

    @publication    = @pub

    ,@article        = N'ALL'

    ,@subscriber     = N'All' 

    ,@destination_db = @db

     

     

    EXEC sp_droppublication @publication = @pub ;


    -- Yaniv www.sqlserverutilities.com http://blogs.microsoft.co.il/blogs/yaniv_etrogi
    • Marked as answer by Ivangelion.tw Monday, November 21, 2011 3:47 AM
    Thursday, November 17, 2011 6:39 AM

All replies

  • Hi Ivangelion.tw,

    I suggest you to drop and recreate the publication and subscription.You could follow this KB to drop replication.

     


    Best Regards,
    Peja

    Please remember to click "Mark as Answer" on the post that helps you, and to click "Unmark as Answer" if a marked post does not actually answer your question. This can be beneficial to other community members reading the thread.
    Thursday, November 17, 2011 5:51 AM
  • Yes, you can delete the pub on b. Doing so should not effect the pub on a

     

    Use the procedures bellow or the UI. If you use the UI, when asked to delete repl settings on the sub (a in this case) uncheck the check box and delete.

     

    EXEC sp_dropsubscription

    @publication    = @pub

    ,@article        = N'ALL'

    ,@subscriber     = N'All' 

    ,@destination_db = @db

     

     

    EXEC sp_droppublication @publication = @pub ;


    -- Yaniv www.sqlserverutilities.com http://blogs.microsoft.co.il/blogs/yaniv_etrogi
    • Marked as answer by Ivangelion.tw Monday, November 21, 2011 3:47 AM
    Thursday, November 17, 2011 6:39 AM