Forum aux questions sur SQL&Erreurs d'E/S, mise en miroir de base de données et bien plus encore

Paul S. Randal

Q : J’ai commencé à effectuer régulièrement des vérifications de cohérence des bases de données que je gère et j'ai même ajouté certaines alertes de l'Agent SQL pour intercepter toutes les erreurs d'E/S que rencontrent les requêtes utilisateur. Je ne sais pas si la logique que j'ai implémentée pour les vérifications et les alertes fonctionnera car aucune de mes bases de données ne montre de problèmes d'altération. Comment puis-je créer une altération dans une base de données de test afin de m'assurer que tout ce que j'ai configuré fonctionne correctement ? Par ailleurs, puis-je faire quelque chose d'autre pour détecter les erreurs d'E/S ?

R : Dans SQL Server® 2000, la vieille astuce pour créer une base de données altérée pour le test consistait à supprimer une ligne de la table sysindexes d'une base de données de test. Mais avec SQL Server 2005, l'altération d'une table système d'une telle manière est très difficile. La meilleure façon d'altérer une base de données de test est d'utiliser un éditeur hexadécimal pour modifier un fichier de données pendant que la base de données est arrêtée. Voici comment faire :

• Arrêtez la base de données afin que les fichiers de données ne soient pas verrouillés. (Veillez toutefois à ne pas détacher la base de données, car si vous altérez la mauvaise page, il est possible que ne puissiez pas la rattacher).
• Choisissez un décalage de disons 100 pages dans le fichier (au moins 819 200 octets), mais assurez-vous qu'il est aligné sur une limite de 8 192 octets (une limite de page). Ceci évite les pages de métadonnées essentielles et les bitmaps d'allocation, ce qui vous permet de démarrer la base de données et d'exécuter DBCC CHECKDB sur celle-ci.
• Écrivez quelques octets de zéros dans le fichier au décalage choisi. L'utilisation de cette technique garantit presque dans tous les cas l'introduction de certaines erreurs d'altération d'en-tête de page.

Ceci dit, la façon la plus rapide de créer une base de données de test altérée est d'en utiliser une créée par quelqu'un d'autre. Vous trouverez des exemples de bases de données SQL Server 2000 et SQL Server 2005 altérées (avec des explications) sur mon blog (go.microsoft.com/fwlink/?LinkId=115151).

En ce qui concerne votre deuxième question, ayant trait à ce que vous devez faire pour détecter les erreurs d'E/S, vous devez activer les sommes de contrôle de pages. Cette fonctionnalité a été introduite dans SQL Server 2005 afin de protéger une page de base de données entière des erreurs qui ont été introduites par le sous-système d'E/S.

En gros, lorsqu'une page est enregistrée sur le disque, la dernière chose effectuée par SQL Server est le calcul d'une somme de contrôle de l'intégralité des 8 ko de la page et la mise en place de la somme de contrôle sur la page. Lorsqu'une page est lue à partir du disque, si elle possède une somme de contrôle de page, la somme de contrôle est recalculée et comparée à celle qui est enregistrée sur la page. Si elles ne correspondent pas, un élément extérieur à SQL Server a altéré la page et une erreur 824 est signalée. L'erreur est affichée à la connexion qui a provoqué la lecture de la page, et consignée dans le journal d'erreurs de SQL Server et le journal des événements d'applications de Windows®.

Les sommes de contrôle de page sont activées par défaut pour toutes les bases de données créées avec SQL Server 2005 et SQL Server 2008. Cependant, elles doivent être activées manuellement pour les bases de données mises à niveau à partir des versions précédentes de SQL Server. Vous pouvez activer les sommes de contrôle de page avec ce code :

ALTER DATABASE mydb SET PAGE_VERIFY CHECKSUM;

Conseil : Changez le port par défaut de SQL Server

Par défaut, le port configuré pour les instances de SQL Server est 1433. Une fois ce port utilisé par une instance, il ne peut pas être occupé par une autre instance. Par conséquent, si vous installez une seconde instance (nommée) à l'écoute sur Internet avec tcp, elle aura besoin d'un autre port. Dans certains cas, l'administrateur peut souhaiter modifier le port pour des raisons de brouillage (bien que cette forme de brouillage soit mineure et puisse être facilement mise en échec par un scanneur de port). Bien entendu, vous devez alors configurer le client pour qu'il utilise un port différent. Il existe trois approches courantes pour effectuer ceci.

Premièrement, en supposant que l'administrateur ait changé le port d'une instance pour utiliser 5555, vous pouvez spécifier simplement le numéro de port de l'instance dans le nom de l'ordinateur auquel vous voulez vous connecter en utilisant la syntaxe MonNomDeServeur,5555. Si le port change à nouveau, les clients devront également changer leurs chaînes de connexion.

Une autre option consiste à utiliser des alias SQL Server, qui sont configurés sur le client. Outre le nom d'alias, le nom du serveur, le nom du port et un protocole doivent être spécifiés. Une fois configuré, l'alias peut être utilisé comme nom de serveur pour la connexion à l'instance de base de données. L'avantage de cette option est que les modifications de configuration de serveur peuvent être déployées par un administrateur de domaine, car les paramètres sont enregistrés dans le Registre.

La troisième option, pour les instances nommées avec lesquelles l'utilisateur connaît seulement le nom d'instance et spécifie le nom en utilisant Nomd'ordinateur\Nomd'instance dans la chaîne de connexion, consiste à utiliser le service SQL Server Browser. Ceci est déjà implémenté dans SQL Server 2000 dans un service exécuté. Cependant, dans SQL Server 2005, le service SQL Server Browser a été créé comme service séparé. Outre la découverte d'instances pour l'ordinateur, il répond également aux requêtes entrantes au protocole UDP (User Datagram Protocol) sur le port 1434, avec le numéro de port approprié pour l'instance demandée, en permettant le réacheminement pour le client et la prise en charge de connexion transparente.

—Jens K. Suessmeyer, consultant en base de données chez Microsoft

Q : Pour supprimer toute fragmentation de ma base de données, j'ai configuré un plan de maintenance nocturne qui reconstruit tous les index de la base de données de production. Il s'exécute sur SQL Server 2005 Enterprise Edition avec SP2. J'ai remarqué que ceci provoquait un accroissement excessif de la taille de la base de données et j'ai donc ajouté une étape pour la réduction de tout l'espace supplémentaire, car il n'y a pas beaucoup d'espace libre sur le disque. Il semble maintenant que l'étape de regénération ne fonctionne plus. Que se passe-t-il ?

R : Vous faites face à un problème courant auquel les gens sont confrontés lors de la mise en place d'un plan de maintenance. Vous êtes coincé dans un cycle réduction-accroissement-réduction-accroissement.

Lorsqu'un index est recréé, une nouvelle copie de l'index est créée avant que l'index existant soit supprimé. Cette procédure nécessite de l'espace supplémentaire dans les fichiers de base de données, habituellement une quantité d'espace équivalente à celle utilisée par l'index actuel. Dans SQL Server 2000, l'espace supplémentaire était également nécessaire pour trier les lignes d'index (à peu près 20% de la taille de l'index), mais cette exigence a été éliminée pour une regénération d'index simple dans SQL Server 2005.

Les administrateurs souhaitent parfois supprimer l'espace supplémentaire créé pendant la reconstruction d'index et ajoutent donc une opération de réduction au plan de maintenance après l'étape de reconstruction. Peu de gens savent cependant que cette réduction provoque également une fragmentation d'index en raison de la nature de son algorithme. Ceci signifie que l'index récemment reconstruit et défragmenté deviendra immédiatement fragmenté, en annulant l'effet de la regénération dont il a fait l'objet en premier lieu.

Étant donné que le fichier de base de données grossira de nouveau la prochaine fois qu'aura lieu une regénération d'index, il est préférable de permettre à la base de données d'avoir de l'espace supplémentaire et d'éviter entièrement l'opération de réduction. (En outre, l'accroissement et la réduction continus des fichiers de base de données provoqueront une fragmentation de fichiers au niveau du système d'exploitation, ce qui peut provoquer des performances passables, à l'image de la fragmentation d'index)

Pour terminer, il peut être souhaitable d'envisager de réduire la fréquence à laquelle vous regénérez les index. Il peut même être préférable d'utiliser une autre méthode, telle que le vieux DBCC INDEXDEFRAG que j'ai écrit pour SQL Server 2000 ou la syntaxe ALTER INDEX REORGANIZE plus récente dans SQL Server 2005 et SQL Server 2008.

Il existe un livre blanc utile qui a trait à la fragmentation d'index et fournit des recommandations pour la suppression de la fragmentation (à l'adresse go.microsoft.com/fwlink/?LinkId=115154). Bien que ce livre blanc ait été écrit pour SQL Server 2000, les concepts sont toujours les mêmes.

