From the previous AlwaysOn Availability Group article, we provided a name for the availability group listener which is simply a unique DNS name as a Virtual Network Name (VNN) to direct read-write requests to the primary replica and read-only requests to the read-only secondary replica. In this way the client doesn’t need to know which physical server is hosting the primary replica and which is hosting the secondary replica for the read-only load.
A client connects to the current primary replica using the availability group listener for a read-write operation, for read-only it connects using the availability group listener but this time it also needs to provide ApplicationIntent=ReadOnly in the connection string. In this way the read-only requests are routed to the readable secondary replica.
Connecting to The Primary Replica Using The Physical Server Name
Not only you can use an availability group listener to connect the replica/instance but if you know the physical server name, you can directly connect to it. If you expand Availability Groups node under AlwaysOn High Availability node, you will be shown the current instance is the primary replica (ARSHAD-PC in this example) whereas another instance (ARSHAD-LP) is the secondary replica as shown below:
Connecting To The Secondary Replica Using Physical Server Name
The way you can connect to primary replica using physical server name, you can also connect to secondary replica using the physical server name. If you expand Availability Groups node under AlwaysOn High Availability node, you can notice the current instance is the secondary replica (ARSHAD-LP) whereas another instance (ARSHAD-PC) is the primary replica as shown below:
Connecting To The Availability Group Using The Availability Group Listener
As shown below, you can see that I connected to availability group using the listener which directs connections to appropriate replica based on the type of connection I am making.
Managing And Monitoring Availability Groups
There are several ways to effectively manage and monitor availability groups, replicas and databases in SQL Server 2012 but the most efficient is the inbuilt dashboard which displays information in very a intuitive way for high-level monitoring and management. In addition, you can also use the newly introduced catalog views and dynamic management views, extended events as well as system performance counters.
Viewing The Availability Group information Dashboard
To the view availability group dashboard, connect to the server/replica (using physical server name or availability group listener), expand the nodes and then right-click on availability group node and click on Show Dashboard as shown below:
Clicking on the Show Dashboard as above will open the selected availability group dashboard. The dashboard displays primary replica and all the secondary replicas and its synchronization status along with overall health indicators as shown below. If there is any failure or any exception, the dashboard displays the messages or links for details message and what can be done to resolve the issues:
Monitoring Health Events
Every events on the the availability groups are tracked by SQL Server which can be very helpful for troubleshooting. On the top right of the availability group dashboard you can see these two links, click View AlwaysOn Health Events link to display health events:
Clicking on View AlwaysOn Health Events will open a grid-view list for all the events tracked to date, you can sort these events by name or by timestamp by clicking on the column heading. Clicking/selecting on each event row will display the details on that particular event in the bottom section of page as shown below:
SQL Server 2012 has now two more SQL Server performance objects for performance monitoring:
- SQLServer:Availability Replica – contains performance counters for reporting information about the availability replicas in availability groups.
- SQLServer:Database Replica – contains performance counters for reporting information on the secondary databases on secondary replica of the availability group.
For those who prefer T-SQL, SQL Server 2012 also introduces several new catalog views and dynamic management views and extended events for managing and monitoring availability groups, replicas and databases, to learn more on these please check out msdn.