SQL SERVER – Scripts to Overview HADR / AlwaysOn Local Replica Server

Today, I have received a very interesting script from SQL Server Expert Dominic Wirth. He has written a very helpful script which displays a utilization overview of the local availability group replica server. The overview will contain the number of databases as well as the total size of databases (DATA, LOG, FILESTREAM) and is group by following two categories

1. Replica role (PRIMARY / SECONDARY)
2. Availability Group

Let us first see the script:

/*==================================================================
Script: HADR Local Replica Overview.sql
Description: This script will display a utilisation overview
of the local Availability Group Replica Server.
The overview will contain amount of databases as
well as total size of databases (DATA, LOG, FILESTREAM)
and is group by ...
1) ... Replica role (PRIMARY / SECONDARY)
2) ... Availability Group
Date created: 05.09.2018 (Dominic Wirth)
Last change: -
Script Version: 1.0
SQL Version: SQL Server 2014 or higher
====================================================================*/
-- Load size of databases which are part of an Availability Group
DECLARE @dbSizes TABLE (DatabaseId INT, DbTotalSizeMB INT, DbTotalSizeGB DECIMAL(10,2));
DECLARE @dbId INT, @stmt NVARCHAR(MAX);
SELECT @dbId = MIN(database_id) FROM sys.databases WHERE group_database_id IS NOT NULL;
WHILE @dbId IS NOT NULL
BEGIN
SELECT @stmt = 'USE [' + DB_NAME(@dbId) + ']; SELECT ' + CAST(@dbId AS NVARCHAR) + ', (SUM([size]) / 128.0), (SUM([size]) / 128.0 / 1024.0) FROM sys.database_files;';
INSERT INTO @dbSizes (DatabaseId, DbTotalSizeMB, DbTotalSizeGB) EXEC (@stmt);
SELECT @dbId = MIN(database_id) FROM sys.databases WHERE group_database_id IS NOT NULL AND database_id > @dbId;
END;
-- Show utilisation overview grouped by replica role
SELECT AR.replica_server_name, DRS.is_primary_replica AS IsPrimaryReplica, COUNT(DB.database_id) AS [Databases]
,SUM(DBS.DbTotalSizeMB) AS SizeOfAllDatabasesMB, SUM(DBS.DbTotalSizeGB) AS SizeOfAllDatabasesGB
FROM sys.dm_hadr_database_replica_states AS DRS
INNER JOIN sys.availability_replicas AS AR ON DRS.replica_id = AR.replica_id
LEFT JOIN sys.databases AS DB ON DRS.group_database_id = DB.group_database_id
LEFT JOIN @dbSizes AS DBS ON DB.database_id = DBS.DatabaseId
WHERE DRS.is_local = 1
GROUP BY DRS.is_primary_replica, AR.replica_server_name
ORDER BY AR.replica_server_name ASC, DRS.is_primary_replica DESC;
-- Show utilisation overview grouped by Availability Group
SELECT AR.replica_server_name, DRS.is_primary_replica AS IsPrimaryReplica, AG.[name] AS AvailabilityGroup, COUNT(DB.database_id) AS [Databases]
,SUM(DBS.DbTotalSizeMB) AS SizeOfAllDatabasesMB, SUM(DBS.DbTotalSizeGB) AS SizeOfAllDatabasesGB
FROM sys.dm_hadr_database_replica_states AS DRS
INNER JOIN sys.availability_groups AS AG ON DRS.group_id = AG.group_id
INNER JOIN sys.availability_replicas AS AR ON DRS.replica_id = AR.replica_id
LEFT JOIN sys.databases AS DB ON DRS.group_database_id = DB.group_database_id
LEFT JOIN @dbSizes AS DBS ON DB.database_id = DBS.DatabaseId
WHERE DRS.is_local = 1
GROUP BY AG.[name], DRS.is_primary_replica, AR.replica_server_name
ORDER BY AG.[name] ASC, AR.replica_server_name ASC;
GO

Here is the screenshot of the resultset which you will get if you run above script.

There are many different kinds of reports which you can run via SQL Server Management Studio. However, sometimes the scripts simple as this script are very helpful and returns us quick results. You can further modify the above script to get additional details for your server as well.

Here are few additional scripts which also discusses the various concepts related to AlwaysOn.

• SQL SERVER – Adding New Database to AlwaysOn Replica is Slow
• SQL SERVER – AlwaysOn Automatic Seeding – Database Stuck in Restoring State
• SQL SERVER – AlwaysOn Availability Groups: Script to Sync Logins Between Replicas?
• SQL SERVER – AlwaysOn Availability Group Listener – This TCP Port is Already in Use

If you have any other interesting script, please let me know and I will be happy to publish on the blog with due credit to you.

Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.