Bonnes pratiques que tout DBA devrait connaître

Juste parce que cela ne fait pas de mal de rappeler certaines règles élémentaires, voici un petit traité (en français) des bonnes pratiques du DBA.

Le DBA ne doit jamais oublier qu'il est le protecteur des informations de l'entreprise.
C'est un serment qu'il fait lorsqu'il accepte la casquette de DBA.

1. Installer et mettre à jour SQL Server
2. Configuration générale du Serveur
3. Les bases de la sécurité
4. Propriétés de SQL Server
5. Configuration de la mémoire
6. Gestion des fichiers de données et de log (MDF, LDF)
7. Gestion de Tempdb
8. Propriétés de la DB
9. Configuration des Jobs - recommendations générales
10. Ne pas tronquer les fichier (Don’t Shrink Files)
11. Les jobs pour reconstruire/reorganiser les indexes (Create Index Rebuilding/Reorganize Job)
12. Un job pour détecter la corruption de donnée
13. Définir des alertes pour les erreurs critiques
14. Implémenter une stratégie Backup/Restore
15. Créer un "Disaster Recovery Plan"
16. Tout documenter
17. Tout tester

1. Installer et mettre à jour SQL Server
D'une façon générale, lorsque l'on installe une nouvelle instance de Sql Serveur.

• Utiliser les drivers matériel les plus récents.
• Utiliser la version de l'OS la plus récente avec le dernier service pack.
• Utiliser la version de SqlServeur la plus récente avec le dernier service pack et tous les Hotfix.
• Tester le tout.
  Une fois stable, être très prudent lorsqu'il faut effectuer des changements.

Lorsque l'on veut faire une mise à jour d'une instance existante de Sql Serveur.

• Ne surtout pas faire de mise-à-jour à moins d'avoir une bonne raison de le faire. Si votre instance fonctionne bien, ne prenez pas le risque de la déstabiliser en modifiant les composants logiciels.
• Par exemple, une mise à jour peut s'envisager lorsque: l'utilisation d'une nouvelle fonctionnalité est nécessaire, que vous avez des problèmes avec l'installation actuelle, que vous avez besoin de faire une mise-à-jour du matériel.
• Il est toujours plus sûr de faire une mise-à-jour vers un nouveau serveur matériel (ayant un nouvel OS et/ou nouvelle version de sql serveur) que de faire une mise-à-jour directement sur le matériel de production.
  Cela permet de faire efficacement des tests et offre l'opportunité de revenir en arrière en cas de problèmes.

2. Configuration générale du Serveur

• Idéalement, les instances de Sql serveur devraient tourner sur un serveur dédicacé (physique ou virtuel) et sans qu'aucun autre logiciel ne fonctionne sur la machine.
• Eviter l'exécution de multiples instances sur un même serveur (à moins d'avoir une bonne raison de le faire).
  Préférer l'option de la virtualisation à l'exécution de multiples instances.
• Les services SQL Serveur non utiles doivent être désactivés ou désinstallés.
• Ne pas exécuter localement d'antivirus ou Anti Spyware sur le serveur SQL (cas il représente un frein énorme qui dégrade fortement les accès matériel).
  Si un tel logiciel est néanmoins obligatoire, pensez à exclure les fichiers MDF, LDF, BAK et TRN.

3. Les bases de la sécurité

• Ne jamais donner aux utilisateurs plus de droits/permissions que ce qui est nécessaire pour effectuer leurs tâches. Cela semble simple et logique mais c'est souvent compliqué dans les faits. Cela n'en reste pas moins un point critique.
• Ne jamais utiliser le compte SA pour quoi que ce soit!
  Lui assigner un mot de passe complexe et le garder sous la main au cas de situation difficile. Privilégier l'utilisation d'un compte de domaine qui est membre du role SysAdmin.
• Ne pas autoriser les applications à utiliser le compte SA ou role SysAdmin pour accéder aux données.
• Lorsque cela est possible, utiliser l'authentification Windows pour se connecter à SqlServer. Cela a surtout du sens pour les équipes de développements.
• Ne jamais donner un accès au rôle SysAdmin aux consultants.... personne n'est à l'abri d'une erreur, surtout dans un environnement qu'il ne connait pas. Il est d'ailleurs courant que les consultant emporte des mots de passes et informations sensibles sur leur cahier de note (raison de plus d'être restrictif).
  Il convient néanmoins de faire une exception pour les contractants qui effectuent des tâches de maintenance.
• Bloquer sa station ou clôturer sa connexion sql lorsque l'on quitte son poste.

4. Propriétés de SQL Server

• Ne jamais modifier les paramètres par défaut d'une instance de Sql Serveur (1*) ... à moins que vous compreniez exactement les implications de ce que vous faite!
  Même documenté, un détail de ce genre peut passer inaperçu lors d'une migration ou d'une remise en production dans des conditions d'urgences.

1*: Voici quelques exemples de propriétés Sql Serveur: Memoire, processeurs, sécurité, connections, paramètres DB, paramètres avancés, permissions.

5. Configuration de la mémoire

• Privilégier des versions 64 bits pour le hardware, l'OS et Sql Serveur.
• En mémoire 64 bits, activer l'option "Lock pages in memory" et laisser SqlServeur gérer dynamiquement sa mémoire (la version 2008 est très efficace).
• Pour une version 32 bits de Sql Serveur sur un serveur ayant au moins 4Gb de mémoire, assurez vous que le switch /3GB est actif et que la mémoire AWE est correctement configurée. Un configuration correcte dépend de la quantité de mémoire disponible.

6. Gestion des fichiers de données et de log

• Eliminer la fragmentation physique (défragmenter le disque) avant de créer de nouveaux MDF et LDF.
• Allouer un espace suffisant aux fichiers MDF et LDF nouvellement créés, cela minimisera les événements AutoGrowth.
• Les fichiers MDF devraient être localisés sur leurs propres disques.
• Les fichiers LDF devraient être localisés sur leurs propres disques.
• Les fichiers de backups (BAK et TRN) doivent être localisés sur leurs propres disques physiques (et donc même pas le même SAN que les MDF et LDF)

Instant File Initialization

• Activer l'option "Instant File Initialization". Cela permet de créer ou d'agrandir des fichiers MDF plus rapidement. L'espace alloué n'est alors plus remplis avec des 0 avant d'être utilisé.
  NB: les fichier LDF ne sont pas concernés par cette option.
• Améliorera la performance des opérations: CREATE DATABASE, RESTORE DATABASE, ALTER DATABASE, AutoGrowth.
• Nécessite au moins SqlServer 2005/2008 et Windows 2003/2008.

Note:
Instant file initialization is turned on if the SQL Server (MSSQLSERVER) service account has been granted the SE_MANAGE_VOLUME_NAME permission by adding the account to the Perform Volume Maintenance Tasks security policy.
Members of the local Windows Administrator group automatically have this right.

7. Gestion de Tempdb

• Donner une taille correcte à TempDB, cela évitera les évènement AutoGrowth. Par défaut, la taille de TempDB est de 8Mb ce qui est très peu.
• Fixer l'AutoGrowth à une taille fixe plutôt que les 10% par défaut. Cela minimisera les événements AutoGrowth car 10% génère beaucoup d'évènements.
• Placer la TempDB sur sont propre disques car SQL serveur y a souvent recourt pour y stoquer des résultats temporaires.
• Sur un serveur à forte activité, considérer de séparer la TempDB dans plusieurs fichiers physiques (1*).
  Si possible utiliser des devices sql différents, cela permettra au noyau Sql d'envoyer plusieurs demande de traitement en parallèle (cela stress plus les IO mais augmente également le temps de réponse générale.

