?
Solved

Load balancing SQL Server

Posted on 2012-03-16
13
Medium Priority
?
1,000 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
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 
LVL 40

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 40

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
 
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 40

Accepted Solution

by:
lcohan earned 1500 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 40

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

Nothing ever in the clear!

This technical paper will help you implement VMware’s VM encryption as well as implement Veeam encryption which together will achieve the nothing ever in the clear goal. If a bad guy steals VMs, backups or traffic they get nothing.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Want to know how to use Exchange Server Eseutil command? Go through this article as it gives you the know-how.
Steps to fix error: “Couldn’t mount the database that you specified. Specified database: HU-DB; Error code: An Active Manager operation fail”
This tutorial will walk an individual through the steps necessary to configure their installation of BackupExec 2012 to use network shared disk space. Verify that the path to the shared storage is valid and that data can be written to that location:…
This tutorial will show how to configure a new Backup Exec 2012 server and move an existing database to that server with the use of the BEUtility. Install Backup Exec 2012 on the new server and apply all of the latest hotfixes and service packs. The…

807 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