Question subsidiaire : « Combien y a-t-il de base de données système sur SQL Server ? »

 

Cette question n'est vraiment pas évidente étant donné que leur nombre est variable, la réponse est entre 4 et 6. Voici une petite liste ainsi que les explications.

 

A noter que les informations qui suivent s'adressent aux versions suivantes :

• SQL Server 7
• SQL Server 2000
• SQL Server 2005

Aux éditions suivantes :

• Enterprise & Developer
• Standard
• Workgroup
• Personal
• MSDE & Express

Les bases systèmes « standard »

« master »

Cette base de données est essentielle au moteur, puisqu'en effet celui-ci refusera de démarrer s'il ne la trouve pas ou si elle est inaccessible.

 

Dans cette base de données vous trouverez entre autres :

• Les comptes de connexion au serveur (Logins)
• La définition des bases de données utilisateurs et autres bases de données systèmes
• La majorité des procédures stockées systèmes (sp_xxxxxx)
• Les procédures stockées étendues (xp_xxxxx)

 

C'est cette base de données qui est principalement mise à jour lors de l'installation d'un Service Pack.

 

master

Bases de données systèmes

Bases de données utilisateurs

 

è Il est conseillé de sauvegarder cette base de données après l'une des situations suivantes

• Installation d'un service pack / hotfix / patch, etc.
• Création d'une nouvelle base de données
• Création de comptes de connexion sur le serveur
• Modification des paramètres systèmes du serveur
• Ajout / suppression de serveurs liés

 

à SQL Server 2005 : Sous SQL Server 2005 cette obligation de sauvegarder la base master à chaque Hotfix ou Service Pack n'est plus. En effet la base de données de ressources remplace master dans ce cas (voir dernier point).

 

à Il est recommandé de ne pas ajouter d'objets à cette base de données.

 

Fichier	Nom Logique	Emplacement	Incrément
Données	master	master.mdf	10 % jusqu'au remplissage du disque.
Log	mastlog	mastlog.ldf	10 % jusqu'à 2 To.

 

« model »

Comme son nom l'indique cette base de données sert de modèle aux autres bases, comme par exemple un modèle de document qui est copié pour servir de base à un nouveau document. Le principe est similaire puisque les fichiers de cette base de données sont copiés pour créer toute nouvelle base de données utilisateur.

 

Cette base de données est initialement vide, et n'a pas de paramétrage particulier. Tout ce qui est modifié ou créer dans cette base de données sera présent dans toute nouvelle base de données utilisateur créée après la modification dans « model ». A noter que le paramétrage des fichiers (mdf, ndf et ldf) est aussi pris en compte.

 

Les modifications apportées à « model » n'ont aucuns effets sur les bases de données déjà existantes.

 

nouvelle base de données

model

mdf

ldf

ndf

mdf

ldf

ndf

Copie des fichiers

 

Fichier	Nom Logique	Emplacement	Incrément
Données	modeldev	model.mdf	10 % jusqu'au remplissage du disque.
Log	modellog	modellog.ldf	10 % jusqu'à 2 To.

 

 

è Il est conseillé de sauvegarder cette base de données après l'une des situations suivantes

• Lors de toute modification ou ajout apporté à cette base de données

« tempdb »

Comme son nom l'indique c'est la base de données temporaire du moteur, son rôle est de stocker tous les objets temporaires du serveur.

 

Dans cette base de données vous trouverez entre autres :

• Objets utilisateur
  • Tables définies par l'utilisateur et index
  • Tables système et index
  • Tables temporaires globales et index
  • Tables temporaires locales et index
  • Variables de tables
  • Tables renvoyées dans les fonctions table
• Objets internes
  • les tables de travail de curseur et le stockage temporaire d'objets LOB
  • les fichiers de travail de jointures ou d'agrégations
  • les résultats de tris intermédiaires de création d'index (si SORT_IN_TEMPDB est spécifié) ou pour certaines requêtes GROUP BY, ORDER BY ou UNION.
• Magasin de versions (Version Store, nouveauté de SQL Server 2005)
  • Gestion des versions d'enregistrement pour la création d'index en ligne, les triggers, les fonctionnalités MARS, etc.

Cette base de données à la particularité d'être vidée lors du redémarrage du service SQL Server. Autre particularité elle est en mode de récupération simple, c'est-à-dire que son journal est vidé de manière automatique sans nécessiter de sauvegarde.

 

Cette base de données est sensible étant données qu'elle est très sollicitée par différentes tâches (voir ci-dessus). Pour cette raison le paramétrage de cette base de données doit suivre certaines règles dès lors que votre serveur commence à avoir une charge conséquente. Allez consulter ces différents articles à ce sujet :

http://support.microsoft.com/kb/328551/en-us

http://support.microsoft.com/kb/917047/en-us

http://www.microsoft.com/technet/prodtechnol/sql/2005/workingwithtempdb.mspx

 

è Aucune sauvegarde n'est à réaliser de cette base de données

 

L'optimisation de cette base doit être faite sur toutes les versions en fonction de l'usage de tempdb, voici les points particulier d'optimisation à réaliser :

• Séparer tempdb des autres base (disque(s) séparé(s)) et si possible utilisez un système RAID
• La multiplication des fichiers de base de données (idéalement 1 par processeur)
• D'autres paramétrages assez pointus

