Mode de secours SQL Server (DAC)

12 janvier 2017

Commentaires fermés

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 !

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 :

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 :

EXEC sys.sp_configure N'remote admin connections', N'1'
GO
RECONFIGURE WITH OVERRIDE
GO

EXEC sys.sp_configure N'remote admin connections', N'1'

RECONFIGURE WITH OVERRIDE

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.

SQLCMD -A -S SERVER\INSTANCE -d master 
1> EXEC sp_who2
2> GO
3> EXIT

SQLCMD -A -S SERVER\INSTANCE -d master

1> EXEC sp_who2

2> GO

3> EXIT

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.

SQLCMD -A -S SERVER\INSTANCE -d master -U loginame -P password
1> EXEC sp_who2
2> GO
3> EXIT

SQLCMD -A -S SERVER\INSTANCE -d master -U loginame -P password

1> EXEC sp_who2

2> GO

3> EXIT

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.

SQLCMD -A -S SERVER\INSTANCE -d master -Q "SET NOCOUNT ON;EXEC sp_who2" -o "%cd%\result_sp_who2.csv" -W -w 8000 -s ";" -u

1	SQLCMD -A -S SERVER\INSTANCE -d master -Q "SET NOCOUNT ON;EXEC sp_who2" -o "%cd%\result_sp_who2.csv" -W -w 8000 -s ";" -u

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 :

Sélectionner l’option Delimited

Cocher Semicolon

Utiliser les options par défaut

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

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.

SET NOCOUNT ON;

DECLARE @retention int = 7, @destination_table varchar(500) = 'WhoIsActive', @destination_database sysname = 'master', 
        @schema varchar(max), @SQL nvarchar(4000), @parameters nvarchar(500), @exists bit;
SET @destination_table = @destination_database + '.dbo.' + @destination_table;

--create the logging table
IF OBJECT_ID(@destination_table) IS NULL
BEGIN;
    EXEC sp_WhoIsActive  @get_transaction_info = 1,  @get_outer_command = 1,  @get_plans = 1,  @return_schema = 1,  @schema = @schema OUTPUT;
    SET @schema = REPLACE(@schema, '<table_name>', @destination_table);  
    EXEC(@schema);
END;

--create index on collection_time
SET @SQL = 'USE ' + QUOTENAME(@destination_database) + '; IF NOT EXISTS (SELECT * FROM sys.indexes WHERE object_id = OBJECT_ID(@destination_table) AND name = N''cx_collection_time'') SET @exists = 0';
SET @parameters = N'@destination_table varchar(500), @exists bit OUTPUT';
EXEC sp_executesql @SQL, @parameters, @destination_table = @destination_table, @exists = @exists OUTPUT;
IF @exists = 0
BEGIN;
    SET @SQL = 'CREATE CLUSTERED INDEX cx_collection_time ON ' + @destination_table + '(collection_time ASC)';
    EXEC (@SQL);
END;

--collect activity into logging table
EXEC dbo.sp_WhoIsActive @get_transaction_info = 1,  @get_outer_command = 1,  @get_plans = 1,  @destination_table = @destination_table; 
	 
--purge older data
SET @SQL = 'DELETE FROM ' + @destination_table + ' WHERE collection_time < DATEADD(day, -' + CAST(@retention AS varchar(10)) + ', GETDATE());';
EXEC (@SQL);

SET NOCOUNT ON;

DECLARE @retention int = 7, @destination_table varchar(500) = 'WhoIsActive', @destination_database sysname = 'master',

@schema varchar(max), @SQL nvarchar(4000), @parameters nvarchar(500), @exists bit;

SET @destination_table = @destination_database + '.dbo.' + @destination_table;

--create the logging table

IF OBJECT_ID(@destination_table) IS NULL

BEGIN;

EXEC sp_WhoIsActive @get_transaction_info = 1, @get_outer_command = 1, @get_plans = 1, @return_schema = 1, @schema = @schema OUTPUT;

SET @schema = REPLACE(@schema, '<table_name>', @destination_table);

EXEC(@schema);

END;

--create index on collection_time

SET @SQL = 'USE ' + QUOTENAME(@destination_database) + '; IF NOT EXISTS (SELECT * FROM sys.indexes WHERE object_id = OBJECT_ID(@destination_table) AND name = N''cx_collection_time'') SET @exists = 0';

SET @parameters = N'@destination_table varchar(500), @exists bit OUTPUT';

EXEC sp_executesql @SQL, @parameters, @destination_table = @destination_table, @exists = @exists OUTPUT;

IF @exists = 0

BEGIN;

SET @SQL = 'CREATE CLUSTERED INDEX cx_collection_time ON ' + @destination_table + '(collection_time ASC)';

EXEC (@SQL);

END;

--collect activity into logging table

EXEC dbo.sp_WhoIsActive @get_transaction_info = 1, @get_outer_command = 1, @get_plans = 1, @destination_table = @destination_table;

--purge older data

SET @SQL = 'DELETE FROM ' + @destination_table + ' WHERE collection_time < DATEADD(day, -' + CAST(@retention AS varchar(10)) + ', GETDATE());';

EXEC (@SQL);

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

sqlcmd -A -S SERVER\INSTANCE  -d master -i "C:\temp\snap.sql"

1	sqlcmd -A -S SERVER\INSTANCE -d master -i "C:\temp\snap.sql"

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.

DECLARE @SQLDyn VARCHAR(MAX)

SET @SQLDyn = 
				( 
					SELECT	DISTINCT 'KILL ' + CAST(blocking_session_id AS VARCHAR) + ';' + CHAR(10)  AS [text()]
					FROM	sys.dm_os_waiting_tasks 
					WHERE	blocking_session_id <> 0
					FOR XML PATH('')
				)	

PRINT @SqlDyn
EXEC (@SqlDyn)

DECLARE @SQLDyn VARCHAR(MAX)

SET @SQLDyn =

(

SELECT DISTINCT 'KILL ' + CAST(blocking_session_id AS VARCHAR) + ';' + CHAR(10) AS [text()]

FROM sys.dm_os_waiting_tasks

WHERE blocking_session_id <> 0

FOR XML PATH('')

)

PRINT @SqlDyn

EXEC (@SqlDyn)

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

sqlcmd -A -S SERVER\INSTANCE  -d master -i "C:\temp\kill.sql"

1	sqlcmd -A -S SERVER\INSTANCE -d master -i "C:\temp\kill.sql"

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

Sarah Béquet

Archietcte Data Microsoft, les maîtres mots sont : performance, industrialisation, méthodologie & bonne humeur.

Derniers articles

2019.12.02BI & Big Data Etude des contextes en DAX (4/4) : Transition de contexte
2019.11.25BI & Big Data Etude des contextes en DAX (3/4) : Contexte de requête
2019.11.17BI & Big Data Etude des contextes en DAX (2/4) : Contexte de filtre
2019.11.11BI & Big Data Etude des contextes en DAX (1/4) : Contexte de ligne

Articles liés

Retain CR/LF : Retours chariot et sauts de ligne dans SQL Management Studio 2016

DELETE ou UPDATE en masse (patterns)

Parallélisme SSIS par package

sp_help_revlogin : Copie des logins d’une instance à l’autre

Blog