Tuesday, January 3, 2012

New AlwaysOn DMV on SQL 2012

This is the first post this year so I decided to publish something related to the upcoming SQL version (SQL Server 2012)

AlwaysOn is the new High Availability - Disaster Recovery Technique (HA/DR) which is a huge improvement compared with the existing Database Mirroring. (up to 4 read/only replicas. More info http://www.microsoft.com/sqlserver/en/us/future-editions/mission-critical/SQL-Server-2012-high-availability.aspx)

SQL Server 2012 has a new set of Dynamic Management Views and Functions (DMV/DMF).  Most of them are related to AlwaysOn. Note that all of them start with sys.dm_hadr_%

They provide all the state of the Availability groups, replicas, cluster, etc. Useful when you have to troubleshoot

select * from sys.dm_hadr_auto_page_repair

select * from sys.dm_hadr_availability_group_states

select * from sys.dm_hadr_availability_replica_states

select * from sys.dm_hadr_cluster

select * from sys.dm_hadr_cluster_members

select * from sys.dm_hadr_cluster_networks

select * from sys.dm_hadr_database_replica_cluster_states

select * from sys.dm_hadr_database_replica_states

select * from sys.dm_hadr_instance_node_map

select * from sys.dm_hadr_name_id_map


  1. Resources like the one you mentioned here will be very useful to me! I will post a link to this page on my blog. I am sure my visitors will find that very useful.
    I can see that you are are genuinely passionate about this! I am trying to build my own website and youve helped me with some great information.

  2. Dear Web site owner. My partner and i actually enjoy this post and the internet site all in all! Your piece of writing is really plainly composed as well as simply understandable. Your current Blog design is awesome as well! Would be awesome to know where I are able obtain it. Please maintain up the very good job. We all require far more such website owners like you on the net and much fewer spammers. Fantastic mate!