How to config sql-relay with ms-sql, high-availability with keep-alived

Topology

SQL-relay01 & SQL-relay02
       OS: CentOS 6.8 bit final.
       Service: sql-relay, keepalived.

DB01 & DB02
       OS: Windows server 2008 sp2.
       Service: MS-SQL 2012 sp2.

Step 1 - Install sql-relay and keepalived

1. Installation Rudiments

  # Tar vxzf rudiments-0.28.2.tar.gz
  # Cd rudiments-0.28.2
  #. /Configure --prefix =/usr/local/rudiments
  # Make
  # Make install 

  At this point, rudiments end of the installation 

2. Installation of SQL Relay

  # Tar vxzf sqlrelay-0.36.4.tar.gz
  # Cd sqlrelay-0.36.4
  #. /Configure --prefix=/usr/local/sqlrelay --with-rudiments-prefix=/usr/local/rudiments --with-freetds-prefix=/etc
  # Make
  # Make install 

  Installation of the end, the above parameters compiled based on individual needs to set up, because I also need Oracle SQLserver MySQL, this has added so many parameters.

3. Installation Free-TDS

# yum install -y freetds

# vi /etc/freetds.conf

[ServerLocal7]
        host = 172.30.27.7
        port = 1433
        tds version = 7.0
        client charset = GB2312
[ServerLocal11]
        host = 172.30.27.11
        port = 1433
        tds version = 7.0
        client charset = GB2312

Test FreeTDS

# TDSVER=7.0 tsql -H myhostname.mssqlserver.com -p 1433 -U myusername

DB01 and DB02, i install MS-SQL 2012 sp2. FreeTDS is soft establish to database ms-sql, between CentOS and Windows Server 2008.

4. Install and config KeepAlived

# yum install -y keepalived

# vi /etc/keepalived/keepalived.conf

vrrp_script chk_nginx {
        script "/usr/local/bin/check_nginx.sh"
        interval 2
        fall 2
        rise 2
}

vrrp_instance VI_1 {
        interface eth0
        state MASTER
        virtual_router_id 51
        priority 101                    # 101 on master, 100 on backup
        nopreempt

        unicast_src_ip 172.30.27.36
        unicast_peer {
                172.30.27.71
        }

        virtual_ipaddress {
            172.30.27.38
        }
        track_script {
            chk_nginx
        }
        notify /usr/local/bin/keepalived.state.sh

}

# vi /usr/local/bin/check_nginx.sh
#!/bin/bash
HAPROXY_STATUS=$(/bin/ps ax | grep -w [n]ginx)
if [ "$NGINX_STATUS" != "" ]
then
  exit 0
else
  logger "Nginx is NOT running. Setting keepalived state to FAULT."
  exit 1
fi


# vi /usr/local/bin/keepalived.state.sh
#!/bin/bash
TYPE=$1
NAME=$2
STATE=$3
echo $STATE > /var/run/keepalived.state


# ip addr show

This command is show server hold virtual ip.

SQL-relay will define servers database at the backend, and connect to servers. Here is sql-relay config, example:

# vi /usr/local/sqlrelay/etc/sqlrelay.conf

<?xml version="1.0"?>

<!DOCTYPE instances SYSTEM "sqlrelay.dtd">

<instances>

        <instance id="master" port="9001" socket="/tmp/master.socket" dbase="freetds" connections="10" maxconnections="20" maxqueuelength="0" growby="1" ttl="60" endofsession="commit" sessiontimeout="5" runasuser="nobody" runasgroup="nobody" cursors="5" authtier="listener" handoff="pass">

                <users>

                        <user user="mssql" password="password"/>

                </users>

                <connections>

                        <connection connectionid="master" string="server=ServerLocal7;db=Data;user=sa;password=password;" metric="1"/>

                        <connection connectionid="slave" string="server=ServerLocal11;db=Data;user=sa;password=password;" metric="1"/>

                </connections>

        </instance>

</instances>

# export PATH=$PATH:/usr/local/sqlrelay/bin 

# service sqlrelay start

# sqlr-start -id master

          This topology have two sql-relay server running keepalived for high-availability, sure for service sql-relay running 24/7. And use FreeTDS establish connection to ms-sql running on Windows server 2008 r2.

Read More