Surveiller SQL Server sous Windows

Ce que vous saurez

• Le port accepte les connexions depuis l’endroit d’où vos applications se connectent. Une vérification TCP sur le port 1433, envoyée depuis une autre machine du même réseau, suit le même chemin que vos applications.
• SQL Server répond à une requête, et chaque base de données est en ligne. Un court script PowerShell sur le serveur se connecte avec le propre compte Windows de l’agent et interroge sys.databases sur chaque base de données. Une base suspecte, en attente de récupération ou en mode d’urgence est un problème. Une base encore en récupération après un redémarrage est un avertissement.

La vérification du port détecte un changement de réseau ou de pare-feu entre vos applications et le serveur. Le script détecte un serveur qui accepte les connexions sans pouvoir y répondre, et une seule base de données en difficulté alors que les autres vont bien.

Ce que cela ne vous dira pas : si les requêtes sont rapides, si quelque chose est bloqué, ni si la sauvegarde de la nuit dernière s’est exécutée. Pour les sauvegardes, voyez Vérifier les sauvegardes SQL Server sous Windows.

Avant de commencer

• La machine SQL Server, ajoutée comme hôte dans Gryphon, avec l’agent Gryphon installé et connecté : la page de l’hôte indique Connecté.
• Une fenêtre PowerShell en mode administrateur sur cette machine, et une connexion SQL Server ayant le rôle sysadmin ou securityadmin.
• Pour la vérification du port, un agent sur une autre machine du même réseau, par exemple l’un de vos serveurs d’applications.

Étapes

1Surveiller le port

Sur l’hôte d’un serveur d’applications où tourne l’agent, ouvrez Services, choisissez Ajouter le service, puis Port TCP (agent). Son Hôte est le SQL Server tel que cette machine le joint : une adresse privée, ou un nom que votre DNS interne résout.

Une connexion refusée, ou aucune réponse en 10 secondes, est un problème. Une instance par défaut écoute sur le port 1433. Une instance nommée a habituellement son propre port, indiqué dans SQL Server Configuration Manager sous Protocols → TCP/IP → IP Addresses.

Si SQL Server est censé être joignable depuis Internet, Port TCP, sans agent, le vérifie depuis les propres serveurs de Gryphon, à l’adresse publique de l’hôte. La plupart des serveurs SQL Server ne devraient pas l’être, et c’est la version avec agent qu’il faut utiliser.

2Donner une connexion à l’agent

Sous Windows, l’agent s’exécute sous le compte de service NT SERVICE\GryphonAgent. Dans SQL Server Management Studio, connecté en tant qu’administrateur, créez une connexion pour ce compte :

CREATE LOGIN [NT SERVICE\GryphonAgent] FROM WINDOWS;

C’est tout ce qu’il lui faut. Une connexion permet de se connecter, et toute connexion voit la liste des bases de données et leur état ; il n’y a donc aucune base à laquelle l’ajouter ni aucun rôle à accorder. Elle n’a pas de mot de passe, puisque c’est une connexion Windows. Installez d’abord l’agent : Windows ne connaît le compte qu’une fois son service créé.

3Activer les vérifications par script

Les vérifications par script sont désactivées tant que l’agent ne sait pas dans quel dossier les exécuter. Dans PowerShell en mode administrateur, ouvrez C:\ProgramData\Gryphon\agent.env dans le Bloc-notes (Notepad), décommentez la ligne GWC_SCRIPTS_DIR et redémarrez l’agent :

GWC_SCRIPTS_DIR=C:\ProgramData\Gryphon\scripts

Restart-Service GryphonAgent

4Ajouter le script

Créez le script dans ce dossier depuis la même fenêtre PowerShell en mode administrateur, collez le texte ci-dessous et enregistrez :

notepad C:\ProgramData\Gryphon\scripts\sql-ping.ps1

# Does SQL Server answer a query, and is every database online?
# Exit 0 healthy, 1 warning, 2 problem: the first line printed is the message.
$Server = 'localhost'   # a named instance is localhost\NAME

$ErrorActionPreference = 'Stop'
$down = @()
$recovering = @()
try {
    $conn = New-Object System.Data.SqlClient.SqlConnection
    $conn.ConnectionString = "Server=$Server;Database=master;Integrated Security=SSPI;Connect Timeout=5;Application Name=Gryphon"
    $conn.Open()
    $cmd = $conn.CreateCommand()
    $cmd.CommandTimeout = 5
    $cmd.CommandText = "SELECT name, state_desc FROM sys.databases WHERE state_desc IN ('RECOVERING', 'RECOVERY_PENDING', 'SUSPECT', 'EMERGENCY')"
    $reader = $cmd.ExecuteReader()
    while ($reader.Read()) {
        if ($reader['state_desc'] -eq 'RECOVERING') {
            $recovering += $reader['name']
        } else {
            $down += '{0} ({1})' -f $reader['name'], $reader['state_desc']
        }
    }
    $conn.Close()
} catch {
    $e = $_.Exception
    if ($e.InnerException) { $e = $e.InnerException }
    Write-Output ('Cannot query {0}: {1}' -f $Server, ($e.Message -split '\r?\n')[0])
    exit 2
}

if ($down.Count -gt 0) {
    Write-Output ('Not online: ' + ($down -join ', '))
    exit 2
}
if ($recovering.Count -gt 0) {
    Write-Output ('Still recovering: ' + ($recovering -join ', '))
    exit 1
}
Write-Output "$Server answers, and every database is online."
exit 0

Pour une instance nommée, remplacez $Server par localhost\NAME. Créez le fichier dans le dossier plutôt que de l’y copier. Un nouveau fichier prend les autorisations du dossier, que seuls les administrateurs peuvent modifier. Un fichier déplacé depuis vos Téléchargements garde les anciennes, et l’agent refuse de l’exécuter, avec un message qui indique la commande icacls qui corrige le problème.

5Ajouter la vérification par script

Sur l’hôte du SQL Server lui-même, ajoutez Script (agent) :

La première ligne de sortie du script devient le message de la vérification. Le code de sortie 0 signifie en bon état, 1 avertissement et 2 problème. Le script a 10 secondes pour se terminer, et une connexion abandonne après 5.

Faire un essai

1. Utilisez le bouton Vérifier maintenant de la vérification. Il exécute le script sous le compte de l’agent, ce qui prouve que la connexion fonctionne. Le message devrait être localhost answers, and every database is online. S’il indique Login failed for user 'NT SERVICE\GryphonAgent', revenez à l’étape 2.
2. Mettez volontairement une base de données en difficulté, dans SQL Server Management Studio : CREATE DATABASE GryphonTest; ALTER DATABASE GryphonTest SET EMERGENCY; À sa prochaine exécution, la vérification indique Not online: GryphonTest (EMERGENCY). Gryphon la refait ensuite chaque minute et alerte dès que trois résultats de suite concordent. Prévenez d’abord les personnes qui reçoivent les alertes.
3. Supprimez la base de test avec DROP DATABASE GryphonTest;. Trois résultats en bon état de suite rétablissent la vérification, et le rétablissement est annoncé.

Variantes

SQL Server Agent est-il en cours d’exécution ?

Si SQL Server Agent s’arrête, le serveur continue de répondre, mais aucune tâche planifiée ne s’exécute, sauvegardes comprises. Surveillez son service avec Vérifier qu’un service Windows est en cours d’exécution. C’est SQLSERVERAGENT pour une instance par défaut et SQLAgent$NAME pour une instance nommée.

Un SQL Server sur une autre machine

Le script peut s’exécuter sur n’importe quelle machine dotée de l’agent qui peut joindre le serveur : remplacez $Server par son nom. Sur le réseau, le compte de l’agent se connecte au nom de la machine sur laquelle il tourne ; dans un domaine, la connexion est donc le compte de cet ordinateur, par exemple CREATE LOGIN [CORP\APP01$] FROM WINDOWS;. Hors domaine, un script aurait besoin d’une connexion SQL Server et de son mot de passe ; installez plutôt l’agent sur le SQL Server lui-même.

Espace disque des lecteurs de données et de journaux

Un lecteur de journaux plein arrête une base de données. La vérification Espace disque de l’agent accepte un lecteur Windows comme Chemin à surveiller, par exemple D:\. Ajoutez-en une pour chaque lecteur qui contient des fichiers de données ou de journaux, avec vos propres niveaux Avertissement à et Problème à dans ses paramètres.