Forum aux questions sur SQLAu cœur de la mise en clusters, des mises en suspens mystérieuses, du compte administrateur système et plus encore

Par Nancy Michell

Q : J’ai besoin de mieux comprendre le fonctionnement des clusters. Notre environnement comprendra Windows Server® 2003 64 bits exécutant SQL Server™ 2005, un groupe de serveurs SSRS (SQL Server Reporting Services) autrement dit un déploiement horizontal de serveurs de rapports, un serveur de catalogue TempDB SSRS et SQL Server qui extrait des données d’une base de données tierce à l’aide d’un serveur lié et enregistre les données pour SSRS.

Nous souhaitons un cluster à 3 nœuds actif/actif/passif. Le nœud Node1 serait actif et stockerait les données extraites de la base de données tierce. Le nœud Node2 serait actif et stockerait le catalogue SSRS. Le nœud Node3 serait passif et servirait de basculement pour l’un des deux autres nœuds. Avez-vous des conseils ?

R : Malheureusement, les termes actif/actif et actif/passif en référence aux clusters SQL Server sont trompeurs pour beaucoup de personnes. Elles pensent que les clusters SQL prendront en charge l’évolution d’une base de données ou de l’instance SQL sur plusieurs serveurs. Ce n’est pas le cas. Sur SQL Server, il n’y a pas de bases de données ou d’instances actives/actives. Une « instance » est une installation de SQL Server avec des bases de données correspondantes. Notre mise en clusters par instance de SQL Server est toujours active (1) à passive (n) ; notez que la valeur de n est comprise entre 1 et 7 en fonction de votre version de SQL Server. C’est pour cette raison que l’on parle de clusters de basculement.

Une fois cela compris, les gens peuvent commencer à envisager l’installation de plusieurs instances de clusters de basculement sur une série de nœuds. Ainsi, trois serveurs physiques utilisant tous des disques partagés pourraient avoir une instance active par défaut sur le nœud 1 et une seconde instance active par défaut sur le nœud 2 et tous deux peuvent basculer sur le nœud 3. Les instances sont complètement distinctes ; elles ne partagent pas de données et ne sont pas actives/actives. Elles sont toutes les deux actives/passives et ont la même instance de basculement. Si les deux instances basculent sur le nœud 3, le problème sera de savoir si ce nœud sera à la hauteur. Le basculement a été conçu pour s’appuyer sur une puissance de traitement égale pour le basculement. Si la charge maximale requière deux nœuds pour un traitement en fonctionnement normal, il est peu probable que le nœud 3 supporte la charge maximale attribuée normalement à deux nœuds.

Ceci dit, étant donné le coût relatif du matériel capable d’exécuter un cluster, nous comprenons que les gens se disent que la probabilité que les deux nœuds principaux tombent en panne simultanément et forcent la charge entière à passer à un nœud est faible. Forts de cela, ils peuvent choisir de courir le risque plutôt que d’acheter une capacité de basculement à 100 %.

Heureusement, voici la bonne nouvelle : SQL Server 2005 offre beaucoup plus d’options pour la haute disponibilité, notamment des alternatives qui peuvent basculer plus vite qu’un cluster et peuvent même signifier une duplication des données (la mise en clusters s’appuie sur un seul réseau SAN). Les options incluent la mise en miroir, la réplication égal à égal et plus encore. Avec ces nouvelles alternatives, nous avons beaucoup plus d’options pour répondre à toutes sortes de besoins, y compris ceux combinant un certains nombres de fonctionnalités haute disponibilité.

L’Assistant Microsoft® Cluster Configuration Validation (ClusPrep), désormais disponible au téléchargement, remplace ce qui était le test de la Liste de certification matérielle (HCL), qui pouvait prendre des mois pour valider une configuration complète pour l’estimer comme « pouvant être prise en charge » en présence de clusters. Ceci met l’outil de validation de matériel entre les mains de l’administrateur de base de données et permet d’économiser (du temps et de l’argent) pour mettre en place le matériel certifié. Il peut même être possible de valider et déployer un matériel hétérogène dans un groupe de nœuds de cluster.

