SQL Q&APartizionamento, verifiche di coerenza e altro ancora

Paul S. Randal

Ho collegato accidentalmente un database di produzione a un server SQL Server® 2005 e ora sto tentando di collegarlo al server corretto, su cui è installato SQL Server 2000. Ho provato semplicemente a scollegare il database e a ricollegarlo al server SQL Server 2000 e ho tentato di fare lo stesso con backup e ripristino, ma nessuna delle due procedure ha funzionato. Perché non ottengo il risultato desiderato sul server SQL Server 2000? Questa è l'unica copia del database di cui dispongo.

R Il primo punto da sottolineare è l'importanza di disporre di backup. In genere, gli amministratori di database ritengono che i backup siano necessari solo per recuperare i dati che hanno subito danneggiamenti o in altri casi di emergenza. Tuttavia, può capitare di imbattersi in problemi meno ovvi, ad esempio qualcosa che va storto durante un aggiornamento, sebbene in questo caso si tratti di un aggiornamento accidentale. Resta comunque il fatto che è opportuno disporre sempre di un backup di database completo recente nel caso in cui si verifichino imprevisti.

L'aggiornamento, che sia intenzionale o accidentale, è un'operazione unidirezionale e annullarne gli effetti è estremamente complesso. Quando si esegue l'aggiornamento da una versione di SQL Server a un'altra, il database è sottoposto a una serie di fasi di aggiornamento. In genere, ciascuna fase comprende modifiche fisiche al database e ne incrementa il numero di versione.

Ad esempio, una delle principali modifiche eseguite durante l'aggiornamento di un database da SQL Server 2000 a SQL Server 2005 è la variazione della struttura dei cataloghi di sistema del database stesso (spesso denominati tabelle di sistema o metadati del database) che contengono diversi metadati relativi a tabelle, indici, colonne, allocazioni e altri dettagli inerenti alla struttura fisica e relazionale del database.

Con il procedere delle fasi di aggiornamento, il numero di versione del database viene incrementato. Ad esempio, i database SQL Server 7.0 hanno il numero di versione 515, i database SQL Server 2000 il numero di versione 539 e i database SQL Server 2005 il numero di versione 611 (o 612 se è attiva la funzionalità vardecimal). Ciò consente a SQL Server di riconoscere l'ultima fase di aggiornamento eseguita sul database.

Le versioni precedenti di SQL Server non sono in grado di leggere i database aggiornati a versioni più recenti (ad esempio, SQL Server 2000 non può leggere un database aggiornato a SQL Server 2005). Questo perché le versioni precedenti non dispongono del codice necessario per interpretare le strutture e il layout aggiornati del database. Ed è proprio questo il problema del caso in questione: il database è stato aggiornato a SQL Server 2005 e non è in grado di ricollegarsi a SQL Server 2000.

In mancanza di un backup di database completo, l'unica possibilità è esportare tutti i dati dal database aggiornato e trasferirli manualmente a un nuovo database SQL Server 2000. Se non sono state utilizzate nuove funzionalità in SQL Server 2005, è possibile eseguire uno script della struttura del database, creare un database in SQL Server 2000, quindi esportare/importare i dati.

Per eseguire lo script del database in SQL Server 2005, è possibile utilizzare la funzione Esplora oggetti in SQL Server Management Studio (fare clic con il pulsante destro del mouse sul database, selezionare Attività, quindi l'opzione Genera script). La procedura guidata è molto semplice e genera uno script per la creazione di tutti gli oggetti, gli indici, i vincoli, i trigger e così via.

D Di recente abbiamo riprogettato il nostro schema per l'utilizzo del partizionamento tabelle sulla tabella principale, poiché ci è stato detto che potrebbe migliorare le prestazioni. Il database è memorizzato su un'unica unità da 120 GB e la tabella è contenuta in un singolo filegroup. Non vengono utilizzate finestre scorrevoli, semplicemente vengono aggiunte nuove partizioni ogni settimana e tutti i dati devono essere disponibili in linea. La maggior parte delle query elaborano i dati di una sola settimana e alcune operano nell'arco dell'ultimo anno. L'operazione sembra più semplice se eseguita in un unico filegroup. Queste informazioni sono corrette o c'è altro da sapere?

R Sebbene possa sembrare più semplice utilizzare un unico filegroup, in questo modo è improbabile che il partizionamento risulti vantaggioso. Il partizionamento è per lo più utilizzato per garantire una manutenzione più efficiente del database e per aumentare la disponibilità di dati in situazioni di emergenza, oltre a consentire di creare uno schema che migliori le prestazioni.

L'esempio archetipo del partizionamento è una tabella vendite con 12 partizioni, ognuna delle quali rappresenta un mese dei dati delle vendite dell'ultimo anno. Alla fine del mese, la partizione meno recente viene estromessa (e archiviata o eliminata) e viene immessa una nuova partizione. Si tratta dello scenario a finestra scorrevole citato. Per la partizione del mese corrente è consentito l'accesso in lettura/scrittura, mentre quelle dei mesi precedenti sono in sola lettura. Ciascuna partizione è memorizzata in un filegroup separato. Questo schema offre tutti i vantaggi del partizionamento, ma non è la soluzione ottimale in tutte le circostanze.

Mia moglie, Kimberly, ha elaborato una modifica dello schema descritto in precedenza che consente un'indicizzazione più efficiente. Innanzitutto, suddividere la tabella vendite in due tabelle, una per l'unica partizione in lettura/scrittura e l'altra per le 11 partizioni in sola lettura, con una vista partizionata su entrambe le tabelle.

Questo consente alla tabella in lettura/scrittura di avere un numero ridotto di indici e a quella in sola lettura di avere più indici per supportare le query di report. Le operazioni DML (Data Manipulation Language) sui dati in lettura/scrittura risultano di conseguenza molto più efficienti, in quanto non devono mantenere un numero elevato di indici non cluster.

Inoltre, le query sui dati in lettura/scrittura non devono elaborare anche i dati in sola lettura. L'eliminazione di partizioni nei piani di query non è ottimale in SQL Server 2005, in particolare in presenza di predicati complessi, ma è stata molto migliorata in SQL Server 2008. Per ulteriori informazioni al riguardo, consultare il post del blog di Kimberly all'indirizzo sqlskills.com/blogs/kimberly/default,month,2007-10.aspx#a71c70243-3d57-4940-9af7-a802b73f2f93.

Per dimostrare quanto illustrato, spiegherò alcune delle funzionalità abilitate dal partizionamento su più filegroup.

Disponibilità parziale del database Si tratta della capacità di un database di restare in linea e accessibile durante una situazione di ripristino di emergenza, a condizione che il filegroup principale sia in linea. Se si dispone di un unico filegroup, durante il ripristino l'intero database non sarà disponibile. Se i dati sono suddivisi tra più filegroup, invece, durante il ripristino solo quelli danneggiati non saranno in linea e l'applicazione potrà continuare a operare.

Ripristino a fasi Questo schema è simile alla disponibilità parziale del database. Con un unico filegroup, l'unità di ripristino può essere una sola pagina o l'intero database. Con più filegroup, è possibile ripristinarne uno solo, consentendo in tal modo una disponibilità parziale del database.

Manutenzione partizionata del database Con uno degli schemi di partizione indicati in precedenza, è possibile rimuovere la frammentazione degli indici per ogni singola partizione, anche se si trovano tutte in un unico filegroup. Tuttavia, con un unico filegroup, viene meno la possibilità di eseguire verifiche di coerenza per filegroup. Ciò può ridurre notevolmente la quantità di dati che le verifiche di coerenza del database (DBCC, Database Consistency Checking) devono elaborare (e, al contempo, la quantità di risorse di I/O e CPU utilizzate).

Detto in parole semplici, è possibile utilizzare più partizioni nello stesso filegroup, ma una corrispondenza 1-1 tra partizioni e filegroup presenta numerosi vantaggi.

D Di recente si è verificato un grave problema in uno dei nostri server di database di fascia alta: una scheda di memoria malfunzionante causava danneggiamenti. Ce ne siamo resi conto quando nell'applicazione hanno iniziato a comparire dati casuali. Abbiamo eseguito DBCC CHECKDB, rilevando ogni genere di danneggiamento. Purtroppo, questi interessavano anche i backup, dai quali abbiamo dovuto eliminare i dati danneggiati manualmente.

In breve, abbiamo sostituito l'hardware che causava problemi e attivato i checksum di pagina. Vorremmo eseguire regolarmente verifiche di coerenza, ma non disponiamo di una finestra di manutenzione estesa e per eseguire il controllo del nostro database da 2,4 TB è necessario molto tempo. Cosa possiamo fare?

R Le domande relative alle modalità di esecuzione di verifiche di coerenza e altre operazioni di manutenzione sui VLDB (Very Large Database) sono sempre più frequenti. Molti amministratori di database rinunciano dopo aver scoperto che per l'esecuzione di DBCC CHECKDB è necessario più tempo di quanto consentito dalla finestra di manutenzione (in alcuni casi, il database è utilizzato 24 ore su 24, 7 giorni su 7 e non si trovano momenti per rinunciare all'overhead di I/O e CPU richiesto da DBCC CHECKDB per un lungo periodo di tempo).

