SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Is it possible to turn off replication?


Is it possible to turn off replication?

Author
Message
Matt Klein-228552
Matt Klein-228552
SSC-Addicted
SSC-Addicted (420 reputation)SSC-Addicted (420 reputation)SSC-Addicted (420 reputation)SSC-Addicted (420 reputation)SSC-Addicted (420 reputation)SSC-Addicted (420 reputation)SSC-Addicted (420 reputation)SSC-Addicted (420 reputation)

Group: General Forum Members
Points: 420 Visits: 589
The application is doing an upgrade, and replication needed to be stopped (vendor's statement was "broken") during the upgrade. I extracted the scripts to later rebuild the publisher and subscription on the A server, and dropped those two, then disabled the jobs.

The upgrade continues to fail because it sees replication as "still running". Any ideas?

Thank you!

Matt
fmylon
fmylon
SSC Journeyman
SSC Journeyman (81 reputation)SSC Journeyman (81 reputation)SSC Journeyman (81 reputation)SSC Journeyman (81 reputation)SSC Journeyman (81 reputation)SSC Journeyman (81 reputation)SSC Journeyman (81 reputation)SSC Journeyman (81 reputation)

Group: General Forum Members
Points: 81 Visits: 324
in case of MERGE replication:

if it is a matter of schema changes, then perhaps the upgrade should be made in a diferent way, depending on the sql version:
-- in 2000 speciall stored porcedures should be called for the new schema to be applied
-- in 2005, "alter column" statments can also be replicated if you run the script on the publisher.

in both cases new tables should be added as new articles to the publication.

breaking replication and re-initialzing would be nice to avoid, specially for big db's and slow unstable netwroks. it could take hours to re-initialize

what you really need to take care off before you begin upgrading, is to make sure the application users are not using the application and that every data is merged in the publisher.

in this case you dont have any fear of loosing any data in case you need to re-intialize.

give more info on your case.
Matt Klein-228552
Matt Klein-228552
SSC-Addicted
SSC-Addicted (420 reputation)SSC-Addicted (420 reputation)SSC-Addicted (420 reputation)SSC-Addicted (420 reputation)SSC-Addicted (420 reputation)SSC-Addicted (420 reputation)SSC-Addicted (420 reputation)SSC-Addicted (420 reputation)

Group: General Forum Members
Points: 420 Visits: 589
Thank you - actually I found (from the vendor notes) a reference to linked servers, which led me back, eventually, to the replication area where right-clicking I saw a "disable replication" option which I had not seen before. I ran that, and it cleaned everything up for me.

Upgrade racing onward.

Thanks all for you time!
Jeff Moden
Jeff Moden
SSC Guru
SSC Guru (214K reputation)SSC Guru (214K reputation)SSC Guru (214K reputation)SSC Guru (214K reputation)SSC Guru (214K reputation)SSC Guru (214K reputation)SSC Guru (214K reputation)SSC Guru (214K reputation)

Group: General Forum Members
Points: 214026 Visits: 41979
Matt Klein (7/30/2008)
Thank you - actually I found (from the vendor notes) a reference to linked servers, which led me back, eventually, to the replication area where right-clicking I saw a "disable replication" option which I had not seen before. I ran that, and it cleaned everything up for me.


I really need to disable all replication on a particular server, but only temporarily. Where is this wonderful "disable replication" option that you speak of?

Thanks, folks.

--Jeff Moden

RBAR is pronounced ree-bar and is a Modenism for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column.
If you think its expensive to hire a professional to do the job, wait until you hire an amateur. -- Red Adair

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
Jeff Moden
Jeff Moden
SSC Guru
SSC Guru (214K reputation)SSC Guru (214K reputation)SSC Guru (214K reputation)SSC Guru (214K reputation)SSC Guru (214K reputation)SSC Guru (214K reputation)SSC Guru (214K reputation)SSC Guru (214K reputation)

Group: General Forum Members
Points: 214026 Visits: 41979
I think I've got this... I'd have to disable all 70 Log Reader Agents... and then turn on just the ones that were previously disabled. Sounds like a real pain. I'll look into writing a script for that (I know... system table modification) and it may be good fodder for an article.

In the mean time, I've modified my "delete crawler" script to limit deletes to batches of 40k rows... it'll take about 35 hours to run in the highly replicated environment, but that's what weekend runs are for. Tongue

--Jeff Moden

RBAR is pronounced ree-bar and is a Modenism for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column.
If you think its expensive to hire a professional to do the job, wait until you hire an amateur. -- Red Adair

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
Jeff Moden
Jeff Moden
SSC Guru
SSC Guru (214K reputation)SSC Guru (214K reputation)SSC Guru (214K reputation)SSC Guru (214K reputation)SSC Guru (214K reputation)SSC Guru (214K reputation)SSC Guru (214K reputation)SSC Guru (214K reputation)

Group: General Forum Members
Points: 214026 Visits: 41979
I'm such a dummy... to do this in almost a single click, I just need to turn of the SQL Server Agent and turn it back on when I'm done. Thanks anyway, folks.

--Jeff Moden

RBAR is pronounced ree-bar and is a Modenism for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column.
If you think its expensive to hire a professional to do the job, wait until you hire an amateur. -- Red Adair

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
quackhandle1975
quackhandle1975
SSCarpal Tunnel
SSCarpal Tunnel (4.2K reputation)SSCarpal Tunnel (4.2K reputation)SSCarpal Tunnel (4.2K reputation)SSCarpal Tunnel (4.2K reputation)SSCarpal Tunnel (4.2K reputation)SSCarpal Tunnel (4.2K reputation)SSCarpal Tunnel (4.2K reputation)SSCarpal Tunnel (4.2K reputation)

Group: General Forum Members
Points: 4208 Visits: 1242
Jeff Moden (8/15/2008)
I'm such a dummy... to do this in almost a single click, I just need to turn of the SQL Server Agent and turn it back on when I'm done. Thanks anyway, folks.


Hi Jeff,

Sorry to dig up an old thread, I am looking to disable (not drop) on an 2005 instance where dbs are going to migrated from. I only want to disable since if migration fails and we need to backout and turn the replication back on, can you simply stop the SQL Agent? (This wil be stop anyway.)

Thanks

qh

Who looks outside, dreams; who looks inside, awakes. – Carl Jung.
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search