DMV And system catalog view

Here we mainly introduce AlwaysON Dynamic management view of , Can be used to monitor and check your AG.

stay AlwaysOn Dashboard, You can simply configure GUI Showing many copies available DMV And available databases by right clicking on their respective headers and selecting the ones you want to add and hide DMV.

More about DMV Information view : AlwaysOn Availability Groups Dynamic Management Views and Functions (Transact-SQL). To view more AG Catalog view view :AlwaysOn Availability Groups Catalog Views (Transact-SQL).

1. Check WSFC Cluster node configuration

use master


select * from sys.dm_hadr_cluster_members


The result set reports the status of each member node .quorum Defined as node and file sharing most . You can see the status of each node , Including the weight of each node voting ( number_of_quorum_votes value ).

2. Cluster network

The following query gets WSFC Network configuration of cluster :

select * from sys.dm_hadr_cluster_networks

This result ,WSFC Each network configuration in the cluster returns the same . For example, in a two node cluster, each node contains 2 A network adapter , So return 4 That's ok .

3. Available groups

The following statement gets AG Information .

select primary_replica, primary_recovery_health_desc, synchronization_health_desc from sys.dm_hadr_availability_group_states


select * from sys.availability_groups


select * from sys.availability_groups_cluster


DMV sys.dm_hadr_availability_group_states (Transact-SQL)sys.availability_groups (Transact-SQL), and sys.availability_groups_cluster  return WSFC In the cluster about AG All the information about . actually sys.availability_groups (Transact-SQL), and sys.availability_groups_cluster Will return the same information .

actually , sys.availability_groups_cluster  Return to save in WSFC In the cluster AG Metadata . and sys.availability_groups (Transact-SQL)  return SQL Server Process space cache AG Metadata information .sys.dm_hadr_availability_group_states (Transact-SQL)  Back to the current AG Health status .

4. Available copies

The following query returns Ag Available copies in :
select replica_id, role_desc,
synchronization_health_desc from sys.dm_hadr_availability_replica_states


select replica_server_name, replica_id,
availability_mode_desc, endpoint_url from sys.availability_replicas


select replica_server_name, join_state_desc from


Be similar to AG Of DMV, Yes 3 About available copies DMV.sys.dm_hadr_availability_replica_states Returns the current state of available copies , sys.dm_hadr_availability_replica_cluster_states  from WFSC Returns the status of available copies in . sys.availability_replicas Returns configuration information for available copies .

5. Available copy health

Get the currently available replica health information through the following query

select replica_id, role_desc, recovery_health_desc, synchronization_health_desc from sys.dm_hadr_availability_replica_states


Compare primary Copies and secondary copy , Be careful secondary copy , Health information only reports AG Available copies within .

6. Available databases

The following information gets the database information in the available replica . You can observe the changes of available databases before and after the interruption of data movement :
select * from sys.availability_databases_cluster


select group_database_id, database_name,
is_failover_ready  from sys.dm_hadr_database_replica_cluster_states


select database_id, synchronization_state_desc,
synchronization_health_desc, last_hardened_lsn, redo_queue_size,
log_send_queue_size from sys.dm_hadr_database_replica_states


Yes 3 About the available databases DMV,sys.availability_databases_cluster Back in the WSFC Information about the available databases in .sys.dm_hadr_database_replica_cluster_states Returns the status information of the database copy in the database . It contains some important information , For example, are you ready for replica transfer . sys.dm_hadr_database_replica_states It's the most detailed , Returns status information about each available database , such as LSN Process .

7. Available database health

Here's the information to get the health information for each available database .

select dc.database_name, dr.database_id, dr.synchronization_state_desc,

dr.suspend_reason_desc, dr.synchronization_health_desc

from sys.dm_hadr_database_replica_states dr  join sys.availability_databases_cluster dc

on dr.group_database_id=dc.group_database_id

where is_local=1


