F5 to load balance MS Sql Servers

Hi Ross: You don't have to install any software on F5 for setting up a SQL monitor. All you have to do is create a new monitor, in the type, select MSSQL and the rest is straight forward parameters for your sql servers.

 

Great thread, thought I'd jump in with a few comments.

 

 

First of all, definitely use the built in SQL monitor: works great, fully supported, checkbox config.

 

 

chenna said: Create a VIP that will listen on 1433 (SQL Server Port) and will point at a pool with 2 servers of which at any given point only one will be active. The pool members may be running SQL on a different port, like 1437 or something (I hope it works, I never tried this but not sure if the VIP and the Pool Member service should be on the same port..sounds like I am missing basics here..). just to clarify, yes, the pool members may run on different ports than the VS, and ports are automatically translated by default.

 

 

jr6311 said: I would rarely allow any software to have programmatic control over the settings on a production load balancer(s)...you're looking for trouble...

 

and citizen_elah said: A well designed integration not only increases the flexibility and power of the solution, but it also cuts the manpower required to manage the tedium of something much better controlled by a process. I have to agree with citizen_elah on this one. A simple iControl script triggered at the end of the data load that updates the pool on LTM to use the new DB pool member may be the best solution in this case, especially given the request for a fast changeover.

 

 

jr6311 also said: If your device is polling the DB (this table entry) and you want to either manually control what machine is used or there is a real machine failure, given your proposed solution won't you introduce your own issues if the DB table entry the F5 is polling for keeps sending it back to an undesireable server? Don't you now have to worry about load balancing the database table entry the F5 keeps polling for (what if it's not available)? If you do go the DB row route to use a monitor to set the status, these are is valid concerns, as is contention for the same login or same row concurrently by monitors originating from both members of the redundant pair. You would want to set a different value for that row when the DB is supposed to be UP, and only that server would be available for load balancing. That way a non-responsive server would never be chosen.

 

 

You're problem seems to be not one of load balancing, but of making available and controlling successful data loads. which is why the iControl solution is likely the most optimal for this requirement -- monitor timing is non-deterministic and not coordinated, and the chance of neither or both servers becoming available for the period equal to the monitor interval.

 

 

HTH

 

/deb

Thanks for the replies guys.

 

I am still having issues.

 

 

My problem appears to be when i use a named instance as opposed to the default instance.

 

1) I am not able to set up a Health Monitor

 

Here are my settings.

 

 

2) I disabled any health monitors. When i use the default instance i can connect in through the F5. When i specify a named instance it does not work. I have a VIP set up on port 1433. I have the named instanced specified to use port 1437. I have the node configured for this port in the F5. Any ideas? (I can connect directly to the SQL server so its not that)

 

 

Thanks,

 

Ross

 

Hey Guys,

 

I actually just managed to get my connections through to the SQL servers.

 

My only problem now is the Health monitors.

 

 

I can use TCP and it is working but regarding using the SQL health monitor:

 

Do i have to specify a user and password?

 

Can someone give me an example of a SQL health monitor or a location with detials on this?

 

Thanks guys,

 

Ross

I have done this procedure in production through Cisco and F5 load balancers and you may have some issues with Connection Pooling during the transition.

 

 

If you have .NET code, or any other code that can take advantage of connection pooling, then you may have issues during the transition. Connection pooling persists connections to the database from the client and re-uses them. Without pooling, each query opens a new connection. During a failover, the persisted connection to the DB will break, but you have to ensure that a "TCP FIN" is sent back to the client, or all subsequent queries to the pool will fail. The client assumes that it can still send queries over the connection to the VIP via the same TCP/IP session, but the session paramaters have changed because the connection is now on a new back-end server. At some point, the pool will establish a new connection, but you would likely get lots of GNE's (General Network Errors) during the transition. The Cisco's were very bad at this, but some revision of their code allowed long lived connections to the DB and things were somewhat better. The F5's were less problematic, but we still received GNE's occasionally. Make sure you set your connection timeouts to "infinite" or a length of time longer than the pool timeout.

 

 

 

 

Hi.

 

 

I am new to this community. Came across this thread because of a similar problem I am trying to solve. I currently don't have access to a F5 device, so my questions is only theoretical.

 

 

Can I use a F5 LTM device to manage the front-end traffic for a set of regionally dispersed MSSQL 2005 servers ?

 

 

The challenge is to create a DR environment for MSSQL in a secondary site. There is many-to-one problem, about 20 client connections to a local MSSQL cluster. I like to virtualize the access traffic by inserting a traffic VIP/TCP proxy in active/stand-by mode in front of the cluster and use the back-end connections direct the traffic to either site.

 

 

The problem I am running into for the POC, using a simple software implementation (cross-winds, balance), it doesn't work. Client connections are timing out with "name pipe error". I am using TCP 1433 and TCP 135 (epmap) for the VIP.

 

 

Will a F5 do any better in this scenario ?

 

 

Much appreciated.

 

kbsartain

 

 

If you have .NET code, or any other code that can take advantage of connection pooling, then you may have issues during the transition. Connection pooling persists connections to the database from the client and re-uses them. Without pooling, each query opens a new connection. During a failover, the persisted connection to the DB will break, but you have to ensure that a "TCP FIN" is sent back to the client, or all subsequent queries to the pool will fail.

 

 

 

 

You can configure LTM to send a TCP reset if the selected pool member is marked down. This can be configure in the pool properties under 'Action on Service Down'.

 

 

 

atmfrank

 

Can I use a F5 LTM device to manage the front-end traffic for a set of regionally dispersed MSSQL 2005 servers ?

 

 

 

 

LTM can load balance hosts that are on a local subnet or on remote networks with a route.

 

 

Aaron

Hi Aaron, Sorry to dredge up this old topic, but I'm dealing with the exact instance you describe here with the .NET connection pooling.

 

 

"You can configure LTM to send a TCP reset if the selected pool member is marked down. This can be configure in the pool properties under 'Action on Service Down'. "

 

 

I've tried each of the options in "Action on Service Down", Reject, Drop and Reselect, but with each one there is still a failure on the next request from the connection pool. If I had control of the .NET code accessing the server I could just add a retry block, but that's not an option. Can you think of any other settings I might need to check, or other ideas to try?

 

 

Thanks in advance,

 

-Patrick

I got just about everything to work regarding setting up MS SQL Server behind a VIP. The problem we are seeing is with the authentication, all of our SQL Servers use kerberos. When we connect via a VIP it logs in as NTLM. Has anyone figured out how to log in and keep the kerberos authentication valid?

Good afternoon-

 

 

When you say "it logs in as NTLM", where are you seeing this? BIG-IP LTM won't add or change authentication provided by the client -- and the client, in this case, is presumably an application, right?

 

 

If you're just seeing the IP address of the BIG-IP, that's expected, assuming you're using SNAT (which is common). But again, this doesn't actually change the Kerberos ticket presented by the client.

 

 

If you can post some more details about your topology and BIG-IP configuration, we'll see if we can help. Please make sure to indicate where you're seeing the logs of the connection attempts.