Home > AlwaysOn 2012 > SQL Server AlwaysOn Troubleshooting Part -1

SQL Server AlwaysOn Troubleshooting Part -1

 

Get Availability group information

 
T-SQL :

select name,group_id,health_check_timeout from master.sys.availability_groups

image

 

Get Availability group Replica information

T-SQL :

select replica_server_name,create_date,
       modify_date,endpoint_url,read_only_routing_url,avalibility_mode,
       failover_mode,session_timeout,backup_priority
from master.sys.avalibility_replicas

image

 

Get Availability group databases

T-SQL :

select database_name,is_failover_ready,is_pending_secondary_suspend,is_database_joined
from master.sys.dm_hadr_database_replica_cluster_states

select * from sys.dm_hadr_database_replica_states

 

image

 

Get Availability group listeners

T-SQL:

select * from sys.availability_group_listeners

image

 

Join all DMV’s for AlwaysOn configuration

T-SQL :

select ag.name,replicas.replica_server_name,replicas.availability_mode_desc,
         replicas.failover_mode_desc,replicas.session_timeout,
         agd.database_name,agd.is_database_joined,drs.is_local,
         drs.is_primary_replica,drs.synchronization_state_desc,
         drs.synchronization_health_desc,drs.database_state_desc,agl.dns_name,
         agl.port,agl.ip_configuration_string_from_cluster
            from       master.sys.availability_replicas replicas 
            inner join master.sys.availability_groups ag on replicas.group_id=ag.group_id
            inner join master.sys.dm_hadr_database_replica_cluster_states agd on agd.replica_id=replicas.replica_id 
            inner join master.sys.dm_hadr_database_replica_states drs on drs.replica_id=replicas.replica_id
            inner join master.sys.availability_group_listeners agl on agl.group_id=ag.group_id

 

image

Categories: AlwaysOn 2012
  1. No comments yet.
  1. No trackbacks yet.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: