March 27, 2008 at 7:43 am
We have Transactional replication running from ONE DB on our sql server 2000 box to a SQL Server off our network. While DR testing is going on, the Tunnel to the subscriber server will be down (FOR A COUPLE OF DAYS)
What is the best way to pause replication so I don't have a problem with logs filling up etc...
I stopped the log reader on the publisher... but I don't know if that was the right thing to do?????
March 27, 2008 at 11:41 am
Unless you want to rebuild/reload all replication data on the subscriber, you will have to store the change information somewhere. You can stop the distribution agents and then the transactional change info will be held in your Distribution DB, until you restart it or until the subscriptions expire (after which you will have to rebuild/reload on the subscriber).
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
March 27, 2008 at 12:06 pm
Thanks. Should I DISABLE those distribution jobs (there are two) or just disable the schedule for both?
March 27, 2008 at 2:01 pm
I haven't done this for a while, but I think 1) disable the Agent/Job and then 2) Stop the agent if it is currently running.
The schedule(s) should not be able to run if their job is disabled.
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
March 31, 2008 at 6:53 am
thank for all your help! !!!
as I said in my original post - this was our annual DR test. With replication paused, they tested against old data on the subscriber Databsase and some some of the testing was inconsistent. wrong forms coming up, forms missgin etc..
If something did get out of sync between the publisher DB and subscriber DB at some point, how do I go about rectifying this?
March 31, 2008 at 6:20 pm
Well, if replication is "out of synch" because it was stopped or it gets behind, it should catch up when it has a chance.
If it's "out of synch" because of some other issue (this is not supposed to happen, but...) then the SOP is to push a new snapshot out (for instance, by dropping the subscription and then recreating it).
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
June 13, 2008 at 11:17 am
Here we go again.. a DR Test but a short one this time.. only a couple of hours.
While my distribution job is disabled, what would the proper procedure be to drop and re-add one article? again, we have transactional replication. our log reader is running all the time, once a day snaphot and a REPL push job every 15 minutes.
a couple of weeks one new table was added to the replication and it has never worked.. I'd like to drop just the one article and readd it.. proper procedure?
June 13, 2008 at 12:26 pm
which should I do first?
exec sp_dropsubscription @publication = N'TowerDB to KOCSQL03', @article = N'cbl', @subscriber = N'all', @destination_db = N'all'
exec sp_droparticle @publication = N'TowerDB to KOCSQL03', @article = N'cbl', @force_invalidate_snapshot = 1
GO
June 13, 2008 at 12:58 pm
Jpotucek (6/13/2008)
Here we go again.. a DR Test but a short one this time.. only a couple of hours.While my distribution job is disabled, what would the proper procedure be to drop and re-add one article? again, we have transactional replication. our log reader is running all the time, once a day snaphot and a REPL push job every 15 minutes.
a couple of weeks one new table was added to the replication and it has never worked.. I'd like to drop just the one article and readd it.. proper procedure?
The procedure should be no different if Distribution is or is not running. However, I would encourage you to wait for Distribution to be up and running again before doing because 1) you can make sure that there are not other problems that might interfere and 2) you will be able to check and see that the new areticel is replicating correctly.
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
June 13, 2008 at 1:01 pm
ok.. I understand that the procedure would be no different if the DIST agent is running or not.. but what WOULD be the procedure for droppin/re-adding an article?
June 14, 2008 at 10:02 am
It has been a long time since I did this on SQL 2000, but I think that you drop the subcriptions first, then the article. Then add the article, then add the subscription.
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
Viewing 11 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply