SQLServerCentral / Editorials / SQLServerCentral.com / The Best High Availability / Latest Posts

RE: The Best High Availability

Bill Kline-270970 — Wed, 20 May 2009 09:44:31 GMT

I've supported log shipping and mirroring as HA solutions in the past. At my current position, we implement clustering and I'm a huge fan. The failover is quick, painless, and automatic. Disk failure? Implement disk mirroring with a RAID configuration and make sure someone monitors the hardware.If your situation is really, really complex, the hire an HA consultant to cover your asterick! :-D

RE: The Best High Availability

jay holovacs — Thu, 07 May 2009 07:40:28 GMT

oops worng thread

RE: The Best High Availability

Jason Shadonix — Wed, 06 May 2009 14:40:21 GMT

We used DoubleTake a few years ago, but found it to be buggy. Since (at least the version we were using) is basically glorified file replication on the database files at the byte level (not the transaction level) if something hiccuped in the process it sometimes wasn't able to make things right again and the result was a corrupt/useless remote set of database files. But that was several versions of DoubleTake ago, so it may have improved since then.We are currently using DB mirroring (asynchronous) from 20 offices all over the country (including east and west coast, and north and sough) to our corporate office in KC, and it's keeping up great.

RE: The Best High Availability

Rudy Panigas — Wed, 06 May 2009 14:25:34 GMT

Hello Everyone,Has anyone looked at 3rd party software for HA like Double Take, XOSoft or Sonasafe? We are in the initial stages of HA and it seem that there is confusion all around, even with SQL contractors. We are looking at a 2 stage approach. 1) HA within the building then 2) HA to the DR site.Comments?Rudy

RE: The Best High Availability

Jason Shadonix — Fri, 01 May 2009 12:07:31 GMT

Ah, good point. In our case we care more about not losing data than we do a couple of min. of downtime, so mirroring works out swell.

RE: The Best High Availability

Steve Jones - Editor — Fri, 01 May 2009 11:44:30 GMT

Mirroring is by database, doesn't move logins, jobs, etc. Clustering is by instance.

RE: The Best High Availability

Jason Shadonix — Fri, 01 May 2009 09:04:23 GMT

[quote][b]thirumalai (4/29/2009)[/b][hr]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.[/quote]We also use mirroring as our primary HA/DR solution here. I've not used clustering, or read much about it. What does it give you that mirroring doesn't?

RE: The Best High Availability

Steve Jones - Editor — Fri, 01 May 2009 08:55:51 GMT

It's a good point. I would be interested to know if there are some surveys anywhere.May try to run one here.

RE: The Best High Availability

mike brockington — Fri, 01 May 2009 05:37:20 GMT

[quote][b]Steve Jones - Editor (4/30/2009)[/b][hr]... I think the disk issues that affect the server are rare.[/quote]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?

RE: The Best High Availability

jay holovacs — Thu, 30 Apr 2009 11:19:02 GMT

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.

RE: The Best High Availability

Steve Jones - Editor — Thu, 30 Apr 2009 09:55:56 GMT

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.

RE: The Best High Availability

mike brockington — Thu, 30 Apr 2009 08:26:03 GMT

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.

RE: The Best High Availability

David Reed-223505 — Wed, 29 Apr 2009 15:48:57 GMT

[quote][b]bob.boule (4/29/2009)[/b][hr]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.[/quote]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.

RE: The Best High Availability

bob.boule — Wed, 29 Apr 2009 15:40:49 GMT

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.

RE: The Best High Availability

thirumalai-1079039 — Wed, 29 Apr 2009 04:18:36 GMT

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.

RE: The Best High Availability

thirumalai-1079039 — Wed, 29 Apr 2009 04:18:34 GMT

RE: The Best High Availability

Billy-767564 — Tue, 28 Apr 2009 18:26:45 GMT

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)

RE: The Best High Availability

Rick Romack — Tue, 28 Apr 2009 13:02:54 GMT

We implement HA in various ways here. We have some clusters of SQL 2005 on Windows 2003 which means we cannot geographically separate the servers. We use Log Shipping on some databases and Database Mirroring on others. In one particular case we mirror a database that is on a cluster to a geographically separate site. In some instances we use the old tried and true backup and restore.When a business unit presents the need to store data within SQL I present them with the options trying to give them an over view of the good and the bad of each option. Surprisingly most requests end up being that "Last nights backup is good enough for us." One other thing I see a lot of is implementing an HA plan and then never testing it. Testing an HA has to be done on a regular basis or you may and probably will end up with unrecoverable data during a disaster.Rick Romack

RE: The Best High Availability

David Reed-223505 — Tue, 28 Apr 2009 12:23:10 GMT

