asked on 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.
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.
Microsoft SQL Server 2008Disaster RecoveryWindows Networking
Last Comment
Anthony Perkins
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
<<... but I don't want a cluster>>
explain while it is pretty much doing what you want
ASKER
I should have said specifically, I don't want to use MSCS.
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....)
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....)
ASKER
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.
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.
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.
ASKER
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.
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.
ASKER CERTIFIED SOLUTION
THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
ASKER
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/
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/
ASKER
Good input with regard to mirrroing, but not the information I was hoping to obtain.
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.
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.
ASKER
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.
If you are serious about this, you really need to take a look at AlwaysOn:
http://www.microsoft.com/sqlserver/en/us/solutions-technologies/mission-critical-operations/SQL-Server-2012-high-availability.aspx
http://www.microsoft.com/sqlserver/en/us/solutions-technologies/mission-critical-operations/SQL-Server-2012-high-availability.aspx
Not with failover clustering
http://msdn.microsoft.com/en-us/library/ms189134.aspx