Q : Une procédure de suppression sur l’un de mes ordinateurs a l’air de se mettre en suspens au bout de 12 heures. Elle n’est pas bloquée. En observant le plan de requête le plus lent, on constate qu’un déclencheur s’exécute pendant 87 327 secondes, ce qui laisse supposer que la procédure est mise en suspens dans ce déclencheur. Comment savoir exactement quelle instruction est mise en suspens ?

R : Il est tout à fait possible qu’une boucle au sein du déclencheur ne se ferme pas pour un tas de raisons. Si vous êtes mis en suspens pendant un bon moment et que vous souhaitez savoir quelle instruction s’exécute, exécutez le code de la figure 1. Il vous dira quelle instruction est en cours d’exécution et il devrait s’agir de celle qui entraîne la mise en suspens de votre ordinateur.

Figure 1 Rechercher l’instruction en cours d’exécution

-- Look at the current statement being run:
-- Put results to text (Ctrl + T)
DECLARE @Handle binary(20), 
        @start int, 
        @end int,
        @SPID int

SET    @SPID = spid

SELECT @Handle = sql_handle, 
        @start = stmt_start, 
        @end = stmt_end 
FROM Master..sysProcesses(NOLOCK) 
WHERE SPID = @SPID

IF NOT EXISTS (SELECT * FROM ::fn_get_sql(@Handle))PRINT ‘Handle not found in cache’
ELSE
   SELECT ‘Current Statement’= substring(text, (@start + 2)/2, CASE @end WHEN -1 THEN (datalength(text))
       ELSE (@end -@start + 2)/2 END)
       FROM ::fn_get_sql(@Handle)

Q : J’ai besoin de prendre en charge la réplication transactionnelle par le biais d’un pare-feu. L’éditeur et le distributeur sont à l’extérieur du pare-feu et l’abonné se trouve à l’intérieur. L’écoute de l’abonné est définie sur 1433 et voici les noms de mes ordinateurs : Éditeur : PUBMACHINE, Distributeur : DISTMACHINE, Abonné : SUBMACHINE. Quels ports dois-je ouvrir pour faire en sorte que la capture instantanée initiale et l’émission de données de la publication réussissent ?

R : Si vous utilisez un abonnement par émission de données, l’ouverture du port SQL Server (1433 dans votre cas) devrait suffire comme l’agent de distribution s’exécutera sur l’ordinateur du distributeur (à l’extérieur du pare-feu) et aura probablement un accès local aux fichiers de captures instantanées générées par l’agent de capture instantanée. En revanche, si vous utilisez un abonnement par extraction de données, l’agent de distribution exécuté sur l’ordinateur de l’abonné devra trouver un moyen de franchir le pare-feu pour accéder aux fichiers d’instantanés. Voici les options que vous pouvez envisager.

Si l’on présume que les fichiers de captures instantanées sont déjà accessibles depuis un partage de fichiers à l’extérieur du pare-feu, vous pouvez ouvrir le(s) port(s) de partage de fichiers Windows® via le pare-feu pour que l’agent de distribution s’exécutant à l’intérieur du pare-feu puisse accéder aux fichiers d’instantanés situés à l’extérieur (méfiez-vous toutefois des implications de sécurité que ceci peut avoir pour d’autres parties de votre infrastructure). Notez que si vous avez configuré un chemin local comme emplacement par défaut des captures instantanées (SSMS par défaut), vous devrez peut-être utiliser l’option /AltSnapshotFolder de l’agent de distribution pour remplacer l’emplacement de collecte des fichiers de captures instantanées.

Vous pouvez également configurer la réplication pour utiliser FTP pour le transfert de fichiers de captures instantanées (vous devrez dans ce cas ouvrir le port 21).

