Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
johny_eu
Starting Member
12 Posts |
 Posted - 2010-12-10 : 10:48:42
|
| Hi guys,I was just doing some tests with my two instance's of SQL Server 2008 R2 and 10 Mbps limited LAN. This is what I don't understand:I setup synchronous database mirroring. Database log file is 25000 MB and 99% free before the test. I started to move some records and rebuild every index in database, that took 2 hours and 7 minutes. For the last hour in that test the network utilization was 99% used, before that the usage was up and down ...Immediately after test ended (2 hours and 7 minutes) the network utilization went to 0%.This is was I don't understand. After these actions database log file is 23% free which means that this test generated 19 GB of log records. How can 19 GB of log records be transferred to mirror instance if my network has 10 Mbps? 10 Mbps has 1.20 MB/s which means it would take than 4 hours to transfer all that data? Kind regards |
|
|
AndrewMurphy
Master Smack Fu Yak Hacker
2916 Posts |
Posted - 2010-12-10 : 12:57:56
|
| you may misunderstand the "space free" bit.when filling files, sql doesn't necessarily work from the top....it could, for it's own internal purposes, have done some data storage 18% of the way down your log file and then just added 1% of data. the o/s will report the file as 19% full, even though what you would consider 1%-17% is empty/skipped over. |
 |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2010-12-10 : 13:02:08
|
| I would highly recommend that you use async mirroring instead as your network is far too slow. With sync, you've got a two-phase commit happening. Your slow network is going to impact DML queries drastically. Our network latency between our primary and DR site is 13 milliseconds and it's across 300 miles, so our network is pretty darn good. We use async on all of our mirrors as 13ms is too high! We just can't afford the perf hit of 13ms for each DML query.Tara KizerMicrosoft MVP for Windows Server System - SQL Serverhttp://weblogs.sqlteam.com/tarad/Subscribe to my blog |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
johny_eu
Starting Member
12 Posts |
Posted - 2010-12-11 : 09:07:41
|
| AndrewMurhpy, tkizer, thanks for the reply!Seems like the answer is compression. SQL Server uses it by default in 2008 and later in database mirroring. Tkizer, since I'm using standard edition I have only synchronous mirroring as option. This was my database maintenance job which I run once a month, and as you tkizer stated, 10 Mbps is obviously not enough :(To be honest, this isn't really what's troubling me. I could run that inside regular scheduled downtime. My application is OLTP which consists of fast and small transactions with many concurrent connections and that's why I'm not feeling comfort with 10 Mbps link between principal (primary location) and mirror (secondary location) instance. Since this is synchronous mirroring every link problem directly affects the transaction performance. My idea was to set up mirror instance on primary location so link wouldn't be a problem and log shipping on secondary location. In that case I solved my disaster recovery plan. I cannot afford to lose any data, that's why I use synchronous mirroring. I suggest synchronous mirroring on primary location in case that my principal somehow gets destroyed.Does this make sense? |
|
|
|
|
|
|
|
|
synchronous mirroring and 10 Mbps