T-SQL script to capture topology of an AlwaysOn configuration in SQL Server

This T-SQL sample script quickly captures topology of an AlwaysOn configuration in SQL Server.

Knowing a topology is a frequent ask in TechNet forums and in CSS for troubleshooting.  IT professionals want to understand the topology so as to quickly understand help in troubleshooting. There are multiple dynamic views (DMVs) to get this data so getting this data manually is possible but can take longer. This script has been tested in SQL Server 2012.

This script queries Dynamic Management Views DMVs and draws out the AlwaysOn topology. It helps us understand the topology and displays various other details that we need to understand the issue and thus helps us to troubleshoot the issue faster.

This script should be run using a query tool like SQL Server Management Studio. This can be run in the context of any database.

Here are some code snippets for your references. To get the complete script sample, please click the download button at the beginning of this page.

SET NOCOUNT ON;

SET ANSI_WARNINGS OFF;     -- to ignore null value message of sys.dm_hadr_availability_group_states (when it has data)
DECLARE @ConsoleMsg VARCHAR(1000);
USE master;  -- to ensure script runs in context of database that is online.
SET @ConsoleMsg = 'Starting AlwaysOn script. DateTime is ' + CONVERT(VARCHAR(24), GETDATE(), 121) + ' (' + CONVERT(VARCHAR(24), GETUTCDATE(), 121) + ' UTC).';
RAISERROR (@ConsoleMsg,10,1) WITH NOWAIT; 
SET @ConsoleMsg = 'Script running on SQL Server with MajorVersion ';
DECLARE @MajorVersion int;
SET @MajorVersion = CONVERT(int, SUBSTRING(
CONVERT(VARCHAR(128), SERVERPROPERTY('ProductVersion')) , 0  , CHARINDEX('.', CONVERT(VARCHAR(128), SERVERPROPERTY('ProductVersion'))) ));

SET @ConsoleMsg = @ConsoleMsg + CONVERT(VARCHAR(2), @MajorVersion) + '. ';
IF CONVERT(INT, @MajorVersion) < 11
BEGIN
       SET @ConsoleMsg= @ConsoleMsg + 'SQL Server connected to is not SQL Server 2012. Exiting.';
       RAISERROR (@ConsoleMsg,10,1) WITH NOWAIT;
       SET NOEXEC ON;      -- do not execute TSQL further below (avoiding complex-looking IF...ELSE code)
END;
RAISERROR (@ConsoleMsg,10,1) WITH NOWAIT;

SET NOCOUNT ON; 
 
SET ANSI_WARNINGS OFF;     -- to ignore null value message of sys.dm_hadr_availability_group_states (when it has data) 
DECLARE @ConsoleMsg VARCHAR(1000); 
USE master;  -- to ensure script runs in context of database that is online. 
SET @ConsoleMsg = 'Starting AlwaysOn script. DateTime is ' + CONVERT(VARCHAR(24), GETDATE(), 121) + ' (' + CONVERT(VARCHAR(24), GETUTCDATE(), 121) + ' UTC).'; 
RAISERROR (@ConsoleMsg,10,1) WITH NOWAIT;  
SET @ConsoleMsg = 'Script running on SQL Server with MajorVersion '; 
DECLARE @MajorVersion int; 
SET @MajorVersion = CONVERT(int, SUBSTRING( 
CONVERT(VARCHAR(128), SERVERPROPERTY('ProductVersion')) , 0  , CHARINDEX('.', CONVERT(VARCHAR(128), SERVERPROPERTY('ProductVersion'))) )); 
 
SET @ConsoleMsg = @ConsoleMsg + CONVERT(VARCHAR(2), @MajorVersion) + '. '; 
IF CONVERT(INT, @MajorVersion) < 11 
BEGIN 
       SET @ConsoleMsg= @ConsoleMsg + 'SQL Server connected to is not SQL Server 2012. Exiting.'; 
       RAISERROR (@ConsoleMsg,10,1) WITH NOWAIT; 
       SET NOEXEC ON;      -- do not execute TSQL further below (avoiding complex-looking IF...ELSE code) 
END; 
RAISERROR (@ConsoleMsg,10,1) WITH NOWAIT;

Step 2. The above script will show sample output like below.

Starting AlwaysOn script. DateTime is 2013-07-05 01:40:51.620 (2013-07-05 08:40:51.620 UTC).
Script running on SQL Server with MajorVersion 11.
Script running on SQL instance SQLONE (11.0.3000.0 X64 SQLNotClustered).
SQL instance running on computer SQLONE.
|- AvailabilityGroupName: SQLPRODUCTION_AG | FailureConditionLevel: 3 | HealthCheckTimeoutMilliSecs: 30000 | AutomatedBackupPreference: secondary | AvailabilityGroupListener: SQLPRODUCTION
|- DatabaseName: ContosoSalesDB | DatabaseIdentifierInAvailabilityGroup: F8E7FB0C-C61E-4680-B923-A2418D59A1CE
|- DatabaseName: ContosoCRM | DatabaseIdentifierInAvailabilityGroup: 2B2F3739-CA06-48AF-B8CE-28AF1DA359AC
|- ReplicaName: SQLONE | AvailabilityMode: SYNCHRONOUS_COMMIT | FailoverMode: AUTOMATIC | PrimaryRole: ALL | SecondaryRole: ALL | JoinState: JOINED_STANDALONE | ClusterNodeName: SQLONE
|- ReplicaName: SQLTHREE | AvailabilityMode: ASYNCHRONOUS_COMMIT | FailoverMode: MANUAL | PrimaryRole: ALL | SecondaryRole: ALL | JoinState: JOINED_STANDALONE | ClusterNodeName: SQLTHREE
|- ReplicaName: SQLTWO | AvailabilityMode: SYNCHRONOUS_COMMIT | FailoverMode: AUTOMATIC | PrimaryRole: ALL | SecondaryRole: ALL | JoinState: JOINED_STANDALONE | ClusterNodeName: SQLTWO
|- AvailabilityGroupName: TestAG | FailureConditionLevel: 3 | HealthCheckTimeoutMilliSecs: 30000 | AutomatedBackupPreference: secondary | AvailabilityGroupListener: NoListener
|- DatabaseName: testdb | DatabaseIdentifierInAvailabilityGroup: 0308748E-AAE3-48B9-AA37-4F9D21EB0275
|- ReplicaName: SQLONE | AvailabilityMode: SYNCHRONOUS_COMMIT | FailoverMode: MANUAL | PrimaryRole: ALL | SecondaryRole: NO | JoinState: JOINED_STANDALONE | ClusterNodeName: SQLONE
|- ReplicaName: SQLTWO | AvailabilityMode: ASYNCHRONOUS_COMMIT | FailoverMode: MANUAL | PrimaryRole: ALL | SecondaryRole: NO | JoinState: JOINED_STANDALONE | ClusterNodeName: SQLTWO
Script complete.