I'm still wondering which HA scenarios clusters can't solve, Steve. Please elaborate... Offsite DR doesn't quite fit as a typical HA scenario, but it's still doable with dark fiber and the right SAN gear.The "best" HA will be a layered approach anyway. Mirroring or clustering for "immediate" HA (such as hardware hiccoughs) combined with log shipping for warm standby at a secondary NOC (to deal with minor disasters, internet service outages, failure to pay the light bill) and offsite backups on removeable SSDs or DVDs in another region or country for recovery from true disasters.

RE: The Best High Availability

DPhillips-731960 — Tue, 28 Apr 2009 09:56:50 GMT

Just thought of another way to put it... I personally view VM technology as optimum when the O/S is merely an interface.For example, it is great in dev-test environments, where testing against multiple systems and platforms is needed.It is great for small web-hosting instances too, where each instance can have 'root-like" access to their own instance.It is still [i]not[/i] great for databases (especially large implementations), nor integration, report, and analysis servers.

RE: The Best High Availability

DPhillips-731960 — Tue, 28 Apr 2009 09:50:40 GMT

It is OK to VM the SQL Server instances but I wouldn't vm the data with it. The nodes need to see the same disks (as Steve has said).Still VM is some order slower than a dedicated box, as everything is being shared from network connections to CPU cycles. Some intelligent analysis and testing would be in order. I wouldn't use it for HA systems unless those systems are strictly lookup systems with DBs that can reside in memory, or are extremely focused and minimal in overhead.

RE: The Best High Availability

Steve Jones - Editor — Tue, 28 Apr 2009 07:22:06 GMT

Virtualization definitely can, and I wouldn't be surprised to see more of this.You can cluster two virtual servers as long as they can see the same shared disk. So the underlying Windows host(s) need to be able to share a disk somehow.

RE: The Best High Availability

Jason Miller-476791 — Tue, 28 Apr 2009 07:17:10 GMT

[quote][b]webrunner (4/28/2009)[/b][hr]are there still ways to make use of virtualization of SQL servers?[/quote]We have a couple of VM servers that house production SQL environments. Our corporate accounting system, a bug tracker, and an on-site inventory management system are all on VM SQL... That works well for us for they are very low usage/volumes. The corporate accounting system is the biggest with a little over 1GB...That being said, I am familiar with a company that is running a 660GB email tracking system on a virtualized SQL box. Its performance is pretty abysmal. I wouldn't recommend any system more complicated than a recipe look-up on a virtualized box.

RE: The Best High Availability

webrunner — Tue, 28 Apr 2009 07:09:24 GMT

I have a general question - can virtualization play a part in HA? In other words, is it possible to have a setup where a SQL cluster is not needed if virtual servers are used?If so, are there sites or articles someone can point me to for discussions and pointers?If not, are there still ways to make use of virtualization of SQL servers?Thanks for any advice.- webrunner

RE: The Best High Availability

jay holovacs — Tue, 28 Apr 2009 07:07:21 GMT

Our old 2000 server was clustered and was problematic at best due especially due to the shared drive requirement. Drive hiccups on the RAID box were our most common problem. For 2005 we implemented mirroring which so far has been satisfactory. Since the drives are on two physically separate systems, we can even bring down the drives for maintenance (firmware etc) while keeping the database up.

RE: The Best High Availability

Jason Miller-476791 — Tue, 28 Apr 2009 06:24:43 GMT

We have medium-high availability. Our db servers run fibre channel over to our EVA, which is a single-point of failure. (I know it's got redundant this and that.) If a box goes belly up, we created a script that transfers all of the storage from our production box to a test box. (All db servers are identical.) Total down time should be <5 minutes. That includes detecting the problem.Our investment accounting system runs on a cluster, serviced by the same EVA. The amusing thing is, if a cluster node goes down and the db needs to migrate to the new node, SQL Server will stop for a couple of seconds (as expected), the problem is... The IA system is so well written, that if SQL server goes down, it'll puke and die, but not before corrupting the databases that support it. I could go into detail as to why, but let's just say they (the vendor) do not believe in such trivial things as transactional integrity, indexing schemes, data validation, stored procedures, system views (why bother when you can update the system tables directly?)... You get the idea.I like the idea of our current environment, along with the addition of log shipping down to our DR site. :w00t: We're on a 15 minute lag, so for us it's not bad. After all, we're not Wal-mart, or Amazon...(Steve, nice new pic... Shamed me into changing mine.)

The Best High Availability

Steve Jones - Editor — Mon, 27 Apr 2009 16:10:14 GMT

Comments posted to this topic are about the item [B]The Best High Availability[/B]