August 30, 2002 at 1:16 pm
Our environment is such that we replicate OLTP data to a second server where we do reporting. Whenever a change is made on publisher, users expect to see that change immediately on the subscriber when they run their report.
I am frequently asked the question, "How far behind is replication?"
Does anyone have a good way of responding to this question and actually having a good idea of what hasn't been written to subscriber yet?
Remember this when a developer tells you it will just be temporary. Temporary = Permanent.
August 30, 2002 at 2:21 pm
You can look at the log reader/distrib agent to see the latency. It'll vary depending on tons of things but the average is usually what they want. You can do a simple test by inserting a row and watching for it to pop on the subscriber. On a decent lan it'll usually be 1-10 secs. If they do a huge transaction it will take time to post.
Andy
September 5, 2002 at 9:15 am
I can add to that. I have a transactional replication system similar to yours and for the same reasons. I have seen anywhere from 3 to 15 seconds latency. It isn't 'instantaneous', but it ain't bad<g>.
I have a problem with my system that fits in to the subject of this thread. Again, I have transactional replication, the distributor and subscriber on a remote (different) server and a pull subscription....
Two nights ago I performed an upload of fresh data to our production database. The data contained a refresh of Payor and Employer information for the Child Support System in a state we work for. There are four tables involved and all of them are replicated. The refresh completely replaces the data in them. Table sizes range from 150,000+ rows to 319,000+ rows.
Since that night the distribution agent hasn't failed. However it has 'Delivering Replicated Transactions' in the 'Last Action' column.
This is a problem for us. The state of NC uses that database to provide reports and there is no data on the subscriber for yesterday. The log reader agent shows that it was copied to the distributor, however there it has stopped.
Any ideas?
Richard Dawson
ICQ# 867490
Systems & Methods, Inc.
Richard L. Dawson
Database Admin/Developer
ICQ# 867490
Richard L. Dawson
Microsoft Sql Server DBA/Data Architect
I can like a person. People are arrogant, ignorant, idiotic, irritating and mostly just plain annoying.
September 5, 2002 at 10:40 am
Sounds like it is still posting transactions. If you're in a hurry you might be better off to stop replication, reinit, send over a new snapshot. On a high speed link you can get away with huge updates, on a slower link...well, its slow! I did an update on just over a millions rows and replicated the change across a 256k link, took about 6 hours I think (other stuff using the link too of course).
Andy
September 5, 2002 at 11:21 am
Hi Andy,
Thanks for the reply. That's the part I don't get about this. These two servers are on the same segment and switch. They sit one atop the other in the same rack.
Can't do the snapshot thing. The reports server (subscriber) is also our repository. It holds all the data from day one. the production server (publisher) only holds 90 days.
I's looking like I will have to stop the subscription and update the tables manually. Ugh! I'm luck it's only 61 table total.
Richard
quote:
Sounds like it is still posting transactions. If you're in a hurry you might be better off to stop replication, reinit, send over a new snapshot. On a high speed link you can get away with huge updates, on a slower link...well, its slow! I did an update on just over a millions rows and replicated the change across a 256k link, took about 6 hours I think (other stuff using the link too of course).Andy
http://www.sqlservercentral.com/columnists/awarren/
Richard L. Dawson
Microsoft Sql Server DBA/Data Architect
I can like a person. People are arrogant, ignorant, idiotic, irritating and mostly just plain annoying.
September 5, 2002 at 12:44 pm
Before you do that you might try enabling the logging of the distribution agent, see what it says its doing, maybe profile it as well. Were the updates done as one transaction? Maybe getting blocked on the subscriber, timing out?
Andy
September 5, 2002 at 1:50 pm
Thanks Andy. I'll try it and let you know what I find out. Not sure I can wait too long on this one though. The State folks get a little anxious if they can't get the data they want.
quote:
Before you do that you might try enabling the logging of the distribution agent, see what it says its doing, maybe profile it as well. Were the updates done as one transaction? Maybe getting blocked on the subscriber, timing out?Andy
http://www.sqlservercentral.com/columnists/awarren/
Richard L. Dawson
Database Admin/Developer
ICQ# 867490
Richard L. Dawson
Microsoft Sql Server DBA/Data Architect
I can like a person. People are arrogant, ignorant, idiotic, irritating and mostly just plain annoying.
September 6, 2002 at 7:38 am
Here is the latest in my little story. I have had the Output logging turned on and I can see where things are being done. But I have not had any experience with this type of log before.
I don't understand much of what is being written. Is there anyone out there that has and can help me interpret this log?
Email me and I will send an excerpt.
TIA
Richard L. Dawson
Database Admin/Developer
ICQ# 867490
Richard L. Dawson
Microsoft Sql Server DBA/Data Architect
I can like a person. People are arrogant, ignorant, idiotic, irritating and mostly just plain annoying.
September 6, 2002 at 7:56 am
I'll look. Dont know how much I'll help, but I'll look!
Andy
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply