SQL Clone
SQLServerCentral is supported by Redgate
Log in  ::  Register  ::  Not logged in

Loadbalancing SQLServer

Loadbalancing SQLServer is not something that comes straight out of the box. If you want to use load balancing you have to do some work of your own.

There are a few facts you need to know when you want to load balance your SQL Server:

  • You can loadbalance SQL Server.
  • There is no data synchronization between SQL Servers.
  • Works with existing hardware.
  • Load balancing database queries is possible on ALL SQL Server versions (even starting from SQL Server Compact.
  • No additional licenses are required. Load balancing works with every version.
  • Load balancing database queries is possible in a hybrid environment (SQL Server 2000, 2005 , 2014, Express, Standard, Developer Edition).

Make sure that your queries are compatible on all versions.

  • Loadbalancing is scalable up to 32 (SQL) Servers

This is a scale limit of the Windows Server Network Load Balancing component.

Here is how we implement SQL Server Load balancing in our projects.

Load balancing a SQL Server Database Environment

For example: You have a SQL Server 2008/2008R2/2012 Production Cluster environment in an Active / Passive configuration. You have 2 single / standalone SQL Servers 2008 R2 that receive production data via SQL Replication. For SQL Replication the destination SQL versions do not need to be equal to the source SQL version. Therefore a SQL Server 2012 production server can replicate data to SQL Server 2008.

Classic SQLServer implementation
On the front end you have a reporting application or a website or any other application that is querying the Reporting Environment.

That application is creating a connection on demand to a specific DNS or IP address. The application chooses which SQL Server to connect to.

The loadbalancing solution consists of implementing Network Load Balancing on (Windows Server 2000, 2003, 2008(R2), 2012 and up) and configuring it.
By implementing NLB you will generate a new virtual IP address. Add a DNS entry on your DNS server to point to that virtual IP address.

Once you yave done that, you can connect to your SQL Server box via the newly created DNS or IP address. If you perform these steps on the other node (up to a maximum of 32 nodes) by joining that host in the NLB cluster you just created you will not end up with many new IP addresses but only 1 new virtual IP address that is shared amongst all hosts that you place in a cluster.

Loadbalancing SQLServer with NLB
Any new request to that DNS or IP address is load balanced over your 2 hosts. You can configure the actual load balance rule (round robin, 50/50 split, … ) in NLB.

Network Load Balancing servers (also called hosts) in a cluster communicate among themselves to provide key benefits, including:

  • Scalability. Network Load Balancing scales the performance of a server-based program, such as a Web server, by distributing its client requests across multiple servers within the cluster. As traffic increases, additional servers can be added to the cluster, with up to 32 servers possible in any one cluster.
  • High availability. Network Load Balancing provides high availability by automatically detecting the failure of a server and repartitioning client traffic among the remaining servers within ten seconds, while providing users with continuous service.
Tags: , , , , , , ,


Leave a comment on the original post [www.sqltreeo.com, opens in a new window]

Loading comments...