Architettura di file e filegroup

In SQL Server viene eseguito il mapping del database a un set di file del sistema operativo. I dati e le informazioni sul log non vengono archiviati nello stesso file e i singoli file vengono utilizzati solo da un database. I filegroup sono insiemi denominati di file utilizzati a supporto della posizione dei dati e delle attività amministrative, ad esempio operazioni di backup e ripristino.

File di database

I database di SQL Server includono tre tipi di file:

• File di dati primari

  Il file di dati primario è il punto di partenza del database e fa riferimento agli altri file del database. In ogni database è presente un unico file di dati primario. L'estensione consigliata per i file di dati primari è mdf.

• File di dati secondari

  I file di dati secondari includono tutti gli altri file di dati ad eccezione del file di dati primario. In alcuni database potrebbero non essere presenti file di dati secondari, mentre in altri potrebbero esserne presenti diversi. L'estensione consigliata per i file di dati secondari è ndf.

• File di log

  I file di log contengono tutte le informazioni utilizzate per il recupero del database. In ogni database deve essere presente almeno un file di log, ma possono anche essercene di più. L'estensione consigliata per i file di log è ldf.

In SQL Server non è obbligatorio utilizzare le estensioni mdf, ndf e ldf, ma l'utilizzo di tali estensioni consente di identificare in modo più immediato i diversi tipi di file e la relativa funzione.

In SQL Server i percorsi di tutti i file in un database vengono registrati nel file primario del database e nel database master. Nella maggior parte dei casi Motore di database di SQL Server utilizza le informazioni sul percorso del file contenute nel database master. Tuttavia nelle situazioni seguenti Motore di database utilizza le informazioni sul percorso dei file presenti nel file primario per inizializzare le voci relative a tali percorsi nel database master.

• Quando un database viene collegato tramite l'istruzione CREATE DATABASE con le opzioni FOR ATTACH o FOR ATTACH_REBUILD_LOG.

• Quando si esegue l'aggiornamento da SQL Server 2000 o 7.0.

• Quando si ripristina il database master.

Nomi di file logici e fisici

I file di SQL Server hanno due nomi:

logical_file_name

logical_file_name è il nome utilizzato per fare riferimento al file fisico in tutte le istruzioni Transact-SQL. Il nome di file logico deve essere conforme alle regole per gli identificatori di SQL Server e deve essere univoco tra i nomi di file logici nel database.

os_file_name

os_file_name è il nome del file fisico che include il percorso di directory. Tale nome deve essere conforme alle regole relative ai nomi di file del sistema operativo.

I dati e i file di log di SQL Server possono essere memorizzati sia in file system FAT che NTFS. È consigliabile utilizzare il file system NTFS per i vantaggi di sicurezza intrinseci di NTFS. I file di log e i filegroup di dati di lettura/scrittura possono essere memorizzati in un file system compresso NTFS. Solo i database e i filegroup secondari di sola lettura possono essere memorizzati in un file system compresso NTFS. Per ulteriori informazioni, vedere Filegroup di sola lettura e compressione.

Se nello stesso computer sono in esecuzione più istanze di SQL Server, a ogni istanza viene assegnata una directory predefinita specifica in cui verranno archiviati i file dei database creati nell'istanza. Per ulteriori informazioni, vedere Percorsi dei file per le istanze predefinite e denominate di SQL Server.

Pagine di file di dati

Le pagine dei file di dati di SQL Server sono numerate progressivamente a partire dalla prima, che corrisponde al numero zero (0). A ogni file di un database è associato un numero di ID file univoco. Per identificare in modo univoco una pagina in un database, sono necessari sia ID file che numero di pagina. Nell'esempio seguente vengono illustrati i numeri di pagina di un database che include un file di dati primario di 4 MB e un file di dati secondario di 1 MB.

La prima pagina di ogni file è la pagina dell'intestazione, che include informazioni sugli attributi del file. Anche molte altre pagine all'inizio del file contengono informazioni di sistema, ad esempio mappe delle allocazioni. Una delle pagine di sistema archiviate sia nel file di dati primario che nel primo file di log è una pagina di avvio del database contenente informazioni sugli attributi del database. Per ulteriori informazioni sulle pagine e sui tipi di pagina, vedere Informazioni su pagine ed extent.

Dimensioni file