Q : J’aimerais savoir s’il y a des inconvénients à désactiver le compte administrateur système dans SQL Server 2005 et si la désactivation de l’administrateur système ajoute une véritable valeur de sécurité. Un livre blanc a-t-il été consacré à ce sujet ?

R : Sur une nouvelle installation de SQL Server 2005, lorsque le mode mixte n’est pas activé, le compte administrateur système est désactivé par défaut et un mot de passe aléatoire est généré pour lui. Vous pouvez également le désactiver vous-même. Aucun livre blanc n’est paru à ce sujet, mais la désactivation et les affectations de nouveaux noms aux ouvertures de sessions sont abordées dans un document recensant les méthodes recommandées.

Si vous voulez vous protéger contre les tentatives de violation du compte administrateur système, vous pouvez aussi le renommer. N’oubliez pas cependant que, si vous activez un compte désactivé, il est recommandé de définir un nouveau mot de passe pour ce compte.

Pour répondre à votre question en ce qui concerne la sécurité, souvenez-vous que la sécurité complémentaire qu’apporte la désactivation du compte provient du fait qu’il serait vain d’essayer de deviner le mot de passe lorsque le compte est désactivé. Quel que soit le délai dont dispose un pirate ou un virus informatique, une attaque en force contre un compte verrouillé ne réussira pas. Le fait de donner un nouveau nom ou de désactiver l’administrateur système endommagera des applications dont la connectivité dépend de l’utilisation du compte administrateur système. Il est toujours essentiel de trouver et réparer ou éliminer ces applications. Comme mentionné précédemment, le compte ne peut pas être utilisé pour se connecter à la base de données tant qu’il n’a pas été réactivé. De plus, comme le processus d’authentification échoue plus tôt, une tentative échouée aura moins de conséquences sur le système attaqué.

Q : L’une des mes plus grandes bases de données OLTP (traitement des transactions en ligne) a un fichier journal qui fait deux fois la taille du fichier de données. J’ai essayé d’utiliser les commandes suivantes pour réduire la taille du fichier journal, mais il doit encore être réduit :

backup database syslogs to backupfile
DBCC SHRINKFILE (syslogs_log)

R : Vous devez modifier votre base de données de sauvegarde en une instruction de journal de sauvegarde. Vous pouvez aussi placer votre base de données dans le mode de récupération simple et émettre l’instruction shrinkfile. Une fois le journal réduit, configurez la base de données sur son modèle de récupération précédent et sauvegardez-la. S’il ne réduit toujours pas, assurez-vous qu’aucune transaction n’est ouverte (utilisez dbcc opentran). L’article de la base de connaissances suivant fournit plus d’informations : support.microsoft.com/kb/907511.

Q : Si le basculement survient pendant une tâche de l’agent SQL Server planifiée, qu’arrive-t-il à cette tâche après le basculement ? Dois-je la relancer manuellement ?

R : Oui, il vous faudra la relancer manuellement si vous n’avez pas un autre processus en place. Si vous ne voulez pas avoir à relancer des tâches manuellement, vous pourriez écrire un script qui mette à jour une table une fois la tâche achevée. Si la valeur est égale à 1, la tâche a été exécutée ; toute autre valeur indique que la tâche n’a pas été exécutée et une deuxième tâche sera exécutée ultérieurement et lancera la commande de démarrage. Donc, si la tâche doit être réexécutée dans l’éventualité d’un basculement survenant pendant son exécution, en écrivant un script, vous pouvez atténuer une part de l’inquiétude concernant ces tâches nocturnes essentielles qui doivent absolument être exécutées avant la prochaine journée de travail.

Conseil : Mise à niveau et DBCC UPDATEUSAGE

Réalisez-vous une mise à niveau à partir de SQL Server 2000 vers SQL Server 2005 ?

Si c’est le cas, veillez à exécuter DBCC UPDATEUSAGE juste après la mise à niveau de vos bases de données.