1*: Les fichiers physiques doivent avoir la même taille.
Le nombre de fichiers répond à la formule Nbre_CPU_Core/4  (jusque Nbre_CPU_Core/2) avec un maximum de 8 fichiers.

8. Propriétés de la base de données

• Ne jamais modifier les propriétés d'une base de données à moins d'avoir une excellente raison de le faire.
• Auto Create Statistics: Actif
• Auto Update Statistics: Actif
• Auto Shrink: désactivé
• Autogrowth: Laisser Actif (habituellement pour éviter les erreurs).
  Filegrowth L'accroissement de la taille des fichiers devrait être géré manuellement. Sinon, utiliser un AutoGrowth de taille fixe pour minimiser les évènements AutoGrowth.
• Recovery Mode: Full
  Utiliser l'option "full" pour toutes les DB de production ainsi, il est possible de faire un backup du transaction log.
• Page Verify: Checksum (2005/2008)
• Compatibility Level: devrait être la version courante de Sql Serveur... a moins qu'il existe un problème de compatibilité.

9. Configuration des Jobs - recommendations générales

• Si le serveur n'a pas de Job configuré alors il y a un problème car tous les serveurs ont besoins de jobs.
• Planifierl'exécution des Jobs en heures creuses (comme cela ils n'interfèrent pas avec la production).
• Eviter que l'exécution des Jobs se chevauchent.
• Utiliser les alertes pour être notifier des jobs qui ont échoués.
• Vérifier l'exécution des jobs journellement (pas de plantage, exécution anormalement longue, ...).
• Utiliser le "Maintenance Plan Wizard" avec prudence. Une mauvaise utilisation peut facilement créer des jobs qui dégradent les performances du serveur Sql.

10. Ne pas tronquer les fichier
Don’t Shrink Files

• Si la taille des fichiers MDF et LDF sont correctement configurés, alors il n'est pas nécessaire de les tronquer.
• Ne jamais planifier de job tronquant les fichiers (ou les DB).
• Si vous devez malgré tout tronquer une base de donnée:
  
  • Faite le manuellement (ex en utilisant aussi le script sp_force_shrink_log)
  • Reconstruisez les indexes après l'opération.
  • Planifiez cette opération en heure creuse.

Ne pas tronquer les fichiers offre quelques bénéfices:

• Elimine le syndrome "Growth and Shrink".
• Réduit la fragmentation physique des fichiers.
• Libère les ressources utilisées pour ces opérations très couteuses en I/O.

11. Les jobs pour reconstruire/reorganiser les indexes
Create Index Rebuilding/Reorganize Job

• Les indexes ont besoin d'être reconstruit ou réorganisés régulièrement pour minimiser la fragmentation et réduire les espaces perdu.
• Reconstruire an index s'il est fortement fragmenté (>30%).
  Sql Serveur Enterprise Edition peut effectuer cette tâche online.
  Cette opération met automatiquement les statistiques à jours (donc plus besoin de le faire)
• Reorganiser l'index s'il n'est pas fortement fragmenté ( 5% < frag% < 30%).
  Cette opération consomme peu de ressource et peut toujours être conduite online. Cependant il faut cependant faire une mise à jour des statistiques manuellement
• Reconstruire ou réorganiser uniquement les indexes qui en ont besoins.
  La vue sys.dm_db_index_physical_stats permet d'identifier de tels indexes.
  L'article "Performance - Fragmentation des indexes" présente un script permettant d'ailleurs de faire ce tri.

Ressources:

• L'article "Performance - Fragmentation des indexes" présente un script permettant d'évaluer la fragmentation des indexes et de préparer un script a exécuter pour remédier au problème.
• Index Defrag Script est un script très complet avec plein d'options permettant de faire un traitement très pointu de défragmentation des indexes.

12. Le job pour détecter la corruption de donnée

