F5 to load balance MS Sql Servers

Chenna,

 

 

While load balancing SQL servers does in fact work, and we have customers who have implemented it, it's important to uderstand some of the challenges associated with SQL LBing.

 

 

One of the most challenging aspects of load balancing SQL servers is managing reads vs. writes, and making sure the changes get replicated amongst the SQL servers fast enough. If a 'user' is load balanced to one server and makes a record write, the SQL synchronization technology you are using (most likely log shipping or mirroring), must be able to replicate that change to the other SQL servers before the next 'user' requests that data, as they may have been sent to a different SQL server in the farm.

 

 

More often than not, when I hear that the BIG-IP is being used to load balance a SQL farm, the farm is either built of read-only servers, or the BIG-IP is actually being used to 'load balance' active-passive SQL clusters.

 

 

I'm not sure what your specific requirements are for this deployment, however I thinks its important to weigh the benefits and drawbacks of clustering vs. load balancing in this case. With SQL, a lot of times clustering is the technology that makes sense.

 

 

-Ryan

 

 

Thanks for your reply Ryan. We did consider the synchronization risk that goes with load balancing solution and as you said, we are not looking to use Big IP to load balance, instead it is to do switching between database servers the fastest possible way. For example, when the huge data load job on the data warehouse side is completed, users need to be switched to a different database server and vice versa. Using Microsoft Cluster Services, there is a downtime for the services to go down on one box and come up on the other, in short it can't do Active-Active cluster.

 

 

I hope I made sense with my description. Welcome any questions.

 

 

Appreciate your time.

Now that I see your scope I may be able to help while keeping it simple. We had/have a similar issue.

 

 

We created a new table or DB where we kept the "switch" (0 or 1) AFter the SUCCESSFUL data load the process would

 

flip the switch and the downstream applications, which were programmed to look for the switch, then knew what DB to go to.

 

The "old" DB (populated with the previous data load) then sits idle waiting for the next load (and is availble as some redundancy)

I proposed a solution like this: (hope it works)

 

 

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..).

 

Next thing is the trigger that makes the switch, which is a database table entry that F5 keeps polling for. Its a select statement and when a specific result is returned from the database, F5 needs to switch between nodes.

 

 

This is what I proposed for the business on the white board. Haven't tried this solution yet.

 

 

What do you think about this? Do you think it might work ? Do I need to make any major changes to this solution.

 

 

Thanks--Chenna

You also might think of Icontrol maybe?

dchenna - I'm an old Cisco guy and and new to F5, but I would rarely allow any software to have programmatic control over the settings on a production load balancer(s)...you're looking for trouble. (I don't need an answer, but something to think about) 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)? I'm not suggesting you haven't thought of this already, but just something (if you're already in a complicated environment) that may not require the complexity of what you're trying to do. You're problem seems to be not one of load balancing, but of making available and controlling successful data loads.

Coming from a Cisco world (as I did), that comment (...looking for trouble) makes sense, but I couldn't disagree more regarding F5 unless the solution is half-baked. 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.

Hi,

 

I have a similar requirement ot the above but my problem appears to be a lot more simplier.

 

I have two SQL servers in my pool and i am trying to set up a health monitor for them. I can not do this. Is it possilbe to use a TCP monitor with an SQL instance that is not the default name?

 

 

Thanks,

 

Ross

 

For SQL Servers, you might want to consider the SQL Monitor that comes out of the box. You can set it to make a query and look for specific result set. This will be the ideal way to monitor your SQL Servers.

Hi,

 

Thanks dchenna - i have been looking at this.

 

I am not too keen on having to install software onto the F5 - its just not ideal with the environment i am in.

 

Is it not possible to use the TCP monitor for a SQL Server that does not use the default instance name?

 

 

Thanks,

 

Ross