http://windowsitpro.itpro.fr/Dossiers-par-Theme/2007/5/28/050542819-SQL-SERVER-101-:-Concepts-essentiels-pour-administrateurs-Windows.htm Commençons par le commencement L’essentiel de SQL Server Bases de données. Tables. Index. Vues. Procédures stockées et déclencheurs. Marquez une pause Dans les PME qui n’ont pas d’administrateur de base de données (DBA, database administrator) à plein temps, c’est à l’administrateur Windows qu’il incombe de gérer les systèmes Microsoft SQL Server : de quoi intimider quiconque n’est pas familiarisé avec ce produit. Si vous êtes un débutant en SQL Server et si vous ne savez pas par où commencer son apprentissage, ne cherchez pas plus loin. Cette série en deux parties vous donnera les connaissances essentielles nécessaires pour gérer efficacement un système SQL Server pour le compte d’un service ou d’une petite entreprise. Dans cet article, je fournis quelques informations de démarrage indispensables et explique les composantes de base de SQL Server. Dans la deuxième partie, à paraître dans un prochain numéro de Windows IT Pro, je présenterai quelques outils indispensables pour gérer SQL Server et indiquerai des pistes pour créer de bonnes stratégies de sécurité SQL Server et de sauvegarde de base de données. Commençons par le commencement Pour mettre en place un système SQL Server, il faut commencer par bien dimensionner la mémoire. En effet, les systèmes base de données se nourrissent de RAM, et SQL Server ne fait pas exception. Le minimum doit être de 512 Mo pour un système départemental. Pour de plus grands systèmes, il en faudra beaucoup plus. Compte tenu du prix actuel de la RAM, il est facile d’ajouter de 1 à 2 Go de RAM à votre système SQL Server sans assécher le budget. L’investissement d’aujourd’hui en RAM supplémentaire peut éviter de futurs problèmes de performances qui coûteront très cher en temps d’immobilisation et donc en perte de productivité. Microsoft a voulu que SQL Server 2000 soit facile à installer et à exécuter. On pourrait même dire que l’installation est un peu trop facile. Ainsi, en acceptant les paramètres par défaut proposés au moment de l’installation, vous aurez un système médiocrement performant. Par défaut, l’installation de SQL Server crée les fichiers log et les fichiers de données de la base de données sur le même lecteur. Or, votre système SQL Server sera bien plus à l’aise si ces fichiers se trouvent sur des lecteurs différents. Par conséquent, la première chose à faire après avoir installé SQL Server, est de mettre vos fichiers de données et vos fichiers log sur des lecteurs différents. Bien entendu, pour cela, il faut que le système SQL Server ait suffisamment de lecteurs. Vous aurez vérifié ce point au préalable. Au minimum, il faut trois lecteurs : un pour l’OS, un pour les fichiers de données et un pour les fichiers log (fichiers de journalisation). La figure 1 montre une configuration de lecteurs classique pour une installation de SQL Server en PME. Dans cet exemple de système à huit lecteurs, l’OS, les fichiers de données et les fichiers log sont tous sur des lecteurs séparés et les fichiers OS et log utilisent le mode miroir pour assurer la redondance des données. Les fichiers de données utilisent RAID 5 (data striping ou segmentation des données) pour obtenir un stockage plus efficace. Pour obtenir une protection des données maximale, vous pourriez utiliser à la place RAID 1 (mirroring) pour vos lecteurs de données. Mais cette solution est beaucoup plus chère que RAID 5 parce que le mirroring demande deux fois plus d’espace disque que vous n’en utilisez actuellement, pour permettre la duplication des lecteurs en miroir. Les lecteurs SCSI surclassent les lecteurs IDE et, en bonne logique, les lecteurs plus rapides sont aussi plus performants. Pour une meilleure récupération, songez à appliquer le mode miroir aux fichiers log. Une configuration classique d’installation de SQL Server consiste à utiliser RAID 1 pour les volumes disque qui contiennent des fichiers, et RAID 5 pour ceux qui contiennent des fichiers de données. Vous pouvez y ajouter un autre lecteur consacré aux traces ou autres opérations de diagnostic. Enfin, pour soigner la performance et la sécurité, il vaut mieux installer SQL Server sur un serveur membre, pas sur un DC (domain controller). Le type d’authentification souhaité est la prochaine considération importante dans la mise en place d’un système SQL Server. SQL Server accepte deux types d’authentification : l’authentification Windows et l’authentification SQL Server (dite aussi mode mixte). Sous l’authentification Windows, SQL Server vérifie les références de login entrante par rapport aux comptes utilisateur Windows. Sous l’authentification SQL Server, vous devez créer et maintenir un jeu séparé de logins dans SQL Server. Bien que chacun de ces deux types ait ses avantages et ses inconvénients, l’authentification Windows sera généralement préférable, si on peut l’appliquer. Elle vous permet de ne maintenir qu’un jeu de mots de passe, et les applications qui se connectent au système SQL Server n’ont pas besoin de transmettre l’information de login dans la chaîne de connexion. Windows maintient toutes les références de login. Enfin, quoique vous fassiez, il faut absolument donner un mot de passe puissant au compte sa. Beaucoup d’attaques dirigées contre SQL Server sont expressément conçues pour profiter d’un compte sa sans mot de passe. Ne laissez pas le mot de passe sa vierge et n’utilisez pas non plus des valeurs faciles à deviner comme sa ou motdepasse. L’essentiel de SQL Server Il vous sera probablement plus facile de gérer SQL Server quand vous aurez compris son principe de fonctionnement. SQL Server est livré avec quatre bases de données système – master, model, msdb et tempdb – et deux bases de données utilisateur. La base de données master est probablement la plus importante des bases de données système. Elle contient des tables qui décrivent toutes les autres bases de données du système et qui contiennent aussi l’information de login et de sécurité. La base de données model, comme son nom l’indique, sert de modèle pour toutes les nouvelles bases de données. Toute nouvelle base de données créée sur le serveur hérite des paramètres de la base de données model. La base de données msdb est utilisée par SQL Server Agent pour stocker l’information de planification des jobs et aussi pour maintenir l’information de sauvegarde et de réplication. La base de données tempdb stocke les tables de travail temporaires. Les objets présents dans tempdb ne durent que pendant le temps de connexion de l’utilisateur qui les a créés. Les deux exemples de bases de données utilisateur sont Pubs et Northwind. Pubs est une petite base de données de publications d’auteurs contenant des informations sur un groupe d’auteurs, leurs livres et leurs éditeurs. La base de données Northwind est similaire à la base de données Northwind livrée avec Microsoft Access. Elle contient des exemples d’information de commandes et de ventes d’une société fictive appelée Northwind Traders. Bien que Northwind ne soit pas très grande comparé à la taille d’une base de données SQL Server classique, elle est plus grande que la minuscule base de données Pubs. Si l’on ne vous demande que de gérer un système SQL Server et pas de créer de nouvelles bases de données, il est probable que vous n’aurez pas besoin d’apprendre les détails de création d’objets base de données. En principe, votre service IT ou votre fournisseur d’applications les créera. Toutefois, une bonne compréhension des objets base de données centraux - bases de données, tables, index, vues, procédures stockées et déclencheurs – vous sera précieuse. Bases de données. Les bases de données contiennent l’information que les applications utilisent. Les bases de données SQL Server contiennent des collections de tables, de vues, d’index et de procédures stockées. Chaque application est généralement conçue de manière à se connecter à sa propre base de données. Un système SQL Server unique peut supporter jusqu’à 32 767 bases de données par serveur. Les bases de données SQL Server peuvent atteindre de grandes tailles : le maximum est de 1 048 516 To. Chaque base de données doit comporter au moins deux fichiers : un fichier de données et un fichier log. Le fichier de données contient l’information de table, ligne et colonne, stockée dans la base de données. Le fichier log contient toutes les opérations de transactions (INSERT, UPDATE et DELETE) que les utilisateurs ou applications exécutent sur la base de données. Comme mentionné précédemment, pour obtenir des performances optimales, il ne faut jamais placer les fichiers de données et les fichiers log sur le même lecteur sur les systèmes de production. De même, il ne faut pas placer les fichiers de données et les fichiers log sur des lecteurs compressés ou cryptés. Pendant la création des bases de données, SQL Server utilise une copie de la base de données model comme gabarit pour la nouvelle base. Vous pouvez spécifier une marge de croissance maximale pour la base de données, en méga-octets ou en pourcentage de sa taille. Mais, pour la plupart des installations, il vaudra mieux choisir l’option auto-grow, qui laisse la base de données croître toute seule en fonction des besoins. Si vous connaissez bien votre application, vous pouvez prévoir la croissance de la base de données et la dimensionner en conséquence, afin que SQL Server évite d’utiliser auto-grow pendant les périodes de production, avec pour effet de diminuer temporairement la performance. Tables. La table est l’unité de base de stockage pour toutes les bases de données relationnelles. Les tables contiennent un ensemble d’informations associées. Par exemple, chaque ligne d’une table client contient toute l’information concernant un client donné. En principe, il s’agit du numéro, nom, adresse et information de contact du client. Chaque élément d’information (par exemple, numéro client) est logé dans une colonne et chaque colonne est définie pour ne contenir qu’un certain type de donnée. Ainsi, la première colonne dans la table Customer pourrait être nommée CustomerID et être définie en int, signifiant qu’elle ne peut stocker que des entiers. La deuxième colonne, CustomerName, pourrait être définie en varchar(40), signifiant qu’elle peut stocker jusqu’à 40 caractères de données texte. Index. La principale utilité des index est de rendre les requêtes plus performantes. Les index sont construits sur certaines colonnes dans une table. SQL Server utilise deux types d’index : en cluster et sans cluster. Un index en cluster détermine l’ordre des données dans la table. Quand un index en cluster est créé sur une table, SQL Server dispose les lignes dans la table de base, conformément à l’ordre défini dans l’index en cluster. Chaque table ne peut avoir qu’un index en cluster. Les index sans cluster ne réordonnent pas les données dans la table de base ; ils fournissent simplement un autre chemin d’accès optimisé conduisant aux données. Vous pouvez supprimer ou ajouter des index sans affecter le modèle de base de données de départ. Bien qu’un index serve surtout à accélérer l’extraction de données, trop d’index peuvent avoir l’effet inverse. Quand un index est construit, SQL Server doit le maintenir (autrement dit, le tenir à jour). Quand des données sont ajoutées aux tables d’une base de données, le moteur SQL Server doit non seulement ajouter les données à la table mais aussi mettre à jour les index existants avec les nouvelles données. On l’imagine aisément, plus on a créé d’index et plus cette mise à jour est longue. Un trop grand nombre d’index risque de ralentir la performance du système. Il existe une technique courante pour améliorer le traitement batch : elle consiste à écrire un script qui supprime les index affectés avant qu’une procédure batch ne commence, puis à écrire un autre script qui recrée les index une fois la procédure terminée. SQL Server 2000 fournit un Index Tuning Wizard qui analyse les requêtes et suggère les index susceptibles d’améliorer la performance des requêtes. Vues. Une vue est comme une table virtuelle ou une requête stockée. Les données qui sont accessibles par l’intermédiaire d’une vue ne sont pas stockées dans un objet base de données discret. On crée une vue en utilisant une instruction SQL SELECT. Quand il accède à une vue, l’utilisateur voit le résultat de cette instruction SELECT. Les vues servent généralement à restreindre les utilisateurs à un sous-ensemble de lignes ou de colonnes dans une ou plusieurs tables. Vous pouvez aussi utiliser des vues pour joindre des tables multiples, les faisant ainsi apparaître comme une seule table. Procédures stockées et déclencheurs. Les procédures stockées, que vous créez en utilisant le code T-SQL compilé, sont la colonne vertébrale de la plupart des applications base de données. Les déclencheurs sont un type spécial de procédure stockée qui peut être attaché à une table. Comme les procédures stockées sont compilées, elles offrent une meilleure performance que SQL dynamique (c’est-à-dire, le code SQL qu’un programme génère avant que le code SQL ne s’exécute). Quand SQL dynamique est exécuté sur le serveur, le moteur base de données analyse d’abord syntaxiquement l’instruction pour s’assurer que la syntaxe est valide. Ensuite le serveur construit un plan d’accès aux données. Les procédures stockées laissent SQL Server effectuer ce travail au moment où la procédure stockée est créée, plutôt qu’à l’exécution, ce qui confère aux procédures stockées un avantage de performance à l’exécution par rapport à SQL dynamique. SQL Server met en cache les requêtes de manière très intelligentes et il met même en cache le SQL dynamique ad hoc pendant un certain temps, mais rien ne dit que le SQL dynamique sera encore en cache lors de sa prochaine utilisation. Comme les procédures stockées, vous utilisez T-SQL pour créer des déclencheurs. Mais, contrairement aux procédures stockées, exécutables librement par tout utilisateur qui en a la permission, les déclencheurs ne sont exécutés que par la base de données elle-même. On attache les déclencheurs à une table en utilisant l’instruction CREATE TRIGGER, et ils ne s’exécutent que quand une action INSERT, UPDATE ou DELETE a lieu sur la table. Les DBA utilisent couramment des déclencheurs pour aider à imposer la DRI (Declarative Referential Integrity). Vous pouvez aussi disposer en cascade les opérations DELETE de la table référencée vers les lignes associées dans la table référençante. Le code déclencheur ne s’active qu’après que SQL Server ait vérifié les contraintes, y compris DRI. Marquez une pause Il est temps de prendre un peu de recul et d’absorber ce que vous venez d’apprendre sur SQL Server 2000. Parvenus à ce stade, vous avez compris les principes de base de dimensionnement de la RAM et de détermination de la configuration disque sur un système SQL Server. Vous êtes aussi familiarisés avec les principales composantes de SQL Server. Tenez-vous prêts pour la deuxième partie de cet article, où je révèlerai d’autres particularités de SQL Server, pour que votre job de DBA à temps partiel soit plus facile.