Petri.com forums Home Forums Start Page Forums Frequently Asked Questions FAQ Member List Members List
Go Back   Petri IT Knowledgebase Forums > Virtualization > VMware Virtualization
Petri.com is happy to award RicklesP the title of Most Valuable Member !!!
Register Calendar Calendar Search Petri IT Knowledgebase Forums Search Todays Posts Today's Posts Mark Forums Read
Notices

VMware HA design consideration for MS SQL Server 2008 failover clustering

VMware HA design consideration for MS SQL Server 2008 failover clustering

this thread has 8 replies and has been viewed 7703 times

Closed Thread
 
Thread Tools Search this Thread Display Modes
  #1  
Old 31st May 2011, 17:45
Albertwt Albertwt is offline
Member
Someone to look up to
 
 Join Date: Feb 2008
  6 month star 12 month star
 Location: Sydney
 Posts: 662
 Reputation: Albertwt is on a distinguished road (18)
Question VMware HA design consideration for MS SQL Server 2008 failover clustering

Hi All,

I'd like to build highly redundant SQL Server 2008 database and Infrastructure monitoring software for both my production and DR environment, I wonder if this design below is make sense and can provide me with sufficient redundancy at any level ?



My goal is that when the Production site is having a problem, the user can use the monitoring application from DR site as automated fall back plan.

Any kind help and suggestion would be greatly appreciated.

Thanks.
  #2  
Old 31st May 2011, 19:58
gerth's Avatar
gerth gerth is offline
Junior Member
Staying around
 
 Join Date: Jul 2010
  6 month star 12 month star
 Location: The Netherlands
 Posts: 222
 Reputation: gerth will become famous soon enough (73)
Default Re: VMware HA design consideration for MS SQL Server 2008 failover clustering

Are there redundant network paths between the sites?
__________________
gerth

MCITP sa, ea & va, sysadmin@cydonia.
  #3  
Old 31st May 2011, 21:42
PiroNet's Avatar
PiroNet PiroNet is offline
Casual
Casual
 
 Join Date: May 2011
  6 month star 12 month star
 Location: Belgium
 Posts: 18
 Reputation: PiroNet is on a distinguished road (26)
Default Re: VMware HA design consideration for MS SQL Server 2008 failover clustering

Hi Albertwt,

A lot of misunderstandings here.

VMware HA cannot operate accros two VMware Clusters as your drawing shows.
Also HA works at the ESX level to protect your VMs from ESX host crash.
Eventually with VM monitoring enabled HA can protect from a VM crash.

You're drawing a SQL cluster heartbeat network, therefore I suppose you're planing a Microsft Cluster. Doing so accros two sites requires not only a network heartbeat but also a 'heartbeat' at the storage level to avoid the dual-brain syndrome.

If the storage cannot provide such mechanism, you should go for a MNS type of MS Cluster but this type of cluster has other requirements to fullfil like a witness server preferably in a third site...

DR wording is not appropriate here as you are looking more at an active/passive environment and eventually active/active one with load balancers (GSLB?) in front.

There are many things to look at when considering geographically dispersed clusters aka geoclusters. To simplifying your design you should look at SQL DB mirroring as an easy way to provide fault tolerant accros the two sites. Leave VMware HA turned on of course to protect against ESX host failure on each site.

I hope I did not get you more confused

Cheers,
Didier

Last edited by PiroNet; 31st May 2011 at 23:34..
  #4  
Old 1st June 2011, 02:53
Albertwt Albertwt is offline
Member
Someone to look up to
 
 Join Date: Feb 2008
  6 month star 12 month star
 Location: Sydney
 Posts: 662
 Reputation: Albertwt is on a distinguished road (18)
Question Re: VMware HA design consideration for MS SQL Server 2008 failover clustering

@Gerth: yes there will be Fiber channel in between the Prod and DR sites.

@PiroNet: ok, so in this case the ESX HA cluster cannot be spanned across two sites ? I understand that there should be only one DB cluster and App. Server that must be running at the same time.
  #5  
Old 1st June 2011, 05:42
Albertwt Albertwt is offline
Member
Someone to look up to
 
 Join Date: Feb 2008
  6 month star 12 month star
 Location: Sydney
 Posts: 662
 Reputation: Albertwt is on a distinguished road (18)
Wink Re: VMware HA design consideration for MS SQL Server 2008 failover clustering

Quote:
Originally Posted by PiroNet View Post
Hi Albertwt,

A lot of misunderstandings here.

VMware HA cannot operate accros two VMware Clusters as your drawing shows.
Also HA works at the ESX level to protect your VMs from ESX host crash.
Eventually with VM monitoring enabled HA can protect from a VM crash.

You're drawing a SQL cluster heartbeat network, therefore I suppose you're planing a Microsft Cluster. Doing so accros two sites requires not only a network heartbeat but also a 'heartbeat' at the storage level to avoid the dual-brain syndrome.

If the storage cannot provide such mechanism, you should go for a MNS type of MS Cluster but this type of cluster has other requirements to fullfil like a witness server preferably in a third site...

DR wording is not appropriate here as you are looking more at an active/passive environment and eventually active/active one with load balancers (GSLB?) in front.

There are many things to look at when considering geographically dispersed clusters aka geoclusters. To simplifying your design you should look at SQL DB mirroring as an easy way to provide fault tolerant accros the two sites. Leave VMware HA turned on of course to protect against ESX host failure on each site.

I hope I did not get you more confused

Cheers,
Didier
Ok, now i decided to re-think and draw again from scratch as previously it was my idea at 1 AM, so it seems that Failover clustering on top of VMware HA is too complex and slight downtime ~ 5 mins during the failback/server restart is acceptable for me, therefore I have now decided to go with SQL Server 2008 Mirroring HA (see the attached).

the only single point of failure is the Witness VM (which runs SQL Server 2008 Express) if you can suggest any other placement then that'd be great.
Attached Thumbnails
Click image for larger version

Name:	SQL-MirrorHA.jpg
Views:	814
Size:	103.7 KB
ID:	4845  
  #6  
Old 1st June 2011, 11:11
PiroNet's Avatar
PiroNet PiroNet is offline
Casual
Casual
 
 Join Date: May 2011
  6 month star 12 month star
 Location: Belgium
 Posts: 18
 Reputation: PiroNet is on a distinguished road (26)
Default Re: VMware HA design consideration for MS SQL Server 2008 failover clustering

Hi Albertwt,

Your latest drawing is great!

The witness is only required if you go for automatic failover and if so, it has to be placed in a third site. This is recommended if your RTO is less than an hour!

Note that whilst nothing is required on the client side for a regular MS Cluster, for a SQL DB mirroring setup, the client should be 'aware' of it. The connection string (i.e. ODBC) must refer to both SQL IP's. If the first one doesn't work (prod site) the client tries the second one (DR site).

Note that the 'client' can be either the load balancers, or the end user device. As you plan on using load balancers, they should be aware of both IPs and failover to DR in case the prod is not available. Make sure your LBs support SQL mirroring.

Note that if using load balancers, they should be the kind of GSLB, spaning both sites and redundant. If the whole prod site goes down, that is LB, web, db, storage, and network, then still clients can connect to DR site's LB, which connect to the web servers, which talk to the SQL DBs all over the network/storage.

Note that the SQL DB mirroring can be sync (RPO = 0 second) and therefore network latency is critical, no more than 2-3ms all the time for proper user experience. If async then latency is not anymore critical but then you have a bigger RPO (>minutes if lot of IOs on DB) with the risk of data lost.

Regarding this question:
>@PiroNet: ok, so in this case the ESX HA cluster cannot be spanned
>across two sites ?
HA could eventually span accros two site if there is a stretched LAN, that is the same ip range/subnetmask available on both sites, aka stretched L2. But this is definitely not supported at the moment...

Again I hope I haven't put too much here

Cheers,
Didier

Last edited by PiroNet; 1st June 2011 at 11:15..
  #7  
Old 1st June 2011, 12:17
Albertwt Albertwt is offline
Member
Someone to look up to
 
 Join Date: Feb 2008
  6 month star 12 month star
 Location: Sydney
 Posts: 662
 Reputation: Albertwt is on a distinguished road (18)
Question Re: VMware HA design consideration for MS SQL Server 2008 failover clustering

Many thanks once again Didier,

After I submitted and presented the design to my IT manager and director, they would like to know if this topology can do SQL replication (peer to peer) in both direction ?

is it as simple as setting the Prod1 Publisher and Node2 Subscriber alternatively ?
  #8  
Old 1st June 2011, 12:36
PiroNet's Avatar
PiroNet PiroNet is offline
Casual
Casual
 
 Join Date: May 2011
  6 month star 12 month star
 Location: Belgium
 Posts: 18
 Reputation: PiroNet is on a distinguished road (26)
Default Re: VMware HA design consideration for MS SQL Server 2008 failover clustering

Quote:
Originally Posted by Albertwt View Post
Many thanks once again Didier,

After I submitted and presented the design to my IT manager and director, they would like to know if this topology can do SQL replication (peer to peer) in both direction ?

is it as simple as setting the Prod1 Publisher and Node2 Subscriber alternatively ?

Yes it is as simple. Both SQL servers are active while being a mirror for each other DBs.

Wording like prod and DR and not meaningful in such scenario... This is more active/active datacenters.

IMO that's the way to go cause no company can afford a 'stand by' site anymore. Companies want to use their assets. Dormant kits waiting a disaster is wasted money CAPEX and OPEX wise...

Rgds,
Didier

Last edited by PiroNet; 1st June 2011 at 12:46..
  #9  
Old 1st June 2011, 15:41
Albertwt Albertwt is offline
Member
Someone to look up to
 
 Join Date: Feb 2008
  6 month star 12 month star
 Location: Sydney
 Posts: 662
 Reputation: Albertwt is on a distinguished road (18)
Thumbs up Re: VMware HA design consideration for MS SQL Server 2008 failover clustering

Quote:
Originally Posted by PiroNet View Post
Yes it is as simple. Both SQL servers are active while being a mirror for each other DBs.

Wording like prod and DR and not meaningful in such scenario... This is more active/active datacenters.

IMO that's the way to go cause no company can afford a 'stand by' site anymore. Companies want to use their assets. Dormant kits waiting a disaster is wasted money CAPEX and OPEX wise...

Rgds,
Didier
Cool, I thought that it is not possible without clustering.
Now I learn something new with just two SQL Server peer to peer configuration (as per this article:http://msdn.microsoft.com/en-us/libr...=SQL.100).aspx) data replication occurs in both direction so that it synch one and another to form Active/Active configuration without the use of MSCS (Clustering).
Closed Thread


Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is On
HTML code is Off

Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
MSCS Clustering Failover Issue Nosaj Windows Server 2000 / 2003 / 2003 R2 2 4th April 2011 23:09
Failover clustering has failed steviebyrne Windows Server 2008 / 2008 R2 3 8th February 2011 19:06
Using Hyper-V and Failover Clustering Shillelagh Microsoft Hyper-V Technology 13 2nd November 2010 00:58
Windows 2008 Server Clustering for MS-SQL 2005 imran_mcse Windows Server 2008 / 2008 R2 1 28th October 2008 04:28
SQL server clustering Shiva Windows Server 2000 / 2003 / 2003 R2 0 11th March 2005 22:20


All times are GMT +3. The time now is 10:54.

Steel Blue 3.5.4 vBulletin Style ©2006 vBEnhanced
Powered by vBulletin® Version 3.8.4
Copyright ©2000 - 2014, Jelsoft Enterprises Ltd.
 

Valid XHTML 1.0!   Valid CSS!

Copyright 2005 Daniel Petri