Q : Nous avons évalué la stratégie de récupération après incident dans mon entreprise et je pense que la mise en miroir de la base de données est l'approche à utiliser dans notre cas. Le serveur que j'essaie de protéger possède de nombreuses bases de données sans rapport (résultat d'un projet de consolidation des serveurs préalable) et j'aimerais utiliser la mise en miroir de la base de données pour toutes. La question à laquelle je m'efforce de répondre a trait au nombre de bases de données que je peux mettre en miroir avant que les performances ne commencent à se dégrader ?

R : La réponse à cette question est une réponse que j'utilise très fréquemment : Ca dépend ! Les recommandations publiées indiquent de ne pas mettre en miroir plus de 10 bases de données par instance, mais le nombre 10 est uniquement une supposition approximative du maximum qui correspondra à la plupart des utilisateurs. Vous devez prendre en compte les facteurs suivants pour votre configuration matérielle :

• quelle est la taille de mémoire dont disposent les instances principale et mise en miroir ? (dans l'idéal, elle doit être identique pour les deux)
• quelle est la configuration de processeur dont disposent les instances principale et mise en miroir ? (celle-ci doit également être identique)
• de quelle bande passante le sous-système d'E/S dispose-t-il sur l'instance mise en miroir ? (elle doit être identique à celle de la base de données principale)
• quelle est la quantité de journaux de transactions générée par la charge de travail sur chacune des bases de données ?
• quelle est la bande passante réseau disponible entre les bases de données principale et l'instance mise en miroir ?

Ces deux derniers facteurs sont les plus essentiels. Si la bande passante réseau disponible entre les deux instances n'est pas suffisante pour gérer le taux de génération combiné par seconde de journaux de transactions de toutes les bases de données en miroir, les performances chuteront alors sur les bases de données principales. SQL Server 2008 contribue à réduire une partie de ce fardeau avec la compression de flux de journaux.

L'élément le plus essentiel à prendre en compte ensuite a trait aux exigences en termes de mémoire et de threads. Chaque base de données mise en miroir nécessite un thread plus de la mémoire. Sur les serveurs de basse capacité, un nombre important de bases de données mises en miroir peut apporter une charge trop importante sur le serveur lorsqu'elle est combinée à la charge de travail régulière.

Vous devez également prendre en compte la manière dont vous exécuterez la mise en miroir des bases de données. En mode synchrone, les transactions sur la base de données principale ne peuvent pas être validées avant que tous les enregistrements de journal des transactions aient été copiés vers le journal des transactions de la base de données mise en miroir. Par conséquent, tout délai provoqué par un réseau surchargé pourrait provoquer un problème de performances de charge de travail sur la base de données principale.

En mode asynchrone, les transactions peuvent être validées sur la base de données principale sans attendre, mais un délai sur le réseau pourrait provoquer un accroissement de la quantité de journal des transactions en attente d'envoi à la base de données en miroir. Ceci peut causer des problèmes de taille de journal des transactions. Pire, tout journal des transactions non envoyé sera perdu en cas de défaillance. Par conséquent, plus la taille de journal des transactions non envoyé est importante, plus grand est le risque de perte de données dans une situation de récupération.

Les scénarios peuvent varier considérablement, et j'ai vu certains exemples intéressants dans de véritables environnements de production. Par exemple, j'ai vu un environnement avec 150 bases de données, possédant toutes une activité réduite, non simultanée. Les 150 bases de données étaient mises en miroir sans aucun problème.

Par contre, j'ai vu une configuration avec seulement trois bases de données lourdement chargées, mais sans bonne connexion réseau. Dans ce scénario, il est à peine possible d'effectuer la mise en miroir d'une base de données avant que le manque de bande passante réseau provoque une dégradation de charge de travail.

L'élément clé du succès est, avant tout, d'effectuer un calcul de la création de journaux. S'il semble que la bande passante réseau disponible pourra prendre en charge le nombre de bases de données que vous voulez refléter, cela pourrait fonctionner. Testez votre configuration avant de la mettre en production et veillez à inclure toutes les opérations qui pourraient produire des transactions de journaux des transactions, en particulier toute maintenance de base de données que vous pourriez effectuer.

Paul S. Randal est directeur général de SQLskills.COM et MVP SQL Server. Il a travaillé sur le moteur de stockage de données de SQL Server chez Microsoft de 1999 à 2007. Paul a écrit DBCC CHECKDB/repair pour SQL Server 2005 et était responsable de Core Storage Engine pendant le développement de SQL Server 2008. Expert de la récupération après incident, de la haute disponibilité et de la maintenance de base de données, Paul présente fréquemment des conférences. Vous trouverez son blog à l'adresse SQLskills.com/blogs/paul.

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