2

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.

htxryan
  • 123
  • 1
  • 5

1 Answers1

1

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.

Community
  • 1
  • You don't need to understand the specifics of every single protocol in order to track and maintain connection state to the same system. Every load balancer worth anything does this, and NLB is no exception. You're completely right, though, that an NLB solution will buy absolutely nothing without the ability to partition out reads and writes. – jgoldschrafe Feb 08 '11 at 01:47
  • Actually, if he's using transactional replication with immediate updating subscribers, or a similar SQL mechanism that allows writes to be taken at any of the nodes (e.g. merge replication), this might actually work. The failure modes might be ugly though, and managing schema changes with replication is a pain. I think there was a commercial product out at one time that did something similar (except I think it analyzed the actual queries being issued, and sent queries with writes to all the back-end systems). – rmalayter Mar 22 '11 at 13:34