Tout ceci est expliqué en détail juste au dessus, dans les différents liens cités.

 

à SQL Server 2005 : Sous SQL Server 2005 cette base de données système est beaucoup plus utilisé que sous SQL Server 2000 et 7, l'optimisation de cette base de données devient essentielle sur cette version.

 

Fichier	Nom Logique	Emplacement	Incrément
Données	tempdev	tempdb.mdf	10 % jusqu'au remplissage du disque.
Log	templog	templog.ldf	10 % jusqu'à 2 To.

 

« msdb »

C'est un peu la base de données où est stocké tout ce qui ne l'est pas ailleurs. L'usage principal cette base de données et le stockage de tout ce qui à trait à l'Agent SQL

 

Dans cette base de données vous trouverez entre autres :

• Eléments relatifs à l'Agent SQL
  • Les travaux / tâches de l'Agent SQL
  • L'historique des travaux / tâches de l'Agent SQL
  • Les agents, les alertes, etc.
  • Les paramètres de Database Mail ou SQL Mail
• Paramètres de réplication
• L'historique des sauvegardes des bases de données du serveur
• Les lots DTS (Data Transformation Services) sauvés sur msdb
• Les lots SSIS (Integration Services) sauvés sur msdb

 

msdb

Agent SQL

DTS SSIS

Historique Sauvegarde

 

è Il est conseillé de sauvegarder cette base de données après l'une des situations suivantes

• Fréquemment si vous travailler avec des lots SSIS ou DTS stocké dans « msdb »
• A chaque ajout ou modification d'un travail ou d'une tâche

 

à Il est recommandé de ne pas ajouter d'objets à cette base de données.

 

Fichier	Nom Logique	Emplacement	Incrément
Données	MSDBData	MSDBData.mdf	256 ko jusqu'au remplissage du disque.
Log	MSDBLog	MSDBLog.ldf	256 ko jusqu'à 2 To.

 

La base système de distribution

« distribution »

Cette base de données système, n?est présente sur votre serveur que si celui-ci participe à une réplication et est distributeur. Le distributeur est le rôle pivot de la réplication (voir schéma).

 

Dans cette base de données vous trouverez :

• Dans tous les cas
  • Les erreurs des agents et leur historique
  • Le numéro des dernières transactions
• Dans le cas d'une réplication transactionnelle
  • Le schéma des données à répliquer
  • Les données à répliquer

 

éditeur

distributeur

abonné

abonné

distribution

è Il est conseillé de sauvegarder cette base de données après l'une des situations suivantes

• Lors de toutes modifications apporté à la / aux réplication(s)
• Au même moment que les bases de données « éditeur »

Une 6^ème base sous SQL Server 2005 ?

« mssqlsystemresource » ou « RDB »

Nouvelle sous SQL Server 2005, cette base de données système n'est pas visible depuis l'interface graphique, ni même en consultant la liste des bases de données depuis sys.databases (ou sysdatabases dans master sous 2000). Cependant elle est bien présente sous forme de fichiers dans le répertoire contenant les fichiers de bases de données et elle a bien un fichier de données (mdf) et un journal de transaction (ldf).

 

Cette base est une base de données de ressources, c'est-à-dire que son but n'est pas de contenir des données ou du moins pas le but essentiel, mais de contenir des objets autres tels que des vues, des procédures stockées, etc.

 

C'est cette base de données qui contient les vues systèmes et les procédures stockées systèmes.

 

è Il est conseillé de sauvegarder cette base de données après l'une des situations suivantes

• A chaque mise à jour, cependant le seul moyen de sauvegarder cette base de données est de copier le fichier mdf.

à Il est possible d'acceder aux éléments de cette base de données depuis une connection dédié (DAC) mais çà n'est pas conseillé.

 

Avec les scripts si dessus vous pourrez contrôler la version et les mises à jour effectuer dans cette base.

 

-- Version de la base de données de ressources

SELECT SERVERPROPERTY ( 'ResourceVersion' );

GO

-- > 9.00.2153

 

-- Date et Heure de dernière mise à jour

SELECT SERVERPROPERTY ( 'ResourceLastUpdateDateTime' );

GO

-- > 2006-05-09 00:13:45.183

 

Synthèse

 

Nom	Description	Sav.	Modèle de récupération	Commentaires
master	Informations système du serveur et des bases de données.	oui	Simple	A chaque mise à jour (Fréquemment)
model	Modèle pour toutes les nouvelles bases de données.	oui	Complet (défaut)	A chaque mise à jour (Rarement)
msdb	Base de données de l'agent, contient des historiques et des lots DTS / SSIS.	oui	Simple (défaut)	A chaque mise à jour (Fréquemment)
RDB	Base de données en lecture seule contenant les objets systèmes.	oui / non	N/A	Copier le fichier mdf à chaque installation de patch.
tempdb	Espace de travail récréée à chaque démarrage du serveur.	non	Simple	Pas de sauvegarde à réaliser
distribution	Base de données de distribution, n'existe que sur le distributeur dans une réplication.	oui	Complet (défaut)	A chaque mise à jour (Très fréquemment)

 

Bonne lecture...