A parte la possibilità di rinunciare e non eseguire affatto verifiche della coerenza (soluzione che non suggerisco), è possibile utilizzare quattro metodi. Ho personalmente aiutato alcuni clienti a utilizzare tutti e quattro i metodi.

Utilizzo dell'opzione WITH PHYSICAL_ONLY di DBCC CHECKDB Il comando DBCC CHECKDB esegue una grande quantità di verifiche di coerenza logica, con una notevole occupazione della capacità della CPU (trattandosi, fondamentalmente, di un'operazione associata alla CPU). L'utilizzo dell'opzione WITH PHYSICAL_ONLY consente di eseguire solo le verifiche della coerenza dei bitmap di allocazione DBCC CHECKALLOC, che sono molto rapide, e di leggere e controllare tutte le pagine allocate nel database, causando la verifica di eventuali checksum di pagina presenti. Questo trasforma DBCC CHECKDB in un'operazione associata all'I/O, con tempi di esecuzione molto più brevi (infatti, a volte è assai più rapida di un DBCC CHECKDB completo, con un notevole risparmio di tempo).

Suddivisione del carico di lavoro delle verifiche di coerenza Questo metodo comporta la suddivisione delle tabelle nel database in gruppi di uguali dimensioni (l'approccio più semplice è utilizzare i numeri di pagine) e il successivo utilizzo dei comandi DBCC CHECKTABLE per eseguire ogni sera verifiche di coerenza su tutte le tabelle di un singolo gruppo. Così, ad esempio, creando sette gruppi e controllandone uno al giorno, con un DBCC CHECKALLOC e un DBCC CHECKCATALOG una volta la settimana, è possibile eguagliare un DBCC CHECKDB, sebbene ripartito nell'arco di una settimana.

Utilizzo del partizionamento tabelle con più filegroup Le tabelle di maggiori dimensioni nel VLDB possono essere suddivise in più filegroup. Un esempio di schema di verifica della coerenza potrebbe essere eseguire un DBCC CHECKFILEGROUP giornaliero sul filegroup che contiene la partizione in lettura/scrittura e un DBCC CHECKFILEGROUP settimanale sui filegroup che contengono partizioni in sola lettura. La logica è che per i dati in sola lettura viene eseguito un backup completo e non sono utilizzati nell'elaborazione giornaliera. Di conseguenza, non devono essere sottoposti spesso a verifiche di coerenza, in quanto i danneggiamenti al loro interno non sono critici.

Ripartizione del carico di lavoro delle verifiche di coerenza su un altro server Questa opzione comporta il ripristino dei backup regolari del database completi su un altro server e l'esecuzione di DBCC CHECKDB su tale server. Ovviamente, questo consente di scaricare completamente il carico di lavoro delle verifiche di coerenza dal server di produzione. Tuttavia, lo svantaggio è che, qualora sia rilevato un danneggiamento, è necessario eseguire una verifica di coerenza sul server di produzione, sebbene ciò si verifichi di rado.

Come è evidente, gli amministratori di sistema hanno a disposizione numerose opzioni per eseguire verifiche della coerenza su un VLDB, consentendo di ridurre il carico necessario per un DBCC CHECKDB completo. Come detto in precedenza, ho personalmente aiutato alcuni clienti a utilizzare tutti e quattro gli approcci citati. Quindi ritengo che queste opzioni saranno adatte anche al caso in questione.

Suggerimento: utilizzo di trigger per l'implementazione della logica sul lato server

In alcuni scenari è necessario implementare la logica sul lato server mediante trigger. Tuttavia, esistono alcuni inconvenienti che è bene conoscere. Di seguito sono indicati alcuni elementi importanti da tenere in considerazione.

• I trigger sono avviati da istruzioni, non per riga. Detto ciò, assicurarsi di inserire logica aggiuntiva all'interno della logica trigger per gestire il caso in cui più righe o nessuna riga siano interessate dall'istruzione (i trigger sono avviati in base all'istruzione, anche se nessuna riga è interessata). I dati interessati sono contenuti in tabelle virtuali per le istruzioni DML (Data Manipulation Language). Tali tabelle possono essere unite, consentendo di elaborare i dati.
• L'esecuzione dei trigger all'interno della transazione è sincrona. Ricordare questo punto ogni volta che si desidera richiamare un'applicazione esterna o accedere a una risorsa esterna e non si ha la certezza che la velocità di risposta sarà breve o ragionevole. Ad esempio, se si attiva un'istruzione di aggiornamento per una tabella e in quell'operazione viene attivato un trigger, la transazione (la parte implicita dell'istruzione di aggiornamento) non terminerà finché tutta la logica del trigger non sarà completa. Se l'applicazione o il processo esterni restituiscono un codice di errore, SQL Server potrebbe annullare la transazione ed eseguirne il rollback (a seconda della gestione errori implementata e del codice di errore). Pertanto, se si devono eseguire operazioni esterne all'interno di un trigger e questo non è fondamentale per la transazione (o non deve essere eseguito entro lo stesso ambito), occorre scalarlo in un altro processo, prelevando i dati in modo asincrono. SQL Server 2005 ha introdotto SQL Server Service Broker, che può eseguire tali operazioni in modo asincrono.
• Gli errori causati da un'istruzione all'interno di un trigger sono estremamente difficili da individuare. Se la transazione comprende più tabelle, in caso di errore, assicurarsi di ispezionare i trigger e implementare una gestione errori adeguata. Se si modifica lo schema all'interno del proprio database, accertarsi di tenere traccia della logica dei trigger. In caso contrario, un semplice trigger può avere un forte impatto su prestazioni e stabilità globali. È possibile verificare la maggior parte delle conseguenze relative a modifiche dello schema mediante Visual Studio® for Database Professionals, che esegue verifiche di schema automatiche mentre si modifica il progetto ed effettua analisi di codice statico per controllare la presenza di incongruenze nei tipi di dati.

- Jens K. Suessmeyer, Database Consultant presso Microsoft

Paul S. Randal è Managing Director di SQLskills.com e MVP per SQL Server. Ha lavorato nel team di SQL Server Storage Engine in Microsoft dal 1999 al 2007, ha scritto il comando DBCC CHECKDB/repair per SQL Server 2005 ed è stato responsabile di Core Storage Engine durante lo sviluppo di SQL Server 2008. Paul, che vanta una notevole esperienza nell'ambito del ripristino di emergenza, della disponibilità elevata e della gestione dei database, partecipa regolarmente a conferenze in qualità di relatore. Il suo blog è disponibile all'indirizzo SQLskills.com/blogs/paul.

© 2008 Microsoft Corporation e CMP Media, LLC. Tutti i diritti riservati. È vietata la riproduzione completa o parziale senza autorizzazione.