Partager via

Surveillance de la mise en miroir de bases de données (SQL Server)

Cette section présente le moniteur de mise en miroir de bases de données et les procédures stockées système sp_dbmmonitor , explique comment fonctionne la surveillance de la mise en miroir de bases de données (y compris le travail du moniteur de mise en miroir de bases de données ) et récapitule les informations que vous pouvez surveiller sur les sessions de mise en miroir de bases de données. En outre, cette section explique comment définir des seuils d’avertissement pour un ensemble d’événements de mise en miroir de bases de données prédéfinis et comment configurer des alertes sur n’importe quel événement de mise en miroir de bases de données.

Vous pouvez surveiller une base de données mise en miroir pendant une session de mise en miroir pour vérifier si et comment les données circulent correctement. Pour configurer et gérer la surveillance pour une ou plusieurs des bases de données mises en miroir sur une instance de serveur, vous pouvez utiliser le moniteur de mise en miroir de bases de données ou les procédures stockées système sp_dbmmonitor .

Une tâche de surveillance de la mise en miroir de bases de données, la tâche de surveillance du Moniteur de mise en miroir de bases de données, fonctionne en arrière-plan, indépendamment du Moniteur de mise en miroir de bases de données. SQL Server Agent appelle le travail du moniteur de mise en miroir de bases de données à intervalles réguliers, la valeur par défaut est une minute et le travail appelle une procédure stockée qui met à jour l’état de mise en miroir. Si vous utilisez SQL Server Management Studio pour démarrer une session de mise en miroir, le travail du moniteur de mise en miroir de bases de données est créé automatiquement. Toutefois, si vous utilisez uniquement ALTER DATABASE <database_name> SET PARTNER pour commencer la mise en miroir, vous devez créer le travail en exécutant une procédure stockée.

Dans cette rubrique :

Surveillance de l’état de la mise en miroir

Pour configurer et gérer la surveillance pour une ou plusieurs des bases de données mises en miroir sur une instance de serveur, vous pouvez utiliser le moniteur de mise en miroir de bases de données ou les procédures stockées système dbmmonitor . Vous pouvez surveiller une base de données mise en miroir pendant une session de mise en miroir pour vérifier si et comment les données circulent correctement.

Plus précisément, la surveillance d’une base de données mise en miroir vous permet de :

  • Vérifiez que la mise en miroir fonctionne.

    L’état de base inclut savoir si les deux instances de serveur sont actives, que les serveurs sont connectés, et que le journal est déplacé du serveur principal vers le serveur miroir.

  • Déterminez si la base de données miroir suit la base de données principale.

    En mode hautes performances, un serveur principal peut développer un backlog d’enregistrements de journaux non envoyés du serveur principal au serveur miroir. En outre, en tout mode d’exploitation, le serveur miroir peut accumuler un backlog d’enregistrements de journaux non restaurés qui ont été écrits dans le fichier journal, mais qui doivent toujours être restaurés sur la base de données miroir.

  • Déterminez la quantité de données perdues lorsque l’instance du serveur principal devient indisponible en mode hautes performances.

    Vous pouvez déterminer la perte de données en examinant la quantité de journal des transactions non envoyées (le cas échéant) et l’intervalle de temps durant lequel les transactions perdues ont été validées par le principal.

  • Comparez les performances actuelles avec les performances passées.

    Lorsque des problèmes se produisent, un administrateur de base de données peut afficher un historique des performances de mise en miroir pour mieux comprendre l’état actuel. L’analyse de l’historique peut permettre à l’utilisateur de détecter les tendances en matière de performances, d’identifier les modèles de problèmes de performances (par exemple, les heures de jour où le réseau est lent ou le nombre de commandes entrant dans le journal est très volumineux).

  • Résolvez les problèmes liés à la cause d’un flux de données réduit entre les partenaires de mise en miroir.

  • Définissez des seuils d’avertissement sur les métriques de performances clés.

    Si une nouvelle ligne d’état contient une valeur qui dépasse un seuil, un événement d’information est envoyé au journal des événements Windows. Un administrateur système peut ensuite configurer manuellement des alertes en fonction de ces événements. Pour plus d’informations, consultez Utiliser des seuils d’avertissement et des alertes sur les métriques de performances de mise en miroir (SQL Server).

