Useful always on queries

SELECT ag.name AS 'AG Name', ar.replica_server_name AS 'Replica Instance',
dr_state.database_id AS 'Database ID',
Location = CASE
WHEN ar_state.is_local = 1 THEN N'LOCAL'
ELSE 'REMOTE' END,
Role = CASE
WHEN ar_state.role_desc IS NULL THEN N'DISCONNECTED'
ELSE ar_state.role_desc END,
ar_state.connected_state_desc AS 'Connection State', ar.availability_mode_desc AS 'Mode',
dr_state.synchronization_state_desc AS 'State'
FROM ((sys.availability_groups AS ag JOIN sys.availability_replicas AS ar ON ag.group_id =
ar.group_id )
JOIN sys.dm_hadr_availability_replica_states AS ar_state ON ar.replica_id = ar_state.
replica_id)
JOIN sys.dm_hadr_database_replica_states dr_state ON
ag.group_id = dr_state.group_id and dr_state.replica_id = ar_state.replica_id;



SELECT ag.name AS 'AG Name', ar.replica_server_name AS 'Replica Instance',
dr_state.database_id AS 'Database ID',
Location = CASE
WHEN ar_state.is_local = 1 THEN N'LOCAL'
ELSE 'REMOTE' END,
Role = CASE
WHEN ar_state.role_desc IS NULL THEN N'DISCONNECTED'
ELSE ar_state.role_desc END,
ar_state.connected_state_desc AS 'Connection State', ar.availability_mode_desc AS 'Mode',
dr_state.synchronization_state_desc AS 'State'



WITH PR(database_id, last_commit_time) AS
(
SELECT dr_state.database_id as database_id,
dr_state.last_commit_time FROM
((sys.availability_groups AS ag JOIN sys.availability_replicas AS ar ON ag.group_id=ar.
group_id)
JOIN sys.dm_hadr_availability_replica_states AS ar_state ON ar.replica_id = ar_state.
replica_id)
JOIN sys.dm_hadr_database_replica_states dr_state on ag.group_id=dr_state.group_id and dr_
state.replica_id=ar_state.replica_id
WHERE ar_state.role=1
)
SELECT ar.replica_server_name AS 'Replica Instance', dr_state.database_id as 'Database ID',
DATEDIFF(s,dr_state.last_commit_time,PR.last_commit_time) AS 'Seconds Behind Primary'
FROM ((sys.availability_groups AS ag JOIN sys.availability_replicas AS ar ON ag.group_id =
ar.group_id)
JOIN sys.dm_hadr_availability_replica_states AS ar_state ON ar.replica_id = ar_state.
replica_id)
JOIN sys.dm_hadr_database_replica_states dr_state
ON ag.group_id = dr_state.group_id and dr_state.replica_id = ar_state.replica_id
JOIN PR ON PR.database_id=dr_state.database_id
WHERE ar_state.role!=1 and dr_state.synchronization_state=1



FROM ((sys.availability_groups AS ag JOIN sys.availability_replicas AS ar ON ag.group_id =
ar.group_id )
JOIN sys.dm_hadr_availability_replica_states AS ar_state ON ar.replica_id = ar_state.
replica_id)
JOIN sys.dm_hadr_database_replica_states dr_state ON
ag.group_id = dr_state.group_id and dr_state.replica_id = ar_state.replica_id;






sp_server_diagnostics

ALTER AVAILABILITY GROUP AG1 SET(FAILURE_CONDITON_LEVEL=1)

ALTER AVAILABILITY GROUP AG1 SET(HEALTH_CHECK_TIMEOUT=6000)


Data Source=tcp:ENTER_DB_IP/HOSTNAME (10.0.0.20 or 10.0.0.25 - SQL Listener or Internal Load Balancer)
Database=ENTER_DB_NAME; (CeledonTestDatabase)
User ID=ENTER_DB_USER; (SA)
Password=ENTER_DB_PASSWORD (My SA password)



sp_server_diagnostics

ALTER AVAILABILITY GROUP AG1 SET(FAILURE_CONDITON_LEVEL=1)

ALTER AVAILABILITY GROUP AG1 SET(HEALTH_CHECK_TIMEOUT=6000)


Data Source=tcp:ENTER_DB_IP/HOSTNAME (10.0.0.20 or 10.0.0.25 - SQL Listener or Internal Load Balancer)
Database=ENTER_DB_NAME; (CeledonTestDatabase)
User ID=ENTER_DB_USER; (SA)
Password=ENTER_DB_PASSWORD (My SA password)