troubleshooting Question

Load balancing SQL Server

Avatar of dbaSQL
dbaSQLFlag for United States of America asked on
Windows NetworkingMicrosoft SQL Server 2008Disaster Recovery
13 Comments1 Solution1952 ViewsLast Modified:
Regardless of the method of data redundancy, be it replication, mirroring, log shipping, or a combination thereof, the problem is the application failover.  Assume there are a gazillion different apps, with a gazillion different ini's, and all kinds of connection strings hard-coded in each.  

In the event of failure, the mirrored or replicated database is up and available, but all of the clients have to be reconfigured, and restarted.  Very tedious, very timely.  Not at all what I would call 'automatic' failover.

I want to know what we can do at the middle tier to ease the application failover.  I've read up on barracuda load balancer,(,
and right now i am trying to determine what other options are available to me, before I decide how to move forward.

Simply said, I would like a virtual IP, or hostname, that straddles my two SQL Servers, and routes traffic to the primary instance.  Everybody connects to that virtual ip, rather than the SQL Server directly.  if my primary SQL fails, traffic should be redirected to the other one.  No connection changes are necessary at the client layer.

Say I use Mirroring, high-safety w/automatic failover -- the primary and secondary alternate roles, as needed.  great.  BUT -- when that occurs, I don't want the entire application layer to have to stop/change their connection/restart.  Rather, I want the quiet little IP out there that everybody is referencing, to dynamically fall to the secondary.

Mirroring, log shipping, replication, whatever... but I don't want a cluster.  

I don't mind the third party product, such as barracuda.  
I also wonder whether I could use the Network Load Balancing feature of the OS.  I haven't really touched the NLB before, but I'm reading that 'incoming requests are balanced out among all the servers in the server farm'.   Sounds pretty good, but again, I do not want a cluster -- SQL Server or Windows.

All input is hugely, hugely welcome.  All I am trying to do is ease the application failover, in the event of a SQL failover.
Join the community to see this answer!
Join our exclusive community to see this answer & millions of others.
Unlock 1 Answer and 13 Comments.
Join the Community
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 1 Answer and 13 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros