Is it possible/feasible/reasonable to use HAProxy to load-balance three SQL Server 2008 database servers?
No, it isn't. And even if you could use Windows NLB, that wouldn't work reliably either.
When your application servers talk to the SQL Server, the TDS communications protocol holds 'state'. Think about transactions or long-running queries for example.
HAProxy and NLB do not understand TDS. If you just used HAProxy to load balance in TCP/IP mode, then the connection could be switched over right in the middle of a transaction -- giving you highly unpredictable results and possibly data loss.
AFAIK, there is no plug'n'play load balancer for SQL Server.
Update (28 Aug 2012): I now tend to use haproxyctl nowadays, which utilizes the methods described below.
I've fixed it after a little more research, for anyone else with the same issue:-
You can add a unix socket in your config, then interact with that (here are the possible commands).
To set up:
sudo nano /etc/haproxy/haproxy.cfg
In your 'global' section add in:
stats socket /etc/haproxy/haproxysock level admin
Restart your haproxy daemon (e.g. sudo service haproxy restart)
Now you need socat (if you don't have it, just apt-get install socat on Ubuntu).
Now all you need to do is fire off this command to take down a node:
echo "disable server yourbackendname/yourservername" | socat stdio /etc/haproxy/haproxysock
To bring it back up replace disable with enable in the command above.
Best Answer
DSR is implemented in the Linux Kernel (IPVS) which only works with LVS (Linux Virtual Server). You will also need a health checking daemon like keepalived or ldirectord. BTW DSR does not work in Amazon AWS or in Azure due the to the network virtualisation security that they use. It won't work in things like Docker either.
http://www.loadbalancer.org/blog/load-balancing-methods