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).
- Loadbalancing is scalable up to 32 (SQL) Servers
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.
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.
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.
- 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: DNS, load balancing, Microsoft SQL Server, Network Load Balancing, relational database management, SQL, Web server, Windows Server
