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:
- 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
- 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.