Les bases de données SQL Server disposent de trois types de fichiers :

• Fichiers de données primaires

  Le fichier de données primaire constitue le point de départ de la base de données et il pointe vers les autres fichiers de la base de données. Chaque base de données comprend un fichier de données primaire. L'extension de fichier recommandée est .mdf.

• Fichiers de données secondaires

  Ces fichiers constituent tous les fichiers de données autres que le fichier de données primaires. Certaines bases de données possèdent plusieurs fichiers de données secondaires, tandis que d'autres n'en possèdent aucun. L'extension de fichier recommandée est .ndf.

• Fichiers journaux

  Ces fichiers contiennent toutes les informations de suivi nécessaires à la récupération de la base de données. Il doit exister au moins un fichier journal par base de données. L'extension de fichier recommandée est .ldf.

SQL Server ne vous oblige pas à utiliser les extensions de fichier .mdf, .ndf et .ldf, mais celles-ci permettent d'identifier les différents types de fichiers et leur utilisation.

Dans SQL Server, les emplacements de tous les fichiers d'une base de données sont enregistrés dans le fichier primaire de la base de données et dans la base de données MASTER. En général, le moteur de base de données SQL Server utilise les informations d'emplacement de fichier contenues dans la base de données MASTER. Toutefois, le moteur de base de données utilise les informations d'emplacement de fichier contenues dans le fichier primaire afin d'initialiser les entrées d'emplacement de fichier dans la base de données MASTER dans les situations suivantes :

• attachement d'une base de données à l'aide de l'instruction CREATE DATABASE et de l'option FOR ATTACH ou FOR ATTACH_REBUILD_LOG ;

• Mise à niveau à partir de SQL Server version 2000 ou 7.0.

• restauration de la base de données master.

Noms de fichiers logiques et physiques

Pages de fichiers de données

Les pages d'un fichier de données SQL Server sont numérotées de manière séquentielle, zéro (0) correspondant à la première page. Chaque fichier d'une base de données possède un numéro d'identification de fichier unique. L'ID de fichier et le numéro de page sont nécessaires pour identifier de manière unique une page d'une base de données. L'exemple ci-dessous montre les numéros de page d'une base de données disposant d'un fichier de données primaire de 4 Mo et d'un fichier de données secondaire de 1 Mo.

La première page de chaque fichier est une page d'en-tête qui contient des informations sur les attributs du fichier. D'autres pages situées au début du fichier contiennent également des informations sur le système, comme les tables d'allocation. Une des pages système stockée à la fois dans le fichier de données primaire et dans le premier fichier journal est une page d'amorçage de base de données qui contient des informations sur les attributs de la base de données. Pour plus d'informations sur les pages et les types de pages, consultez Fonctionnement des pages et étendues.

Taille du fichier

Le format de fichier utilisé par un instantané de base de données pour stocker ses données de copie lors de l'écriture varie selon que l'instantané a été créé par un utilisateur ou utilisé en interne :

• Un instantané de base de données créé par un utilisateur stocke ses données dans un ou plusieurs fichiers partiellement alloués. La technologie des fichiers partiellement alloués constitue une fonctionnalité du système de fichiers NTFS. Au départ, un fichier partiellement alloué ne contient pas de données utilisateur et aucun espace disque pour les données utilisateur ne lui a été alloué. Pour des informations générales sur l'utilisation des fichiers partiellement alloués dans un instantané de base de données et sur le schéma de croissance des instantanés de bases de données, consultez Fonctionnement des captures instantanées de base de données et Tailles des fichiers partiellement alloués dans les instantanés de bases de données.

• Les instantanés de base de données sont utilisés en interne par certaines commandes DBCC. Citons notamment les commandes DBCC CHECKDB, DBCC CHECKTABLE, DBCC CHECKALLOC et DBCC CHECKFILEGROUP. Un instantané interne de base de données utilise les flux de données de remplacement fragmentés des fichiers de la base de données d'origine. Comme les fichiers partiellement alloués, les flux de données de remplacement sont une fonctionnalité du système de fichiers NTFS. L'utilisation de flux de données de remplacement fragmentés permet d'associer plusieurs affectations de données avec un seul fichier ou dossier sans influer sur les statistiques de taille de fichier ou de volume.

Les objets et les fichiers de la base de données peuvent être regroupés en groupes de fichiers à des fins d'allocation de pages et d'administration. Il existe deux types de groupes de fichiers :

Primaires

Le groupe de fichiers primaire contient le fichier de données primaire ainsi que tous les autres fichiers qui ne sont pas spécifiquement affectés à un autre groupe de fichiers. Toutes les pages des tables système sont allouées au groupe de fichiers primaire.

Définis par l'utilisateur

Ce groupe désigne tous les groupes de fichiers créés à l'aide du mot clé FILEGROUP dans une instruction CREATE DATABASE ou ALTER DATABASE.

Les fichiers journaux ne font jamais partie d'un groupe de fichiers. L'espace qui leur est réservé est géré indépendamment de l'espace réservé aux données.

Un fichier ne peut pas appartenir à plusieurs groupes de fichiers. Les tables, les index et les données LOB (Large Object) peuvent être associés à un groupe de fichiers particulier. Dans ce cas, toutes leurs pages sont allouées à ce groupe de fichiers ou les tables et les index peuvent être partitionnées. Les données des index et des tables partitionnés sont réparties en unités, dont chacune peut être placée dans un groupe de fichiers distinct au sein d'une base de données. Pour plus d'informations sur les tables et les index partitionnés, consultez Tables et index partitionnés.

Dans chaque base de données, un groupe de fichiers est désigné comme groupe de fichiers par défaut. Lorsque vous créez une table ou un index sans spécifier un groupe de fichiers, le système considère que toutes les pages doivent être allouées depuis le groupe de fichiers par défaut. Seul un groupe de fichiers à la fois peut faire office de groupe de fichiers par défaut. Les membres du rôle de base de données fixe db_owner peuvent faire basculer le groupe de fichiers par défaut d'un groupe à un autre. En l'absence de spécification, c'est le groupe de fichiers primaire qui sert de groupe de fichiers par défaut.

Exemple de fichier et de groupe de fichiers

L'exemple ci-dessous crée une base de données sur une instance de SQL Server. La base de données possède un fichier de données primaire, un groupe de fichiers défini par l'utilisateur et un fichier journal. Le fichier de données primaire fait partie du groupe de fichiers primaire et le groupe de fichiers défini par l'utilisateur possède deux fichiers de données secondaires. Une instruction ALTER DATABASE fait du groupe de fichiers défini par l'utilisateur le groupe par défaut. Une table est ensuite créée en spécifiant le groupe de fichiers défini par l'utilisateur.

USE master;
GO
-- Create the database with the default data
-- filegroup and a log file. Specify the
-- growth increment and the max size for the
-- primary data file.
CREATE DATABASE MyDB
ON PRIMARY
  ( NAME='MyDB_Primary',
    FILENAME=
       'c:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\data\MyDB_Prm.mdf',
    SIZE=4MB,
    MAXSIZE=10MB,
    FILEGROWTH=1MB),
FILEGROUP MyDB_FG1
  ( NAME = 'MyDB_FG1_Dat1',
    FILENAME =
       'c:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\data\MyDB_FG1_1.ndf',
    SIZE = 1MB,
    MAXSIZE=10MB,
    FILEGROWTH=1MB),
  ( NAME = 'MyDB_FG1_Dat2',
    FILENAME =
       'c:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\data\MyDB_FG1_2.ndf',
    SIZE = 1MB,
    MAXSIZE=10MB,
    FILEGROWTH=1MB)
LOG ON
  ( NAME='MyDB_log',
    FILENAME =
       'c:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\data\MyDB.ldf',
    SIZE=1MB,
    MAXSIZE=10MB,
    FILEGROWTH=1MB);
GO
ALTER DATABASE MyDB 
  MODIFY FILEGROUP MyDB_FG1 DEFAULT;
GO

-- Create a table in the user-defined filegroup.
USE MyDB;
CREATE TABLE MyTable
  ( cola int PRIMARY KEY,
    colb char(8) )
ON MyDB_FG1;
GO

L'illustration ci-dessous récapitule les résultats de l'exemple précédent.

Concepts