Vijayrod.files.wordpress.com



/*-- should be run in a New Query window in SQL Server Management Studio* ********************************************************************************** PURPOSE : Display AlwaysOn Topology* VERSION : 1.0.0.0* RUNS ON : The SQL Server; against any Database; in any (primary, replica) AlwaysOn instance.* Author: XXX.* ********************************************************************************** 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.* * Currently we support only SQL Server 2012 instances.* **********************************************************************************/ 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) < 11BEGINSET @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 @ConsoleMsg = 'Script running on SQL instance '+ CONVERT(nvarchar(256), SERVERPROPERTY('ServerName')) + ' (' + CONVERT(nvarchar(128), SERVERPROPERTY('ProductVersion')) + ' ' + SUBSTRING(@@VERSION, CHARINDEX('X', @@VERSION), 3) + ' ';-- IIF not in earlier SQL versions so using IF. IF(SERVERPROPERTY('IsClustered') = 1) SET @ConsoleMsg = @ConsoleMsg + 'SQLIsClustered' ELSE SET @ConsoleMsg = @ConsoleMsg + 'SQLNotClustered';SET @ConsoleMsg = @ConsoleMsg + ').';RAISERROR (@ConsoleMsg,10,1) WITH NOWAIT;SET @ConsoleMsg = 'SQL instance running on computer '+ CONVERT(nvarchar(128), SERVERPROPERTY('ComputerNamePhysicalNetBIOS')) + '.';RAISERROR (@ConsoleMsg,10,1) WITH NOWAIT;IF NOT EXISTS (SELECT 1 FROM sys.availability_groups)BEGINSET @ConsoleMsg = 'No configured Availability Groups in this SQL instance.';RAISERROR (@ConsoleMsg,10,1) WITH NOWAIT;ENDDECLARE @AGName sysname;DECLARE CurAGs CURSORFOR SELECT name FROM sys.availability_groups;OPEN CurAGs;FETCH NEXT FROM CurAGs INTO @AGName;WHILE @@FETCH_STATUS = 0BEGINDECLARE @AGListener nvarchar(63);SET @AGListener = '';-- for next loop if anySELECT TOP 1 @AGListener = dns_name FROM sys.availability_group_listeners agl JOIN sys.availability_groups ag ON agl.group_id = ag.group_id WHERE ag.name = @AGName;SELECT @AGListener = (CASE WHEN @AGListener IS NULL OR @AGListener = '' THEN 'NoListener' ELSE @AGListener END);SET @ConsoleMsg = '|- AvailabilityGroupName: ';SELECT @ConsoleMsg = @ConsoleMsg + ag.name + ' | FailureConditionLevel: ' + CONVERT(char(1), ag.failure_condition_level)+ ' | HealthCheckTimeoutMilliSecs: ' + CONVERT(varchar(10), ag.health_check_timeout) + ' | AutomatedBackupPreference: ' + ag.automated_backup_preference_desc + ' | AvailabilityGroupListener: ' + @AGListenerFROM sys.availability_groups ag WHERE ag.name = @AGName;RAISERROR (@ConsoleMsg,10,1) WITH NOWAIT;SELECT @ConsoleMsg = ' '+ ' |- CurrentPrimaryReplica: ' + (CASE WHEN ags.primary_replica IS NULL THEN 'IsNull_NotPrimaryReplicaOrNoCommunicationWithWindowsCluster' ELSE ags.primary_replica END) + ' | PrimaryRecoveryHealth: ' + (CASE WHEN ags.primary_recovery_health_desc IS NULL THEN 'IsNull' ELSE ags.primary_recovery_health_desc END)+ ' | SecondaryRecoveryHealth: ' + (CASE WHEN ags.secondary_recovery_health_desc IS NULL THEN 'IsNull' ELSE ags.secondary_recovery_health_desc END)+ ' | OverallSynchronizationHealth: ' + (CASE WHEN ags.synchronization_health_desc IS NULL THEN 'IsNull' ELSE ags.synchronization_health_desc END)FROM sys.availability_groups ag JOIN sys.dm_hadr_availability_group_states ags ON ag.group_id=ags.group_id WHERE ag.name = @AGName;RAISERROR (@ConsoleMsg,10,1) WITH NOWAIT;DECLARE @DatabaseId int;DECLARE CurDatabases CURSORFOR SELECT drs.database_id FROM sys.availability_groups ag JOIN sys.dm_hadr_database_replica_states drs ON ag.group_id = drs.group_id WHERE ag.name = @AGName GROUP BY ag.name, drs.database_id;OPEN CurDatabases;FETCH NEXT FROM CurDatabases INTO @DatabaseId;WHILE @@FETCH_STATUS = 0BEGINSELECT @ConsoleMsg = ' |- DatabaseName: ' + DB_NAME(drs.database_id) + ' | DatabaseIdentifierInAvailabilityGroup: ' + CONVERT(varchar(36), drs.group_database_id) FROM sys.availability_groups ag JOIN sys.dm_hadr_database_replica_states drs ON ag.group_id = drs.group_id WHERE ag.name = @AGName AND drs.database_id = @DatabaseId GROUP BY ag.name, drs.database_id, drs.group_database_id;RAISERROR (@ConsoleMsg,10,1) WITH NOWAIT;FETCH NEXT FROM CurDatabases INTO @DatabaseId;END;CLOSE CurDatabases;DEALLOCATE CurDatabases;DECLARE @ReplicaName nvarchar(256);DECLARE CurReplicas CURSORFOR SELECT ar.replica_server_name FROM sys.availability_groups ag JOIN sys.availability_replicas ar ON ag.group_id = ar.group_id WHERE ag.name = @AGName;OPEN CurReplicas;FETCH NEXT FROM CurReplicas INTO @ReplicaName;WHILE @@FETCH_STATUS = 0BEGINSELECT @ConsoleMsg = ' |- ReplicaName: ' + ar.replica_server_name + ' | AvailabilityMode: ' + ar.availability_mode_desc + ' | FailoverMode: ' + ar.failover_mode_desc + ' | PrimaryRole: ' + ar.primary_role_allow_connections_desc + ' | SecondaryRole: ' + ar.secondary_role_allow_connections_desc+ ' | JoinState: ' + rcs.join_state_desc+ ' | ClusterNodeName: ' + rcn.node_nameFROM sys.availability_groups ag JOIN sys.availability_replicas ar ON ag.group_id = ar.group_idJOIN sys.dm_hadr_availability_replica_cluster_states rcs ON (rcs.group_id = ag.group_id AND rcs.replica_id = ar.replica_id)JOIN sys.dm_hadr_availability_replica_cluster_nodes rcn ON (rcn.group_name=ag.name AND rcn.replica_server_name=ar.replica_server_name)WHERE ag.name = @AGName AND ar.replica_server_name = @ReplicaName;RAISERROR (@ConsoleMsg,10,1) WITH NOWAIT;FETCH NEXT FROM CurReplicas INTO @ReplicaName;END;CLOSE CurReplicas;DEALLOCATE CurReplicas;FETCH NEXT FROM CurAGs INTO @AGName;END;CLOSE CurAGs;DEALLOCATE CurAGs;IF NOT EXISTS (SELECT 1 FROM sys.dm_hadr_cluster)BEGINSET @ConsoleMsg = 'SQL instance on standalone Windows server (not on cluster nodes) OR the Windows Cluster node has no quorum.';RAISERROR (@ConsoleMsg,10,1) WITH NOWAIT;ENDELSEBEGINSELECT @ConsoleMsg = '|- WindowsCluster: ' + cluster_name + ' | QuorumType: ' + quorum_type_desc + ' | QuorumState: ' + quorum_state_desc FROM sys.dm_hadr_cluster;RAISERROR (@ConsoleMsg,10,1) WITH NOWAIT;DECLARE @MemberName nvarchar(128);DECLARE CurMemberNames CURSORFOR SELECT member_name FROM sys.dm_hadr_cluster_members;OPEN CurMemberNames;FETCH NEXT FROM CurMemberNames INTO @MemberName;WHILE @@FETCH_STATUS = 0BEGINSELECT @ConsoleMsg = ' |- MemberName: ' + cm.member_name + ' | MemberType: ' + cm.member_type_desc + ' | MemberState: ' + cm.member_state_desc+ ' | QuorumVotes: ' + CONVERT(char(1), cm.number_of_quorum_votes)FROM sys.dm_hadr_cluster_members cmWHERE cm.member_name = @MemberName;RAISERROR (@ConsoleMsg,10,1) WITH NOWAIT;DECLARE @NetworkIP nvarchar(48);DECLARE CurNetworkIPs CURSORFOR SELECT network_subnet_ip FROM sys.dm_hadr_cluster_networks WHERE member_name = @MemberName;OPEN CurNetworkIPs;FETCH NEXT FROM CurNetworkIPs INTO @NetworkIP;WHILE @@FETCH_STATUS = 0BEGINSELECT @ConsoleMsg = ' |- NetworkIP: ' + network_subnet_ip + ' | NetworkSubnetMaskIfIPv4: ' + (CASE WHEN network_subnet_ipv4_mask IS NULL THEN 'IsNull_SinceIPv6Subnet' ELSE network_subnet_ipv4_mask END) + ' | NetworkType: ' + (CASE WHEN is_public = 0 THEN 'Private' ELSE 'Public' END)FROM sys.dm_hadr_cluster_networks WHERE network_subnet_ip = @NetworkIP;RAISERROR (@ConsoleMsg,10,1) WITH NOWAIT;FETCH NEXT FROM CurNetworkIPs INTO @NetworkIP;END;CLOSE CurNetworkIPs;DEALLOCATE CurNetworkIPs;FETCH NEXT FROM CurMemberNames INTO @MemberName;END;CLOSE CurMemberNames;DEALLOCATE CurMemberNames;END;IF CONVERT(INT, @MajorVersion) < 11BEGINSET NOEXEC OFF;-- turn on execution for earlier SQL versions to print script completeEND;SET @ConsoleMsg = 'Script complete.';RAISERROR (@ConsoleMsg,10,1) WITH NOWAIT; ................
................

In order to avoid copyright disputes, this page is only a partial summary.

Google Online Preview   Download