Connexion de diagnostic pour les administrateurs de base de données

Article
06/13/2017

SQL Server fournit une connexion de diagnostic spéciale pour les administrateurs lorsque les connexions standard au serveur ne sont pas possibles. Cette connexion de diagnostic permet à un administrateur d’accéder à SQL Server pour exécuter des requêtes de diagnostic et résoudre les problèmes même si SQL Server ne répond pas aux demandes de connexion standard.

Cette connexion administrateur dédiée (DAC) prend en charge le chiffrement et d'autres fonctions de sécurité de SQL Server. Elle permet uniquement de changer de contexte utilisateur pour un autre administrateur.

SQL Server effectue chaque tentative pour établir la connexion DAC, mais cela peut échouer dans des situations extrêmes.


S’applique à : SQL Server (SQL Server 2008 à la version actuelle), SQL Database V12.

Connexion avec DAC

Par défaut, la connexion est uniquement autorisée à partir d'un client s'exécutant sur le serveur. Les connexions réseau sont autorisées uniquement si elles sont configurées en utilisant la procédure stockée sp_configure avec l’option remote admin connections.

Seuls les membres du rôle administrateur système SQL Server peuvent se connecter à l'aide de la connexion DAC.

La connexion DAC est disponible et prise en charge par le biais de l’utilitaire d’invite de commandes sqlcmd , au moyen d’un commutateur d’administrateur spécial (-A). Pour plus d’informations sur l’utilisation de sqlcmd, consultez Utiliser sqlcmd avec des variables de script. Vous pouvez également connecter le préfixe au nom de admin:l’instance au format sqlcmd -Sadmin :<instance_name.> Vous pouvez également lancer une DAC à partir d’un Éditeur de requête SQL Server Management Studio en vous connectant à admin:<instance_name.>

Restrictions

Comme la connexion DAC n'est prévue que pour diagnostiquer des problèmes de serveur dans de rares circonstances, certaines restrictions sont imposées sur la connexion :

Pour garantir que des ressources sont disponibles pour la connexion, une seule connexion DAC est autorisée par instance de SQL Server. Si une connexion DAC est déjà active, toute nouvelle demande de connexion établie par son intermédiaire est refusée avec l'erreur 17810.
Pour conserver les ressources, SQL Server Express n’écoute pas sur le port DAC, sauf si vous avez démarré avec un indicateur de trace 7806.
Au départ, la connexion DAC tente une connexion à la base de données par défaut associée à la connexion. Une fois la connexion établie, vous pouvez vous connecter à la base de données master. Si la base de données par défaut est hors connexion ou non disponible pour une raison quelconque, la connexion retourne l'erreur 4060. Cependant, elle réussit si vous remplacez la base de données par défaut pour vous connecter à la base de données master au lieu d'utiliser la commande suivante :

sqlcmd -A -d master

Nous vous recommandons de vous connecter à la base de données master avec la DAC, car la base de données maître est garantie d’être disponible si l’instance du Moteur de base de données est démarrée.
SQL Server interdit l'exécution de requêtes ou de commandes parallèles avec la connexion DAC. Par exemple, l'erreur 3637 est générée si vous exécutez l'une des instructions ci-dessous avec la connexion DAC :
- RESTORE
- BACKUP
Seules des ressources limitées sont garanties disponibles avec la connexion DAC. N’utilisez pas la DAC pour exécuter des requêtes gourmandes en ressources (par exemple, une jointure complexe sur une table volumineuse) ou des requêtes qui peuvent bloquer. Cela permet d'éviter que la connexion DAC n'amplifie d'éventuels problèmes de serveur existants. Pour éviter les scénarios de blocages potentiels, si vous devez exécuter des requêtes pouvant créer un blocage, exécutez la requête sous des niveaux d'isolement basés si possible sur un instantané ; sinon, réglez le niveau d'isolement des transactions à READ UNCOMMITTED et attribuez à LOCK_TIMEOUT une valeur faible, par exemple 2 000 millisecondes, ou les deux. Vous éviterez ainsi le blocage de la session DAC. Cependant, selon l'état de SQL Server , la session DAC pourrait se bloquer sur un verrou. Vous pourriez être en mesure de terminer la session DAC en appuyant sur CTRL-C, mais ce n'est pas garanti. Dans ce cas, la seule option consiste à redémarrer SQL Server.
Pour garantir la connectivité et le dépannage avec la connexion DAC, SQL Server réserve des ressources limitées pour traiter les commandes exécutées sur la connexion DAC. Ces ressources ne permettent généralement que de simples fonctions de diagnostic et de dépannage, telles que celles répertoriées ci-dessous.

Même si vous pouvez théoriquement exécuter une instruction Transact-SQL qui n’a pas besoin d’être exécutée en parallèle sur la connexion DAC, nous vous recommandons fortement de vous limiter aux commandes de diagnostic et de résolution de problèmes suivantes :

