Take the 2-minute tour ×
Server Fault is a question and answer site for professional system and network administrators. It's 100% free, no registration required.

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.

share|improve this question
2  
What about 3. Use SQL Server's built-in high avaliability functionality? –  Mark Henderson Jan 29 '10 at 3:50
add comment

2 Answers 2

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.

share|improve this answer
    
Remus Russanu: By "data mirrored" I mean using SQL Server's built-in Database Mirroring capability. –  Jason Jan 29 '10 at 15:58
    
Remus Rusanu: Your update is very helpful. It corrected my misunderstanding of Database Mirroring. I had the false assumption that both the principal and the mirror server are active (e.g., active/active). –  Jason Jan 30 '10 at 3:01
add comment

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.

share|improve this answer
    
Nick: By mirrored SQL Server, I mean using SQL Server's built-in Database Mirroring capability. –  Jason Jan 29 '10 at 15:58
    
then stick with automatic failover & use the failover partner in your connection string. The witness instance of sql server will detect the failure & switch principal & witness –  Nick Kavadias Jan 30 '10 at 2:23
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.