• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1105
  • Last Modified:

Load balancing SQL Server

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,(http://www.barracudanetworks.com/ns/products/balancer_overview.php,
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.
0
dbaSQL
Asked:
dbaSQL
  • 6
  • 4
  • 2
  • +1
1 Solution
 
jogosCommented:
<<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.>>
Not with failover clustering
http://msdn.microsoft.com/en-us/library/ms189134.aspx
0
 
jogosCommented:
And as I read you don't want load balancing (as in distribute workload to different servers, , with different IP's,  that can do the same at the same time)  but you want high availability.

<<... but I don't want a cluster>>
explain while it is pretty much doing what you want
0
 
dbaSQLAuthor Commented:
I should have said specifically, I don't want to use MSCS.
0
Network Scalability - Handle Complex Environments

Monitor your entire network from a single platform. Free 30 Day Trial Now!

 
lcohanDatabase AnalystCommented:
Not sure what would be wrong with SQL clustering using Microsoft cluster? You get that with speciffic OS/SQL versions anyway (so you may have it allready) and as far as stability and performance it is (at least) as good as other products (if not even better).

Only if you allready have other clustering products in use and you have already experience with them I would agree with you on "don't want to use MSCS" (just my 1 cent....)
0
 
dbaSQLAuthor Commented:
Understandably, some people may or may not agree with me.  The fact is still the same, I do not want to use MSCS.  And, I am wondering if anyone has any suggestions, given my requirements.
0
 
lcohanDatabase AnalystCommented:
In that case if you afford the additional hardware, complexity (in my opinion) and the fact that this scenario (no matter what you do) will intoduce some transaction latency because 2 differentr SQL servers dbs must be updated at the same time then you could go with high-safety - synchronous mirroring mode.

One benefit to Missoring high-safety mode versus clustering is the fact that usualy the servers are in different location so for instance from a financial institution risk factor this would be a asset for sure - of yourse its not a must but an opion to put the servers in different locations.
0
 
dbaSQLAuthor Commented:
Maybe I am not voicing myself properly.  I know that I can use the mirroring on the SQL side of things.  I guess this is really not a SQL question.  Rather, networking?

I spoke to my network team, and they suggest F5:
http://www.f5.com/solutions/availability/global-load-balancing/


this is much like the barracuda i referenced in the first post -- theorectically, i could configure it to primary/standby, which will only route to my secondary upon failure.

i am just looking for any input on that path -- not the mirror or even the cluster, but the IP sitting in front of it, that all my clients will connect to, and be directed to whichever SQL is primary.

I can't imagine I am the only person whose ever gone down this path.
0
 
lcohanDatabase AnalystCommented:
Well we are talking about two overlaping things in my opinion.

SQL Mirroring hight-safety requires 2 servers and 1 witness for automatic failover so as long as the hardware and networking is always there I can't see why you would need load balancing on the top of that. The witness and automatic failover will take care of that.

Are your servers in different locations?

http://msdn.microsoft.com/en-us/library/ms189852.aspx

"High-safety mode with automatic failover requires a third server instance, known as a witness. Unlike the two partners, the witness does not serve the database. The witness supports automatic failover by verifying whether the principal server is up and functioning. The mirror server initiates automatic failover only if the mirror and the witness remain connected to each other after both have been disconnected from the principal server. "

.....

"Role Switching
 

Within the context of a database mirroring session, the principal and mirror roles are typically interchangeable in a process known as role switching. Role switching involves transferring the principal role to the mirror server. In role switching, the mirror server acts as the failover partner for the principal server. When a role switch occurs, the mirror server takes over the principal role and brings its copy of the database online as the new principal database. The former principal server, if available, assumes the mirror role, and its database becomes the new mirror database. Potentially, the roles can switch back and forth repeatedly.

The following three forms of role switching exist.

Automatic failover

This requires high-safety mode and the presence of the mirror server and a witness. The database must already be synchronized, and the witness must be connected to the mirror server.

The role of the witness is to verify whether a given partner server is up and functioning. If the mirror server loses its connection to the principal server but the witness is still connected to the principal server, the mirror server does not initiate a failover. For more information, see Database Mirroring Witness."
0
 
dbaSQLAuthor Commented:
Yes, we're definitely talking about different things here.  Though I appreciate the input, I was not looking for the jist on database mirroring.  Very simply, I am looking to ease the failover at the application layer, NOT at sql server.

Thank you for your time, lcohan.  I will award you the points, and close this inquiry.  I have spoken at length with a network associate of mine over the weekend, and I hope to utilize F5 for the primary/standby failover that I am to accomplish at the application layer, referencing my SQL layer.

http://www.f5.com/solutions/availability/global-load-balancing/
0
 
dbaSQLAuthor Commented:
Good input with regard to mirrroing, but not the information I was hoping to obtain.
0
 
lcohanDatabase AnalystCommented:
I believe that if you look down to the details for database mirroring you could use the "initial partner name" and "failover partner name" as per link below to "to ease the failover at the application layer" right?

http://msdn.microsoft.com/en-us/library/ms366348.aspx

And this should take care of the retries for TCP/IP as well. The only thing to worry about is "If the mirroring session is paused, the client typically connects to the principal server and it downloads the partner name. However, the database is unavailable to the client until mirroring resumes.

This was the purpose of my comment that in my opinion you don't need another (mandatory) layer on the top of SQL mirroring. (I actualy have one instance like this installed and have it working on SQL 2005)  Of course if you have F5 in house allready the load ballancing combined with this can't harm but only help.
0
 
dbaSQLAuthor Commented:
Thank you for the additional follow up, lcohan.  I always welcome suggestions, and other ways to get something done.  I will keep digging and see what I can put together.
0
 
Anthony PerkinsCommented:
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Network Scalability - Handle Complex Environments

Monitor your entire network from a single platform. Free 30 Day Trial Now!

  • 6
  • 4
  • 2
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now