[AlwaysOn Availability Groups]DMV More articles on system catalog view

  1. [AlwaysOn Availability Groups]AG Screening and monitoring guidelines

    AG Screening and monitoring guidelines 1. Check the scene The following table contains common troubleshooting scenarios . It is divided into several scene types according to the scene type , such as Configuration,client connectivity,failover and performance ...

  2. [AlwaysOn Availability Groups] screening :AG To configure

    screening AG To configure This article is mainly used to help check in AG Problems with configuration , Include ,AG Feature disabled , Incorrect account configuration , database mirroring endpoint non-existent ,endpoint Cannot access . Section Description A ...

  3. [SQL in Azure] Tutorial: AlwaysOn Availability Groups in Azure (GUI) Tutorial: AlwaysOn Availability Groups i ...

  4. [AlwaysOn Availability Groups] Health model Part 2 —— Expand

    Health model extension The first part has already introduced AlwayOn An overview of the health model . Now it's time to create your own PBM Strategy , Then set it to the established classification . Create these strategies , After creation, modify the configuration ,dashboard These strategies are automatically evaluated . scene , ...

  5. [AlwaysOn Availability Groups] Health model Part 1—— summary

    Health Model Overview In a successful deployment AG after , It's important to track and maintain your health . 1.AG Health Model Overview AG Our health model is based on policy management (Policy Based Management PBM) Of . If you're not familiar with this feature , can ...

  6. [AlwaysOn Availability Groups] monitor AG performance

    monitor AG performance AG The performance aspect of the performance , It's important to maintain performance at the statement level on mission critical databases . understand AG How to transfer logs to secondary A copy of the assessment RTO and RPO, indicate AG Whether the performance is not good . 1. Data synchronization steps For comment ...

  7. [AlwaysOn Availability Groups] screening :AG exceed RPO

    screening :AG exceed RPO In the case of asynchronous submission secondary The switch was performed on , You may find that the loss of data is greater than RPO, Or in the calculation, the tolerable data is more than RPO. 1. The usual reason is 1. Network latency is too high , Network throughput is too low , Lead to Pr ...

  8. [AlwaysOn Availability Groups] screening :Primary Changes on cannot be made in Secondary reflect

    screening :Primary Changes on cannot be made in Secondary reflect The client process is in primary Successfully modified on , But in Secondary You can't see the results of the changes on the screen . This case Suppose your availability group has synchronous health issues . In many cases, this ...

  9. [AlwaysOn Availability Groups]CLUSTER.LOG(AG)

    CLUSTER.LOG(AG) As a failover resource , stay SQL Server and windows Resources for the failover cluster service DLL(hadrres.dll) There's extra internal communication between them ,DLL Can't be SQL Server monitor ...

Random recommendation

  1. Eclipse swt Development environment construction

    raw material : eclipse swt. The download link is : Eclipse 4.6.2 Release Build: 4.6.2 Configuration instructions : Developing SWT applications using Ec ...

  2. C Detailed explanation of language bit operation

    Bit operations are binary operations . In system software , You often have to deal with binary bits .C Language provides 6 One bit operator . These operators can only be used to shape operands , That is, it can only be used with or without symbols And long type ...

  3. struts ( 7、 ... and ) Domain model

    1. Domain model gets parameters domain Model 2. vo  value object   The value object do  data object Data objects dto data transfer object   Data transmission is very important for ...

  4. [ primary ]Unity3D Explain profound theories in simple language - Understand the development environment of GameObject menu bar

    Create Empty: Create an empty object Create Other: Create other objects Particle System: Create a particle system Camera: Create a camera GUI Text:GUI Text GUI Texture ...

  5. Load balancing Lvs nat Pattern notes

    nat technology ( Network address translation ) The disadvantage is that both the entry and return go through the scheduler's server , The future may become a bottleneck experiment :nat Pattern Install... In the scheduler first ipvsadm Then add the network card , There's no Internet in the back (yum -y install ...

  6. Ignore SIGPIPE The signal

    #include <stdlib.h> #include <sys/signal.h> void SetupSignal() { struct sigaction sa; // ...

  7. exception ‘PHPExcel_Calculation_Exception‘ with message ‘ Fan data !C2679 -&gt; Formula Error: Operator ‘=‘ has no operands

    The cause of the problem may be that the export field contains   ‘=’  , terms of settlement : Put a half single quotation mark in front of the field . if(strpos($lists[$i-2][‘nickname‘],‘=‘) === 0){ $lists[$ ...

  8. Palindrome Bo ( Preprocessing + Section DP)

    The first line is discretization , Then preprocess the next element in all positions , It's very important to optimize the time . The rest is the general DP 了 . Section DP #include<bits/stdc++.h> using names ...

  9. linux Graphics and command interface switching

    One . When the system is not in the virtual machine Use ctrl+alt+F1~6 Switch to the command line interface :ctrl+alt+F7 Switch to the GUI Two . The situation of the system in the virtual machine Ctrl+Alt+shift+F1~6 Switch to the command line interface : Use ...

  10. Parents and children this Related issues

    1.SinglyLinkedList: package No3_PolySinglyList; /* Realization A single chain table with leading nodes SinglyLinkedList class */ public class Singly ...