January 15, 2014 at 8:48 am
I have a 2012 publisher database that only receives new transactions over night and a subset of tables which receive new transactions every 2 hours during the day.
We have 2 subscriber servers.
The developer designed the system to turn off the Snapshot Agent during periods when no transactions are being received and turn it back on at set times.
Is this the correct way?
In my mind I believe all agents should be left running 24/7 or should we be turning the Log Reader Agent on and off instead of the Snapshot Agent.
This is my first venture into Replication so could do with some guidance.
Thanks. Tim.
January 16, 2014 at 9:15 am
There is nothing wrong with only running the Snapshot Agent at certain times. If there is nothing for it to do, i.e. you have not added or dropped any articles (tables, views, stored procs) to or from your Publication then the Snapshot Agent will start up, run a few checks, determine that all Subscribers are synchronised and then shut down again.
You can even run the Log Reader Agent on a scheduled basis if you want, but this means you will not be running 'real-time' transactional replication, more like 'batched' replication.
I suggest you grab yourself a book on SQL Server Replication or search online for articles about it. Here are a couple of links to get you started:
http://www.sql-server-performance.com/2010/transactional-replication-2008-r2/
Regards
Lempster
January 16, 2014 at 9:24 am
Thank's Lemster.
All of the transactions this db receive are at set times. My way of thinking is that while the log is being written to on the Publisher, to decativate the log reader until the Insert, Updates and Deletes are finished and then re-start the log reader so as not to cause a contention issue. The rest of the time the Publisher database will be idle. Its sole pupose is to feed the Subscibers and nothing else.
I will check out the links and do some futher reading.
January 16, 2014 at 9:48 am
It's actually the Distribution database (via the Distribution Agent) that 'feeds' the transactions to the Subscribers so you won't get any contention by running the Log Reader agent on a continuous schedule.
The links I posted were for 2008, but I don't think replication has changed in 2012 and I'm sure you can find 2012-specific articles.
Regards
Lempster
January 16, 2014 at 10:02 am
Hi Lemster.
You are correct in that the Distributor handles the feed to he subscribers. I think I wasn't clear in my explantion.
The point I was trying to make is the possible contention of the Log Reader trying to read the log whilst new transactions ar still pouring in to the Publisher. I was thinking it would be better to disable the Log Reader until the update of the Publisher is complete.
January 17, 2014 at 2:43 am
Hi Tim,
I wouldn't worry about that personally, but if you know that the Publisher database will be updated only at specific times then, yes, you could stop the Log Reader Agent for that period. In an OLTP system where inserts, updates and deletes can happen at any time and you want to keep one or more Subscribers in sync with a Publisher in near real-time, you'd run the Lor Reader Agent continuously.
Regards
Lempster
January 17, 2014 at 4:56 am
Hi Lemster.
Talking this trough has been very helpful, you have affirmed my understanding of how replication works. As you say we could just leave the log reader running.
If I have time I will try some performance comparisons to see if it makes any significant difference.
Thank you for your time.
Tim
January 17, 2014 at 6:00 am
You're very welcome. 🙂
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply