AG : requêtes d'état des groupes de disponibilité
Requêtes T-SQL pour lister les groupes de disponibilité, leurs réplicas primaire et secondaires, leurs bases, leurs réglages, et une version condensée du tableau de bord AG.
Un ensemble de requêtes T-SQL pour inspecter les groupes de disponibilité d'une instance : leurs réplicas primaire et secondaires, leurs bases, les réglages des réplicas, et une version condensée du tableau de bord des groupes de disponibilité.
Activer la bascule automatique sur deux réplicas
USE [master]
GO
ALTER AVAILABILITY GROUP [AppAG]
MODIFY REPLICA ON N'sqlserver01' WITH (FAILOVER_MODE = AUTOMATIC)
GO
USE [master]
GO
ALTER AVAILABILITY GROUP [AppAG]
MODIFY REPLICA ON N'sqlserver02' WITH (FAILOVER_MODE = AUTOMATIC)
GO
Groupes de disponibilité dont cette instance est le réplica primaire
SELECT Groups.[Name] AS AGname
FROM sys.dm_hadr_availability_group_states States
INNER JOIN master.sys.availability_groups Groups ON States.group_id = Groups.group_id
WHERE primary_replica = @@Servername;
Groupes de disponibilité dont cette instance est un réplica secondaire
SELECT Groups.[Name] AS AGname
FROM sys.dm_hadr_availability_group_states States
INNER JOIN master.sys.availability_groups Groups ON States.group_id = Groups.group_id
WHERE primary_replica != @@Servername;
Bases des groupes de disponibilité dont cette instance est le réplica primaire
SELECT
Groups.[Name] AS AGname,
AGDatabases.database_name AS Databasename
FROM sys.dm_hadr_availability_group_states States
INNER JOIN master.sys.availability_groups Groups ON States.group_id = Groups.group_id
INNER JOIN sys.availability_databases_cluster AGDatabases ON Groups.group_id = AGDatabases.group_id
WHERE primary_replica = @@Servername
ORDER BY
AGname ASC,
Databasename ASC;
Bases des groupes de disponibilité dont cette instance est un réplica secondaire
SELECT
Groups.[Name] AS AGname,
AGDatabases.database_name AS Databasename
FROM sys.dm_hadr_availability_group_states States
INNER JOIN master.sys.availability_groups Groups ON States.group_id = Groups.group_id
INNER JOIN sys.availability_databases_cluster AGDatabases ON Groups.group_id = AGDatabases.group_id
WHERE primary_replica != @@Servername
ORDER BY
AGname ASC,
Databasename ASC;
Bases de tous les groupes de disponibilité du cluster
Cette requête n'est pas limitée au serveur courant.
SELECT Groups.[name] AS AGName ,
Databaselist.[database_name] AS DatabaseName
FROM sys.availability_databases_cluster Databaselist
INNER JOIN sys.availability_groups_cluster Groups ON Databaselist.group_id = Groups.group_id
ORDER BY
AGName ,
DatabaseName;
Synthèse des réplicas : primaire, mode synchrone, secondaire lisible et mode de bascule
La requête liste, pour chaque groupe de disponibilité visible par le serveur, les réplicas avec l'indicateur de primaire, le mode synchrone, le réglage de lecture sur secondaire et le mode de bascule. Ajouter une clause WHERE pour filtrer le résultat.
-- Show availability groups visible to the server and replica information such as which server is the primary,
-- synchronous and asynchronous modes, readable secondary and failover mode. These can all be filtered using a WHERE clause.
-- Without a WHERE clause, all of the information is shown.
WITH AGStatus AS(
SELECT
name as AGname,
replica_server_name,
CASE WHEN (primary_replica = replica_server_name) THEN 1
ELSE '' END AS IsPrimaryServer,
secondary_role_allow_connections_desc AS ReadableSecondary,
[availability_mode] AS [Synchronous],
failover_mode_desc
FROM master.sys.availability_groups Groups
INNER JOIN master.sys.availability_replicas Replicas ON Groups.group_id = Replicas.group_id
INNER JOIN master.sys.dm_hadr_availability_group_states States ON Groups.group_id = States.group_id
)
Select
[AGname],
[Replica_server_name],
[IsPrimaryServer],
[Synchronous],
[ReadableSecondary],
[Failover_mode_desc]
FROM AGStatus
--WHERE
--IsPrimaryServer = 1
--AND Synchronous = 1
ORDER BY
AGname ASC,
IsPrimaryServer DESC;
Tableau de bord des groupes de disponibilité (condensé)
Cette requête est adaptée du tableau de bord des groupes de disponibilité de SQL Server Management Studio. Elle affiche des colonnes lisibles, comme la taille et le débit de la file de redo, la perte de données estimée et le temps de reprise estimé, ainsi que les informations LSN. Renseigner @AGname avec un groupe précis, ou avec NULL pour tous les afficher.
SET NOCOUNT ON;
DECLARE @AGname NVARCHAR(128);
DECLARE @SecondaryReplicasOnly BIT;
SET @AGname = 'AppAG'; --SET AGname for a specific AG, or to NULL for ALL AGs
IF OBJECT_ID('TempDB..#tmpag_availability_groups') IS NOT NULL
DROP TABLE [#tmpag_availability_groups];
SELECT *
INTO [#tmpag_availability_groups]
FROM [master].[sys].[availability_groups];
IF(@AGname IS NULL
OR EXISTS
(
SELECT [Name]
FROM [#tmpag_availability_groups]
WHERE [Name] = @AGname
))
BEGIN
IF OBJECT_ID('TempDB..#tmpdbr_availability_replicas') IS NOT NULL
DROP TABLE [#tmpdbr_availability_replicas];
IF OBJECT_ID('TempDB..#tmpdbr_database_replica_cluster_states') IS NOT NULL
DROP TABLE [#tmpdbr_database_replica_cluster_states];
IF OBJECT_ID('TempDB..#tmpdbr_database_replica_states') IS NOT NULL
DROP TABLE [#tmpdbr_database_replica_states];
IF OBJECT_ID('TempDB..#tmpdbr_database_replica_states_primary_LCT') IS NOT NULL
DROP TABLE [#tmpdbr_database_replica_states_primary_LCT];
IF OBJECT_ID('TempDB..#tmpdbr_availability_replica_states') IS NOT NULL
DROP TABLE [#tmpdbr_availability_replica_states];
SELECT [group_id],
[replica_id],
[replica_server_name],
[availability_mode],
[availability_mode_desc]
INTO [#tmpdbr_availability_replicas]
FROM [master].[sys].[availability_replicas];
SELECT [replica_id],
[group_database_id],
[database_name],
[is_database_joined],
[is_failover_ready]
INTO [#tmpdbr_database_replica_cluster_states]
FROM [master].[sys].[dm_hadr_database_replica_cluster_states];
SELECT *
INTO [#tmpdbr_database_replica_states]
FROM [master].[sys].[dm_hadr_database_replica_states];
SELECT [replica_id],
[role],
[role_desc],
[is_local]
INTO [#tmpdbr_availability_replica_states]
FROM [master].[sys].[dm_hadr_availability_replica_states];
SELECT [ars].[role],
[drs].[database_id],
[drs].[replica_id],
[drs].[last_commit_time]
INTO [#tmpdbr_database_replica_states_primary_LCT]
FROM [#tmpdbr_database_replica_states] AS [drs]
LEFT JOIN [#tmpdbr_availability_replica_states] [ars] ON [drs].[replica_id] = [ars].[replica_id]
WHERE [ars].[role] = 1;
SELECT [AG].[name] AS [AvailabilityGroupName],
[AR].[replica_server_name] AS [AvailabilityReplicaServerName],
[dbcs].[database_name] AS [AvailabilityDatabaseName],
ISNULL([dbcs].[is_failover_ready],0) AS [IsFailoverReady],
ISNULL([arstates].[role_desc],3) AS [ReplicaRole],
[AR].[availability_mode_desc] AS [AvailabilityMode],
CASE [dbcs].[is_failover_ready]
WHEN 1
THEN 0
ELSE ISNULL(DATEDIFF([ss],[dbr].[last_commit_time],[dbrp].[last_commit_time]),0)
END AS [EstimatedDataLoss_(Seconds)],
ISNULL(CASE [dbr].[redo_rate]
WHEN 0
THEN-1
ELSE CAST([dbr].[redo_queue_size] AS FLOAT) / [dbr].[redo_rate]
END,-1) AS [EstimatedRecoveryTime_(Seconds)],
ISNULL([dbr].[is_suspended],0) AS [IsSuspended],
ISNULL([dbr].[suspend_reason_desc],'-') AS [SuspendReason],
ISNULL([dbr].[synchronization_state_desc],0) AS [SynchronizationState],
ISNULL([dbr].[last_received_time],0) AS [LastReceivedTime],
ISNULL([dbr].[last_redone_time],0) AS [LastRedoneTime],
ISNULL([dbr].[last_sent_time],0) AS [LastSentTime],
ISNULL([dbr].[log_send_queue_size],-1) AS [LogSendQueueSize],
ISNULL([dbr].[log_send_rate],-1) AS [LogSendRate_KB/S],
ISNULL([dbr].[redo_queue_size],-1) AS [RedoQueueSize_KB],
ISNULL([dbr].[redo_rate],-1) AS [RedoRate_KB/S],
ISNULL(CASE [dbr].[log_send_rate]
WHEN 0
THEN-1
ELSE CAST([dbr].[log_send_queue_size] AS FLOAT) / [dbr].[log_send_rate]
END,-1) AS [SynchronizationPerformance],
ISNULL([dbr].[filestream_send_rate],-1) AS [FileStreamSendRate],
ISNULL([dbcs].[is_database_joined],0) AS [IsJoined],
[arstates].[is_local] AS [IsLocal],
ISNULL([dbr].[last_commit_lsn],0) AS [LastCommitLSN],
ISNULL([dbr].[last_commit_time],0) AS [LastCommitTime],
ISNULL([dbr].[last_hardened_lsn],0) AS [LastHardenedLSN],
ISNULL([dbr].[last_hardened_time],0) AS [LastHardenedTime],
ISNULL([dbr].[last_received_lsn],0) AS [LastReceivedLSN],
ISNULL([dbr].[last_redone_lsn],0) AS [LastRedoneLSN]
FROM [#tmpag_availability_groups] AS [AG]
INNER JOIN [#tmpdbr_availability_replicas] AS [AR] ON [AR].[group_id] = [AG].[group_id]
INNER JOIN [#tmpdbr_database_replica_cluster_states] AS [dbcs] ON [dbcs].[replica_id] = [AR].[replica_id]
LEFT OUTER JOIN [#tmpdbr_database_replica_states] AS [dbr] ON [dbcs].[replica_id] = [dbr].[replica_id]
AND [dbcs].[group_database_id] = [dbr].[group_database_id]
LEFT OUTER JOIN [#tmpdbr_database_replica_states_primary_LCT] AS [dbrp] ON [dbr].[database_id] = [dbrp].[database_id]
INNER JOIN [#tmpdbr_availability_replica_states] AS [arstates] ON [arstates].[replica_id] = [AR].[replica_id]
WHERE [AG].[name] = ISNULL(@AGname,[AG].[name])
ORDER BY [AvailabilityReplicaServerName] ASC,
[AvailabilityDatabaseName] ASC;
/*********************/
END;
ELSE
BEGIN
RAISERROR('Invalid AG name supplied, please correct and try again',12,0);
END;