• Idéalement, l'exécution de DBCC CHECKDB devrait être exécuté aussi souvent que possible.
• Si un problème existe, il est préférable de le détecter aussi tôt que possible pour réduire le risque de perte de donnée.
  Ne pas utiliser l'option DBCC CHECKDB Repair a moins de pleinement en comprendre les implications.
• Créer un job qui permet exécute la commande suivante (ou similaire)
  DBCC CHECKDB ('DATABASE_NAME') WITH NO_INFOMSGS, ALL_ERRORMSGS;
• Pour les serveurs de production vraiment chargé ou les grosses DB, il est possible d'utiliser l'option PHYSICAL_ONLY, cela réduit le temps d'exécution.

13. Définir des alertes pour les erreurs critiques

• Créer un "Sql Server Event Alert" pour tous les évènements ayant une sévérité supérieure ou égale à 19 (fatale).
• Que les alertes soient envoyées à la personne adéquate!
  Vous en tant que DBA ou la personne responsable du monitoring journalier.
• Considérer l'usage d'un outil spécifique si les Alertes sql ne rencontre pas vos besoins.

14. Implémenter une stratégie Backup/Restore

• Créer un Job qui effectue un backup journalier des DB de production et un backup du transaction log toutes les heures (ou similaire).
• Si le Recovery Model est de type Bulk ou Full, vous devez impérativement faire des backups du transaction log (sinon ce dernier atteindra assez vite une taille incontrôlable).
• Utiliser la commande "RESTORE WITH VERIFYONLY" pour vérifier l'intégrité du backup. Attention, cela ne garanti pas de bons backups.
• Tester périodiquement les backups.
  Faire un restore périodique des backups pour s'assurer qu'ils sont bien utilisable.
• Mettre en place une politique de rétention pour les backups.
• Stocker les backup dans un endroit sûr et hors du site.
• Ne pas stocker les backups sur le même disque array que les DB , ni sur le même SAN.
• Penser à utiliser "backup compression" pour gagner du temps ou de l'espace disque... particulièrement utile dans un environnement de production fort sollicité.

15. Créer un "Disaster Recovery Plan"

• Il est impératif de créer une document qui résume, étape par étape, mais aussi en détail, comment récupérer votre serveur Sql ou DB en cas de problème.
  Le document doit couvrir tous les scénarios incluant petit et grand problèmes.
• Il est important de s'entrainer à utiliser le plan afin de s'y familiariser et de pouvoir facilement le mettre en oeuvre.
• Garder le numéro de téléphone du support produit de Microsoft sous la main.
• Souvenez-vous que: La plupart des incidents sont mineurs, telle la corruption de DB. Les grands incidents (désastres) se produisent très rarement, voir jamais. Mais dans tous les cas, vous devez être préparé aux deux.

16. Tout documenter

• Nous le savons tous, la documentation c'est barbant et rare sont les personne qui apprécient faire de la documentation (sauf peut-être moi?).
  Pourtant la documentation est un élément critique pour un DBA.
  Les éléments suivants doivent être documentés:
  
  • L'installation et la configuration de chaque instance.
  • L'installation et la configuration de chaque application qui utilise le serveur SQL comme back-end.
  • Les tâches de dépannages. Comme cela, il n'est pas nécessaire de réinventer la roue si un problème se produit encore.
  • Chaque fois qu'un changement (même mineur) est effectuer sur n'importe quelle instance de Sql Serveur... et ce quelque soit la raison.

• S'assurer que la documentation est facilement disponible et que toute personne susceptible de l'utiliser puisse y avoir accès.

17. Tout tester

• Avant de faire un quelconque changement sur un serveur de production, assurez-vous toujours que cette modification ait été préalablement testé dans un environnement de test... sans exception!

Source:
Le contenu de cet article est issus de la présentation Best Practices Every SQL Server DBA Must Know.pdf parue sur SqlServeur Central.
Merci à de Brad M. McGehee pour cet excellent document.