SELECT replica_server_name
, d.role_desc
, d.connected_state_desc
–, endpoint_url
–, availability_mode_desc
–, failover_mode_desc
–, session_timeout
–, backup_priority
–, secondary_role_allow_connections_desc AS Secondary_Readable
, Pri_Check.pri_Status
–, Pri_Check.Sec_Status
–, Pri_Check.Sync_Status
, d.operational_state_desc
, d.recovery_health_desc
, d.synchronization_health_desc AS Sync_Status
, d.last_connect_error_description AS ErrorMsg
, DATEADD(hh, DATEDIFF(hh, GETUTCDATE(), GETDATE()), d.last_connect_error_timestamp) AS ErrorDateTime
FROM sys.availability_replicas r
OUTER APPLY (
SELECT 1 AS IsPrimary
, c.ip_address AS listnerIP
, c.state_desc AS listnerStatus
, a.primary_recovery_health_desc AS pri_Status
, a.secondary_recovery_health_desc AS Sec_Status
, a.synchronization_health_desc AS Sync_Status
FROM sys.dm_hadr_availability_group_states a
JOIN sys.availability_group_listeners B
ON A.group_id = B.group_id
JOIN sys.availability_group_listener_ip_addresses c
ON b.listener_id = c.listener_id
WHERE primary_replica = r.replica_server_name
) Pri_Check
JOIN sys.dm_hadr_availability_replica_states d
ON r.replica_id = d.replica_id
/*
WHERE d.role NOT IN (1,2) — 1:Primary, 2:Secondary, 0:Resolving
OR d.operational_state <> 2 –0 = Pending failover, 1 = Pending, 2 = Online, 3 = Offline, 4 = Failed, 5 = Failed, no quorum
OR d.recovery_health <> 1 –0:In progress. At least one joined database has a database state other than ONLINE ( database_state is not 0).1- Online. All the joined databases have a database state of ONLINE ( database_state is 0).
OR d.synchronization_health <>2 — 0 = Not healthy. At least one joined database is in the NOT SYNCHRONIZING state.
— 1 = Partially healthy. Some replicas are not in the target synchronization state: synchronous-commit replicas should be synchronized, and asynchronous-commit replicas should be synchronizing.
— 2= Healthy. All replicas are in the target synchronization state: synchronous-commit replicas are synchronized, and asynchronous-commit replicas are synchronizing.
OR d.connected_state <> 1 –0 Disconnected. The response of an availability replica to the DISCONNECTED state depends on its role, as follows:
— On the primary replica, if a secondary replica is disconnected, its secondary databases are marked as NOT SYNCHRONIZED on the primary replica, which waits for the secondary to reconnect.
— On a secondary replica, upon detecting that it is disconnected, the secondary replica attempts to reconnect to the primary replica.
— 1 Connected
OR d.last_connect_error_number IS NOT NULL
*/
Archive for June, 2015
AlwaysOn AG group status check
Posted by Simon Cho on 06/15/2015
Posted in Common | Leave a Comment »
SQL 2014 service pack for SP1 or CU7?
Posted by Simon Cho on 06/15/2015
https://support.microsoft.com/en-us/kb/2936603
SQL service pack SP1 is announced.
https://support.microsoft.com/en-us/kb/2936603
And CU7 and SP1 both of the latest build for now.
The problem is, SP1 has higher version. But, it looks not containing CU6 and CU7.
CU6 and CU7 include very critical KB as well.
You may not want to apply SP1. Please check CU6 and CU7 first.
Posted in Common | Leave a Comment »