Le dimensioni dei file di SQL Server possono aumentare automaticamente rispetto ai valori originari. Quando si definisce un file, è possibile specificare un incremento di crescita specifico. Quando lo spazio assegnato al file si esaurisce, le sue dimensioni aumentano in base all'incremento specificato. Se un filegroup include più file, le loro dimensioni non aumentano automaticamente finché lo spazio di tutti i file non si esaurisce. L'aumento delle dimensioni avviene quindi in base a un meccanismo round robin.

È inoltre possibile specificare le dimensioni massime di ogni file. Se non vengono specificate le dimensioni massime, il file può continuare ad aumentare fino a occupare tutto lo spazio disponibile nel disco. Questa caratteristica è particolarmente utile quando SQL Server viene utilizzato come database incorporato in un'applicazione per cui l'utente non può rivolgersi direttamente all'amministratore di sistema. L'utente può lasciare aumentare i file in base alle necessità per alleggerire il carico amministrativo derivante dal monitoraggio dello spazio libero nel database e dall'allocazione manuale di spazio aggiuntivo.

File di snapshot di database

La forma del file utilizzato da uno snapshot del database per archiviare i propri dati copy-on-write cambia a seconda che lo snapshot venga creato da un utente o utilizzato internamente:

• Quando lo snapshot del database viene creato da un utente, i dati vengono archiviati in uno o più file sparse. La tecnologia file sparse è una caratteristica del file system NTFS. Inizialmente un file sparse non contiene alcun dato utente e lo spazio su disco per i dati utente non viene allocato per tale file. Per informazioni generali sull'utilizzo di file sparse negli snapshot del database e sul modo in cui aumentano le dimensioni degli snapshot del database, vedere Funzionamento degli snapshot del database e Informazioni sulle dimensioni dei file sparse negli snapshot del database.

• Gli snapshot del database vengono utilizzati internamente da alcuni comandi DBCC, tra cui DBCC CHECKDB, DBCC CHECKTABLE, DBCC CHECKALLOC e DBCC CHECKFILEGROUP. Uno snapshot interno del database utilizza flussi di dati alternativi sparse dei file originali del database. Come i file sparse, anche i flussi di dati alternativi rappresentano una caratteristica del file system NTFS. L'utilizzo dei flussi di dati alternativi sparse consente l'associazione di più allocazioni di dati a un solo file o a una sola cartella senza incidere sulla dimensione del file o sulle statistiche del volume.

Filegroup di database

I file e gli oggetti di database possono essere raggruppati in filegroup ai fini dell'allocazione e dell'amministrazione. Esistono due tipi di filegroup:

Primario

Il filegroup primario include il file di dati primario e qualsiasi altro file non assegnato in modo specifico a un altro filegroup. Tutte le pagine delle tabelle di sistema vengono allocate nel filegroup primario.

Definito dall'utente

I filegroup definiti dall'utente sono quelli specificati utilizzando la parola chiave FILEGROUP in un'istruzione CREATE DATABASE o ALTER DATABASE.

I file di log non vengono mai inclusi in un filegroup. Lo spazio del log viene gestito separatamente rispetto allo spazio dei dati.

Lo stesso file non può essere membro di più filegroup. Tabelle, indici e dati LOB possono essere associati a un filegroup specifico. In questo caso, tutte le relative pagine vengono allocate in tale filegroup oppure le tabelle e gli indici possono essere partizionati. I dati di tabelle e indici partizionati vengono divisi in unità, ognuna delle quali può essere archiviata in un diverso filegroup in un database. Per ulteriori informazioni su tabelle e indici partizionati, vedere Tabelle e indici partizionati.

In ogni database è presente un solo filegroup predefinito. Quando una tabella o un indice viene creato senza specificare un filegroup, si presuppone che tutte le pagine vengano allocate dal filegroup predefinito. È possibile impostare come predefinito un solo filegroup per volta. I membri del ruolo predefinito del database db_owner possono impostare di volta in volta il filegroup predefinito. Se non viene specificato alcun filegroup predefinito, viene impostato come predefinito il filegroup primario.

Esempio di file e filegroup

Nell'esempio seguente viene illustrata la creazione di un database in un'istanza di SQL Server. Nel database sono presenti un file di dati primario, un filegroup definito dall'utente e un file di log. Il file di dati primario è incluso nel filegroup primario e il filegroup definito dall'utente include due file di dati secondari. Tramite l'istruzione ALTER DATABASE viene impostato come predefinito il filegroup definito dall'utente e quindi viene creata una tabella che specifica tale filegroup.

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

Nella figura seguente vengono illustrati i risultati dell'esempio precedente.

Vedere anche

Concetti

Informazioni su pagine ed extent