Outils de surveillance de l’état de la mise en miroir de bases de données

L'état de mise en miroir peut être surveillé à l'aide du Moniteur de mise en miroir des bases de données ou de la procédure stockée système sp_dbmmonitorresults. Ces outils peuvent être utilisés pour surveiller la mise en miroir de bases de données sur n’importe quelle base de données mise en miroir sur l’instance de serveur local par les deux administrateurs système, c’est-à-dire les membres du rôle serveur fixe sysadmin et l’utilisateur qui ont été ajoutés au rôle de base de données fixe dbm_monitor dans la base de données msdb par un administrateur système. Lorsque vous utilisez l’un ou l’autre outil, un administrateur système peut également actualiser manuellement l’état de mise en miroir.

Remarque

Les administrateurs système peuvent également configurer et afficher des seuils d’avertissement pour les métriques de performances clés. Pour plus d’informations, consultez Utiliser des seuils d’avertissement et des alertes sur les métriques de performances de mise en miroir (SQL Server).

  • Moniteur de mise en miroir de bases de données

    Le moniteur de mise en miroir de bases de données est un outil d’interface utilisateur graphique qui permet aux administrateurs système d’afficher et de mettre à jour l’état et de configurer des seuils d’avertissement sur plusieurs métriques de performances clés. Le moniteur de mise en miroir de bases de données peut également être utilisé par les membres du rôle de base de données fixe dbm_monitor pour afficher la ligne la plus récente de la table d’état de mise en miroir, bien qu’ils ne puissent pas mettre à jour la table d’état.

    Le moniteur affiche l’état, y compris les métriques de performances, pour une base de données sélectionnée dans la page onglet État . Le contenu de cette page provient des instances du principal et du serveur miroir. La page est remplie de manière asynchrone au fur et à mesure que l'état est recueilli par le biais de connexions distinctes aux instances du principal et du serveur miroir. Le moniteur tente de mettre à jour la table d’état à des intervalles de 30 secondes. La mise à jour réussit uniquement si la table n’a pas été mise à jour dans les 15 secondes et que l’utilisateur est membre du rôle serveur fixe sysadmin . Pour obtenir un résumé des informations signalées sur la page État , consultez État affiché par le moniteur de mise en miroir de bases de données, plus loin dans cette rubrique.

    Pour une présentation de l’interface du moniteur de mise en miroir de bases de données, consultez Vue d’ensemble du moniteur de mise en miroir de bases de données. Pour plus d’informations sur le lancement du moniteur de mise en miroir de bases de données, consultez Démarrer le moniteur de mise en miroir de bases de données (SQL Server Management Studio).

  • Procédures stockées sur système

    Vous pouvez également récupérer ou mettre à jour l’état actuel en exécutant la procédure stockée système sp_dbmmonitorresults . D’autres procédures stockées dbmmonitor vous permettent de configurer la surveillance, de modifier les paramètres de surveillance, d’afficher la période de mise à jour actuelle et de supprimer la surveillance sur l’instance de serveur.

    Le tableau suivant présente les procédures stockées pour la gestion et l’utilisation de la surveillance de la mise en miroir de bases de données indépendamment du moniteur de mise en miroir de bases de données.

    Procédure Descriptif
    sp_dbmmonitoraddmonitoring Crée un travail qui met régulièrement à jour les informations d’état de chaque base de données mise en miroir sur l’instance de serveur.
    sp_dbmmonitorchangemonitoring Modifie la valeur d’un paramètre de surveillance de la mise en miroir de bases de données.
    sp_dbmmonitorhelpmonitoring Retourne la période de mise à jour actuelle.
    sp_dbmmonitorresults Retourne les lignes d’état d’une base de données surveillée et vous permet de choisir si la procédure obtient l’état le plus récent au préalable.
    sp_dbmmonitordropmonitoring Arrête et supprime l'opération de surveillance de la mise en miroir pour toutes les bases de données sur l'instance du serveur.

    Les procédures stockées système dbmmonitor peuvent être utilisées comme complément au moniteur de mise en miroir de bases de données. Par exemple, même si la surveillance a été configurée à l’aide de sp_dbmmonitoraddmonitoring, le moniteur de mise en miroir de bases de données peut être utilisé pour afficher l’état.

