Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase ««123»»

The Best High Availability Expand / Collapse
Author
Message
Posted Tuesday, April 28, 2009 6:26 PM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Monday, March 5, 2012 9:27 PM
Points: 279, 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)
Post #706403
Posted Wednesday, April 29, 2009 4:18 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, April 30, 2009 9:29 AM
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.
Post #706610
Posted Wednesday, April 29, 2009 4:18 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, April 30, 2009 9:29 AM
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.
Post #706611
Posted Wednesday, April 29, 2009 3:40 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, August 27, 2009 10:09 AM
Points: 2, 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.
Post #707259
Posted Wednesday, April 29, 2009 3:48 PM


SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Monday, June 24, 2013 11:26 AM
Points: 208, 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.
Post #707263
Posted Thursday, April 30, 2009 8:26 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Monday, November 11, 2013 2:42 AM
Points: 150, 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

Post #707718
Posted Thursday, April 30, 2009 9:55 AM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: Administrators
Last Login: Today @ 8:26 AM
Points: 33,191, Visits: 15,331
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
Post #707791
Posted Thursday, April 30, 2009 11:19 AM
Right there with Babe

Right there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with Babe

Group: General Forum Members
Last Login: Friday, August 22, 2014 9:18 AM
Points: 751, Visits: 1,915
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 --
Post #707863
Posted Friday, May 1, 2009 5:37 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Monday, November 11, 2013 2:42 AM
Points: 150, 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

Post #708349
Posted Friday, May 1, 2009 8:55 AM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: Administrators
Last Login: Today @ 8:26 AM
Points: 33,191, Visits: 15,331
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
Post #708503
« Prev Topic | Next Topic »

Add to briefcase ««123»»

Permissions Expand / Collapse