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 provideApplicationIntent=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.

Manual Failover

You can configure your availability group for automatic failover or for manual failover. In case of automatic failover if the primary replica/instance does down the AlwaysOn availability group switches the role of primary replica/instance to another synchronous secondary replica.

You can also elect for manual failover. The failover will occur without data loss if both primary and secondary replicas are in synchronous commit mode and are in a healthy state, data loss will occur if the secondary replica is in asynchronous commit availability mode. To do a manual failover using the wizard, click on the Start Failover Wizard link on the top right corner of the availability group dashboard as shown below:

This will launch the failover wizard, the first screen of the wizard is the welcome screen so click Next to proceed:

On the Select New Primary Replica page of the Failover Availability Group wizard, you need to specify the secondary replica which will be the new primary replica after the failover, the screen will also display the failover readiness and availability mode (synchronous or asynchronous). Click next continue:

On the Connect to Replica page of the wizard, you need to connect to the secondary replica which you want to make the primary replica after failover, click on the Connect button to get connected to the secondary replica:

The Summary page of the Failover Availability Group wizard will display a summary on your selection and actions that wizard is going to perform. Click on Next to failover. You can also generate the failover T-SQL script by clicking on Script button at the bottom:

Clicking on Finish will start switching the role or performing the failover, you can monitor the progress and overall of status of operation as shown in screen below:

If I open the availability group dashboard again or refresh it, I can see role has now, my earlier primary replica (ARSHAD-PC) is now the secondary replica whereas my earlier secondary replica (ARSHAD-LP) is now the primary replica:

Please note, this example and demonstration is based on SQL Server 2012 RC0 and there might be slight changes in the RTM release; refer to BOL (Books online) or msdn for updated information.

Summary

In this article I discussed the AlwaysOn availability group feature of SQL Server 2012. AlwaysOn is a new High Availability (HA) and Disaster Recovery (DR) solution in SQL Server 2012 which improves high availability and protects data of your mission critical applications. AlwaysOn availability group is recommended over database mirroring since it combines the best of failover clustering and database mirroring and overcomes the several limitations imposed in failover clustering or database mirroring setup.

Source:

http://www.sql-server-performance.com/2012/managing-and-connecting-to-alwayson-availability-groups/

http://www.sql-server-performance.com/2012/managing-and-connecting-to-alwayson-availability-groups/2/

Understanding Perfmon Counters while troubleshooting SQL Server Performance Issues

To troubleshoot overall Database system performance issue, analyzing performance counters is the best way to start.    By collecting performance counters during busy period for few days consistently and analyzing those data would give a better idea about overall system problems regarding Memory, CPU, and/or Disk I/O.   Please note, for troubleshooting a particular SQL problem such as a stored procedure or a piece of T-SQL, it is better to look at the query execution plan and SQL Trace data and identify the need of redesigning a query or table indexes.    

 Following are some key performance counters to use while assessing a performance issues on SQL Server. 

Memory:

Memory and Disk I/O complements each other.   Memory issues on the system could affect disk I/O and vice versa.   It is very critical to carefully observe the trend of performance counters data over a long period of time to identify the real problem.     

·         Memory\Available Mbytes

Threshold: A consistent value of less than 20 to 25 percent of installed RAM is an indication of insufficient memory.

Significance: This indicates the amount of physical memory available to processes running on the computer. Note that this counter displays the last observed value only. It is not an average.

·         Memory\Page Reads/sec

Threshold: Sustained values of more than five indicate a large number of page faults for read requests.

Significance: This counter indicates that the working set of your process is too large for the physical memory and that it is paging to disk. It shows the number of read operations, without regard to the number of pages retrieved in each operation. Higher values indicate a memory bottleneck.

If a low rate of page-read operations coincides with high values for Physical Disk\% Disk Timeand Physical Disk\Avg. Disk Queue Length, there could be a disk bottleneck. If an increase in queue length is not accompanied by a decrease in the pages-read rate, a memory shortage exists.

·         Memory\Page Input/Sec:

Threshold: The value should not exceed 15.  Higher the value poor the performance.

Significance:  Pages Input/sec is the rate at which pages are read from disk to resolve hard page faults. Hard page faults occur when a process refers to a page in virtual memory that is not in its working set or elsewhere in physical memory, and must be retrieved from disk.

·         Memory\Pages/sec

Threshold: Sustained values higher than five indicate a bottleneck.

Significance: This counter indicates the rate at which pages are read from or written to disk to resolve hard page faults. To determine the impact of excessive paging on disk activity, multiply the values of the Physical Disk\ Avg. Disk sec/Transfer and Memory\ Pages/sec counters.  If the product of these counters exceeds 0.1, paging is taking more than 10 percent of disk access time, which indicates that you need more RAM.  If this occurs over a long period, you probably need more memory.

