Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Is it possible to turn off replication? Expand / Collapse
Author
Message
Posted Wednesday, July 30, 2008 8:25 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Tuesday, September 23, 2014 11:06 AM
Points: 57, Visits: 520
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
Post #543540
Posted Wednesday, July 30, 2008 9:04 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Monday, October 13, 2014 9:22 AM
Points: 7, Visits: 281
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.
Post #543587
Posted Wednesday, July 30, 2008 9:29 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Tuesday, September 23, 2014 11:06 AM
Points: 57, Visits: 520
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!
Post #543620
Posted Thursday, August 14, 2008 1:31 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 1:17 PM
Points: 35,350, Visits: 31,890
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."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #553026
Posted Thursday, August 14, 2008 2:55 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 1:17 PM
Points: 35,350, Visits: 31,890
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. :P


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

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #553123
Posted Friday, August 15, 2008 7:58 AM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 1:17 PM
Points: 35,350, Visits: 31,890
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."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #553501
Posted Monday, March 10, 2014 6:05 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Yesterday @ 9:56 AM
Points: 2,406, Visits: 1,000
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


SQL 2K acts like a spoilt child - you need to coax it round with lollipops.
Post #1549199
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse