Cet article explique à quoi servent les différentes bases de données système et comment les administrer.

SQL Server contient jusqu’à 6 bases de données système. 4 sont visibles (master, model, msdb, tempdb) dans une installation standard une cinquième apparue depuis la version 2005 est invisible et inatteignable (resource). Enfin une sixième (en principe de nom distribution) apparait lorsque vous vous lancez dans la réplication.

Voici maintenant quelques détails sur ces bases.

Notez que, pour des raisons de performances, toutes ces bases sont en mode de journalisation SIMPLE, sauf pour model (full). Il est alors impossible d’en sauvegarder le journal de transaction. Ceci peut conduire à une erreur si vous utilisez les plans de maintenance standard de SQL Server et prévu la sauvegarde des journaux de transactions et des bases systèmes.
Il est aussi conseillé de dédier un disque du serveur pour y placer les espaces de stockage des bases système, notamment pour la base tempdb.

master

Elle contient la liste des bases de données, l’ensemble des comptes de connexion, les messages d’erreur et la clef maître de cryptage au niveau serveur générée lors de l’installation.
Avant la version 2005, cette base contenait aussi toutes les objets systèmes (procédures et fonctions notamment), mais ce n’est plus le cas aujourd’hui ou ces objets sont stockés dans la base resource.
La perte de master entraîne ipso facto l’impossibilité de fonctionner correctement pour SQL Server.
Il est conseillé de sauvegarder master dès que sont créé, modifié ou supprime, une base de données, un compte de connexion ou un message d’erreur. Notez que la sauvegarde de master entraîne de fait la sauvegarde de la base resource.
En cas de problème on peut restaurer la base master, mais attention, ceci n’est possible que dans certaines condition (en effet la commande de sauvegarde n’est, en principe accessible qu’à travers master !). Dans c le cas ou la commande RESTORE est inaccessible il faudra reconstruire la base master. Dans ce cas vous aurez besoin du disque d’installation initial. Lire la procédure à cette URL :http://msdn.microsoft.com/en-us/library/dd207003.aspx

Notez que vous pouvez créer votre propres procédures ou fonction dans la base master afin qu’elles puissent être appelées de n’importe quelle autre base. Pour cela il faut que vos procédure commencer par le trigramme « sp_ ».

Exemple :

Cependant, pour des raisons de performances (résolution de nom et donc recompilation) nous vous déconseillons d’utiliser cette technique.

resource

Cette base invisible et inatteignable est intimement associée à master et contient tous les objets systèmes. Avant la version 2005, cette base n’existait pas et tous les objets système était dans master ce qui posait l’inconvénient de devoir arrêter le serveur le temps de passer les correctifs aux objets système. Ce n’est aujourd’hui plus le cas, car la base resource est en fait à l’usage exclusif de master, ce qui fait qu’en cas de mise à jour par pacth des objets système aucune interruption du serveur n’est désormais plus nécessaire.
Néanmoins il est possible de se rendre compte de sa présence en allant dans le répertoire système ou sont stockées les bases à défaut (C:\Program Files\Microsoft SQL Server\2008\MSSQL10…\MSSQL\DATA – pour 2005, ou \Binn pour 2008). Vous y constaterez la présence de deux fichiers : mssqlsystemresource.ldf et mssqlsystemresource.mdf, soit respectivement le journal de transaction et l’espace de stockage des données.
Il est cependant possible de voir les objets que contient la base resource en utilisant la vue sys.system_objects et le code dans sys.system_sql_modules ou par l’intermédiaire de la fonction de méta données OBJECT_DEFINITION.
Exemple :

Vous ne pouvez pas sauvegarder spécifiquement la base resource, mais la sauvegarde de master entraine la sauvegarde de la base resource.
En cas de perte de la base resource, aucun appel d’aucune sorte à une procédure ou une fonction système ne peut se faire. Lorsque l’on sait que pour la plupart des requêtes envoyées au serveurs, de nombreux appels sont fait à des objets système, il est clair que la perte de la base resource entraîne l’arrêt immédiat du serveur.
Notez que le nom interne de cette base est « mssqlsystemresource » de telle sorte qu’un ordre comme :

model

Cette base de données est la matrice qui sert à créer toute nouvelle base dans le serveur a l’instar du fichier normal.dot de Word. A chaque fois que vous créez une base de données, l’ensemble des objets de la base model est copié dans votre nouvelle base. Cette opération nécessite de poser un verrou exclusif sur cette base. C’est pourquoi il faut s’assurer qu’aucun utilisateur n’est connecté à cette base lorsque vous créez une nouvelle base de données.
Si vous alimentez model avec des types utilisateurs, des fonctions, des procédures voire des tables et vues, alors elle seront présente dans chacune de vos bases nouvelles. C’est donc ici que vous pouvez placer des procédures ou des fonctions génériques par exemple.
De même les propriétés de votre base de données hériterons des propriété de la base model à chaque nouvelle base. Par exemple si vous modifiez le mode de journalisation de model, chaque nouvelle base créée empruntera ce mode.
Il est inutile de sauvegarder model sauf si vous y avez placé des objets.

