Nell'ambito dei sistemi di gestione di database (DBMS) come SQL Server, il termine MDF assume un ruolo centrale. Il file è l'acronimo .mdf di Master Database File ed è un database SQL inserito in un file. Questi file hanno un associato .ldf (file di database di log) che contiene i log per tale database. Ogni database SQL Server dispone di almeno due file nel sistema operativo: un file di dati e un file di log (transaction log).
I file di dati contengono dati e oggetti come tabelle, indici, procedure e viste. I file di log contengono le informazioni necessarie per ripristinare tutte le transazioni nel database. L'estensione del nome file consigliata per i file di dati primari è .mdf.
Tipologie di File di Database SQL Server
I database SQL Server sono costituiti da diverse tipologie di file, ognuna con una funzione specifica e un ruolo nel mantenimento dell'integrità e della disponibilità dei dati. Ecco una panoramica dettagliata:
| Tipo di File | Estensione consigliata | Descrizione |
|---|---|---|
| Primario | .mdf | Contiene informazioni di avvio per il database e punta agli altri file nel database. Ogni database ha un file di dati primario. |
| Secondario | .ndf | Sono file di dati (opzionali) definiti dall'utente. I dati possono essere distribuiti su più dischi inserendo ciascun file su un'unità disco diversa. |
| Transaction Log | .ldf (implicito) | Il log contiene le informazioni utilizzate per ripristinare il database. Deve essere presente almeno un file di registro per ciascun database. |
Per impostazione predefinita, i dati e i transaction log vengono inseriti nello stesso disco e percorso, per gestire i sistemi a disco singolo.
Nomi Logici e Fisici dei File
La gestione dei file di database richiede la comprensione dei loro nomi logici e fisici. Il logical_file_name è il nome utilizzato per fare riferimento al file fisico in tutte le istruzioni Transact-SQL. Il nome del file logico deve essere conforme alle regole per gli identificatori di SQL Server e deve essere univoco tra i nomi di file logici all’interno del database. L'os_file_name è il nome del file fisico incluso il percorso della directory.
Leggi anche: Creare database SQL Server con script in Visual Studio
È una buona idea mantenere le estensioni di file predefinite, poiché non ci sono veri vantaggi nell'usare estensioni diverse e farlo aggiunge ulteriore complessità. I file di dati e transaction log di Sql Server possono essere scritti nei file system FAT o NTFS.
Attenzione: I filegroup di dati e transaction log in lettura/scrittura non sono supportati su un file system NTFS compresso. Solo i database di sola lettura e i filegroup secondari di sola lettura possono essere inseriti in un file system NTFS compresso.
Organizzazione Interna dei Dati nei File MDF
All'interno di un file di dati di SQL Server, le informazioni sono organizzate in pagine. Le pagine in un file di dati di SQL Server sono numerate in sequenza, a partire da zero (0) per la prima pagina del file. Ogni file in un database ha un numero ID file univoco. Per identificare in modo univoco una pagina in un database, sono richiesti sia l'ID file che il numero di pagina.
Una pagina di intestazione del file è la prima pagina che contiene informazioni sugli attributi del file. Molte delle altre pagine all'inizio del file contengono anche informazioni di sistema, come le mappe di allocazione.
Gestione dello Spazio e Crescita dei File
I file di SQL Server sono progettati per gestire la crescita dei dati in modo dinamico. I file di SQL Server possono aumentare automaticamente rispetto alla dimensione specificata originariamente. Quando si definisce un file, si può specificare un incremento di crescita specifico. Ogni volta che il file si riempie, aumenta le sue dimensioni in base all'incremento specificato.
Leggi anche: Modificare nomi colonne database
Spazio Database: Utilizzato e Allocato
Comprendere la differenza tra spazio utilizzato e allocato è fondamentale per una gestione efficiente del database.
| Definizione | Note |
|---|---|
| Spazio dati utilizzato | La quantità di spazio utilizzata per archiviare i dati del database. Generalmente, lo spazio utilizzato aumenta (diminuisce) durante gli insert (delete). In alcuni casi, lo spazio utilizzato non cambia negli insert o nelle delete a seconda della quantità e del tipo di dati coinvolti nell'operazione e nell'eventuale frammentazione. Ad esempio, l'eliminazione di una riga da ogni pagina di dati non diminuisce necessariamente lo spazio utilizzato. |
| Spazio dati allocato | La quantità di spazio file formattato reso disponibile per l'archiviazione dei dati del database. La quantità di spazio allocato aumenta automaticamente, ma non diminuisce mai dopo operazioni di delete. |
Snapshot del Database e File Sparsi
Gli snapshot del database rappresentano una copia statica e di sola lettura di un database SQL Server in un momento specifico. Uno snapshot del database creato da un utente memorizza i propri dati in uno o più file sparsi. La tecnologia dei file sparsi è una caratteristica del file system NTFS. Per informazioni generali sull'uso di file sparsi negli snapshot del database e su come crescono gli snapshot del database, è possibile consultare risorse specifiche.
Gli snapshot del database vengono utilizzati internamente da alcuni comandi DBCC. Questi comandi includono DBCC CHECKDB, DBCC CHECKTABLE, DBCC CHECKALLOC e DBCC CHECKFILEGROUP. Uno snapshot del database interno utilizza stream di dati alternati sparsi dei file di database originali. Come i file sparsi, gli stream di dati alternati sono una caratteristica del file system NTFS.
Ottimizzazione delle Prestazioni con i Filegroup
I filegroup sono un meccanismo potente in SQL Server per gestire la distribuzione fisica dei dati e migliorare le prestazioni. I filegroup utilizzano una strategia di riempimento proporzionale su tutti i file all'interno di ogni filegroup. Quando i dati vengono scritti nel filegroup, SQL Server Database Engine scrive una quantità proporzionale allo spazio libero nel file su ogni file all'interno del filegroup, invece di scrivere tutti i dati sul primo file fino al riempimento. Ciò significa che per i filegroup contenenti più di un file, non si ha alcun controllo su quale file viene utilizzato per archiviare il singolo oggetto.
Il Filegroup Primario
Il filegroup Primario è il filegroup che contiene il file primario. Quando gli oggetti vengono creati nel database senza specificare a quale filegroup appartengono, vengono assegnati al filegroup predefinito. In qualsiasi momento, un filegroup viene designato come filegroup predefinito. Il filegroup PRIMARY è il filegroup predefinito a meno che non venga cambiato tramite l'istruzione ALTER DATABASE.
Strategie di Ottimizzazione
- La maggior parte dei database funziona bene con un singolo file di dati e un singolo file di transaction log.
- Se si utilizzano più file di dati, creare un secondo filegroup per il file aggiuntivo e rendere quel filegroup il filegroup predefinito. In questo modo, il file primario conterrà solo tabelle e oggetti di sistema.
- Per massimizzare le prestazioni, creare file o filegroup su dischi diversi il più possibile. Mettere gli oggetti che utilizzano spazio in modo massivo in filegroup diversi.
- Utilizzare i filegroup per consentire il posizionamento di oggetti su dischi fisici specifici.
- Inserire tabelle diverse utilizzate nelle stesse query di join in filegroup diversi. Questo passaggio migliorerà le prestazioni, grazie alla ricerca parallela di I/O su disco per i dati in join.
- Collocare le tabelle con accesso intensivo e gli indici non cluster che appartengono a tali tabelle su filegroup diversi. L'utilizzo di filegroup diversi migliorerà le prestazioni, a causa dell'I/O parallelo se i file si trovano su dischi fisici diversi.
- Non posizionare il o i file di transaction log sullo stesso disco fisico in cui si trovano gli altri file e filegroup.
Ad esempio: Data1.ndf, Data2.ndf e Data3.ndf possono essere creati rispettivamente su tre unità disco e assegnati al filegroup fgroup1. È quindi possibile creare una tabella specificatamente sul filegroup fgroup1. Le query per i dati della tabella verranno distribuite sui tre dischi: migliorerà le prestazioni. Lo stesso miglioramento delle prestazioni può essere ottenuto utilizzando un singolo file creato su uno stripe set RAID (array ridondante di dischi indipendenti).
Considerazioni sulla Gestione e Best Practice
La gestione dei file di database richiede attenzione e pianificazione. Se c’è la necessità di estendere un volume o una partizione su cui risiedono file di database utilizzando strumenti come Diskpart, si dovrebbe prima eseguire il backup di tutti i database di sistema e utente e arrestare i servizi di SQL Server.
Utilizzo dei File MDF in Ambienti di Sviluppo (Visual Studio)
Gli sviluppatori spesso interagiscono con i file MDF direttamente in ambienti come Visual Studio. È possibile aprire una connessione a un database o a un servizio o a un database LocalDB aperto da un .mdf file e visualizzare e modificare tabelle e righe di dati tramite Esplora server o Esplora oggetti di SQL Server. È possibile creare nuovi database archiviati nei .mdf file aggiungendo un elemento database basato sul servizio al progetto.
In Visual Studio, è possibile usare direttamente i dati: eseguire query, modificare i dati, creare e modificare le tabelle e le altre proprietà dello schema, modificare le stored procedure e le funzioni, i trigger e così via. La funzionalità di queste finestre (Esplora server ed Esplora oggetti di SQL Server) si sovrappone in qualche misura.
Esplora server e Esplora oggetti di SQL Server
Esplora server è installato per impostazione predefinita in Visual Studio. Può essere usato per testare le connessioni e visualizzare i database di SQL Server, qualsiasi altro database con un provider di ADO.NET installato e alcuni servizi di Azure. Mostra anche oggetti di basso livello, ad esempio contatori di prestazioni di sistema, registri eventi e code di messaggi.
Esplora oggetti di SQL Server è installato con SQL Server Data Tools e visibile nel menu Visualizza. Se non viene visualizzato, è possibile installarlo tramite il Pannello di controllo. Usare Esplora oggetti di SQL Server per visualizzare i database SQL (se hanno un provider di ADO.NET), creare nuovi database, modificare gli schemi, creare stored procedure, recuperare stringhe di connessione, visualizzare i dati e altro ancora.
Creazione di Connessioni
Per creare una connessione al database, fare clic sull'icona Connetti al database in Esplora server oppure fare clic con il pulsante destro del mouse in Esplora server nel nodo Connessioni dati e selezionare Aggiungi connessione. Scegliere un'origine dati e selezionare Continua per passare alla finestra di dialogo Aggiungi connessione.
Se non si ha accesso a un altro database e non è installato LocalDB, è possibile installare LocalDB tramite il programma di installazione di Visual Studio, come parte del carico di lavoro Archiviazione ed elaborazione dei dati, il carico di lavoro di sviluppo ASP.NET e Web o come singolo componente. Verrà visualizzata la finestra di dialogo Aggiungi connessione. È possibile scegliere tra diversi tipi di autenticazione che coprono un'ampia gamma di scenari. Testare la connessione e, in caso di esito positivo, scegliere OK.
Se l'origine dati non è quella desiderata, fare clic sul pulsante Cambia per scegliere una nuova origine dati o un nuovo provider di dati ADO.NET. Dopo aver scelto l'origine dati, fare clic su Test connessione. Viene visualizzata la finestra di dialogo Connetti. Se è necessario impostare altre impostazioni nella stringa di connessione, è possibile usare il collegamento Avanzate, che visualizza tutte le impostazioni.
Sicurezza e Connessioni in Visual Studio 2022
In Visual Studio 2022 versione 17.8 e successive la finestra di dialogo include due nuove opzioni (Crittografa e certificato del server di attendibilità) che entrano nella stringa di connessione e influiscono sulle impostazioni di sicurezza usate per la connessione. Queste opzioni supportano le funzionalità di sicurezza più rigorose del driver di database Microsoft.Data.SqlClient 4.0. La procedura di sicurezza consigliata consiste nell'usare la crittografia e installare un certificato nel server. Se non si imposta Crittografa su facoltativo con Visual Studio 17.8 o versione successiva, per impostazione predefinita la crittografia è obbligatoria. Questa è una modifica sostanziale rispetto al comportamento nelle versioni precedenti. Un errore comune è: "È stata stabilita una connessione con il server, ma si è verificato un errore durante il processo di accesso. (provider: provider SSL, errore: 0 - La catena di certificati è stata rilasciata da un'autorità non attendibile."
Considerazioni sui Provider a 32 bit
Alcuni degli strumenti di dati in Visual Studio non saranno in grado di connettersi ai database OLEDB o ODBC usando provider di dati a 32 bit. Se è necessario gestire applicazioni a 32 bit che si connettono a OLEDB o ODBC, sarà comunque possibile compilare ed eseguire l'applicazione con Visual Studio 2022 e versioni successive. Tuttavia, se è necessario usare uno degli strumenti dati di Visual Studio, ad esempio Esplora server, Creazione guidata origine dati o Progettazione set di dati, sarà necessario usare una versione precedente di Visual Studio che è ancora un processo a 32 bit.
Se l'applicazione usa database di Microsoft Access e può convertire il progetto a 64 bit, è consigliabile usare il motore di database di Microsoft Access a 64 bit, denominato anche Access Connectivity Engine (ACE).
Integrazione con Framework .NET
Se si usa .NET Framework (non .NET Core o .NET 5 o versione successiva) e Windows Form o WPF, è possibile usare la finestra Origini dati, ad esempio, per configurare il data binding per i controlli nelle applicazioni Windows Form e WPF. Se si usa .NET 5 o versione successiva, .NET Core o ASP.NET Core, è possibile connettere l'app al database usando Servizi connessi. Usando Servizi connessi, è possibile usare facilmente un database di sviluppo locale, ospitato da SQL LocalDB, SQL Server in esecuzione in un contenitore o un'istanza locale di SQL Server e quindi passare al database SQL di Azure quando si è pronti per la distribuzione nel cloud.
Un esempio di problema comune in fase di sviluppo si presenta quando si pubblica un sito basato su starter kit. Se il provider non accetta le user instance, è necessario ricreare le tabelle sul DB SQL del server e cambiare la stringa di connessione. Anche il file aspnetdb.mdf, che si crea al momento dell'iscrizione di un utente, può causare problemi se non gestito correttamente sul server del provider, richiedendo la ricreazione delle tabelle sul database del server e la modifica del file di configurazione.
tags: #mdf #signigficato #infortmatica #database