July 30, 2011 at 9:50 am
Right now replication has got me stressed to the max
Details:
Transactional Replication
1 Pull Subscriber
About 20 or so articles, starting with a limited set so shouldn't be more than a hundred thousand commands per day (would eventually like to add additional high transaction articles getting 10-15 million inserts per day, but starting very limited
pulling every 10 seconds ( I have tried continuous, and other various schedules)
snapshot already generated and delivered (probably reinitialized over 100 times trying to get replication working, in addition to starting over from scratch at least 5 times)
NO ERRORS ON EITHER SIDE!! No errors in Log Reader, No errors in Subscriber job history, no errors on any SQL Log. All jobs are always running as if everything is working flawlessly. But performance is always critical and tracer tokens don't ever return, except right after I recreate replication from scratch I can get them to return.
NO transaction blocking on either side, I have been monitoring very closely.
NO IO waits that are oddly out of the ordinary. BOTH servers are on dedicated Fusion IO cards ... I know typical question is to ask if there are IO bottlenecks
Commands are making it to the MSrepl_commands table
Nothing is making it to to the subscriber, or if they are so slowly I am not picking up on it. Again no errors on the subscriber job.
Steps already taken:
I have tried increasing command batch size and threshold in Agent Profile, which seems unnecessary for this load in the first place..i'll try but at this point i'll try anything
obviously tried recreating replication many times, restarting agents many times, even servers at one point
regenerating snaphots, reinitializing many times.
tried a odd suggestion about changing the status of my articles
yelling, whining, evil staring, cursing
If anyone has any good suggestions on anything else overlooked it would be much appreciated! Whats the best way to watch transactions being submitted to the subscriber (not inserted into the distributor, I know how to do that)?
Thank you!!
July 30, 2011 at 10:23 am
Have you tried changing to push instead of pull, and seeing if it works then?
Tom
August 8, 2011 at 5:15 am
What about distribution job? It appears log reader agent job is running fine as you can get data to distribution db. There is something missing between distributor and subscriber. I would check the REPL-distribution job and also have a look at Tom's comment.
M&M
August 8, 2011 at 9:32 am
Confirm the fallowing:
1) Has Snapshot been completed?Bulk copy and insert both needs to be completed.I have noticed sometimes it takes more than 2-3 hours(500GB Publisher) to complete and, then you start noticing your transactions at the Subscriber.You need to find out undistributed commands in the replication monitor or select * from distribution..msdistribution_status in the distribution database.If you see large amount of commands in UndelivCmdsInDistDB, you might have to wait till it goes down.
2)Check your Log Reader agent is running.Can be verified in the Replication monitor.
Thank you,
Rohan Joackhim
August 8, 2011 at 9:33 am
Thank you Tom, Mohammed and Rohan for your replies. Apologies for not updating this case sooner. The issue ended up being more network configuration related where the subscriber could see the publisher / distributor without any issue, but the publisher was having trouble communicating back with the subscriber.
Besides the configuration update; It's possibly an unconventional update but I moved the distributor to the same server as the pull subscriber instead of having it on the publisher server for some noticeable gains in performance. I am not recommending this for other's people configurations, but in our environment the publishing server is already over-utilized so this ended up being a beneficial change.
Thanks again for your replies
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply