SQL Server : Les 10 meilleurs secrets d’un expert SQL Server

La préservation d’un environnement SQL Server peut s’avérer complexe. Voici les 10 principales méthodes à adopter pour réduire la complexité et atténuer le stress.

Paul S. Randal

Au cours de ces dernières années, nombre d’entreprises ont comprimé leurs services informatiques. Plusieurs administrateurs de base de données (DBA) se sont retrouvés chargés d’un grand nombre de bases de données SQL Server. Pire encore, souvent aucun DBA n’est désigné. Une personne se trouve nommée spontanément ou de fait DBA. Dans certains cas, le DBA finit se trouver dans un état d’urgence, naviguant d’un problème à un autre. Ce type d’environnement est difficile, nuisible et insoutenable. Personne ne peut supporter d’être soumis à un stress et une interruption permanents.

Une solution relative à une telle situation : consacrer un minimum de temps à la rationalisation de votre environnement SQL Server, afin d’en simplifier la compréhension et la gestion. En me basant sur les services de conseil que j’ai fournis, voici les 10 meilleures méthodes par lesquelles un DBA SQL Server peut prendre le contrôle de son environnement, tout en réduisant le risque global relatif à l’apparition des situations de crise. Les éléments de cette liste sont approximativement classés par ordre croissant d’importance.

10. Inventorier

Combien de fois vous a-t-on demandé de restaurer des données endommagées sur une base de données dont vous ne soupçonniez même pas la présence ? Les bases de données SQL Server peuvent facilement s’étaler dans toute une entreprise. L’équipe DBA peut se perdre dans le fil des événements et finit par se retrouver avec des instances de SQL Server non gérées. Cette situation se produit au sein des bases de données qui ne sont pas sauvegardées, qui ne sont dotées d’aucun correctif, qui ne sont pas correctement sécurisées et qui sont absentes d’un hôte de tâches de gestion nécessaires.

Il est primordial de disposer d’un inventaire des instances et des bases de données présentes au sein de l’entreprise qui soit à jour et maîtrisé. C’est le seul moyen de les gérer correctement, d’effectuer des consolidations en cas de besoin, ainsi que de définir l’étendue et planifier les projets et les mises à niveau. Il vous permet également de limiter vos responsabilités en publiant une liste énumérant les instances connues, dont vous assumez la responsabilité, avec l’accord des différentes équipes de votre entreprise. Vous pouvez élaborer des stratégies de support relatives aux instances connues et exiger que les nouvelles instances soient en adéquation avec les instructions de votre configuration, avant de les prendre en charge.

Un grand nombre d’outils vous permettent de créer un inventaire SQL Server, qu’il s’agisse d’outils simples, tels que SQLPing3 et SQLRecon, ou du Microsoft Assessment and Planning Toolkit et du Quest Discovery Wizard.

9. Standardiser les configurations

Si le nombre de bases de données et d’instances SQL dont vous êtes responsable ne cesse d’augmenter, vous saurez que le nombre de configurations différentes s’accroît de façon similaire. Il est extrêmement difficile de travailler de manière efficace lorsque vous devez aller d’instance en instance et que vous devez constamment vous remémorer les détails relatifs à la configuration de chaque instance.

La solution consiste à standardiser votre configuration de la manière la plus étendue possible, en termes de lettres d’unité, d’options de configuration de serveur, de paramètres et de maintenance de bases de données, de paramètres de sécurité et de bien d’autres encore. SQL Server 2008 a ajouté la fonctionnalité de gestion basée sur les stratégies pour définir et appliquer ces dernières. Lara Rubbelke, un spécialiste SQL Server de Microsoft, a également mis en place l’infrastructure Enterprise Policy Management (EPM), dotée d’une facilité d’extension des fonctionnalités vers les instances SQL Server 2005 et SQL Server 2000. Vous pouvez trouver l’infrastructure EPM sur CodePlex. La figure 1 présente un exemple de rapport de l’infrastructure EPM.

Figure 1 Rapport de l’infrastructure Enterprise Policy Management

8. Comprendre le sous-système E/S

Différents facteurs liés au sous-système E/S sont susceptibles d’affecter vos instances SQL Server. Vous devez avoir connaissance de ces facteurs et de leurs conséquences potentielles :

• Capacité d’un sous-système E/S en termes de débit de lecture/écriture et d’espace disque. Il doit être capable de gérer une charge de travail maximale et des demandes similaires, tout en mettant à disposition un espace destiné à accueillir un volume de données à accroître, avant que vous vous retrouviez dans l’obligation d’acheter plus de capacité. Vous pouvez équilibrer la charge en identifiant les goulots d’étranglement E/S et en déplaçant les données et/ou les fichiers journaux vers les autres parties du sous-système.
• Capacités de redondance des sous-systèmes E/S en termes de niveau RAID et sa capacité d’effectuer des actions telles que des sauvegardes en miroir partagé, ou tout autre type de mise en miroir/réplication (au niveau du sous-système E/S, non au niveau SQL Server). Il est important de protéger vos données et vos fichiers journaux des défaillances du lecteur ou de tout autre éventuel problème. Il s’agit souvent d’un compromis : RAID-10 propose une meilleure redondance que RAID-5, mais à un prix plus élevé. Pour des conseils supplémentaires, lisez le livre blanc « Conception de la base de données physique ».
• Le sous-système E/S est configuré correctement en termes de taille de l’agrégat RAID, de taille d’unités d’allocation NTFS/cluster et d’alignement de partitions. Pour de plus amples détails, consultez sur le blog le message suivant : « Les décalages de la partition, la taille de l’agrégat RAID et les unités d’allocation NTFS de votre disque sont-ils correctement définis ? ».

7. Créer un plan de maintenance personnalisé

Lors de mes cours de maintenance de base de données, je commence toujours par dire : « Vous ne pouvez pas simplement mettre une base de données dans une production et vous en tenir là. ». Les index se fragmentent au fil du temps et entraînent une dégradation de la performance. Les statistiques sont périmées, induisant des plans de requête de mauvaise qualité et à une pauvreté de performance. Les sous-systèmes E/S peuvent être corrompus et le besoin de sauvegardes persiste.

Vous pouvez résoudre tous ces problèmes en établissant un plan de maintenance complète adapté à vos bases de données. Un plan personnalisé surpasse de très loin un plan générique qui ne répond pas de façon satisfaisante à vos besoins. Dans le numéro du mois d’août 2008 de TechNet Magazine, la rubrique « Astuces pour une maintenance de base de données efficace » offre une méthode pour l’établissement d’un bon plan de maintenance. Le meilleur point de départ à l’élaboration de votre propre plan de maintenance est le script complet et gratuit d’Ola Hallengren. C’est ce que je recommande à mes clients.

6. Garantir la sécurité de votre système

Consacrer du temps à la détection proactive des problèmes de sécurité est primordial dans le cadre de la prévention de différents incidents que vous devrez résoudre plus tard. Une autre de mes rubriques dans TechNet Magazine : « Problèmes courants liés à la sécurité de SQL Server et solutions » énumère les 10 problèmes relatifs à la sécurité les plus courants et la façon de les éviter. De même, n’oubliez pas de rester à jour dans l’application de correctifs sur vos systèmes dès que vous détectez une vulnérabilité.

5. Être en bons termes avec les développeurs

Un des plus grands domaines de friction au sein d’un service informatique se trouve souvent entre l’équipe de DBA et l’équipe de développement. D’une manière générale, les deux groupes n’ont pas une compréhension mutuelle des priorités et des soucis de chacun, qu’il s’agisse des délais relatifs au développement ou des décisions relatives à la conception SQL Server. Une différence d’opinions au sujet des problèmes de comportement et de performance, ainsi que des responsabilités entourant le déploiement et la prise en charge, est relativement courante.

Vous pouvez effectuer un travail sans heurts en vous associant de façon proactive et productive avec l’équipe de développement. L’organisation de sessions éducatives mutuelles constitue une bonne solution, en particulier lorsqu’elles sont entreprises de manière non accusatoire. Menez des analyses avec une personne présente de l’équipe DBA et testez correctement le code avant qu’il ne soit introduit dans la production et vous éviterez probablement des erreurs susceptibles de dégrader les relations internes entre les équipes.

4. Développer une stratégie de récupération après sinistre

Qu’importe l’efficacité de la sécurité de votre infrastructure, vous devez disposer d’un plan de contingence en cas d’incidents. Il vous est impossible de prévoir l’altération, les pannes de courant, les incendies et les pertes accidentelles de données ou une multitude d’autres problèmes. Vous devez, en revanche, avoir un plan de gestion et de récupération après de tels incidents.

Collaborez avec la direction pour déterminer les temps d’arrêt et les contrats de licence de logiciel relatifs aux pertes de données de vos bases de données, planifiez la récupération de ces données suite à divers types de pertes, déterminez la manière dont toutes les instances SQL sont représentées dans le plan de continuité d’activité de votre entreprise. Étudiez l’importance relative de toutes les bases de données et les instances. Vous pouvez ainsi hiérarchiser la récupération après sinistre.

Vous devez également implémenter certaines technologies vous permettant de définir le moment où les problèmes se produisent, tels que le contrôle de page, les vérifications de cohérence, les alertes de l’agent SQL et les alertes System Center Operations Manager. Cette infrastructure de récupération après sinistre vous permet de protéger vos données à l’aide de sauvegardes, de copie de journaux, de réplication et de mise en miroir de la base de données. Elle vous permet éventuellement de basculer vers un système redondant à l’aide de la mise en miroir de la base de données, ou de basculer le clustering. Deux livres blancs peuvent vous aider à cet effet : « Disponibilité élevée avec SQL Server 2008 » et « Proven SQL Server Architectures for High Availability and Disaster Recovery ».

3. Effectuer et tester régulièrement les sauvegardes

Qu’importe la qualité de votre disponibilité élevée et de votre planification de récupération après incident, vous ne pouvez pas négliger d’effectuer des sauvegardes régulières de vos bases de données. En cas de destruction ou d’altération fatale de votre base de données, il se peut votre unique recours soit une restauration à partir du jeu de sauvegardes dernièrement effectuées. Si vous ne disposez donc d’aucune sauvegarde, votre entreprise peut en ressentir les conséquences. Vous ne devez pas seulement effectuer des sauvegardes, vous devez également procéder de façon régulière à la restauration à partir de celles-ci. Ainsi, vous avez la garantie qu’elles fonctionneront en cas de besoin.

De plus amples informations sont à votre disposition dans deux des articles rédigés par mes soins pour le TechNet Magazine, en 2009 : « Présentation des sauvegardes SQL Server » et « SQL Server : Récupération suite à des sinistres à l’aide de sauvegardes.”

2. Surveiller et entretenir la performance

L’optimisation des performances occupe la plus grande partie du temps d’un DBA, mais il existe différentes manières de rationaliser la procédure :

• Définissez des références de performances pour que vous puissiez voir si la performance a effectivement changé.
• Démontez votre système en primitives que vous pouvez mesurer séparément, à l’abri des incertitudes issues de facteurs externes.
• Utilisez la méthode de temps d’attente et de files d’attente afin de détecter rapidement les problèmes liés à la performance.
• Surveillez les performances à l’aide des primitives système, des compteurs de performance et des statistiques d’attente. De cette manière, vous pourrez déterminer le moment où la performance commence à se dégrader. Utilisez la fonctionnalité Collecteur de données de la performance, présente dans SQL Server 2008, ainsi que le tableau de bord SQL Server 2005.
• Élaborez un plan de maintenance.
• Élaborez et appliquez soigneusement votre stratégie d’indexation à l’aide des outils, tels que l’Assistant de paramétrage du moteur de base de données ou les Vues de gestion dynamiques (DMV) des index manquants et le DMV de l’utilisation des index.

1. Savoir où aller pour obtenir plus d’informations

Avec une liste de tâches sans fin, il est primordial que vous ayez connaissance du moment que vous pouvez abandonner toute tentative et demander de l’aide. Vous devez connaître vos limites et accepter qu’il subsiste des éléments que vous ne connaissez pas concernant SQL Server. Vous n’aboutirez à rien en vous entêtant et perdrez un temps précieux, alors qu’une autre personne peut vous aider dans l’accomplissement de votre tâche ou à résoudre votre problème.

Votre source primaire d’informations relatives à SQL Server est la Documentation en ligne de SQL Server, que vous pouvez télécharger et installer localement, ou bien par une recherche en ligne sur MSDN. La Documentation en ligne de SQL Server est très utile quant à la recherche syntaxique. Cependant, si votre question touche à un sujet nécessitant un approfondissement ou si elle tente de résoudre un problème, le meilleur moyen est encore de poser une question sur le forum en ligne. Il existe un grand nombre de forums SQL Server sur MSDN et des sites de communauté célèbres tels que SQL Server Central.

Un autre moyen rapide d’obtenir de l’aide consiste à recourir à la communauté SQL Server sur Twitter. Publiez votre question avec une balise #sqlhelp. Cette balise est surveillée de près par nombre d’experts SQL (dont je fais partie).

Assistez à une conférence spécifique SQL Server telle que le Sommet annuel de la communauté, les conférences semestrielles SQL Server Connections ou les SQL Saturdays (plus fréquents). Suivez certains des blogs tenus par les experts de la communauté SQL Server. Vous pouvez avoir une idée des blogs actifs à suivre à partir des comparaisons de blogs, tenus par mon collègue MVP, Thomas LaRock.

Il se peut que vous soyez surchargé ou dépassé, mais si vous pouvez fournir un effort supplémentaire et travailler en suivant ces suggestions, vous ne pourrez que constater les indéniables avantages. Vos systèmes fonctionneront avec plus de fluidité, vous serez mieux organisé et plus tranquille : votre compétence en tant que DBA en sera améliorée.

Paul S. Randalest directeur général de SQLskills.com, directeur régional Microsoft et MVP SQL Server. Il a travaillé sur le moteur de stockage de données de SQL Server chez Microsoft de 1999 à 2007. Il 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 d'urgence, de la haute disponibilité et de la maintenance de base de données, il anime fréquemment des conférences. Vous trouverez son blog à l’adresse SQLskills.com/blogs/paul et sa page Twitter à l’adresse Twitter.com/PaulRandal.

Contenu associé

• Forum aux questions sur SQL : Maintenance des journaux et des index
• Forum aux questions sur SQL : Déplacement, performances, réglage, sauvegarde et mise en miroir de bases de données
• Forum aux questions sur SQL : Astuces sur la récupération suite à un endommagement, conseils sur la réduction de base de données et bien plus encore