This week, today, the post will cover an interesting script. I believe there is already a method that gives us Always On Health status details using UI/Report. Sometimes back, when I was talking to a friend of mine he was asking me if I have a TSQL Script to check Always On Health Status. I recall that I have a script handy which I wrote sometimes back in 2016 which I have already shared on the TechNet Gallery, but let me share it here as well.
DECLARE @HADRName varchar(25)
SET @HADRName = @@SERVERNAME
select n.group_name,n.replica_server_name,n.node_name,rs.role_desc,
db_name(drs.database_id) as 'DBName',drs.synchronization_state_desc,drs.synchronization_health_desc
from sys.dm_hadr_availability_replica_cluster_nodes n
join sys.dm_hadr_availability_replica_cluster_states cs
on n.replica_server_name = cs.replica_server_name
join sys.dm_hadr_availability_replica_states rs
on rs.replica_id = cs.replica_id
join sys.dm_hadr_database_replica_states drs
on rs.replica_id=drs.replica_id
where n.replica_server_name <> @HADRName
on rs.replica_id=drs.replica_id
The output will look like below:
I hope you all will like the above script. And yes, I have one more thing for you guys to help you. Just wanted to let you all know, in case you have already decided that you will be traveling to Seattle to attend SQLPASS Summit 2017 and haven’t completed your registration already, please use the below discount code while registering. It will avail you 150 US$ discount
Use Chapter Name: Surat User Group
Unique Discount Code: LC15KCF8
*The discount code cannot be combined with any other PASS Summit 2016 registration, and cannot be applied retroactively
Lorem ipsum dolor sit amet, consectetur adipiscing elit. Ut elit tellus, luctus nec ullamcorper mattis, pulvinar dapibus leo.
