SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


The Best High Availability


The Best High Availability

Author
Message
Billy-767564
Billy-767564
SSC Veteran
SSC Veteran (291 reputation)SSC Veteran (291 reputation)SSC Veteran (291 reputation)SSC Veteran (291 reputation)SSC Veteran (291 reputation)SSC Veteran (291 reputation)SSC Veteran (291 reputation)SSC Veteran (291 reputation)

Group: General Forum Members
Points: 291 Visits: 163
We have implemented a HA solution in a VM cloud. Total nine big boxes forms the VM (2 dedicated) for the SQL HA cluster. (Other VMs are runing 40+ servers)
SQL Sever 2005 is installed on these dedicated VM with active-passive configuration. So effectively both the nodes are sharing the disks. We have also implemented a DR solution using Database mirroring to a different site to provide more HA (Our DR site link is bit slow now, so we have used High Perf - Async - Manual failover and expecting a high speed connectivity soon. And it will be changed to High Perf - Sync - Automatic failover with a witness server).

We are expecting SQL should be up and running within few seconds of any failure (disk/network cards/CPU)
thirumalai-1079039
thirumalai-1079039
Forum Newbie
Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)

Group: General Forum Members
Points: 2 Visits: 5
SQL 2005 Data mirroring (Synchronous) with Log shipping sounds to be a good HA solution, when compared to the clustering. Data mirroring is for HA and Log shipping is for DR (Disaster Recovery).

While we test our application in clustering environment, we found that the failover took around 4 minutes of time. From the technical people perspective, this 4 minutes time is not acceptable and it is not HA and they added that the failover should not exceed 20 seconds of time. Also the clustering requires the shared disk. There are various types of shared disks like SAN, NAS available in the market, which are very expensive. Then we dropped the clustering solution and went for Data Mirroring (Synchronous) with Log shipping solution.

In SQL 2005 Data Mirroring (Synchronous), the transaction gets committed both in the primary database server and in the secondary database server simultaneously. Hence on failure of the primary database server, our application will to connect to the secondary database server immediately, with out any manual intervention, since both primary database and secondary database will be in sync at any time. This failover happened around 20 seconds of time.
thirumalai-1079039
thirumalai-1079039
Forum Newbie
Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)

Group: General Forum Members
Points: 2 Visits: 5
SQL 2005 Data mirroring (Synchronous) with Log shipping sounds to be a good HA solution, when compared to the clustering. Data mirroring is for HA and Log shipping is for DR (Disaster Recovery).

While we test our application in clustering environment, we found that the failover took around 4 minutes of time. From the technical people perspective, this 4 minutes time is not acceptable and it is not HA and they added that the failover should not exceed 20 seconds of time. Also the clustering requires the shared disk. There are various types of shared disks like SAN, NAS available in the market, which are very expensive. Then we dropped the clustering solution and went for Data Mirroring (Synchronous) with Log shipping solution.

In SQL 2005 Data Mirroring (Synchronous), the transaction gets committed both in the primary database server and in the secondary database server simultaneously. Hence on failure of the primary database server, our application will to connect to the secondary database server immediately, with out any manual intervention, since both primary database and secondary database will be in sync at any time. This failover happened around 20 seconds of time.
bob.boule
bob.boule
Forum Newbie
Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)

Group: General Forum Members
Points: 4 Visits: 18
Has anyone had any experiencing combining SQL Server Clustering with Storage level HA (block level mirror with automated failover at the storage level). I work for a small iSCSI storage vendor and we are developing the ability to build HA from the storage level (I know this is nothing new! Others in the space are already doing it) however I am wondering if this an option that is considered viable in the SQL Server community? (assuming the price was right), combining Application clustering with a clustered iSCSI storage solution.
David Reed-223505
David Reed-223505
SSC Veteran
SSC Veteran (278 reputation)SSC Veteran (278 reputation)SSC Veteran (278 reputation)SSC Veteran (278 reputation)SSC Veteran (278 reputation)SSC Veteran (278 reputation)SSC Veteran (278 reputation)SSC Veteran (278 reputation)

Group: General Forum Members
Points: 278 Visits: 380
bob.boule (4/29/2009)
Has anyone had any experiencing combining SQL Server Clustering with Storage level HA (block level mirror with automated failover at the storage level). I work for a small iSCSI storage vendor and we are developing the ability to build HA from the storage level (I know this is nothing new! Others in the space are already doing it) however I am wondering if this an option that is considered viable in the SQL Server community? (assuming the price was right), combining Application clustering with a clustered iSCSI storage solution.


