Use HAProxy to provide fault tolerance for mirrored SQL Servers

Question

We are in the middle of building out our production environment for an upcoming web product. For this stack, a primary SQL Server 2008 will be used for live database operations and a secondary SQL Server 2008 will get its data mirrored from the primary SQL Server (via SQL Server's built-in Mirroring capability). We will have Report Service run against the secondary SQL Server while have it has a hot standby when the primary SQL Server becomes unavailable.

At the application level, we have 2 options:

1. Have failure detection implemented in the app tier so if the primary SQL Server is unresponsive, have our DAL hit the secondary SQL Server. OR
2. Have the app tier point to a vip and have HAProxy handle failure detection.

The question is, is option #2 a viable option?

NOTE: We understand there are other ways of providing High-Availability at the database level (such as clustering), but we are aiming for a solution that is cost effective.

Answer 1

What do you mean by 'data mirrored'?

You can have database mirroring, in which case the client (ie. your DAL) can use the FailoverPartner in the connection string and follow the failover event and connect to the new principal. Your reporting would run off database snapshots, not the database itself, since the mirror is unavailable.

You can have a failover cluster and the client connects to the cluster resource name to start with and it's unaware of the active node host name to start with, but this does not give you any access to data on the standby partner.

You can have hardware mirroring, but that is a separate topic.

Some say that replication is an option, I'm not in that camp.

And... pretty much that's it. Short of brewing your own in-house data mirroring technology, whatever that means.

Updated

If you use Database Mirroring then you simply specify the failover partner in the connection string, see Connecting Clients to a Mirrored Database. You application must handle transactional consistency in the face of failover events. The failover event will abruptly disconnect clients and an exception will be raised in the client code. Any pending transaction will be aborted. Client code must reconnect, read the persisted state and resume the work from the state found in the database. Correctly written applications will handle this gracefully and without problems.

The mirror is always offline and cannot be accessed. If you want to run reports on the mirror you must take a database snapshot and run the reports on the snapshot. The snapshot must be periodically refreshed (dropped and recreated). See Database Mirroring and Database Snapshots.

Network layer load balancers have nothing to do with mirroring and don't resolve anything.

Answer 2

How about none of the above?

Please clarify what you mean by mirrored sql servers. Are you using a SAN of some sort to do mirroring, or using SQL Server's built in mirroring function?
I can see how you could use HAProxy on the web tier, but why do this with SQL Server? there are other much more supported HA options with SQL Server such as clustering,mirroring & replication. I'd investigate those first.