按數據庫/應用程序報告 SQL Server 的正常執行時間指標

我需要按應用程序/數據庫報告 SQL Server 正常執行時間的每月指標。這將在集群級別。IE，如果輔助副本離線，但主副本仍可用於處理事務，則正常執行時間仍將被視為 100%。

我已經查看了可能用於此目的的各種工具，其中一些工具接近了。但是，除了 SQL Server 服務處於聯機狀態並接受連接之外，它們似乎都沒有擷取任何東西。他們也無法在集群/AG 級別聚合這些指標。這意味著如果輔助副本離線，這些正常執行時間報告將受到影響。

例如，假設數據庫離線或日誌文件已滿，並且無法針對單個數據庫處理事務。這些工具會說 SQL Server 已啟動，但我仍然會有人說這是數據庫問題。因此，這些指標需要反映當時 SQL 尚未完全啟動。

在這一點上我想出的最好的想法是創建一個 SQL 代理作業，該作業將記錄插入每個數據庫的 Canary 表中，每分鐘一次。然後在月底，查詢該表並將上個月的行數除以預期的行數。我認為沒有比嘗試插入一行更好的方法來證明數據庫實際上是可用的。

我已經開發、測試和工作了上述解決方案。但我很好奇是否有人知道更好的方法來做到這一點。包括我可能忽略的任何商品工具或 DMV，我可以用來推斷 SQL 實例上所有數據庫的最終使用者可用性指標？

這是我正在使用的解決方案，它正在工作。

下面的儲存過程可用於填充 Canary 表，並且可以從 SQL 代理作業每分鐘執行一次。

CREATE PROCEDURE [dbo].[SQLUptime]
AS
BEGIN 
   SET NOCOUNT ON

   DECLARE
       @DatabaseName nvarchar(128),
       @Query nvarchar(max);

   DECLARE cDatabases CURSOR FOR
       SELECT [name]
       FROM sys.databases
       WHERE database_id > 4

   OPEN cDatabases;
   FETCH NEXT FROM cDatabases INTO @DatabaseName

   WHILE @@FETCH_STATUS = 0
   BEGIN
       BEGIN TRY
           IF COALESCE(sys.fn_hadr_is_primary_replica(@DatabaseName),1) = 1    
           BEGIN
               SET @Query = 'USE [' + @DatabaseName + ']' + CHAR(13)+CHAR(10) + CHAR(13)+CHAR(10) + 
                   'IF NOT EXISTS (SELECT 1 FROM sys.schemas WHERE name = N''dba'')' + CHAR(13)+CHAR(10) + 
                   'BEGIN' + CHAR(13)+CHAR(10) +
                   '    EXECUTE sp_executesql N''CREATE SCHEMA [dba]''' + CHAR(13)+CHAR(10) +
                   'END' +  CHAR(13)+CHAR(10) + CHAR(13)+CHAR(10) +
           
                   'IF NOT EXISTS (SELECT 1 FROM sys.objects WHERE object_id = OBJECT_ID(N''[dba].[Canary]'') AND type in (N''U''))' + CHAR(13)+CHAR(10) +
                   'BEGIN' + CHAR(13)+CHAR(10) +
                   '    CREATE TABLE dba.Canary (' + CHAR(13)+CHAR(10) +
                   '        [Time] Datetime NOT NULL' + CHAR(13)+CHAR(10) +
                   '    )' + CHAR(13)+CHAR(10) + CHAR(13)+CHAR(10) +
           
                   '    CREATE CLUSTERED INDEX CIX_Canary_Time ON dba.Canary ([Time]);' + CHAR(13)+CHAR(10) +
                   'END' + CHAR(13)+CHAR(10) + CHAR(13)+CHAR(10) +

                   'INSERT INTO dba.Canary ([Time])' + CHAR(13)+CHAR(10) +
                   'VALUES (getdate());' + CHAR(13)+CHAR(10) + CHAR(13)+CHAR(10) +
           
                   'DELETE FROM dba.Canary' + CHAR(13)+CHAR(10) +
                   'WHERE [Time] < DATEADD(MONTH, -12, GETDATE());'

           /*PRINT @Query*/
           EXECUTE sp_executesql @Query
       END
       END TRY
       BEGIN CATCH
           SELECT
               ERROR_NUMBER(),
               ERROR_STATE(),
               ERROR_SEVERITY(),
               ERROR_PROCEDURE(),
               ERROR_LINE(),
               ERROR_MESSAGE()
       END CATCH
       
       FETCH NEXT FROM cDatabases INTO @DatabaseName
   END

   CLOSE cDatabases;
   DEALLOCATE cDatabases;
END

然後可以每月使用以下查詢來報告基於上個月成功插入 Canary 表的行數的正常執行時間。

DECLARE
   @StartDate    datetime,
   @CutoffDate   datetime,
   @TotalMinutes int,
   @output       int,
   @SQL          nvarchar(500),
   @Parameters   nvarchar(500),
   @Database     nvarchar(128);

SET @StartDate = DATEADD(MONTH,DATEDIFF(MONTH,0,GETDATE())-1,0);
SET @CutoffDate = DATEADD(DAY,0,DATEADD(MONTH,1,@StartDate));

IF @CutoffDate > GETDATE()
   SET @CutoffDate = GETDATE();

SET @TotalMinutes = DATEDIFF(MINUTE, @StartDate,@CutoffDate);
SET @Parameters = N'@retvalOUT int OUTPUT';

IF OBJECT_ID('tempdb..#UptimeMinutes') IS NOT NULL DROP TABLE #UptimeMinutes

CREATE TABLE #UptimeMinutes (
   DatabaseName nvarchar(128),
   UptimeMinutes int
)

DECLARE cursorDatabases CURSOR
FOR
   SELECT [name]
   FROM sys.databases
   WHERE database_id > 4;

OPEN cursorDatabases;
FETCH NEXT FROM cursorDatabases INTO @Database;

WHILE @@FETCH_STATUS = 0
BEGIN
   SET @SQL = N'USE [' + @Database + '] SELECT @retvalOUT = COUNT(*) FROM dba.Canary';

   EXEC sp_executesql @SQL, @Parameters, @retvalOUT=@output OUTPUT;

   INSERT INTO #UptimeMinutes (DatabaseName,UptimeMinutes)
   SELECT @Database, @output;

   FETCH NEXT FROM cursorDatabases INTO @Database;
END

SELECT SUM(UptimeMinutes) InstanceUptimeMinutes,
   SUM(@TotalMinutes) AS TargetInstanceUptimeMinutes,
   CONVERT(DECIMAL(10,3),(SUM(UptimeMinutes)/CONVERT(DECIMAL,SUM(@TotalMinutes))) * 100) AS InstanceUptimePercentage
FROM #UptimeMinutes;

SELECT DatabaseName,
   DatabaseUptimeMinutes,
   @TotalMinutes AS TargetDatabaseUptimeMinutes,
   CONVERT(DECIMAL(10,3),(UptimeMinutes/CONVERT(DECIMAL,@TotalMinutes)) * 100) AS DatabaseUptimePercentage
FROM #UptimeMinutes;

CLOSE cursorDatabases;
DEALLOCATE cursorDatabases;

引用自：https://dba.stackexchange.com/questions/308323