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 (Minimal version)* 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;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 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.
To fulfill the demand for quickly locating and searching documents.
It is intelligent file search solution for home and business.
Related searches
- wordpress passing data between pages
- wordpress business templates
- wordpress rss feed not working
- wordpress jquery is not defined
- create wordpress blog
- wordpress roles editor
- wordpress full rss feed
- wordpress rss feed settings
- wordpress rss feed plugin
- wordpress display rss feed
- wordpress rss feed link
- wordpress rss feed to post