AlwaysOn + Availability Groups = “load balanced” SQL
Have I piqued your interest yet? SQL 2012 introduces many new features that will make life more efficient for developers, administrators, and report writers, but what is the big, bottom line, “Why do I upgrade” feature? The answer is AlwaysOn. Here are some details on how you can implement a load balancing design.
AlwaysOn in default configuration greatly streamlines setting up a traditional mirrored cluster, but we need to move beyond that to see this application truly shine. SQL 2005 through 2008 R2 clustering utilized a single server for all workloads, but you still had to buy another equally powerful server for failover replica (see Figure 1). In SQL Server 2012 you can redirect some of the day to day workload (ready only operations OR Availability Groups) to the secondary server to utilize that resource from Day 1. In other words, you don’t have to watch the second server contribute zero day-to-day operations workload while waiting for a failover event.
Using AlwaysOn for load balancing, then, you have two options:
Option 1: Load balancing through Read Only replica:
How to: Add a synchronous replica to the AlwaysOn group and specify it as a read-only replica. See figure 2. The AlwaysOn listener knows to redirect queries and reports that are read-only to that 2nd replica instead of to the primary instance. SELECT queries pulling data to CRM applications also get offloaded to the secondary server further alleviating load from production.
Why this method: This approach transfers the load from that accounting manager who wants to run a tweaked report every thirty minutes away from your production server.
Option 2: Load balancing through Availability Groups (AGs):
How to: Availability Groups allow individual databases to be grouped together for failover purposes. This way, a database (or group of databases) can failover together instead of failing over the entire instance. You can group databases together by application or combine them by usage. To load balance your AGs, split up your heavy use databases in the same AlwaysOn instance to separate AGs. Set the AG’s primary servers to be different nodes.
For example , AG1 and AG2 are in AlwaysOn instance given a virtual name. They each contain a dozen different databases for different applications and share the primary node SERVER1 in the Windows Server Failover Cluster (back to Figure 1). Applications for 1 and 2 are heavily utilized, so you want to spread the load. You can do that by moving the primary node for AG2 to the replica instance and immediately the load on SERVER1 falls (Figure 3).
AlwaysOn benefits for techies: Utilizing AlwaysOn, the cluster as a whole has a significant amount of breathing room for more capacity growth whereas previously a significant hardware investment had to be made to meet demands. SQL Server 2012 AlwaysOn + Availability Groups is a combination of clustering and mirroring that creates many exciting scenarios that we have not had before in SQL. It also includes a performance dashboard in SQL Server Management Studio to graphically view the state of your groups.
AlwaysOn benefits for the business: For the cost to license the old passive server, you can immediately double (theoretically) the capacity of your SQL server by using strategically distributed Availability Groups. The return on that license investment begins to be realized on Day 1 as the load is spread across your expensive database servers.