A high value of Pages/sec indicates that your application does not have sufficient memory. The average of Pages Input/sec divided by average of Page Reads/sec gives the number of pages per disk read. This value should not generally exceed five pages per second. A value greater than five pages per second indicates that the system is spending too much time paging and requires more memory (assuming that the application has been optimized). 

Processor

To measure processor utilization and context switching, you can use the following counters:  

·         Processor\% Processor Time

Threshold: The general figure for the threshold limit for processors is 65 percent.

Significance: This counter is the primary indicator of processor activity. High values many not necessarily be bad. However, if the other processor-related counters are increasing linearly such as % Privileged Time or Processor Queue Length, high CPU utilization may be worth investigating.

·         Processor\% Privileged Time

Threshold: A figure that is consistently over 75 percent indicates a bottleneck.

Significance: This counter indicates the percentage of time a thread runs in privileged mode. When your application calls operating system functions (for example to perform file or network I/O or to allocate memory), these operating system functions are executed in privileged mode.

·         System\Context Switches/sec

Threshold: As a general rule, context switching rates of less than 5,000 per second per processor are not worth worrying about. If context switching rates exceed 15,000 per second per processor, then there is a constraint.

Significance: Context switching happens when a higher priority thread preempts a lower priority thread that is currently running or when a high priority thread blocks. High levels of context switching can occur when many threads share the same priority level. This often indicates that there are too many threads competing for the processors on the system. If you do not see much processor utilization and you see very low levels of context switching, it could indicate that threads are blocked.  

Disk I/O

To measure disk I/O activity, you can use the following counters:  

·         PhysicalDisk\Avg. Disk Queue Length

Threshold: Should not be higher than the number of spindles plus two.

Significance: This counter indicates the average number of both read and writes requests that were queued for the selected disk during the sample interval.

·         PhysicalDisk\Avg. Disk Read Queue Length

Threshold: Should be less than two.

Significance: This counter indicates the average number of read requests that were queued for the selected disk during the sample interval.

·         PhysicalDisk\Avg. Disk Write Queue Length

Threshold: Should be less than two.

Significance: This counter indicates the average number of write requests that were queued for the selected disk during the sample interval.

·         PhysicalDisk\Avg. Disk sec/Read

Threshold: No specific value.

      Less than 10 ms – very good

      Between 10-20 ms – okay

      Between 20-50 ms – slow, needs attention

      Greater than 50 ms – Serious I/O bottleneck

Significance: This counter indicates the average time, in seconds, of a read of data from the disk.

·         PhysicalDisk\Avg. Disk sec/Transfer

Threshold: Should not be more than 18 milliseconds.

Significance: This counter indicates the time, in seconds, of the average disk transfer. This may indicate a large amount of disk fragmentation, slow disks, or disk failures. Multiply the values of the Physical Disk\Avg. Disk sec/Transfer and Memory\Pages/sec counters. If the product of these counters exceeds 0.1, paging is taking more than 10 percent of disk access time, so you need more RAM.

·         PhysicalDisk\Disk Writes/sec

Threshold: Depends on manufacturer's specification.

Significance: This counter indicates the rate of write operations on the disk.

·         Physical Disk: %Disk Time

Threshold:  Greater than 50 percent, it represents an I/O bottleneck

Significance: Represents the percentage of elapsed time that the selected disk drive was busy servicing read or write requests.

·         Physical Disk\Avg. Disk Reads/Sec  and Physical Disk\Avg. Disk Writes/Sec

Threshold: It should be less than 85% of the disk capacity

Significance: It represents the rate of read operations on the disk.

When using above counters, you may need to adjust the values for RAID configurations using the following formulas.

Raid 0 -- I/Os per disk = (reads + writes) / number of disks

Raid 1 -- I/Os per disk = [reads + (2 * writes)] / 2

Raid 5 -- I/Os per disk = [reads + (4 * writes)] / number of disks

Raid 10 -- I/Os per disk = [reads + (2 * writes)] / number of disks

For example, you have a RAID-1 system with two physical disks with the following values of the counters.

Disk Reads/sec            80

Disk Writes/sec           70

Avg. Disk Queue Length    5

In that case, you are encountering (80 + (2 * 70))/2 = 110 I/Os per disk and your disk queue length = 5/2 = 2.5 which indicates a border line I/O bottleneck.

Disk I/O issues can be minimized by having larger and sequential I/O activities.    To achieve than you should consider partitioning.    Partitioning can be done in many ways – Database partitioning, Tempdb partitioning, Table partitioning, index partitioning.    At a minimum, it is highly recommended to partition your database in such a way that clustered index, non-clustered index, and transaction logs are each on a separate physical drive and spread across multiple logical filegroups/files within that drive.   

Sources:
http://blogs.technet.com/b/vipulshah/archive/2006/11/30/understanding-perfmon-counters-while-troubleshooting-sql-server-performance-issues.aspx