Is it possible to turn off replication?

Question

Is it possible to turn off replication?

Matt Klein-228552

SSCommitted

Points: 1520
More actions
July 30, 2008 at 8:25 am

#191863

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

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

fmylon SSC Veteran Points: 227 More actions · Answer 1

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 SSCommitted Points: 1520 More actions · Answer 2

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 SSC Guru Points: 1003851 More actions · Answer 3

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.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Jeff Moden SSC Guru Points: 1003851 More actions · Answer 4

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. 😛

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

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Jeff Moden SSC Guru Points: 1003851 More actions · Answer 5

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.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

quackhandle1975 SSChampion Points: 11055 More actions · Answer 6

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

[font="Tahoma"]Who looks outside, dreams; who looks inside, awakes. – Carl Jung.[/font]