Início / dba / Perguntas / 308323
Accepted
Brendan McCaffrey
Asked: 2022-03-05 14:14:56 +0800 CST

Métricas de tempo de atividade do relatório para SQL Server por banco de dados/aplicativo

  • 772

Tenho a necessidade de reportar métricas mensais de uptime do SQL Server, por aplicativo/banco de dados. Isso seria no nível do cluster. IE, se uma réplica secundária ficasse offline, mas uma primária ainda estivesse disponível para processar transações, o tempo de atividade ainda seria considerado 100%.

Analisei várias ferramentas que podem servir a esse propósito, e algumas chegam perto. Mas, nenhum deles parece capturar nada mais do que se o serviço do SQL Server estiver online e aceitando conexões. Eles também não agregam essas métricas no nível do cluster/AG. Isso significa que esses relatórios de tempo de atividade seriam atingidos se uma réplica secundária ficasse offline.

Por exemplo, digamos que um banco de dados fique offline ou um arquivo de log seja preenchido e as transações não possam ser processadas em um único banco de dados. Essas ferramentas diriam que o SQL Server está ativo, mas eu ainda teria pessoas dizendo que era um problema de banco de dados. Assim, essas métricas precisariam refletir que o SQL não estava totalmente ativo naquele momento.

A melhor idéia que tenho neste momento é criar um trabalho do SQL Agent que insira um registro em uma tabela Canary em cada banco de dados, uma vez por minuto. Em seguida, no final do mês, para consultar essa tabela e dividir a contagem de linhas do mês anterior pela contagem de linhas esperada. Achei que não havia melhor maneira de provar que um banco de dados estava realmente disponível do que tentar inserir uma linha.

Já tenho a solução acima desenvolvida, testada e funcionando. Mas estou curioso para saber se alguém conhece uma maneira melhor de fazer isso. Incluindo quaisquer ferramentas de mercadorias ou DMVs que eu possa ter esquecido, que eu poderia usar para extrapolar as métricas de disponibilidade do usuário final de todos os bancos de dados em uma Instância SQL?

sql-server metrics

1 respostas

  1. Best Answer

    Esta é a solução que estou usando, que está funcionando.

    O procedimento armazenado abaixo pode ser usado para preencher uma tabela Canary e pode ser executado a cada minuto a partir de um trabalho do SQL Agent.

    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

    A consulta a seguir pode ser usada a cada mês para relatar o tempo de atividade com base no número de linhas inseridas com êxito na tabela Canary durante o mês anterior.

    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;
    • 2

relate perguntas