Is it possible/feasible/reasonable to use HAProxy to load-balance three SQL Server 2008 database servers?
Here is our situation: we have two web servers that are load-balanced on Amazon EC2. We are currently using one production SQL Server 2008 DB server. That server is starting to get overloaded, so we would like to add two more DB servers, and implement a load-balancing solution.
In Amazon EC2, we are unable to use a Virtual IP address, which prevents us from being able to use Windows Network Load Balancing (NLB) or any other load-balancing method that requires a VIP.
Our application is read-heavy, but we are unable to separate reads from writes, so the load-balancing solution needs to account for this.
We are planning on using SQL Server's built-in replication feature to keep all three DB's current (we understand that there will be some lag time, but that is acceptable).
Any thoughts or suggestions are welcome, and thanks in advance for your help.
Best Answer
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.