Monitoring and Alerting

CREATE OR ALTER PROCEDURE dbo.usp_TDE_HealthCheck
    @AlertThresholdDays INT = 365,
    @SendEmail BIT = 0,
    @EmailRecipients NVARCHAR(500) = NULL
AS
BEGIN
    SET NOCOUNT ON;

    DECLARE @Issues TABLE (
        Severity VARCHAR(20),
        Category VARCHAR(50),
        DatabaseName NVARCHAR(128),
        Issue NVARCHAR(500),
        Recommendation NVARCHAR(500)
    );

    -- Check for databases that should be encrypted but aren't
    INSERT INTO @Issues
    SELECT
        'WARNING',
        'Encryption Gap',
        name,
        'Database is not encrypted',
        'Consider enabling TDE for this database'
    FROM sys.databases
    WHERE database_id > 4  -- Exclude system databases
    AND is_encrypted = 0
    AND name NOT IN ('tempdb', 'ReportServer', 'ReportServerTempDB');

    -- Check for key rotation overdue
    INSERT INTO @Issues
    SELECT
        'WARNING',
        'Key Rotation',
        DB_NAME(database_id),
        'Key rotation overdue (' +
            CAST(DATEDIFF(DAY, ISNULL(regenerate_date, create_date), GETDATE()) AS VARCHAR) +
            ' days since last rotation)',
        'Rotate DEK per security policy'
    FROM sys.dm_database_encryption_keys
    WHERE DATEDIFF(DAY, ISNULL(regenerate_date, create_date), GETDATE()) > @AlertThresholdDays;

    -- Check for stuck encryption operations
    INSERT INTO @Issues
    SELECT
        'CRITICAL',
        'Encryption Stuck',
        DB_NAME(database_id),
        'Encryption operation stuck at ' + CAST(percent_complete AS VARCHAR) + '%',
        'Investigate I/O performance and DuoKey connectivity'
    FROM sys.dm_database_encryption_keys
    WHERE encryption_state IN (2, 4, 5, 6)
    AND percent_complete < 100
    AND DATEDIFF(HOUR, modify_date, GETDATE()) > 4;

    -- Check EKM provider status
    IF NOT EXISTS (
        SELECT 1 FROM sys.cryptographic_providers
        WHERE name = 'DuoKeyProvider' AND is_enabled = 1
    )
    BEGIN
        INSERT INTO @Issues VALUES (
            'CRITICAL',
            'EKM Provider',
            'N/A',
            'DuoKey EKM Provider is not enabled or missing',
            'Verify provider installation and enable if disabled'
        );
    END

    -- Return results
    SELECT * FROM @Issues
    ORDER BY
        CASE Severity WHEN 'CRITICAL' THEN 1 WHEN 'WARNING' THEN 2 ELSE 3 END,
        Category, DatabaseName;

    -- Optionally send email (requires Database Mail configured)
    IF @SendEmail = 1 AND EXISTS (SELECT 1 FROM @Issues)
    BEGIN
        DECLARE @body NVARCHAR(MAX);
        SELECT @body = STRING_AGG(
            Severity + ': ' + Category + ' - ' + DatabaseName + ': ' + Issue,
            CHAR(13) + CHAR(10)
        ) FROM @Issues;

        EXEC msdb.dbo.sp_send_dbmail
            @recipients = @EmailRecipients,
            @subject = 'SQL EKM Health Check Alert',
            @body = @body;
    END
END
GO

-- Create health check job
USE msdb;
GO

EXEC dbo.sp_add_job
    @job_name = N'TDE_HealthCheck_Hourly',
    @enabled = 1,
    @description = N'Hourly TDE encryption health check';
GO

EXEC dbo.sp_add_jobstep
    @job_name = N'TDE_HealthCheck_Hourly',
    @step_name = N'RunHealthCheck',
    @subsystem = N'TSQL',
    @command = N'EXEC dbo.usp_TDE_HealthCheck
        @AlertThresholdDays = 365,
        @SendEmail = 1,
        @EmailRecipients = ''[email protected]''',
    @database_name = N'master';
GO

EXEC dbo.sp_add_schedule
    @schedule_name = N'Hourly',
    @freq_type = 4,
    @freq_interval = 1,
    @freq_subday_type = 8,
    @freq_subday_interval = 1;
GO

EXEC dbo.sp_attach_schedule
    @job_name = N'TDE_HealthCheck_Hourly',
    @schedule_name = N'Hourly';
GO

EXEC dbo.sp_add_jobserver
    @job_name = N'TDE_HealthCheck_Hourly';
GO

PRINT 'Health check job created successfully.';
GO

-- TDE Performance Baseline Query
SELECT
    DB_NAME(fs.database_id) AS DatabaseName,
    mf.name AS FileName,
    mf.type_desc AS FileType,
    fs.num_of_reads AS Reads,
    fs.num_of_writes AS Writes,
    fs.io_stall_read_ms AS ReadStallMs,
    fs.io_stall_write_ms AS WriteStallMs,
    CAST(fs.size_on_disk_bytes / 1024.0 / 1024.0 AS DECIMAL(10,2)) AS SizeMB,
    d.is_encrypted AS Encrypted,
    CASE
        WHEN fs.num_of_reads > 0
        THEN CAST(fs.io_stall_read_ms / fs.num_of_reads AS DECIMAL(10,2))
        ELSE 0
    END AS AvgReadLatencyMs,
    CASE
        WHEN fs.num_of_writes > 0
        THEN CAST(fs.io_stall_write_ms / fs.num_of_writes AS DECIMAL(10,2))
        ELSE 0
    END AS AvgWriteLatencyMs
FROM sys.dm_io_virtual_file_stats(NULL, NULL) fs
INNER JOIN sys.master_files mf
    ON fs.database_id = mf.database_id AND fs.file_id = mf.file_id
INNER JOIN sys.databases d
    ON fs.database_id = d.database_id
WHERE d.is_encrypted = 1
ORDER BY fs.io_stall_read_ms + fs.io_stall_write_ms DESC;
GO

-- Create server audit
CREATE SERVER AUDIT TDE_Audit
TO FILE (FILEPATH = 'C:\SQLAudit\', MAXSIZE = 100 MB)
WITH (QUEUE_DELAY = 1000, ON_FAILURE = CONTINUE);
GO

ALTER SERVER AUDIT TDE_Audit WITH (STATE = ON);
GO

-- Create audit specification for crypto operations
CREATE SERVER AUDIT SPECIFICATION TDE_AuditSpec
FOR SERVER AUDIT TDE_Audit
ADD (DATABASE_CHANGE_GROUP),
ADD (SCHEMA_OBJECT_ACCESS_GROUP),
ADD (BACKUP_RESTORE_GROUP)
WITH (STATE = ON);
GO

PRINT 'Audit configuration created successfully.';
GO