Architecture des fichiers et des groupes de fichiers

SQL Server mappe une base de données sur un ensemble de fichiers du système d'exploitation. Les données et les informations des journaux ne figurent jamais dans le même fichier ; par ailleurs, les fichiers spécifiques ne sont utilisés que par une base de données. Les groupes de fichiers sont des collections nommées de fichiers et facilitent les tâches d'administration et de placement des données, telles que les opérations de sauvegarde et de restauration.

Fichiers de base de données

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

Les fichiers SQL Server portent deux noms :

logical_file_name

Le logical_file_name est le nom utilisé pour faire référence au fichier physique dans toutes les instructions Transact-SQL. Le nom de fichier logique doit respecter les règles régissant les identificateurs SQL Server et doit être unique parmi les noms de fichier logique dans la base de données.

os_file_name

Le os_file_name est le nom du fichier physique, chemin d'accès au répertoire compris. Il doit respecter les règles en vigueur pour les noms de fichiers du système d'exploitation.

Les données et les fichiers journaux SQL Server peuvent être implantés dans les systèmes de fichiers FAT ou NTFS. Nous vous recommandons d'utiliser le système de fichiers NTFS pour des raisons de sécurité. Les fichiers journaux et les groupes de fichiers de données en lecture/écriture ne peuvent pas être implantés dans un système de fichiers compressé NTFS. Seuls les groupes de fichiers secondaires en lecture seule et les bases de données en lecture seule peuvent être implantés dans un système de fichiers compressé NTFS. Pour plus d'informations, consultez La compression et les groupes de fichiers en lecture seule.

Lorsque plusieurs instances de SQL Server sont exécutées sur un ordinateur unique, chaque instance reçoit un répertoire par défaut différent pour contenir les fichiers des bases de données créées dans l'instance. Pour plus d'informations, consultez Emplacements des fichiers pour les instances par défaut et les instances nommées de SQL Server.

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

Les fichiers SQL Server peuvent augmenter automatiquement leur volume et dépasser leur taille d'origine. Lorsque vous définissez un fichier, vous pouvez spécifier un incrément de croissance précis. Chaque fois que le fichier est rempli, sa taille augmente en fonction de l'incrément de croissance. Si un groupe comporte plusieurs fichiers, ces derniers ne s'accroissent pas automatiquement jusqu'à ce que tous les fichiers soient remplis. La croissance se produit dans ce cas selon le principe de chacun son tour.

Chaque fichier peut également avoir une taille maximale. En l'absence de spécification, le fichier continue à s'accroître jusqu'à ce que tout l'espace disque disponible soit utilisé. Cette fonctionnalité s'avère particulièrement utile lorsque SQL Server sert de base de données incorporée dans une application pour laquelle l'utilisateur n'a pas accès à un administrateur système. L'utilisateur peut laisser les fichiers s'accroître automatiquement autant que nécessaire pour réduire la charge administrative liée à la gestion de l'espace disponible dans la base de données et à l'affectation manuelle d'espace supplémentaire.

Fichiers d'instantanés de base de données

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.

Groupes de fichiers de la base de données

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.

Voir aussi

Concepts

Fonctionnement des pages et étendues