Check if high-availability is configured on SQL Server

HACheck if high-availability is configured on SQL Server

I quickly needed to check on a list of instances if any kind of high-availability is configured so, wrote this quick script and wanted to share. See if proves to be useful for you.

SELECT @@servername as 'InstanceName',
A.name as 'DatabaseName',
CASE
WHEN serverproperty('isclustered') = 0 then 'AO not configured' else 'AO configured' end AS 'AO-State',
CASE
WHEN serverproperty('ISHADREnabled') = 0 then 'Clustering not configured' else 'Clustering configured' end AS 'Clustering-State',
CASE
WHEN B.mirroring_state is NULL THEN 'Mirroring not configured'
ELSE 'Mirroring configured'
END as 'Mirroring-State',
CASE
WHEN A.is_published = 0 THEN 'Replication not configured'
ELSE 'Replication configured'
END as 'Replication-State',
CASE
WHEN ls.primary_database is not null then 'LogShipping Configured'
ELSE 'LogShipping Not Configured' end 'LS-State'
FROM
sys.databases A
INNER JOIN sys.database_mirroring B
ON A.database_id=B.database_id
left join msdb.dbo.log_shipping_monitor_primary ls on ls.primary_database=A.name
WHERE A.database_id > 4
ORDER BY A.name

 

Advertisement

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 )

Facebook photo

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

Connecting to %s