msdb

La base msdb contient tous les éléments nécessaire au fonctionnement de l’agent SQL Server (outil de planification des travaux) : travaux, étapes, alertes, plan de maintenance, lots SSIS, processus de réplication, planification des travaux, historique de leur exécution, … Sachez que l’historique d’exécution de l’agent SQL Server est limité à 1000 lignes et au plus 100 par travaux. Cependant vous pouvez modifier tout cela dans les propriété de l’agent SQL. Mais attention à l’augmentation du volume stocké dans la base msdb.
Sauvegardez msdb aussi souvent que possible surtout si vous avez de nombreux travaux.

tempdb

Tempdb contient tous les objets temporaires explicites (ceux que le développeur créé pour ses besoins fonctionnels) et implicite (ceux que le moteur SQL créé pour effectuer certaines opérations.
Ainsi les tables temporaires créées à l’aide du préfixe # (locales) ou ## (globales) comme les variables tables, les tables résultant de l’exécution de fonction, les pseudo tables inserted et deleted des déclencheurs et les curseur déclarés comme locaux, sont créées et réellement instanciées dans cette base. Ces différentes natures de tables temporaires ne sont pas des objets mémoire comme le croient trop souvent certains développeurs et cela signifie que des opérations de disque sont nécessaire pour les faire vivre, notamment afin d’assurer l’acidité transactionnelle (écriture dans le journal de transactions de la base tempdb). Autrement dit la création d’objet temporaire entraîne un coût important.
On peut aussi créer d’autres objets comme des procédures temporaires, des certificats temporaires et encore bien d’autres choses.
Certaines opérations génèrent aussi de la donnée dans la base tempdb, comme l’utilisation du niveau d’isolation snapshot pour la gestion des transactions.
Le moteur de requête créé lui même de nombreux objets temporaires pour résoudre certaines requête (Worktable) ou effectuer les tris nécessaire à la création d’index (option « SORT_IN_TEMPDB »).

Vous comprendrez que la base tempdb peut rapidement devenir très volumineuse, notamment si vos développeurs ont développer de manière cochonne, par exemple en utilisant à outrance des tables temporaires pour stocker des résultats intermédiaires de requête, ont eut recours massive aux variables table ou encore à des fonctions table (en général les mauvais développeurs ont une tendance naturelle à tout faire sous la forme de fonction ce qui pénalise le fonctionnement du serveur, plutôt que de chercher à faire des requêtes qui l’optimise…).

De par sa nature, la base tempdb n’a pas besoin, d’être sauvegardez. En fait elle est supprimée et systématiquement reconstruite à chaque démarrage du serveur.

Vous comprenez maintenant pourquoi il est fortement conseillé de dimensionner des fichiers de grande taille pour le service de la tempdb. Par exemple si votre tempdb a atteint un volume de 3 Go, n’hésitez pas à dimensionner le fichier de données à cette valeur, voire bien au delà et le journal de transaction à un tiers du volume du fichier de données. De plus il est intéressant de placer ces fichiers et notamment celui du journal de transaction sur un disque physique séparé en privilégiant les RAID de niveau 1, 0+1 ou 10.
Cette manœuvre se fait en plusieurs étapes :
1) modification des emplacement et des tailles des fichiers de la base temdb à l’aide de la commande ALTER DATABASE
2) arrêt et redémarrage du serveur
3) suppression des anciens fichiers de tempdb devenus obsolètes

distribution

Cette base de données apparait si vous mettez en place un mécanisme de réplication et se trouve dans le serveur qui assure le rôle de distributeur, c’est à dire le serveur qui centralise les mises à jour.
Bien entendu il est nécessaire de la sauvegarder à la même fréquence que les bases de production visées par cette réplication.
Notez que le nom de cette base n’est pas forcément « distribution », car à la mise en place d’une réplication, les différents assistants de SQL Server vous propose de lui donner un nom, mais propose ce dernier. Il est cependant fortement conseillé de garder ce nom afin quelle soit facile à repérer. Mais ce n’est pas toujours possible si vous voulez réaliser différentes réplications qui doivent être fonctionnellement différentes et pour lesquelles vous avez éventuellement prévu une dissociation physique.

CONSEIL : les bases de données système sont en principes très petites en regard des bases de production. Il est alors intéressant de les sauvegarder toutes à l’exception de tempdb aussi souvent que vos bases de production. Pour cela l’utilisation d’un device simplifie la chose.
Voir les articles suivants à ce sujet :

http://blog.developpez.com/sqlpro/p6092/ms-sql-server/sauvegarder-toutes-les-bases-de-donnees/

http://blog.developpez.com/sqlpro/p5866/ms-sql-server/de-l-interet-des-devices-pour-les-sauveg/

—
Frédéric BROUARD, Spécialiste modélisation, bases de données, optimisation, langage SQL.
Le site sur le langage SQL et les S.G.B.D. relationnels : http://sqlpro.developpez.com/
Expert SQL Server http://www.sqlspot.com : audit, optimisation, tuning, formation
* * * * * Enseignant au CNAM PACA et à l’ISEN à Toulon * * * * *