Fonctionnement de la surveillance

Cette section présente la table d’état de mise en miroir de bases de données, le travail de surveillance de la mise en miroir de bases de données et le moniteur, la façon dont les utilisateurs peuvent surveiller l’état de mise en miroir de bases de données et la façon dont la tâche de surveillance peut être supprimée.

Table d’état de mise en miroir de bases de données

L’état de mise en miroir de bases de données est stocké dans une table d’état de mise en miroir de bases de données interne et non documentée dans la base de données msdb . Cette table d’état est créée automatiquement la première fois que l’état de mise en miroir est mis à jour sur l’instance de serveur.

La table d’état peut être mise à jour automatiquement ou manuellement par un administrateur système, avec un intervalle de mise à jour minimal de 15 secondes. Le minimum de 15 secondes empêche les instances de serveur d’être surchargées avec les demandes d’état.

La table d’état est mise à jour automatiquement par le moniteur de mise en miroir de bases de données et la tâche du moniteur de mise en miroir de bases de données, lorsqu'ils sont en cours d'exécution. Travail du moniteur de mise en miroir de bases de données met à jour la table une fois par minute par défaut (un administrateur système peut spécifier une période de mise à jour de 1 à 120 minutes). Le moniteur de mise en miroir de bases de données met à jour automatiquement la table toutes les 30 secondes. Pour ces mises à jour, la tâche du moniteur de mise en miroir de base de données et le moniteur de mise en miroir de base de données appellent sp_dbmmonitorupdate.

La première fois sp_dbmmonitorupdate s’exécute, il crée la table d’état de mise en miroir de bases de données et le rôle de base de données fixe dbm_monitor dans la base de données msdb . sp_dbmmonitorupdate met généralement à jour l’état de mise en miroir en insérant une nouvelle ligne dans la table d’état pour chaque base de données mise en miroir sur l’instance de serveur ; Pour plus d’informations, consultez « Table d’état de mise en miroir de bases de données », plus loin dans cette rubrique. Cette procédure évalue également les métriques de performances dans les nouvelles lignes et tronque les lignes antérieures à la période de rétention actuelle (la valeur par défaut est de 7 jours). Pour plus d’informations, consultez sp_dbmmonitorupdate (Transact-SQL).

Remarque

Sauf si le moniteur de mise en miroir de bases de données est actuellement utilisé par un membre du rôle serveur fixe sysadmin , la table d’état est automatiquement mise à jour uniquement si le travail du moniteur de mise en miroir de bases de données existe et que SQL Server Agent est en cours d’exécution.

Travail du moniteur de mise en miroir de bases de données

Le travail de surveillance de la mise en miroir de bases de données, Travail du Moniteur de Mise en Miroir de Bases de Données, fonctionne indépendamment du Moniteur de Mise en Miroir de Bases de Données. La tâche du moniteur de mise en miroir de bases de données est créée automatiquement uniquement si SQL Server Management Studio est utilisé pour démarrer une session de mise en miroir. Si les commandes ALTER DATABASE database_name SET PARTNER sont toujours utilisées pour initialiser la mise en miroir, la tâche existe uniquement si l’administrateur système exécute la procédure stockée sp_dbmmonitoraddmonitoring.

Une fois le travail du moniteur de mise en miroir de bases de données créé, en supposant que SQL Server Agent est en cours d’exécution, le travail est appelé une fois par minute, par défaut. Le travail appelle ensuite la procédure stockée système sp_dbmmonitorupdate .

SQL Server Agent appelle la tâche du moniteur de mise en miroir de base de données une fois par minute, par défaut, et la tâche appelle sp_dbmmonitorupdate pour mettre à jour la table de statut. Les administrateurs système peuvent modifier la période de mise à jour à l’aide de la procédure stockée système sp_dbmmonitorchangemonitoring , et ils peuvent afficher la période de mise à jour actuelle à l’aide de la procédure stockée système sp_dbmmonitorchangemonitoring . Pour plus d’informations, consultez sp_dbmmonitoraddmonitoring (Transact-SQL) et sp_dbmmonitorchangemonitoring (Transact-SQL).

Surveillance de l’état de la mise en miroir de bases de données (par les administrateurs système)

Les membres du rôle serveur fixe sysadmin peuvent afficher et mettre à jour la table d’état

  • Utilisation du moniteur de mise en miroir de bases de données

    Lorsque vous utilisez le moniteur de mise en miroir de bases de données, un administrateur système peut actualiser manuellement la page État , l’arborescence de navigation ou la page Historique . Cela met également à jour la table d’état, sauf si elle a déjà été mise à jour au cours des 15 secondes précédentes.

    Pour afficher l’historique de l’état de mise en miroir sur une instance de serveur donnée, l’administrateur système peut également cliquer sur le bouton Historique d’une instance de serveur (dans la page État ). L’historique est affiché dans la fenêtre de dialogue Historique de mise en miroir de bases de données. Là, l’administrateur système peut afficher certaines ou toutes les lignes de la table d’état de l’instance de serveur.

    Pour plus d’informations sur les métriques de la page État , consultez Métriques de performances affichées par le « Moniteur de mise en miroir de bases de données », plus loin dans cette rubrique.

  • Utiliser sp_dbmmonitorresults

    Les administrateurs système peuvent utiliser la procédure stockée système sp_dbmmonitorresults pour afficher et, éventuellement, mettre à jour la table d’état, si elle n’a pas été mise à jour au cours des 15 secondes précédentes. Cette procédure appelle la procédure sp_dbmmonitorupdate et retourne une ou plusieurs lignes d’historique, en fonction du montant demandé dans l’appel de procédure. Pour plus d’informations sur l’état dans son jeu de résultats, consultez sp_dbmmonitorresults (Transact-SQL).

Surveillance de l’état de la mise en miroir des bases de données (par les membres de dbm_monitor)

Comme mentionné, la première fois que sp_dbmmonitorupdate s’exécute, elle crée le rôle de base de données fixe dbm_monitor dans la base de données msdb . Les membres du rôle de base de données fixe dbm_monitor peuvent consulter l’état de la mise en miroir existant à l’aide du moniteur de mise en miroir de bases de données ou de la procédure stockée sp_dbmmonitorresults . Cependant, ces utilisateurs ne peuvent pas mettre à jour la table d'état. Pour connaître l’âge de l’état affiché, un utilisateur peut examiner les heures indiquées dans le journal principal (<heure>) et dans le journal miroir (<heure>) présentés sur la page État.

Les membres du rôle de base de données fixe dbm_monitor dépendent du travail de surveillance de la mise en miroir de bases de données pour mettre à jour la table d’état à intervalles réguliers. Si le travail n’existe pas ou si SQL Server Agent est arrêté, l’état devient de plus en plus obsolète et ne reflète plus la configuration de la session de mise en miroir. Par exemple, après un basculement, les partenaires peuvent sembler partager le même rôle (principal ou miroir), ou le serveur principal actuel peut être affiché comme serveur miroir, alors que le serveur miroir actuel est affiché comme principal.

Suppression de la tâche du moniteur de mise en miroir de bases de données

La tâche de surveillance du miroir de base de données, tâche de surveillance du miroir de base de données, reste jusqu'à ce qu'elle soit supprimée. Le travail de surveillance doit être géré par l’administrateur système. Pour supprimer le travail du moniteur de mise en miroir de bases de données, utilisez sp_dbmmonitordropmonitoring. Pour plus d’informations, consultez sp_dbmmonitordropmonitoring (Transact-SQL).

État affiché par le moniteur de mise en miroir de bases de données

La page État du Moniteur de mise en miroir de bases de données décrit les partenaires, ainsi que l’état de la session de mise en miroir. L’état inclut des métriques de performances telles que l’état du journal des transactions et d’autres informations destinées à estimer actuellement le temps nécessaire pour terminer un basculement et le risque de perte de données, si la session n’est pas synchronisée. En outre, la page État affiche l’état et les informations sur la session de mise en miroir en général.

Remarque

Pour une présentation de la page Moniteur et état de la mise en miroir de bases de données, consultez Outils de surveillance de l’état de la mise en miroir de bases de données, plus haut dans cette rubrique.

Les informations fournies pour chacun de ces éléments sont résumées dans les sections suivantes.

Partenaires

La page État affiche les informations suivantes pour chacun des partenaires :

  • Instance de serveur

    Nom de l'instance de serveur dont l'état est affiché dans la ligne État .

  • Rôle actuel

    Rôle actuel de l’instance de serveur. Les états possibles sont :

    • Principal

    • Miroir

  • État de mise en miroir

    Les états possibles sont :

    • Inconnu

    • Synchronisation

    • Synchronisé

    • Interrompu

    • Déconnecté

  • Connexion de témoin

    État de connexion du témoin. Les états possibles sont :

    • Inconnu

    • Connecté

    • Coupé.

Ouvrir une session sur le serveur principal

La page État affiche les informations suivantes sur l’état du journal sur le serveur principal à partir de l’heure indiquée :

  • Journal non envoyé

    Quantité de journal en attente dans la file d’attente d’envoi en kilo-octets (Ko).

  • Transaction non envoyée la plus ancienne

    Âge de la transaction non envoyée la plus ancienne dans la file d'attente d'envoi. La durée de vie de cette transaction indique la quantité de transactions en minutes qui n'ont pas encore été envoyées à l'instance de serveur miroir. Cette valeur permet de mesurer la perte de données potentielle en termes de temps.

  • Temps d’envoi du journal (estimé)

    Nombre estimé de minutes requis par l’instance de serveur principal pour envoyer le journal qui se trouve actuellement dans la file d’attente d’envoi à l’instance de serveur miroir en fonction du taux d’envoi actuel. Le temps réel d’envoi du journal sera affecté par le taux de transactions entrantes, ce qui peut varier considérablement. Toutefois, la valeur du journal d’envoi (estimé) peut être utile pour estimer approximativement le temps nécessaire pour un basculement manuel.

  • Taux d’envoi actuel

    Fréquence à laquelle les transactions sont envoyées à l’instance de serveur miroir en Ko par seconde.

  • Taux actuel de nouvelles transactions

    Débit auquel les transactions entrantes sont consignées dans le journal du principal en Kilo-octets par seconde. Pour déterminer si la mise en miroir prend du retard, reste à jour ou rattrape le retard, comparez cette valeur à la valeur Durée (estimée) d'envoi du journal .

Ouvrir une session sur le serveur miroir

La page État affiche les informations suivantes sur l’état du journal sur le serveur miroir à partir de l’heure indiquée :

  • Journal non restauré

    Quantité de journal en attente dans la file d’attente de réexécution en kB.

  • Temps de restauration du journal (estimé)

    Nombre approximatif de minutes requises pour que le journal actuellement dans la file d'attente de restauration par progression soit appliqué à la base de données miroir.

  • Taux de restauration actuel

    Débit auquel les transactions sont restaurées dans la base de données miroir (en Kilo-octets par seconde).

Session de mise en miroir

