Blog

Mode de secours SQL Server (DAC)


Microsoft SQL Server fournit une connexion administrateur dédiée (DAC : Dedicated Admin Connection), plus simplement un mode de secours. Cette connexion permet à un administrateur d’accéder à une instance active du moteur de base de données SQL Server pour résoudre les problèmes sur le serveur liés à une ou plusieurs requêtes, même si ce serveur ne répond pas aux autres connexions clientes.

Contexte : Au secours, au secours !

Expert SQL Server - Mode de secours SQL Server (DAC) - SQL Server  - funny-princess-leah-cat-help-me-obi-wan-pics1

Partons du postulat suivant : l’instance n’est plus accessible depuis SQL Management Studio (pas de possibilité de lancer un EXEC sp_whoisactive via l’interface donc) et le serveur est gelé (impossible d’y accéder en RDP) ou trop lent.  Côté SQL Magement Studio, on obtient le message d’erreur suivant lors d’une tentative de connexion à l’instance :

Expert SQL Server - Mode de secours SQL Server (DAC) - SQL Server  - lock_request-timeout

Pour effectuer un diagnostic à distance, il faut que le mode DAC ait été activé au préalable sur l’instance qui pose problème via la commande suivante :

Voir : https://www.concatskills.com/2016/10/20/tuning-dinstance-sql-server/

La prudence nous a conduit à activer cette option peu de temps après le déploiement de l’instance. Passons maintenant à la pratique avec l’utilitaire en ligne de commande SQLCMD qui est disponible sur n’importe quelle machine où l’on a déployé le client SQL Server. L’utilisation de l’option -A dans les exemples qui suivent correspond au mode de secours (DAC).

DAC : Exemple 1

L’ensemble de ces commandes permet de se connecter à une instance en utilisant le mode d’authentification Windows par défaut et de consulter la liste des requêtes en cours. Chaque ligne de commande doit être validée par la touche entrée.

DAC : Exemple 2

L’ensemble de ces commandes permet de se connecter à une instance en utilisant le mode d’authentification SQL et de consulter la liste des requêtes en cours. Même démarche que dans l’exemple précédent : valider chaque ligne de commande par la touche entrée.

DAC : Exemple 3

Cette ligne de commande permet de réaliser un export des requêtes en cours vers un fichier CSV dans le répertorie courant.

Ouvrir l’export result_sp_who2.csv depuis Excel, sélectionner la première colonne A et cliquer sur l’option Text to Columns dans la section Data :

Expert SQL Server - Mode de secours SQL Server (DAC) - SQL Server  - export_sp_who2

Sélectionner l’option Delimited

Expert SQL Server - Mode de secours SQL Server (DAC) - SQL Server  - delimited

Cocher Semicolon

Expert SQL Server - Mode de secours SQL Server (DAC) - SQL Server  - delimited_semicolon

Utiliser les options par défaut

Expert SQL Server - Mode de secours SQL Server (DAC) - SQL Server  - format_data

Résultat final : on observe que c’est le SPID 62 qui bloque le SPID 51 (BlkBy)

Expert SQL Server - Mode de secours SQL Server (DAC) - SQL Server  - result_formated

Sauvegarde des sessions en cours

Pour une analyse rétroactive et avant d’envisager de tuer les sessions qui posent problème à l’étape suivante (recouvrement du service), on peut faire une photo de l’état des processus SQL Server, en capturant la sortie de la procédure sp_whoisactive dans n’importe quelle base de données à l’exception de tempdb. Cela permettra de logger les requêtes consommatrices, ainsi que les blocages avec un niveau de détail beaucoup plus fin que le rendu de la procédure sp_who2.  Alors question : pourquoi ne pas avoir utiliser sp_whoisactive à la place de sp_who2 dans les étapes précédentes ? Tout bonnement parce que la sortie brute de sp_whoisactive est illisible dans un fichier CSV. Typiquement le formatage des données (ex : retours chariot) et types de colonne (XML) renvoyés ne permettent pas d’obtenir un fichier qui soit exploitable. Dans le script de capture ci-dessous, remplacer la valeur <table_name> par le nom de la table de destination (créée à la volée) qui stockera l’état des processus SQL Server.

Le script est disponible dans un fichier sql qu’on exécute avec le client sqlcmd en mode DAC :

Recouvrement du service

Une fois que la ou les sessions générant des lenteurs et/ou blocages ont été identifiées à l’aide du mode de connexion DAC, on peut y mettre fin (ex : KILL 62). Si on se retrouve avec des blocages en cascade, killer les sessions unitairement est une tâche laborieuse. Dans le script ci-dessous, on tue arbitrairement toutes les sessions qui bloquent les requêtes en attente de ressource. Ce n’est pas forcément adapté à votre contexte, c’est juste un exemple pour épurer les sessions bloquantes rapidement.

Le script est disponible dans un fichier sql qu’on exécute avec le client sqlcmd en mode DAC :

Expert SQL Server - Mode de secours SQL Server (DAC) - SQL Server  - kill2_optimized

Sauf cas de force majeur, l’instance SQL Server ne doit jamais être redémarrée. Il y a très peu de contextes qui requièrent un redémarrage brutal de celle-ci et les conséquences peuvent être désastreuses (ex : bases de données en mode récupération pour une période indéterminée).

Voici l’aide disponible en ligne pour l’utilitaire en mode ligne de commande SQLCMD : https://msdn.microsoft.com/fr-fr/library/ms162773.aspx

A noter que la commande Powershell invoke-sqlcmd dispose elle aussi du mode DAC.

Auteur

Expert SQL Server - Mode de secours SQL Server (DAC) - SQL Server  - avatar_ninja_tete-150x150
Sarah Béquet
Archietcte Data Microsoft, les maîtres mots sont : performance, industrialisation, méthodologie & bonne humeur.
error: