Solved

Load balancing SQL Server

Posted on 2012-03-16
13
885 Views
Last Modified: 2012-03-19
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
Comment
Question by:dbaSQL
  • 6
  • 4
  • 2
  • +1
13 Comments
 
LVL 25

Expert Comment

by:jogos
ID: 37729337
<<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
 
LVL 25

Expert Comment

by:jogos
ID: 37729356
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
 
LVL 17

Author Comment

by:dbaSQL
ID: 37729582
I should have said specifically, I don't want to use MSCS.
0
 
LVL 39

Expert Comment

by:lcohan
ID: 37729803
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
 
LVL 17

Author Comment

by:dbaSQL
ID: 37730172
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
 
LVL 39

Expert Comment

by:lcohan
ID: 37730355
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
Google Storage: Standard vs. Nearline vs. Coldline

Google Cloud Storage has a number of classes to choose from. Although there are a lot in common, they vary in price and usage terms. This post explains Google Cloud Storage classes and helps to understand which  one to choose.

 
LVL 17

Author Comment

by:dbaSQL
ID: 37730463
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
 
LVL 39

Accepted Solution

by:
lcohan earned 500 total points
ID: 37730522
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
 
LVL 17

Author Comment

by:dbaSQL
ID: 37737204
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
 
LVL 17

Author Closing Comment

by:dbaSQL
ID: 37737211
Good input with regard to mirrroing, but not the information I was hoping to obtain.
0
 
LVL 39

Expert Comment

by:lcohan
ID: 37737954
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
 
LVL 17

Author Comment

by:dbaSQL
ID: 37740378
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
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 37740502
0

Featured Post

Top 6 Sources for Identifying Threat Actor TTPs

Understanding your enemy is essential. These six sources will help you identify the most popular threat actor tactics, techniques, and procedures (TTPs).

Join & Write a Comment

Suggested Solutions

In this article I will describe the Backup & Restore method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
In this article we have discussed the manual scenarios to recover data from Windows 10 through some backup and recovery tools which are offered by it.
This tutorial will walk an individual through locating and launching the BEUtility application and how to execute it on the appropriate database. Log onto the server running the Backup Exec database. In a larger environment, this would generally be …
To efficiently enable the rotation of USB drives for backups, storage pools need to be created. This way no matter which USB drive is installed, the backups will successfully write without any administrative intervention. Multiple USB devices need t…

760 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

23 Experts available now in Live!

Get 1:1 Help Now