En outre, la page État affiche les informations suivantes sur la session de mise en miroir :

  • Charge supplémentaire d'engagement de mise en miroir

    Délai moyen par transaction en millisecondes (pertinent uniquement en mode haute sécurité). Ce délai correspond au temps de traitement pendant lequel l'instance de serveur principal attend que l'instance de serveur miroir écrive l'enregistrement du journal de transaction dans la file d'attente de restauration par progression.

  • Temps d’envoi et de restauration de tous les journaux actuels (estimé)

    Temps estimé nécessaire pour envoyer tous les journaux non envoyés et validés au niveau du principal et pour restaurer tous les journaux actuellement dans la file d’attente de refait. Cette estimation peut être inférieure à la somme des valeurs des champs Time to send log (estimate) et Time to restore log (estimate), car l’envoi et la restauration peuvent fonctionner en parallèle.

  • Adresse du témoin

    Adresse réseau de l'instance du serveur témoin. Pour plus d’informations sur le format de cette adresse, consultez Spécifier une adresse réseau de serveur (Mise en miroir de bases de données).

  • Mode d’exploitation

    Mode d'opération de la session de mise en miroir de bases de données :

    • Hautes performances (asynchrones)

    • Sécurité élevée sans basculement automatique (synchrone)

    • Sécurité élevée avec basculement automatique (synchrone)

Sources d’informations supplémentaires sur une base de données mise en miroir

Outre l’utilisation du moniteur de mise en miroir de bases de données et des procédures stockées dbmmonitor pour surveiller une base de données mise en miroir et configurer des alertes sur les variables de performances surveillées, SQL Server 2014 fournit des vues de catalogue, des compteurs de performances et des notifications d’événements pour la mise en miroir de bases de données.

Dans cette section :

Métadonnées de mise en miroir de bases de données

Chaque session de mise en miroir de bases de données est décrite dans les métadonnées exposées via les vues de gestion dynamique ou de catalogue suivantes :

  • sys.database_mirroring

    Cette vue affiche les métadonnées de mise en miroir de bases de données pour chaque base de données mise en miroir dans une instance de serveur. Pour plus d’informations, consultez sys.database_mirroring (Transact-SQL).

  • sys.database_mirroring_endpoints

    La vue catalogue sys.database_mirroring_endpoints affiche des informations sur le point de terminaison de l’instance de serveur pour la mise en miroir de la base de données. Pour plus d’informations, consultez sys.database_mirroring_endpoints (Transact-SQL).

  • sys.database_mirroring_witnesses

    Cet affichage catalogue affiche les métadonnées de mise en miroir de bases de données pour chaque session dans laquelle une instance de serveur est le témoin. Pour plus d’informations, consultez sys.database_mirroring_witnesses (Transact-SQL).

  • sys.dm_db_mirroring_connections

    Cette vue de gestion dynamique retourne une ligne pour chaque connexion réseau de mise en miroir de bases de données.

    Pour plus d’informations, consultez sys.dm_db_mirroring_connections (Transact-SQL).

Compteurs de performances de mise en miroir de bases de données

Les compteurs de performances vous permettent de surveiller les performances de mise en miroir de bases de données. Par exemple, vous pouvez examiner le compteur de délai de transaction pour voir si la mise en miroir des bases de données a un impact sur les performances du serveur principal. Vous pouvez également examiner les compteurs de file de réexécution et de file d’envoi de journaux pour voir comment la base de données miroir suit la base de données principale. Vous pouvez examiner le compteur d’octets de journal envoyés/s pour surveiller la quantité de journal envoyée par seconde.

Dans l’Analyseur de performances sur l’un ou l’autre des partenaires, les compteurs de performances sont disponibles dans l’objet de performances « SQLServer : Database Mirroring » pour la mise en miroir de bases de données. Pour plus d’informations, consultez SQL Server, Objet de mise en miroir de bases de données.

Pour démarrer l’analyseur de performances

Notifications d’événements de mise en miroir de bases de données

Les notifications d’événements sont un type spécial d’objet de base de données. Les notifications d’événements s’exécutent en réponse à diverses instructions Transact-SQL langage de définition de données (DDL) et aux événements trace SQL et envoient des informations sur les événements de serveur et de base de données à un service Service Broker.

Les événements suivants sont disponibles pour la mise en miroir de bases de données :

Tâches associées

Procédures stockées

Voir aussi

Mise en miroir de bases de données (SQL Server)
Concepts du fournisseur WMI pour les événements de serveur

  • Last updated on