November 25, 2009 at 8:09 am
We are mirroring a large database for disaster recovery purposes from one location to another over a VPN. We use the high performance (asynch) mode. We do not use any automatica failover and no witness servers. At night when the network is quiet, the mirroring catches up, and moves data at 80% or so of the link speed. During the day when the links are busier, the mirroring stops and starts on a semi-regular pattern. On for 20 to 30 seconds, then off for 2 minutes or so. We get way behind. We get many log entries showing "the mirroring connection to ... has timed out after 45 seconds".
I have tried changed the partner timeout setting to many values, from 10 to 45 seconds. Doesn't have much effect other than the number of log entries.
Questions:
Has anyone seen a MS document that talks about the lower level details of mirroring? How long after a mirroring connection is lost does SQL Server start a retry? Any way to throttle the speed at which it tries to send data (If we could slow it down, it would probably run faster overall)?
Any TCP/IP settings adjustments that would help? Both servers are on high speed networks, but I'd be quite willing to sacrifice local performance to improve the internet performance.
Thanks in advance, I know I'm fishing for answers that only a few people have.
Student of SQL and Golf, Master of Neither
November 25, 2009 at 8:41 am
Hey there,
We run the same setup as you. We mirror 8 databases (MS suggested is upto 10 but not sure what stats thats based on). Our main database is bursty and generates upwards of 110GB of transactions a day. Sometimes the mirror gets lagging behind but it always catches up. We have a 20mbps link to our dr site, which we never push to sweat.
To answer your question, i'm sorry i dont know of a way to throttle the mirror or slow it down (if you want to do that, sounds more like a case for log shipping). I can tell you that there is a finite amount of data that a mirror can slip behind and still be recoverable (after that it breaks and requires manual re-sync) but what that is i cant tell you.
Either way it sounds like the solution is not working for you. Either you need to get a new line or upgrade it; or switch solutions, to either log shipping or backup restores (based on what you have said, replication wont be any good either).
Sorry i couldnt be more constructive.
Adam Zacks-------------------------------------------Be Nice, Or Leave
November 25, 2009 at 8:55 am
Thanks for the initial insight S-M.
I should point out that it IS working, just not as well as I would like. In particular I don't like the 80% of the time where nothing is coming over. I'd like the retry period to be much shorter. And it's not like the link is choked, I can remote desktop to the other machine at any time, use Enterprise Manager, view logs, etc. We're only getting behind by 2 GB in a day, if the service would be less sensitive to congestion, or retry quicker, it would keep up. I know I could just buy a bigger link, but I'd like to know more about the mirroring service, and just can't seem to find any detailed docs.
Student of SQL and Golf, Master of Neither
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply