Testing Times 4 - 4 semiT gnitseT
Yeah I know it's a silly title but I decided to up my testing by mirroring the database that I populate, that way I can create havoc on two servers at the same time and get a feel for how mirroring might be of use to me in the future, or not.
One slight struggle was that I didn't really have enough SQL 2005 enterprise class servers to use, in this instance I couldn't see any value in using a collection of PC's and/or laptops, so all the Servers are pretty hefty x64 boxes.
What did I learn initially? Well using Standard Edition SQL Server is a total no-no as the mirroring degrades performance so badly that it becomes unusable.
test 2 which might run out at around 14 mins was still running some 2 hours later at which point I killed it.
In "fast" mode the mirroring appears to roughly halve performance, I only have 200mbs network connectivity but network activity showed at barely 1% so I figure network wasn't the issue.
Updates seem to fare better than inserts with test 3 seeming to hold up better in proprtion to test 2.
One possible use for me was to make use of the snapshot facility on the mirror, well here's how that worked out! I emptied my databases but left mirroring in place, databases presized to 80GB, I then ran my insert test ( Test 2 ) which populates 4 x 1 million row tables. The actual insert completed quite quickly, about 40% longer than without mirroring. However it took nearly 3 hours for the mirror to synchronise and 2 hours 20 mins for my snapshot to be created.
What I intended was to grab a snapshot before all the data was loaded, what I actually got was a snapshot after all the data had loaded.
OK it's an extreme test I suppose and I used mirroring out of the box, no performance tweaks. From a network point of view the traffic is so low that one can say mirroring doesn't add any appreciable overhead.
So to sum up mirroring didn't give me the parallel load for testing I was hoping to simulate