Benefits of load balancing Microsoft SQL Server

Load balancing Microsoft SQL Server, when used with an environment of multiple, often synchronized, SQL Server instances (like with Always On Availability Groups or separate read-only servers), helps manage incoming client connections and queries across the available database resources.

This provides three main benefits:

• High Availability (HA): Load balancing ensures high availability and reliability by providing fault tolerance. It continuously monitors SQL Servers and, if one fails or needs maintenance, it instantly redirects all client traffic to the remaining healthy servers. This prevents a single point of failure and keeps the application running without interruption.
• Increased scalability: Load balancing enables easy horizontal scalability for SQL Server. As traffic grows, simply add more database instances, and the load balancer will automatically distribute the increased workload. Crucially, it can direct read-only tasks (like reporting) to secondary replicas, offloading resource-intensive operations from the primary server.
• Improved performance and throughput: By distributing the workload, load balancing prevents any single server from becoming overwhelmed, leading to faster response times and higher throughput for the entire application. The distribution methodology (like round-robin or least connections) ensures that the computational burden is shared evenly, which prevents resource bottlenecks on individual servers (such as high CPU or I/O utilization). By spreading the workload and preventing server overload, each query can be processed more quickly, which results in lower query response times (latency) for end-users.

About Microsoft SQL Server

Microsoft SQL Server is a proprietary relational database management system (RDBMS) developed by Microsoft using Structured Query Language that supports a wide variety of transaction processing, business intelligence (BI) and data analytics applications in corporate IT environments.

Why Loadbalancer.org for Microsoft SQL Server?

Loadbalancer’s intuitive Enterprise Application Delivery Controller (ADC) is also designed to save time and money with a clever, not complex, WebUI.

Easily configure, deploy, manage, and maintain our Enterprise load balancer, reducing complexity and the risk of human error. For a difference you can see in just minutes.

And with WAF and GSLB included straight out-of-the-box, there’s no hidden costs, so the prices you see on our website are fully transparent.

More on what’s possible with Loadbalancer.org.

How to load balance Microsoft SQL Server

The load balancer can be deployed in four fundamental ways: Layer 4 DR mode, Layer 4 NAT mode, Layer 4 SNAT mode, and Layer 7 Reverse Proxy (Layer 7 SNAT mode).

For Microsoft SQL Server, Layer 7 Reverse Proxy is recommended.

Virtual service (VIP) requirements

To provide load balancing and HA for Microsoft SQL Server, the following VIPs are required:

Ref.	VIP Name	Mode	Port(s)	Persistence Mode	Health Check
VIP 1	MS-SQL-Server	L7 Reverse Proxy	1433	Source IP	Connect to Port

Load balancing deployment concept

Load balancing topology

Layer 7 Reverse Proxy can be deployed using either a one-arm or two-arm configuration. For two-arm deployments, eth1 is typically used for client side connections and eth0 is used for Real Server connections, although this is not mandatory since any interface can be used for any purpose.

For more on one and two-arm topology see Topologies & Load Balancing Methods.

About Layer 7 Reverse Proxy load balancing

Layer 7 Reverse Proxy uses a proxy (HAProxy) at the application layer. Inbound requests are terminated on the load balancer and HAProxy generates a new corresponding request to the chosen Real Server. As a result, Layer 7 is typically not as fast as the Layer 4 methods. Layer 7 is typically chosen when either enhanced options such as SSL termination, cookie based persistence, URL rewriting, header insertion/deletion etc. are required, or when the network topology prohibits the use of the Layer 4 methods.

The image below shows an example Layer 7 Reverse Proxy network diagram:

Because Layer 7 Reverse Proxy is a full proxy, Real Servers in the cluster can be on any accessible network including across the Internet or WAN. 

Layer 7 Reverse Proxy is not transparent by default, i.e. the Real Servers will not see the source IP address of the client, they will see the load balancer’s own IP address by default, or any other local appliance IP address if preferred (e.g. the VIP address). 

This can be configured per Layer 7 VIP. If required, the load balancer can be configured to provide the actual client IP address to the Real Servers in 2 ways. Either by inserting a header that contains the client’s source IP address, or by modifying the Source Address field of the IP packets and replacing the IP address of the load balancer with the IP address of the client. For more information on these methods, please refer to Transparency at Layer 7 in the Enterprise Admin Manual. 

Related Resources