Load Balancing Microsoft SQL Server With HAProxy
In case you are told it isn’t possible, I can confirm that it is in fact possible to load balance requests to databases using HAProxy. Here are the specifics.
First, the databases in question are SQL 2012 Web Edition databases. They get populated by replication from a SQL 2012 Standard Edition publisher. Reads are sent to these replicated databases, but all writes go to the master database.
Here is the entire HAProxy config for a basic database load balancing listener, taking connections from a list of approved addresses and distributing them to three servers, depending on whether they appear to be up.
listen sql-db bind *:1433 mode tcp balance leastconn acl db_white_list src 0.0.0.0 18.104.22.168 22.214.171.124 126.96.36.199 tcp-request connection reject if !db_white_list option log-health-checks server DB-1 188.8.131.52:1433 check port 1433 inter 1000 server DB-2 184.108.40.206:1433 check port 1433 inter 1000 server DB-3 220.127.116.11:1433 check port 1433 inter 1000
You can get more advanced here – you could perform more advanced checks using SQL to make sure you have more than just a connection to a port – but you get the idea.
What does this cost?
Unlike HTTP load balancing, you will actually start to see CPU usage on your HAProxy server. You will also see a big jump in network traffic, as web pages are usually smaller than the data you might query to generate them. You’ll need to make sure this isn’t a major problem. You’ll also see a little latency as it will take slightly longer to get your data as there is something else in the middle.