Best Practices SQL Server

La simplicité peut n'être qu'une apparence. Microsoft SQL Server est un moteur de bases de données relationnelles qui permet de créer votre première base dès l'installation terminée. Cette apparente simplicité du produit, quelques clics de souris, vous fait croire que votre installation est correcte et sans faille … Il en va tout autrement en réalité...

Les apparences sont trompeuses

Malheureusement, les apparences sont trompeuses. Une installation nécessite la prise en compte de Best Practices en fonction d'éléments tels que :

• vos besoins présents tout en anticipant l'avenir
• votre configuration physique ou virtuelle
• votre type de stockage
• votre configuration réseau

Cette approche vous garantira une pérennité et sera moins coûteuse qu'une réorganisation complète demandant dans la plupart des cas un arrêt de production.

La sécurité

Une installation MS SQL Server commence par ses fondations représentées par le système d'exploitation Windows. Dans la mesure du possible, le serveur fera partie d'un annuaire Active Directory où un compte de service dédié à MS SQL sera créé pour mieux gérer la sécurité. Pourquoi ne pas déléguer la sécurité à l'Administrateur Système en utilisant des Groupe AD au sein de votre instance SQL ?

Le choix de l'édition : une de ses conséquences

Le choix de l'édition Standard et Entreprise est important, ce n'est pas la peine d'installer une seule instance SQL Standard sur un serveur disposant de 32 coeurs car seuls 4 d'entre eux seront utilisés à cause de la limite fixée par l'édition Standard.

 Choix des ressources disques

Lors de l'installation, les ressources disques sont importantes tant pour la sécurité de vos bases de données que pour les performances.

Pour la sécurité:

• Sur le disque C: réservé au système d'exploitation et aux programmes partagées MS SQL
• Sur le disque D: moteur SQL + Bases systèmes master, model, tempdb, msdb, distribution
• Sur le disque E: bases de données utilisateurs, fichiers .mdf et .ndf
• Sur le disque F: journaux de transactions, fichier .ldf et celui de la base tempdb
• Sur le disque G: les sauvegardes locales

Ceci est un exemple type. On peut encore améliorer en ajoutant une ressource pour les index et d'autres pour y stocker des partitions. Mais comme précisé plus avant, ceci s'organise.

Pour les performances:

• Il faut utiliser des groupes de fichiers pour les données, d'autres pour les index, les partitions, constitués de plusieurs fichiers secondaires n'excédants pas la taille de 32 Go. En procédant ainsi, le moteur écrira les informations par bande.
• Pour des bases de taille conséquente, il sera ainsi possible de sauvegarder une base par groupes de fichiers plutôt que de sauvegarder la base de données complète.
• Attention aux informations que vous pouvez trouver sur Internet sur la possibilité de créer la base avec un seul fichier .mdf de 800 Go voir supérieur à 1 To. Oui, c'est possible, mais il faut plutôt rester raisonnable surtout quand on mélange les tables, les vues, les index, les objets systèmes, … dans un seul et même fichier !

Etanchéité

Lorsque votre instance est créée, il y a des actions post-installation importantes à mener pour la rendre "étanche". Les éditeurs, ne connaissant pas la configuration des serveurs de leurs clients, vont s'appuyer sur la base système model pour créer les leurs. Si cette base model n'est pas configurée en fonction de vos exigences, le résultat ne correspondra pas à celui attendu. De nombreux clients utilisent seulement les 2 fichiers principaux qui constituent une base de données minimale : le fichier .mdf et le fichier .ldf. Il faut savoir que le fichier Master Data File (mdf) contient les informations systèmes de sa propre base comme les objets (tables, vues, index, …), les utilisateurs et leurs droits associés, … Créer vos propres objets dans ce fichier revient au même que si vous créez des tables directement dans la base système master de l'instance … Il est recommandé de créer des groupes de fichiers pour les objets utilisateurs.

Type d'utilisation de la base

En fonction de l'utilisation de l'instance en mode OLTP, (transactions courtes de type insert, update, delete, merge, select, …) ou OLAP, pour le décisionnel, les paramètres ne seront pas les mêmes, il faudra les adapter à partir de votre cahier des charges.

Backup et Recover

Il existe 2 méthodes principales pour récupérer une base de données:

• Le mode SIMPLE, qui ne garantit la récupération de données qu'à partir de la dernière sauvegarde. Dans ce cas, vous autorisez la perte de données saisies depuis la dernière sauvegarde en cas de crash.
• Le mode FULL, qui garantit la récupération complète de la base de données en cas de crash.

En fonction du mode de récupération, les procédures de sauvegarde ne seront pas les mêmes. Beaucoup pensent que le Backup Full effectue une sauvegarde de la base de données ainsi que du journal de transaction, c'est faux, il ne prend en charge que la base de données, c’est-à-dire le contenu des fichiers .mdf et .ndf. C'est pour cette raison qu'il est fréquent de trouver un log de 1 Go pour une base de 300 Mo… Pour garantir le maintien des informations en cas de crash, les transactions contenues dans le fichier .ldf ne pourront être purgées qu'à l'issue d'un BACKUP LOG et non d'un BACKUP DATABASE. Si vous trouvez dans les procédures de sauvegarde le passage du mode de récupération FULL à SIMPLE pour le vider puis de SIMPLE à FULL pour sécuriser à nouveau la base de données, c'est que la personne n'a pas compris le principe et risque la perte de données en cas de crash !

PRA

Pour les solutions de PRA, les procédures de sauvegardes méritent une attention plus importante encore. Par exemple, il ne faut pas se limiter à mettre en oeuvre les procédures de sauvegardes que sur le serveur actif d'un miroir SQL. En effet, en cas de bascule vers le serveur de secours, ces procédures de sauvegardes ne seront pas présentes sur ce serveur . Par contre, si ces procédures de sauvegarde sont bien présentes sur les deux serveurs, pensez à les configurer de telle sorte qu'ils ne génèrent pas d'alertes à partir du serveur passif en raison du statut des bases de données sur ce serveur : en mode RECOVERY et donc inaccessibles.

Best Practices

Il n'est jamais trop tard pour se remettre sur les rails des "Best Practices". Souvent, celles-ci ont évité le remplacement de la configuration matérielle qui n'aurait fait que repousser les problèmes de performances dans le temps au lieu de les corriger. ----------------------------------------------------------------------------------------------------------------------------------- Digora possède une large expérience sur la mise en oeuvre et l'administration de Microsoft SQL Server. Vous souhaitez en savoir plus sur sur ce sujet ? Contactez-nous ici pour de plus amples informations. Index thématique du Blog Digora