locked
Disable Replication RRS feed

  • Question

  • I am running a MS SQL 2000 DB server. I have two DB's on there currently.

    My Question:
    How do you disable replication, so that you may run an upgrade of the DB?

    In the past I would delete it, and then run the upgrade. After the upgrade was successful I would recreate the replication.

    I'd like to know how to do this in either TSQL or Ent Mgr.


    Thanks
    Thursday, February 21, 2008 3:23 PM

Answers

  • You don't need to do this for an OS or SQL Server upgrade. If you are "upgrading" the schema you might want to drop all subscriptions and then publications using

     

    sp_dropsubscription

    and then sp_droppublication

     

    if you are using merge it would be

     

    sp_dropmergesubscription and sp_dropmergepublication

    Thursday, February 21, 2008 5:20 PM
    Answerer

All replies

  • You don't need to do this for an OS or SQL Server upgrade. If you are "upgrading" the schema you might want to drop all subscriptions and then publications using

     

    sp_dropsubscription

    and then sp_droppublication

     

    if you are using merge it would be

     

    sp_dropmergesubscription and sp_dropmergepublication

    Thursday, February 21, 2008 5:20 PM
    Answerer
  •  Hilary Cotter wrote:

    You don't need to do this for an OS or SQL Server upgrade. If you are "upgrading" the schema you might want to drop all subscriptions and then publications using

     

    sp_dropsubscription

    and then sp_droppublication

     

    if you are using merge it would be

     

    sp_dropmergesubscription and sp_dropmergepublication



    It is for a DB upgrade.

    How would I then re-enable those subscriptions?
    Is there a way to specify a particular publication rather than ALL?
    Thursday, February 21, 2008 5:33 PM
  •  

    See Hilary's post, he answered your question.

     

    At the same time, why do you need to drop replication?  Replication will upgrade automatically when you upgrade SQL Server. 

    Friday, February 22, 2008 3:42 PM
  • The application I am installing\upgrading sees that there is a replication going on between the two databases, and errors informing me that there is a replication running, and it would need to be stopped in order to continue the Install\Upgrade.

    When I go to Ent Mgr and delete the Replication the Instal\Upgrades are successfull, but requires that I go back in and re-implement a replication between two databases.

    That is why I was hoping to just turn replication off, run my Install\Upgrade, and then turn the replication back on.
    Friday, February 22, 2008 4:12 PM
  • Stopped and Running are two different things.  Does the app require you to uninstall/remove replication, or just stop the agent services?

     

    Assuming the former, then you can always use the wizards to script out the install/uninstall of replication and go from there.

     

    Friday, February 22, 2008 5:01 PM