SQL Server load balancing
Introduction
MS SQL Server to the current version (2014) does not have the capability of load balancing NLB (Network Load Balance).
With load balancing applications if conectariam on the server with the lowestload, it would have performance gains.
MS SQL Server does not have the capability of load balancing, but we can create an environment using the AlwaysOn feature, where the main server would be only for writing and the secondary servers for reads, the use in writing server will be lower and we do read-balancing.
Implementation
We will not create the cluster using the AlwaysOn, this information can be found in the documentation of the MS SQL Server and on the internet.
The solution is simple, in our example we will consider a cluster with four nodesusing the AlwaysOn feature, a primary server and three as secondary.
Create a table, two stored procedure (SP), a JOB and linked server for all serversin the cluster.
The table will contain only one field (server) and only one record, the table will have the name of the server (secondary), the applications will connect on that server.
The "SP de escrita", this SP will examine the conditions of the secondary servers,taking into considerations locks, CPU utilisationand amount of processes,connect the main server in the secondary servers by using the linked server, the end updates the table with the name of the best server at the moment.
The JOB will be responsible for calling the "SP de escrita" and keep the tableupdated with the name of the server, this JOB will run every 3 seconds.
The "SP de leitura", will be used by applications to determine which server should be used for reading.
There will be two sequences happening simultaneously, a JOB and the other initiated by the application.
By JOB:
1. Every 3 seconds the JOB executes the written SP.
2. The "SP Escrita" query all secondary servers to identify the best server at the moment.
3. The "SP Escrita" updates the table with the name of the server.
By Application:
1. The application connects on the primary server (written) and performs the "SP Leitura".
2. The "SP Leitura" queries the table and returns the name of the secondary server (reading).
3. The application connects to the secondary server (reading) and performs your queries.
Conclusion
In the case of AlwaysOn settings with failover, it is necessary to create the linked server in the secondary servers and the JOB, where the JOB will be disabled and enabled when this is the primary.
Download
The codes for creation of the tables, JOB, SP and linked server can be downloaded http://gallery.technet.microsoft.com/Balanceamento-de-carga-SQL-f164bd82
Credits
More information on the Blog of the author: http://www.tuliorosa.com.br/index.php/archives/1931