Nope, not with HA clusters. Plenty of storage-level replication for so-called "geocluster" implementations for DR, but these never supported automated failover. Block level mirroring was used to keep the enormous amount of data in sync.

IMO, you'd need a service/resource that could participate in the cluster in order to support automated failover.

All the successful storage-level HA that I've seen makes the mirroring/failover invisible to Windows and SQL Server.
mike brockington
mike brockington
SSC-Enthusiastic
SSC-Enthusiastic (186 reputation)SSC-Enthusiastic (186 reputation)SSC-Enthusiastic (186 reputation)SSC-Enthusiastic (186 reputation)SSC-Enthusiastic (186 reputation)SSC-Enthusiastic (186 reputation)SSC-Enthusiastic (186 reputation)SSC-Enthusiastic (186 reputation)

Group: General Forum Members
Points: 186 Visits: 245
Steve, in the editorial you say: "Clustering in SQL Server works well" but then point out that it can't cope with a disc failure.
What percentage of outages would ascribe to a server failure, as opposed to a disc failure? To my mind, this is the single most mis-leading feature I have ever seen in SQL server.

Throw away your pocket calculators; visit www.calcResult.com


Steve Jones
Steve Jones
SSC Guru
SSC Guru (61K reputation)SSC Guru (61K reputation)SSC Guru (61K reputation)SSC Guru (61K reputation)SSC Guru (61K reputation)SSC Guru (61K reputation)SSC Guru (61K reputation)SSC Guru (61K reputation)

Group: Administrators
Points: 61287 Visits: 19097
There's a difference between disk failure and a storage failure. Most shared storage is RAID, often with a spare drive, and so I think the disk issues that affect the server are rare.

Same with corruption. It's often a hardware issue, but it does happen.

I'd be curious too, how many times a server issues causes a failover. Personally I've rarely had SQL Servers fail, but you could have memory issues, which does happen. I could see someone forcing a failover to "recover" merory on the server.

Follow me on Twitter: @way0utwest
Forum Etiquette: How to post data/code on a forum to get the best help
My Blog: www.voiceofthedba.com
jay-h
jay-h
SSCommitted
SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)

Group: General Forum Members
Points: 1847 Visits: 2336
the majority of our failovers were storage related. For a number of years we were running a RAID before moving to SAN which has been better. The RAID never actually lost data but DID go down: interface card failure, disk failure (yes it's not supposed to but tell that to the equipment-- an electirically malfunctioning drive can interfere with the operation of the overall system), quorum file corruption. The problem is that a shared storage system is a single point of failure, one that can often be avoided with mirroring.

...

-- FORTRAN manual for Xerox Computers --
mike brockington
mike brockington
SSC-Enthusiastic
SSC-Enthusiastic (186 reputation)SSC-Enthusiastic (186 reputation)SSC-Enthusiastic (186 reputation)SSC-Enthusiastic (186 reputation)SSC-Enthusiastic (186 reputation)SSC-Enthusiastic (186 reputation)SSC-Enthusiastic (186 reputation)SSC-Enthusiastic (186 reputation)

Group: General Forum Members
Points: 186 Visits: 245
Steve Jones - Editor (4/30/2009)
... I think the disk issues that affect the server are rare.


I agree, but we are talking about comparitive numbers - in my experience, the shared external storage infrastructure, (or even internal, non-shared storage) is more likely to suffer a failure than the server itself. One always has to try and consider every possibility, but the more likely a scenario is, the more weight has to be given to mitigation. I can't see how you would set up a system that was able to survive a storage outage, without it also being able to survive a server outage, so what's the point?

Throw away your pocket calculators; visit www.calcResult.com


Steve Jones
Steve Jones
SSC Guru
SSC Guru (61K reputation)SSC Guru (61K reputation)SSC Guru (61K reputation)SSC Guru (61K reputation)SSC Guru (61K reputation)SSC Guru (61K reputation)SSC Guru (61K reputation)SSC Guru (61K reputation)

Group: Administrators
Points: 61287 Visits: 19097
It's a good point. I would be interested to know if there are some surveys anywhere.

May try to run one here.

Follow me on Twitter: @way0utwest
Forum Etiquette: How to post data/code on a forum to get the best help
My Blog: www.voiceofthedba.com
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search