SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2008 Forums
 SQL Server Administration (2008)
 Virtual Machine Failover
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

Kristen
Test

United Kingdom
22403 Posts

Posted - 07/12/2010 :  06:00:26  Show Profile  Reply with Quote
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

USA
571 Posts

Posted - 10/13/2010 :  10:29:17  Show Profile  Reply with Quote
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".
Go to Top of Page

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

USA
7020 Posts

Posted - 10/13/2010 :  11:15:30  Show Profile  Reply with Quote
What is the hosting platform? VMWARE?






CODO ERGO SUM
Go to Top of Page

Kristen
Test

United Kingdom
22403 Posts

Posted - 10/13/2010 :  13:09:32  Show Profile  Reply with Quote
You asking me MVJ? If so I'm happy to find out (not something I know much about!)
Go to Top of Page

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

USA
7020 Posts

Posted - 10/13/2010 :  13:49:11  Show Profile  Reply with Quote
quote:
Originally posted by Kristen

You asking me MVJ?...


Yes.



CODO ERGO SUM
Go to Top of Page

Kristen
Test

United Kingdom
22403 Posts

Posted - 10/14/2010 :  02:39:01  Show Profile  Reply with Quote
Citrix xenserver - 2 x servers with a fibre SAN array
Go to Top of Page

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

USA
7020 Posts

Posted - 10/14/2010 :  14:50:03  Show Profile  Reply with Quote
Looks like it's a version of this:
Xen
http://en.wikipedia.org/wiki/Xen

CODO ERGO SUM
Go to Top of Page

Sitka
Aged Yak Warrior

USA
571 Posts

Posted - 10/14/2010 :  16:40:36  Show Profile  Reply with Quote
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".
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.11 seconds. Powered By: Snitz Forums 2000