Surveiller l’activité de sauvegarde pour Azure SQL Managed Instance

S’applique à :Azure SQL Managed Instance

Cet article vous explique comment superviser l’activité de sauvegarde pour Azure SQL Managed Instance en interrogeant la base de données msdb ou en configurant des sessions d’événements étendus (XEvent).

Vue d’ensemble

Azure SQL Managed Instance stocke les informations de sauvegarde dans la base de données msdb et émet également des événements (également appelés événements étendus ou XEvents) pendant l’activité de sauvegarde, qui peuvent être utilisés pour la création de rapports. Configurez une session XEvent pour suivre des informations telles que l’état, le type, la taille, l’heure et l’emplacement de la sauvegarde dans la base de données msdb. Ces informations peuvent être intégrées à des logiciels de supervision de sauvegarde et également utilisées pour l’audit d’entreprise.

Les audits d’entreprise peuvent nécessiter la preuve de la réussite des sauvegardes, du temps de sauvegarde et de la durée de la sauvegarde.

Interroger la base de données msdb

Pour afficher l'activité de sauvegarde, exécutez la requête suivante à partir de la base de données définie par l'utilisateur :

SELECT TOP (100)
    DB_NAME(DB_ID(bs.database_name)) AS [Database Name],
    CONVERT (BIGINT, bs.backup_size / 1048576) AS [Uncompressed Backup Size (MB)],
    CONVERT (BIGINT, bs.compressed_backup_size / 1048576) AS [Compressed Backup Size (MB)],
    CONVERT (NUMERIC (20, 2),
    CASE
        WHEN bs.compressed_backup_size > 0
        THEN CONVERT (FLOAT, bs.backup_size) / CONVERT (FLOAT, bs.compressed_backup_size)
        ELSE NULL
    END
    ) AS [Compression Ratio],
    bs.is_copy_only,
    -- bs.user_name, -- Applicable only for user-initiated COPY ONLY backups.
    bs.has_backup_checksums,
    DATEDIFF(SECOND, bs.backup_start_date, bs.backup_finish_date) AS [Backup Elapsed Time (sec)],
    bs.backup_finish_date AS [Backup Finish Date],
    bmf.physical_block_size
FROM msdb.dbo.backupset AS bs WITH (NOLOCK)
     INNER JOIN msdb.dbo.backupmediafamily AS bmf WITH (NOLOCK)
         ON bs.media_set_id = bmf.media_set_id
WHERE bs.[type] = 'D'
    -- AND bs.[is_copy_only] = 1  -- If you want to filter out for user initiated COPY ONLY backups.
ORDER BY bs.backup_finish_date DESC
OPTION (RECOMPILE); -- Optimize for ad hoc execution

Configurer une session XEvent

Utilisez l’événement étendu backup_restore_progress_trace pour enregistrer la progression de votre sauvegarde SQL Managed Instance. Modifiez les sessions XEvent en fonction des besoins de suivi des informations qui vous intéressent. Ces extraits de code T-SQL stockent les sessions XEvent dans la mémoire tampon en anneau, mais il est également possible d’écrire dans le Stockage Blob Azure. Les sessions XEvent stockant des données dans la mémoire tampon en anneau ont une limite d’environ 1 000 messages. Elles doivent donc être utilisées uniquement pour suivre l’activité récente. En outre, les données de mémoire tampon en anneau sont perdues lors du basculement. Dès lors, pour un enregistrement d’historique des sauvegardes, écrivez plutôt dans un fichier d’événements.

Suivi de base

Configurez une session XEvent de base pour capturer des événements sur les sauvegardes complètes complètes. Ce script collecte le nom de la base de données, le nombre total d’octets traités et l’heure à laquelle la sauvegarde a été effectuée.

Utilisez Transact-SQL (T-SQL) pour configurer la session XEvent de base :

CREATE EVENT SESSION [Basic backup trace] ON SERVER
ADD EVENT sqlserver.backup_restore_progress_trace
(
        WHERE operation_type = 0
        AND trace_message LIKE '%100 percent%'
)
ADD TARGET package0.ring_buffer WITH (STARTUP_STATE = ON);
GO

ALTER EVENT SESSION [Basic backup trace] ON SERVER
STATE = start;

Suivi détaillé

Configurez une session XEvent détaillée pour suivre plus de détails sur votre activité de sauvegarde. Ce script capture le début et la fin des sauvegardes complètes, différentielles et de journaux. Étant donné que ce script est plus détaillé, il remplit la mémoire tampon en anneau plus rapidement, de sorte que les entrées peuvent recycler plus rapidement qu’avec le script de base.