Interrogation de vues de gestion dynamique (DMV) pour des diagnostics de base, comme sys.dm_tran_locks pour l’état de verrouillage, sys.dm_os_memory_cache_counters pour vérifier l’état des caches, et sys.dm_exec_requests et sys.dm_exec_sessions pour les sessions et les demandes actives. Évitez les vues de gestion dynamiques qui consomment beaucoup de ressources (par exemple, sys.dm_tran_version_store analyse le magasin de versions complet et peut provoquer des E/S intensives) ou qui utilisent des jointures complexes. Pour plus d'informations sur les implications sur les performances, consultez la documentation relative à la vue de gestion dynamiquespécifique.
Interrogation d'affichages catalogue.
Les commandes DBCC de base telles que DBCC FREEPROCCACHE, DBCC FREESYSTEMCACHE, DBCC DROPCLEANBUFFERS, et DBCC SQLPERF. Évitez d’exécuter des commandes à utilisation intensive des ressources telles que DBCC , CHECKDB, DBCC DBREINDEX ou DBCC SHRINKDATABASE.
Commande Transact-SQL KILL*<spid>*. Selon l'état de SQL Server, la commande KILL risque de ne pas toujours aboutir ; dans ce cas, la seule option consiste à redémarrer SQL Server. Voici quelques directives générales :
- Vérifiez que le SPID a été supprimé en interrogeant SELECT * FROM sys.dm_exec_sessions WHERE session_id = <spid>. S'il ne retourne aucune ligne, la session a été supprimée.
- Si la session est toujours active, vérifiez si des tâches sont affectées à cette session en exécutant la requête SELECT * FROM sys.dm_os_tasks WHERE session_id = <spid>. Si vous y voyez la tâche, votre session est très probablement en cours de fermeture. Notez que cette opération peut prendre beaucoup de temps et risque même d'échouer.
- En l'absence de tâches dans le sys.dm_os_tasks associé à cette session, mais si la session est maintenue dans sys.dm_exec_sessions après l'exécution de la commande KILL, vous ne disposez d'aucun thread de travail. Sélectionnez l'une des tâches en cours d'exécution (une tâche répertoriée dans la vue sys.dm_os_tasks avec un sessions_id <> NULL), puis supprimez la session qui lui est associée pour libérer le thread de travail. Notez que la suppression d'une session unique ne sera peut-être pas suffisante : vous devrez éventuellement en supprimer plusieurs.

Port DAC

SQL Server est à l'écoute de la connexion DAC sur le port TCP 1434 s'il est disponible ou un port TCP attribué dynamiquement lors du démarrage du Moteur de base de données . Le journal des erreurs contient le numéro de port sur lequel la connexion DAC écoute. Par défaut, l'écouteur DAC n'accepte une connexion que sur le port local. Pour voir un exemple de code qui active des connexions d’administration à distance, consultez remote admin connections (option de configuration de serveur).

Une fois la connexion d’administration distante configurée, l’écouteur DAC est activé sans nécessiter de redémarrage de SQL Server et un client peut désormais se connecter à la DAC à distance. Vous pouvez autoriser l’écouteur DAC à accepter les connexions à distance même si SQL Server ne répond pas en se connectant d’abord à SQL Server à l’aide de la DAC localement, puis en exécutant la procédure stockée sp_configure pour accepter la connexion à partir de connexions distantes.

Sur les configurations cluster, la connexion DAC est désactivée par défaut. Les utilisateurs peuvent exécuter l'option remote admin connection de sp_configure pour permettre à l'écouteur DAC d'accéder à une connexion distante. Si SQL Server ne répond pas et que l’écouteur DAC n’est pas activé, vous devrez peut-être redémarrer SQL Server pour vous connecter à la DAC. Nous recommandons par conséquent d'activer l'option de configuration remote admin connections sur les systèmes en cluster.

Le port DAC est affecté dynamiquement par SQL Server pendant le démarrage. Lors d’une connexion à l’instance par défaut, la DAC évite d’utiliser une demande SSRP ( SQL Server Resolution Protocol) à SQL Server Browser Service lors de la connexion. Elle se connecte d'abord sur le port TCP 1434. Si cette tentative échoue, elle effectue un appel SSRP pour obtenir le port. Si SQL Server Browser n’écoute pas les requêtes SSRP, la demande de connexion retourne une erreur. Reportez-vous au journal des erreurs pour trouver le numéro de port que la DAC écoute. Si SQL Server est configuré pour accepter les connexions d'administration distante, la DAC doit être initialisée avec un numéro de port explicite :

sqlcmd-Stcp : <serveur>,< port>

Le journal des erreurs de SQL Server indique le numéro de port de la connexion DAC, qui est 1434 par défaut. Si SQL Server est configuré pour accepter uniquement des connexions DAC locales, connectez-vous au moyen de l'adaptateur de bouclage en utilisant la commande suivante :

sqlcmd-S127.0.0.1,1434

Exemple

Dans cet exemple, un administrateur note que le serveur URAN123 ne répond pas et souhaite diagnostiquer le problème. Pour ce faire, l'utilisateur active l'utilitaire de ligne de commande sqlcmd et se connecte au serveur URAN123 en utilisant -A pour indiquer la connexion DAC.

sqlcmd -S URAN123 -U sa -P <xxx> -A

L'administrateur peut maintenant exécuter des requêtes pour diagnostiquer le problème et éventuellement terminer les sessions sans réponse.

Utiliser sqlcmd avec des variables de script

Utilitaire sqlcmd

SELECT (Transact-SQL)

sp_who (Transact-SQL)

sp_lock (Transact-SQL)

KILL (Transact-SQL)

DBCC CHECKALLOC (Transact-SQL)

DBCC CHECKDB (Transact-SQL)

DBCC OPENTRAN (Transact-SQL)

DBCC INPUTBUFFER (Transact-SQL)