How to: Configure SQL Server 2012 AlwaysOn
Explanation of Components
Now that we have our AlwaysOn configuration setup I’d like to go over some of the components. Some are very self explanatory such as the Availability Replicas, Databases, and Group Listeners folders in your object explorer. These we dealt with directly when setting up the Availability Group (AG). Others such as the cluster resources which were implicitly created, the AG dashboards, and certain status information in the object explorer have not yet been covered.
Cluster Resources / Objects
As we have stated in previous parts of this blog series, AlwaysOn can only be used on SQL Server instances which are a member of a Windows Failover Cluster. Up until now we haven’t seen any real reason for this requirement.
Above you can see the cluster resource that was implicitly created when you created your Availability Group (AG). There are two important components to this resource and one very important note.
NOTE: Do not alter this cluster resource through the Server Manager or Fail-over Cluster Manager. Microsoft mentions it in their documentation and I’ve experienced it; you can really mess up your AG if you don’t use SSMS and/or T-SQL to handle your AG changes.
Back to the componets…
When you create an AG it will create the cluster resource (AG_SQLTest as seen in the screenshot) and it will be mostly empty. Unlike the screenshot above you would only have the Other Resources section with the AG_SQLTest component. When you create your first AG Listener the top section of the resource will be added. As you can see it is under the Server Name category and, in this case, named AGTest_Listener.
Within the AGTest_Listener there are currently two IP addresses registered. In this case one is in the sub-net of our data center 1 and the other is in the sub-net of data center two. The listener will always live in only one sub-net at a time which is why there is one marked as offline and the other marked as online. When a server outage occurs the entire AG will attempt to fail-over to an available node. If that fail-over happens to cross sub-nets then the current IP address for the listener will go offline and the other IP address will start up.
Availability Group Dashboards
One nice feature Microsoft included with AlwaysOn is a dashboard for viewing the status of your Availability Group (AG). To open the dashboard you need to:
- Open SSMS and, in the object explorer, right click the name of your AG. This can be found in the AlwaysOn High Availability > Availability Groups folder (as seen in the screenshot below).
- Select Show Dashboard.
Now that the dashboard is open I’d like to bring to your attention that each replica in the AG does not have the same amount of information available to it regarding status. Only the Primary replica will be able to see the whole picture which each individual replica will only be able to speak about themselves. For this reason there are actually two different sets of information you will see from this dashboard depending upon whether you are accessing a primary or secondary replica. The first image I am presenting will be the primary replica. I will explain all the information displayed and then present the secondary replica screenshot with references to what you no longer are capable of seeing from that server.
This is your indicator of which replica type you are viewing for this dashboard. In the screenshot above you will notice that we are viewing the Primary replica’s dashboard.
Availability group state: High level indicator of the health of the replica.
Primary Instance: name of the primary replica.
Failover Mode: This will be manual or automatic indicating what actions would occur if the primary replica went down.
Cluster state: This is the name of the Windows Cluster and Quorum type.
Listing of the replicas, their failover modes, and synchronization state. The synchronization state can be Not Synchronizing, Synchronizing or Synchronized. In transactional systems it is likely that this will always say Synchronizing.
Similar to the data in the RED BOX but as you can see this area can be filtered and sorted for easy viewing. In addition, the Failover Readiness indicates whether or not data loss would occur if you failed over to that replica immediately.
At the top of this box you will notice a last updated and an auto refresh toggle. The date is the time the dashboard last refreshed (pause and unpause the dashboard to force a refresh).
Next we have three very important and useful links:
Start Failover Wizard – this is very self explanatory and the wizard itself is very straight forward to follow. Needless to say, if you want to failover manually or have to failover manually this is what I recommend you use. You also have the option of scripting out the failover from the wizard and you’ll notice that the T-SQL is actually very simple also. If there is a chance of data loss during a failover this requires you to confirm the failover one extra time which is similar to the T-SQL where they make you send a FORCE_FAILOVER_ALLOW_DATA_LOSS switch to the ALTER AVAILABILITY GROUP command.
View AlwaysOn Health Events – once again, these links are all very self explanatory. This will give you a historical break down of events with your AG.
View Cluster Quorum Information – View cluster information (see below).
Moving on to our final dashboard view. Below is a screenshot of the Secondary replica dashboard.
As you can see above this is the exact same setup and information as the Primary replica’s dashboard but with less available to you.
- Your high level health indicators are grey because the secondary replica doesn’t know the state of the entire AG.
- The replica lists only include the current replica because this replica is not aware of any other replica statuses. The only awareness comes from the Primary Instance line at the top. The replica know what instance is currently primary but does not know its status.
- Health events and cluster quorum information links still function because this data is stored at the Windows Cluster level which this replica does have access to rather than the local instance level of the other information.
Object Explorer Status Information
In general I don’t find the Object Explorer extremely useful for monitoring Availability Groups. There are a couple of indicators which give you a very high level view of the status but the dashboard feature is far easier to use and provides far more information. I recommend utilizing the dashboard heavily and possibly look into some of the T-SQL queries for your monitoring solution.
For a measure of completeness below is a reference to the Object Explorer information and to the T-SQL monitoring.
- MSDN: Use the Object Explorer Details to Monitor Availability Groups (SQL Server Management Studio)
- MSDN: Monitoring Availability Groups (Transact-SQL)
Thank you all for following this blog series this far. By now you would know how to setup an AlwaysOn High Availability Cluster and monitor its status. Next section will focus on how to failover and the different methods to do so.
Follow the links below to move on through this tutorial.