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.