Utilisez T-SQL pour configurer la session XEvent verbeuse :

CREATE EVENT SESSION [Verbose backup trace] ON SERVER
ADD EVENT sqlserver.backup_restore_progress_trace(
    WHERE (
              [operation_type]=(0) AND (
              [trace_message] like '%100 percent%' OR
              [trace_message] like '%BACKUP DATABASE%' OR [trace_message] like '%BACKUP LOG%'))
       )
ADD TARGET package0.ring_buffer
WITH (MAX_MEMORY=4096 KB,EVENT_RETENTION_MODE=ALLOW_SINGLE_EVENT_LOSS,
       MAX_DISPATCH_LATENCY=30 SECONDS,MAX_EVENT_SIZE=0 KB,MEMORY_PARTITION_MODE=NONE,
       TRACK_CAUSALITY=OFF,STARTUP_STATE=ON)

ALTER EVENT SESSION [Verbose backup trace] ON SERVER
STATE = start;

Surveiller la progression de la sauvegarde

Une fois la session XEvent créée, vous pouvez utiliser T-SQL pour interroger les résultats de la mémoire tampon en anneau et surveiller la progression de la sauvegarde. Après démarrage, XEvent collecte tous les événements de sauvegarde afin d’ajouter les entrées à la session toutes les 5 à 10 minutes environ.

Suivi de base

Le code T-SQL suivant interroge la session XEvent de base et retourne le nom de la base de données, le nombre total d’octets traités et l’heure de fin de la sauvegarde :

WITH
a AS (SELECT CAST (xet.target_data AS XML) AS xed
    FROM sys.dm_xe_session_targets AS xet
         INNER JOIN sys.dm_xe_sessions AS xe
             ON (xe.address = xet.event_session_address)
    WHERE xe.name = 'Backup trace'),
b AS (SELECT d.n.value('(@timestamp)[1]', 'datetime2') AS [timestamp],
           ISNULL(db.name, d.n.value('(data[@name="database_name"]/value)[1]', 'varchar(200)')) AS database_name,
           d.n.value('(data[@name="trace_message"]/value)[1]', 'varchar(4000)') AS trace_message
    FROM a
CROSS APPLY xed.nodes('/RingBufferTarget/event') AS d(n)
         LEFT OUTER JOIN master.sys.databases AS db
             ON db.physical_database_name = d.n.value('(data[@name="database_name"]/value)[1]', 'varchar(200)'))
SELECT * FROM b;

La capture d’écran suivante montre un exemple de sortie de la requête précédente :

Dans cet exemple, cinq bases de données ont été automatiquement sauvegardées pendant 2 heures et 30 minutes et la session XEvent compte 130 entrées.

Suivi détaillé

Le code T-SQL suivant interroge la session XEvent détaillée et renvoie le nom de la base de données, ainsi que le début et la fin des sauvegardes complètes, différentielles et de journaux.

WITH
a AS (SELECT CAST (xet.target_data AS XML) AS xed
    FROM sys.dm_xe_session_targets AS xet
         INNER JOIN sys.dm_xe_sessions AS xe
             ON (xe.address = xet.event_session_address)
    WHERE xe.name = 'Verbose backup trace'),
b AS (SELECT d.n.value('(@timestamp)[1]', 'datetime2') AS [timestamp],
           ISNULL(db.name, d.n.value('(data[@name="database_name"]/value)[1]', 'varchar(200)')) AS database_name,
           d.n.value('(data[@name="trace_message"]/value)[1]', 'varchar(4000)') AS trace_message
    FROM a
CROSS APPLY xed.nodes('/RingBufferTarget/event') AS d(n)
         LEFT OUTER JOIN master.sys.databases AS db
             ON db.physical_database_name = d.n.value('(data[@name="database_name"]/value)[1]', 'varchar(200)'))
SELECT * FROM b;

La capture d’écran suivante montre un exemple de sauvegarde complète dans la session XEvent :

La capture d’écran suivante montre un exemple de sortie de sauvegarde différentielle dans la session XEvent :

Contenu connexe

• Restaurer une base de données d'Azure SQL Managed Instance à un point antérieur dans le temps
• Gérer la conservation des sauvegardes à long terme Azure SQL Managed Instance
• Sauvegardes automatisées dans Azure SQL Managed Instance