Friday, 19 January 2018

Pause all AGs

Doing some work at DR, want to make sure you dont mess with your AGS?

select DISTINCT name, 'ALTER DATABASE ['+name+'] SET HADR suspend;', 'ALTER DATABASE ['+name+'] SET HADR Resume;'
from sys.dm_hadr_database_replica_states rs
join sys.databases sd on rs.database_id = sd.database_id
join sys.dm_hadr_availability_replica_cluster_states rcs on rs.replica_id = rcs.replica_id
order by 2,1

Then run the output.