Bases de données système de MS SQL Server

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 :

USE master;
GO
CREATE PROC dbo.sp_pi
AS
BEGIN
SELECT 355.0/113.0 AS PI
END;
GO
USE tempdb;
GO
EXEC sp_pi;
PI
---------------------------------------
3.141592

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 :

-- Obtenir la version de la base resource :
SELECT SERVERPROPERTY('ResourceVersion');
-- Obtenir la dernière mise à jour de resource :
SELECT SERVERPROPERTY('ResourceLastUpdateDateTime');
-- Obtenir la définition d'un objet :
SELECT OBJECT_DEFINITION(OBJECT_ID('sys.objects'));
-- ou bien
SELECT * FROM sys.system_sql_modules WHERE object_id = OBJECT_ID('sp_help');

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 :

CREATE DATABASE mssqlsystemresource;
vous renvoie l'erreur suivante :
Msg 4616, Niveau 16, État 1, Ligne 1
Vous ne pouvez pas effectuer cette opération pour la base de données de ressources.

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 * * * * *

2 réflexions au sujet de « Bases de données système de MS SQL Server »

  1. Avatar de sqlprosqlpro Auteur de l’article

    En pratique il est extrêmement rare de perdre la base model. Je n’ai jamais vu cela.
    Néanmoins, dans un tel cas il me parait plus facile de reprendre la base model d’un autre serveur de même version, en faire une sauvegarde et restaurer. D’autant que dans la plupart des cas, comme on ne développe pas sur le serveur de production, il y a toujours un serveur de développement ou de test disponible… !

    A +

  2. Avatar de dbaffaleufdbaffaleuf

    Hello Frédéric,

    Quand tu dis qu’il est inutile de sauvegarder model, je ne suis pas tout à fait d’accord. Si tu perds model, sqlservr ne peux pas créer tempdb au démarrage et s’arrête:

    2007-02-26 14:33:10.74 spid9s Error: 17204, Severity: 16, State: 1.
    2007-02-26 14:33:10.74 spid9s FCB::Open failed: Could not open file C:Program FilesMicrosoft SQL ServerMSSQL.1MSSQLDATAmodel.mdf for file number 1. OS error: 2(Le fichier spécifié est introuvable.).

    2007-02-26 14:33:10.82 spid9s Error: 945, Severity: 14, State: 2.
    2007-02-26 14:33:10.82 spid9s Database ‘model’ cannot be opened due to inaccessible files or insufficient memory or disk space. See the SQL Server errorlog for details.
    2007-02-26 14:33:10.82 spid9s Could not create tempdb. You may not have enough disk space available. Free additional disk space by deleting other files on the tempdb drive and then restart SQL Server. Check for additional errors in the event log that may indicate why the tempdb files could not be initialized.
    2007-02-26 14:33:10.84 spid9s SQL Trace was stopped due to server shutdown. Trace ID = ‘1’. This is an informational message only; no user action is required.

    Si tu dois la recréer, il faut repasser la moulinette DVD avec REINSTALL=SQL_Engine REBUILDDATABASE=1, remonter tes bases systèmes à partir de sauvegardes et rattacher tes bases utilisateur, alors que si tu as un backup, il te suffit de démarrer en -T3608 et de remonter le backup de model.

    David B.
    [dbaffaleuf@capdata.fr]
    [www.capdata.fr – http://www.alldb.fr/blogs

Laisser un commentaire