Smarter by Design.Simpler for Business
TR7Enterprise Application Delivery & Security Platform
Can I load balance database connections (SQL Server, Oracle, MySQL, PostgreSQL)?
Short answer
Yes, with an L7 TCP vService. The database port (SQL Server 1433, MySQL 3306, PostgreSQL 5432, Oracle 1521) is published in the frontend with the TCP protocol, and Least Connection is selected in the LB Algorithm box in the Details panel. Distribution is done at the connection level: TR7 does not read SQL queries, so it cannot separate read and write queries onto different servers; for this, the application must route its read connections to a separate vService address. So that idle connections in application connection pools are not dropped, the Tunnel(Websocket) Timeout value (default 120 seconds) in the Timeouts profile is increased.
Problem: Why Is Database Traffic Different?
Applications usually connect to the database with a connection pool: a small number of connections are opened and used for a long time. The load across the connections is not equal; some connections run heavy queries. In addition, database servers are often not equal (the primary and read replicas); a write operation can go only to the primary server.
Solution
Connection-Level Distribution with an L7 TCP vService
- How it works Each new database connection is sent to the healthy server that has the fewest open connections at that moment; the connection stays on the same server until it closes.
- Where it is opened vService → Type: L7 TCP → Details → LB Algorithm → Least Connection
- Requirement The distributed servers being able to do the same job (e.g. only read replicas), or the application sending its read and write connections to separate addresses
- vService type L7 TCP
- Backend Service side By default, the database server sees the connections as coming from the IP of TR7; if user access is restricted by client IP (e.g. PostgreSQL
pg_hba.conf, the MySQL user host), the IP of TR7 is allowed or SNAT → Use Client IP is selected on the Backend Service.
Step-by-Step Configuration
1
Adım 1 Open the health check add form (if none exists)
On the Load Balancer → Profiles → Health Checks screen, the Add button is clicked (how to add).
2
Adım 2 Enter the profile fields
Name is entered; as the Check Type, Oracle is selected for Oracle (User, Password and Connection String are required), and TCP for SQL Server, MySQL and PostgreSQL; if send/expect steps at the protocol level are needed, steps are added to the TCP Scenario field of the TCP type. Check type comes as HTTP by default and cannot be changed after saving.
3
Adım 3 Save the health check profile
The Add button at the bottom of the form is clicked.
4
Adım 4 Open the Backend Service add form (if none exists)
On the Load Balancer → Backend Services screen, the Add button is clicked (How is a Backend Service added; which information is required?).
5
Adım 5 Enter the server information
Backend Service Name and Address (e.g. 192.0.2.60:1433) are entered; in the Health Check field, the profile created in steps 1–3 is selected.
6
Adım 6 Save the Backend Service
The Add button at the bottom of the form is clicked. Steps 4–6 are repeated for each database server.
7
Adım 7 Open the Timeouts add form (if none exists)
On the Load Balancer → Profiles → Timeouts screen, the Add button is clicked (how to add).
8
Adım 8 Enter the Timeouts fields
Name is entered and, in the Tunnel(Websocket) Timeout field, a value greater than the idle wait time in the connection pool of the application is written in seconds (at most 60,000). If there are long-running queries, Server Timeout (default 90 seconds) is also increased.
9
Adım 9 Save the Timeouts profile
The Add button at the bottom of the form is clicked.
10
Adım 10 Open the L7 TCP vService form
The Load Balancer → Load Balancer vService List → Add steps are followed; Name is entered and L7 TCP is selected as the Type.
11
Adım 11 Define the Frontend with the database port
In the Frontend row, the IP is selected, the port is set to the port of the database and TCP is selected as the Protocol. If the database traffic is encrypted, TCP is selected as well; TLS stays on the server. If the IP is not in the list, it is first added with Network → Interfaces → Actions → Add IP (Why can't I type the Frontend IP manually; what should I do to use an IP that is not in the list (e.g. IPv6) on a vService?).
12
Adım 12 Select the servers
In the Default Backend Services table, the Backend Services added in steps 4–6 are selected.
13
Adım 13 Select Least Connection
The Details switch at the top right of the form is turned on; in the Algorithm field in the LB Algorithm box, Least Connection is selected. The default algorithm is Round Robin.
14
Adım 14 Select the profile in the Timeout box
In the Details panel, the Timeout box is checked and the profile created in steps 7–9 is selected.
15
Adım 15 Save
The Save button is clicked. In a cluster setup, the change is also transferred to the peer device with the default auto-sync; if Manual Sync Mode is on, it is sent manually with Synchronize.
Verification
1By querying the server nameClient side
SQL Server
sqlcmd -S 203.0.113.50,1433 -Q "SELECT @@SERVERNAME"PostgreSQL
psql -h 203.0.113.50 -c "select inet_server_addr()"MySQL
mysql -h 203.0.113.50 -e "select @@hostname"Expected
When the command is run several times, different servers are returned2Open connectionsTR7 side
On the vService Sessions screen of the vService, the distribution of the open connections across the Backend Services is seen.
Expected
The connections are spread across the healthy Backend ServicesCommon Mistakes
- Distributing write operations to read replicas. TR7 does not distinguish queries; write connections are routed to a separate vService address that goes only to the primary server.
- Leaving the duration at the default. If a connection in the pool is not used for 120 seconds, it is closed; the application gets a "connection closed" error.
- Selecting TCP + TLS. In their default connection mode, SQL Server, MySQL and PostgreSQL start TLS later inside their own protocol; this TLS cannot be terminated on TR7, and TCP is selected as the Protocol.
- Forgetting server-side redirection. Some cluster features (redirection to a read replica, listener redirection) can bypass TR7 by advertising the server's own address to the client; in these features, the advertised address is set to the vService address.
- Not assigning a health check. A Backend Service with no health check defined is always considered reachable; a server that goes down continues to receive connections.
How it is done in the documentation
