Load balancing for SQL Server read libraries using Haproxy

Front-facing conditions

Use the Publish subscription feature of SQL Server for read-write separation and create multiple read libraries.

Load balancing in this article is for multiple read libraries.

Test environment

VMware 10 64-bit

Windows Server R2

SQL Server 2008

CentOS 6.6

Haproxy 1.5

Virtual machine configuration

(1) Virtual machine 1: Install CentOS and install Haproxy. IP is: 172.16.1.1. As a load balancer.

(2) Virtual Machine 2: Install Windows Server R2, and install SQL Server 2008. IP is: 172.16.1.2, as database server 1.

(3) Virtual machine 3: Clone virtual machine 2. IP is: 172.16.1.6. As database server 2.

Such as:

Haproxy Configuration

The most important thing here is to configure the Haproxy configuration file as follows:

Global          maxconn 5120          chroot/usr/local/haproxy          uid ©          gid          daemon          quiet          nbproc  2          pidfile/usr/local/haproxy/haproxy.pid  Defaults          log     Global          mode    http          option  httplog          option  dontlognull          log 127.0.0.1 local3          retries 3          option Redispatch          Maxconn          contimeout      50000          clitimeout      50000          srvtimeout      50000     Listen MSSQL : 1433         mode TCP         balance roundrobin         server mssql1 172.16.1.2:1433 Check weight 1 Check       server mssql2 172.16.1.6:1433 Check weight 1 Check      Listen stats:8888         mode http         transparent         stats URI/ Haproxy-stats         stats Realm haproxy \ Statistic  

Test

(1) Create a test table:

CREATE TABLE [dbo]. [Table1] ([F1] [nchar] (TEN) NULL) On [PRIMARY]

(2) To see the results of load balancing, set the data for the Table1 table in the two read library to a different

(3) Connect to 172.16.1.1 (load balancer) and query using the following query statement.

SELECT TOP [F1] from  [test1].[ DBO]. [Table1]

You need to disconnect the connection before each query before establishing a connection to SQL Server, as shown in:

Special Note :

(1) If the connection is not re-established, the result is the same each time, because SQL Server client uses the database connection pooling technology, in the absence of active shutdown, the TCP connection will remain.

(2) After disconnecting and reconnecting again, the result is different.

The methods described in this article are only passed in a test environment and are not yet available in a formal environment.

For haproxy installation, see:

Http://www.cnblogs.com/dehai/p/4885016.html

Load balancing for SQL Server read libraries using Haproxy