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 |
|
Kristen
Test
22859 Posts |
 Posted - 2010-07-12 : 06:00:26
|
Just reporting on a failover test we did last week, in case of interest to others.
We have changed to a new hosting company; the lot we were with have been taken over several times, and are now owned by a Mega Corp and we are too small to get any air-time.
The new lot have set up two servers - Web and SQL - virtual machines, shared disk array (I'm not a hardware bod, so its all a bit "Woosh" over the top of my head!).
We have fail-over of the virtual machines between the two physical servers - so if one server fails the second does both jobs - which I think is a neat compromise based on cost! and whilst it may give us some squeeze on throughput, even if we need a brand new machine the hosting company will normally be able to replace it within a couple of hours
For an unscheduled failover there is a delay of a couple of minutes, and loss of any pending transactions. For a scheduled failover there is a slight (i.e. noticeable) delay of a few seconds, but no loss of transactions.
Test1: Power off a server. System detects power failure and creates a new virtual machine on the second server.
I held a transaction open in SQL whilst this happened. Transaction was lost/rolled-back, as expected.
Powered up the "stopped" machine and made a "scheduled" fail-back. In this mode the new virtual machine is created using memory-copy from the primary. Memory is copied, and then changed memory is recopied, to the point where the machine decides it can then copy the remaining changed-memory in one batch, activity is suspended, final memory copy is made, and control is passed to the other server (IP addresses swapped I presume) [apologies for layman's description, I don't know the technical mumbo-jumbo]
This time the transaction I was holding open paused momentarily as the final memory-copy batch ran, and then continued as if nothing had happened 
My test was something like this (using a table with just a few hundred records)
SELECT *
INTO TEMP_MyTableName
FROM MyTableName
WHERE 1=0
BEGIN TRANSACTION
INSERT INTO TEMP_MyTableName
SELECT *
FROM MyTableName
DECLARE @strMsg varchar(100)
SET NOCOUNT ON
WHILE 1=1
BEGIN
SELECT @strMsg = CONVERT(varchar(24), GetDate(), 113)
+ CONVERT(varchar(20), Count(*))
FROM TEMP_MyTableName
RAISERROR (@strMsg, 0, 1) WITH NOWAIT
WAITFOR DELAY '00:00:00.500'
END
ROLLBACK
and then scrolling the results to keep them in view, manually aborting the loop when the test was completed and running the ROLLBACK and dropping TEMP_MyTableName
I've not been involved in anything like this before and all I can say is I was mightily impressed |
|
|
Sitka
Aged Yak Warrior
571 Posts |
Posted - 2010-10-13 : 10:29:17
|
Cool, I watched some of the Dell Equilogic serie(s) on Youtube when we were considering iSCSI shared storage. Had an inhouse demo from a Dell Storage rep as well. Pretty heavy duty configuration getting it all hooked up. Ultimately I could see the power and promise of failover.
"it's definitely useless and maybe harmful". |
 |
|
|
Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)
7020 Posts |
Posted - 2010-10-13 : 11:15:30
|
What is the hosting platform? VMWARE?
CODO ERGO SUM |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2010-10-13 : 13:09:32
|
| You asking me MVJ? If so I'm happy to find out (not something I know much about!) |
|
|
|
Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)
7020 Posts |
Posted - 2010-10-13 : 13:49:11
|
quote:
Originally posted by Kristen
You asking me MVJ?...
Yes.
CODO ERGO SUM |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2010-10-14 : 02:39:01
|
| Citrix xenserver - 2 x servers with a fibre SAN array |
|
|
|
Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)
7020 Posts |
|
|
Sitka
Aged Yak Warrior
571 Posts |
Posted - 2010-10-14 : 16:40:36
|
I moved two (no three) production SQL instances to Hyper-V 2008 R2. Originally I spec hardware for a dedicated single sproc licences.
Nehelam Xeon,
12 gig mem,
2 raid1 arrays 15000rpm sas drives.
I thought if I needed more performance I could add a second proc and get a SSD for the temp DB.
Anyways I had a small ERP upgrade to go through and decided I'd take a stop along the way and complete the upgrade process onto a virtual machine first and then if it was underperforming. Move it onto the host OS then delete the Virtual Machine. Turns out the SQL is smoking fast for our needs. The network is the bottleneck, the next in line would maybe be tempdb or the 4CPU limit of Hyper-V. The 4 CPU limit had me most worried but it ended up being a non factor. Simple (but diverse) benchmarking showed memory performance and disk performance very similar in the physical vs. virtual showdown. Floating point and Interger calc tests scaled exactly in half 8CPU vs 4CPU, hash benchmark did not suffer.
I was realy sceptical about virtualizing a production sql server but it worked great. I decided to run an iis7 app server on the same hardware and since the communications are now via the 10Gigb/sec virtual switch. Both benefit.
Other production VM SQL instance is a dedicated MS Project Server. I did a P2V (physical->virtual) conversion with that one. Project server is real disk hog and network greedy. 5 users can generate a 3Gig Tlog backup in 30 minutes!!! So the new disk performance is great but pushing the huge project server objects accross the network is still a bottleneck.
The third, is a pet project of Sharepoint 2010 single server farm running as well and yep it is Virtual. So there is a story, Three production instances. I hope I can help some folks with their Hyper-V stuff. Decided to ditch log shipping in exchange for Windows Server Backup. Warm failover DR, will probably still be a couple hours of panic either way but I take a big load off the network 99.99 percent of the time.
"it's definitely useless and maybe harmful". |
|
|
|
|
|
|
|
|
Virtual Machine Failover