DBCC UPDATEUSAGE signale et corrige les pages et les inexactitudes dans les nombres de lignes dans les affichages du catalogue. Ces inexactitudes doivent être corrigées parce qu’elles peuvent entraîner des erreurs dans les rapports d’utilisation de l’espace renvoyés par la procédure sp_spaceused stockée sur le système. Dans SQL Server 2005, ces valeurs sont toujours gérées correctement, donc ces bases de données ne doivent jamais faire l’objet d’erreurs de nombres. Cependant, les bases de données mises à niveau vers SQL Server 2005 peuvent contenir des nombres non valides, c’est pourquoi il est nécessaire d’exécuter DBCC UPDATEUSAGE après la mise à niveau.

Voici comment fonctionne la commande DBCC UPDATEUSAGE. Elle corrige les lignes, les pages utilisées, les pages réservées, les pages feuilles et les nombres de pages de données pour chaque partition dans une table ou un index. S’il n’y a pas d’inexactitudes dans les tables système, DBCC UPDATEUSAGE ne renvoie aucune donnée. Si des inexactitudes sont trouvées et corrigées et que vous n’avez pas utilisé WITH NO_INFOMSGS, DBCC UPDATEUSAGE renvoie les lignes et les colonnes mises à jour dans les tables système.

La commande DBCC UPDATEUSAGE peut également être utilisée pour synchroniser des compteurs d’utilisation de l’espace. Étant donné que l’exécution de la commande DBCC UPDATEUSAGE peut prendre un certain temps sur de grandes tables ou bases de données, il est recommandé de l’utiliser uniquement lorsque vous suspectez que des valeurs inexactes sont renvoyées par sp_spaceused. Notez que sp_spaceused accepte un paramètre facultatif pour exécuter DBCC UPDATEUSAGE avant de renvoyer des informations sur l’espace pour la table ou l’index.

DBCC CHECKDB a été amélioré dans SQL Server 2005 pour détecter le moment où le nombre de pages ou de lignes devient négatif. Lorsqu’une valeur négative est détectée, DBCC CHECKDB émettra un avertissement et une recommandation d’exécution de DBCC UPDATEUSAGE pour résoudre le problème. Bien qu’il puisse sembler que la mise à niveau de la base de données vers SQL Server 2005 soit à l’origine de ce problème, soyez assuré que les nombres non valides existaient avant la procédure de mise à niveau.

À titre d’exemple, voici comment mettre à jour les nombres de pages ou de lignes ou les deux pour tous les objets de la base de données actuelle. La commande suivante spécifie 0 pour le nom de la base de données et DBCC UPDATEUSAGE renvoie des informations mises à jour pour la base de données actuelle :

DBCC UPDATEUSAGE (0);
GO

Pour mettre à jour les nombres de pages ou de lignes ou les deux pour, disons, AdventureWorks, et également pour éliminer des messages d’information, vous exécuteriez une commande similaire à la suivante, qui spécifie AdventureWorks comme nom de la base de données et élimine ensuite tous les messages d’information :

USE AdventureWorks;
GO
DBCC UPDATEUSAGE (‘AdventureWorks’) WITH NO_INFOMSGS; GO

Pour plus d’informations, recherchez DBCC UpdateUsage dans la documentation en ligne de SQL Server.

Thanks to the following Microsoft IT pros for their technical expertise: Ken Adamson, Sunil Agarwal, Siggi Bjarnason, Shaun Cox, Laurentiu Cristofor, Ernie DeVore, Michael Epprecht, Lucien Kleijkers, Raymond Mak, Chat Mishra (MSLI), Niraj Nagrani, Rick Salkind, Jacco Schalkwijk, Vijay Sirohi, Vijay Tandra Sistla, Matthew Stephen, and Buck Woody. Thanks to Saleem Hakani for this month's tip.

© 2008 Microsoft Corporation et CMP Media, LLC. Tous droits réservés. Toute reproduction, totale ou partielle, est interdite sans autorisation préalable.