Managing and Connecting to AlwaysOn Availability Groups

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:

Description: Description: cid:image001.png@01CCCFBD.EA80F430

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:

Description: cid:image005.png@01CCD058.DE58E910


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:

Description: cid:image009.png@01CCD05A.6B3E97C0

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:

Description: cid:image011.png@01CCD05A.6B3E97C0

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.

Pages: 1 2




Array

One Response to “Managing and Connecting to AlwaysOn Availability Groups”

  1. Hi Arshad,

    Suppose the client in the day of MSCS/SQL2008 (new term as FCI) used resource_virtual_host_name/instance_name to connect the database, if we migrate to AAG (as HA, not DR) with VNN as the same of virtual_host_name and keep the same instance name, could the client behave the same (normal connection as well as failover) by the same connection string (not change to AAG listener name)?

Software Reviews | Book Reviews | FAQs | Tips | Articles | Performance Tuning | Audit | BI | Clustering | Developer | Reporting | DBA | ASP.NET Ado | Views tips | | Developer FAQs | Replication Tips | OS Tips | Misc Tips | Index Tuning Tips | Hints Tips | High Availability Tips | Hardware Tips | ETL Tips | Components Tips | Configuration Tips | App Dev Tips | OLAP Tips | Admin Tips | Software Reviews | Error | Clustering FAQs | Performance Tuning FAQs | DBA FAQs |