IIS+SQL Server high availability

[MaxQc]

Hi,
Will be hosting a web server using SQL and it will NEED to be up all the time...
The host provider will care about network connectivity but I have to care about server high availability.

I saw products from Neverfail that looks good... I was wondering about VMs also..

Can you guys give me your taughts ?

Price is a concern but we should be able to have a budget for it..

 

[MorfiusX]

SQL Enterprise supports clustering for HA.

A VMware Infrasctructure supports HA at the virtualized hardware level. I really like this type of solution as you can have HA on an app that is not cluster aware.

This will not be a cheap project. You will easily spend 20k on licensing either route.

For the IIS side of the house, you can use the built in Network Load Balancing, which is a form of clustering. If the VMware Infrastructer set up, you could do the same type HA. VMotion is a beautiful thing.

Depending on the app, you could go MySQL HA and not spend anything on licensing, though the solution is more difficult to configure.

 

[da sponge]

Pretty much what MorfiusX said - SQL Enterprise is pricey as hell, but does a nice job. Do be aware that VMware HA is not fully HA like clustering. You'll still get downtime (however long it takes the VM to boot + heartbeat timeout after the host goes down) and you'll only have a crash consistent database (crapshoot). Don't get me wrong, I love VI3, but in this instance it isn't as robust as SQL Enterprise

IIS, NLB works fine. Web edition is cheap and you can have a bunch of them in the 'cluster'. For really high traffic you may want to look into a hardware load balancer.

 

[MorfiusX]

Pretty much what MorfiusX said - SQL Enterprise is pricey as hell, but does a nice job. Do be aware that VMware HA is not fully HA like clustering. You'll still get downtime (however long it takes the VM to boot + heartbeat timeout after the host goes down) and you'll only have a crash consistent database (crapshoot). Don't get me wrong, I love VI3, but in this instance it isn't as robust as SQL Enterprise

IIS, NLB works fine. Web edition is cheap and you can have a bunch of them in the 'cluster'. For really high traffic you may want to look into a hardware load balancer.

A VMware Infrastructure can be fully fault tolerant if built properly. You should look at the automatic VMotion stuff. When configured properly, there will be 0 downtime.

http://www.vmware.com/products/vi/vc/ha.html

 

[calebb]

Yay for misinformation

(I assume you are referring to MSSQL and not an open source DB product)

This is valid for SQL Server 2000/2005/2008 Standard and up. (possibly Workgroup edition as well, but I have not checked)

http://download.microsoft.com/downl...b-61377df9c5c2/SQLServer2005Licensingv1.1.doc

Licensing for a Failover Cluster Configuration

If your organization uses SQL Server 2000 in a failover cluster configuration, this means servers are clustered together to pick up each others' processing if one computer fails, in this situation, you have special licensing considerations. This option is only available in SQL Server 2000 Enterprise Edition.

Failover clustering support can be configured two ways:
•

Active/Active. This option allows all servers in the failover cluster to regularly process information. When a server fails, one server or more takes on the additional workload of the failed server.
•

Active/Passive. This option is characterized by at least one server in the cluster that do not regularly process information, but waits to pick up the workload when an Active server fails.

All Active servers in a cluster must be fully licensed, with either Processor Licenses or Server Licenses. However, if a server is strictly Passive, and works only when an Active server has failed, no additional licenses are needed for that Passive server. The exception to this is if the failover cluster is licensed under Processor License, and the number of processors on the Passive server exceeds the number of processors on the Active server. In this case, additional Processor licenses must be purchased for the additional processors on the Passive computer.

 

[da sponge]

A VMware Infrastructure can be fully fault tolerant if built properly. You should look at the automatic VMotion stuff. When configured properly, there will be 0 downtime.

http://www.vmware.com/products/vi/vc/ha.html

I have looked at it - I've implemented VI3 and tested both VMotion and HA. You get a few minutes of downtime with HA, not zero.

Automatically Restart Virtual Machines
* Monitors and detects virtual machines for “guest OS” failures and automatically starts virtual machines after user-specified intervals.
* Detects server failures automatically, using a “heartbeat” on servers.
* Restarts virtual machines almost instantly without human intervention on a different physical server within the same resource pool.
* Continuously monitors and chooses the optimal physical servers within a resource pool on which to restart virtual machines (if used in conjunction with VMware DRS).

ESX host A with VM1, VM2 Fails.

After the HA agent on ESX host B detects the heartbeat failure/waits for time out, it powers up the VM1 and VM2 on host B (if host A has really crashed, if it's just a heartbeat failure, action depends on isolation response setting of the VM). You're still waiting for the heartbeat timeout and the bootup time ("Automatically Restarts Virtual Machines...") of the VM.

VMotion only works when both hosts are running. It's magical for planned downtime and automatic load balancing, but it can't save you downtime resulting from host failure - at that point the execution state of the vm is toast and memory/registers can't be copied to a running host.

VI3 HA is hardware availability - it will recover the VMs on a different host after a hardware/host failure, but it does this by powering them up from their last, on disk, crash consistent state, not by using vmotion. You still have to wait for the vm to boot, then deal with any database corruption/recovery issues (if any, i've had SQL/Exchange crash a couple times over the past 5 years and both have booted up just fine afterwards).

 

[MaxQc]

VM Solution looks interesting... Thanks for the tips ...

Any one has experience with solutions like Nerverfail or other apps like this ?

 

[MorfiusX]

First, I was using terms loosly when talking about VMotion in that I didn't want to go into a ton of detail, just guide the OP in a certian direction.

Second, yes I was a bit off about the zero downtime thing. It's been about two years since I have implemented this type of setup. But still, VMware is one of my favorite technologies.

 

[da sponge]

But still, VMware is one of my favorite technologies.

Oh absolutely. If the OP's goal is to reduce the impact that a single failure has on his setup and waiting a few minutes for recovery is ok, definitely go VI3 - I love it.

 

[StarTrek4U]

I'll echo the sentiments about VMWare, it's quite sweet and VMotion is awsome. One thing that seems to have been missed is that you absolutely do not want to VM your SQL servers, they will just get insanely bogged down in a quick hurry and basically run like crap. As far as your web servers go there a cheaper way to load balance- DNS Round Robin, sort of a DNS load-balancing. If one of your web servers goes down the other will pick it up, worst case after the DNS TTL refreshes, you could save money going that way instead of dedicated hardware device, etc.

 

[da sponge]

One thing that seems to have been missed is that you absolutely do not want to VM your SQL servers, they will just get insanely bogged down in a quick hurry and basically run like crap.

Highly depends on the load characteristics of SQL. A buddy runs some large SQL servers for BI analysis (50GB+ DBs) and they perform pretty well virtualized Obviously not as good as native, but better than the previous generation hardware they were bare metal on. Especially with his workload, where the VMs aren't typically running maxed out simultaneously (one or two may, but not all).

I'm running the back end of our small test site (intermittent writes, frequent reads) virtualized and have zero issues.

 

[MorfiusX]

Highly depends on the load characteristics of SQL. A buddy runs some large SQL servers for BI analysis (50GB+ DBs) and they perform pretty well virtualized Obviously not as good as native, but better than the previous generation hardware they were bare metal on. Especially with his workload, where the VMs aren't typically running maxed out simultaneously (one or two may, but not all).

I'm running the back end of our small test site (intermittent writes, frequent reads) virtualized and have zero issues.

I'll second this. There are many things you can do to tweak the performance of a I/O intensive VM. First, you can create a higher priority resources pool. Second, you can create dedicated resources. Third, if using a SAN, you can dedicate a LUN.

I have ran both Exchange and SQL in VM without noticeable performance loss. If fact, my companies servers all run in VM. A single quad core with 8GB RAM and 4x 10K SATA drives runs: AD/File/Print Server, An Exchange 2007 server, An SQL and Application Server, and a second Apps/CRM server. Now, we don't put a huge load on the thing, but it works like it is supposed to without any issues.