November 4, 2009 at 10:24 pm
I have a need to pause replication on my published database occasionally. I do not want to drop or remove transactional replication, I only want to pause it momentarily and then restart it with minimum number of issues. What is the easiest way of doing this? Stopping/disabling and then restarting/enabling just the distribution jobs on the publisher? Am I missing anything else that needs to be done? I have read other posts concerning this topic, but they seem vague or incomplete..
"Technology is a weird thing. It brings you great gifts with one hand, and it stabs you in the back with the other. ...:-D"
November 5, 2009 at 7:17 am
I suppose it depends on where you are happy to have a backlog build up. You can disable and stop the Log Reader Agent job in which case the log file on your published database will start to grow if transactions requiring replication are generated whilst it's stopped.
You could disable and stop the Distribution Agent job(s) in which case, if you leave the Log Reader Agent active, then transactions will back up in the distribution database.
Whenever I do this, I usually stop the distribution agents mainly because I have more capacity on my distribution servers than I do on my publishers. We use a 3 server architecture with publisher, distributor and subscriber each on its own server.
Once you are ready to restart replication just enable and start the jobs you stopped.
Mike
November 5, 2009 at 8:01 am
Ok, just stopping/disabling distribution agent seems to be the smart thing to do here but what if after my changes to the published database I decided they are not working and need to back them out before restarting that agent? Am I still covered here in this scenario just stopping/disabling the distribution agent only?
"Technology is a weird thing. It brings you great gifts with one hand, and it stabs you in the back with the other. ...:-D"
November 5, 2009 at 8:30 am
I'm assuming these are data changes not schema changes. I'm also assuming these changes can't be tested in an environment where they can be checked before going into production. Since you are using transactional replication, once you perform an operation, which is marked as requiring replication, on a published table that operation is replicated. In other words, if I have a table on which inserts are replicated and I insert a row and commit it then the transaction in the log file is marked as requiring replication and the Log Reader Agent will pick it up.
If you subsequently back out your changes and wish nothing to be delivered to your subscriber then, because you have paused the Distribution Agent, the easiest solution would be to drop and re-create the subscription. The commands in the distribution agent will not get picked up by the new subscription. But, without knowing your set up, this may cause problems with other parts of your replication system, it may not.
November 5, 2009 at 9:54 am
No, not possibly just data changes, schema changes and functions,stored procs,etc. We do have QA and DEV environmnts and yes, those block changes have been tested beforehand, but we still need a stop gap when we push to the production publisher db so that if there is an issue in production after a shot period of time we can make sure that those changes are not pushed on through to a subscriber server and then eventually pushed on through to an offsite subsctiber server from the onsite subscriber. Hope that helps..
"Technology is a weird thing. It brings you great gifts with one hand, and it stabs you in the back with the other. ...:-D"
November 5, 2009 at 10:05 am
At this point in time, all I can suggest is dropping replication, making your changes, putting replication back on once you are happy that all is well and then generating a snapshot to sync up the remote sites. Though that might not be possible. Have to sleep on it.
November 5, 2009 at 10:15 am
Ok thanks, but i was hoping not to remove/drop replication and recreating it every time we push to production which sometimes can be daily. That would be a real pain in the rear. I hoping for something a little more simpler like pausing it. But sometimes, it is what it is.. 🙂
"Technology is a weird thing. It brings you great gifts with one hand, and it stabs you in the back with the other. ...:-D"
November 5, 2009 at 9:16 pm
I'd recommend devloping a different philosophy... test on a dev box before you move to production to be absolutely sure it works correctly.
--Jeff Moden
Change is inevitable... Change for the better is not.
November 6, 2009 at 1:59 am
Reading is fundamental. We do test in DEV and QA before moving code to production, as I already stated earlier in this thread. Just because a block change has been tested in DEV and QA does not necessarily guarantee that same code is going to work in a production environment every single time. Haven't you ever had this happen before? I kind of find that hard to believe with all your experience. Well, we have and because of this, we always have to have a rollback strategy just in case the tested code for what ever reason falls flat on its face in production. Even with the best QA testing cycle in place, thngs have been known to get through the fence and you have to be ready to handle that contigency. This has got nothing to do with first testing the code in DEV and QA, which we do emphatically anyway. But you never know what is going to happen when you push code to a production environment for the very first time. Granted, most of the time the push to production is uneventful because it has been thoroughly tested beforehand. However, every once in a while stuff happens. So, I have to be sure the new code is good with at laast a quick final run in prod before I turn it loose to our users and let it start replicating to our subscriber db server, and eventually to an offsite db server as well. This is just good common sense. Otherwise, this would be like trying to close the barn door after the horse has already galloped down the road... It's too late by then. 🙂
"Technology is a weird thing. It brings you great gifts with one hand, and it stabs you in the back with the other. ...:-D"
November 6, 2009 at 10:04 pm
talltop-969015 (11/6/2009)
Just because a block change has been tested in DEV and QA does not necessarily guarantee that same code is going to work in a production environment every single time. Haven't you ever had this happen before?
Nope...
I kind of find that hard to believe with all your experience.
... and experience in how to setup Dev for testing is the reason why. 😉
I do agree, though... you've got to have a backout plan just in case. On the replication side of things, I've not had to deal with that too much but one of the guys at the old job had to deal with it every day just like you need to. He developed a T-SQL script that would take just a couple of parameters and it took care of the drop and rebuild of replication by table on both the distributor and the subscriber. It seemed to be a pretty simple script. I don't have the script, either, or I'd share it with you on this thread.
--Jeff Moden
Change is inevitable... Change for the better is not.
November 6, 2009 at 11:14 pm
Oh I can script out replication and drop and rebuild it, that is not a problem. However, as my post stated earlier though, I am looking for an easier way to solve this than completely dropping replication. Thanks anyway..
"Technology is a weird thing. It brings you great gifts with one hand, and it stabs you in the back with the other. ...:-D"
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply