SQL Q&AErrori di I/O, mirroring del database e altro

Paul S. Randal

D Ho iniziato a eseguire controlli di coerenza periodici sui database che gestisco e ho persino aggiunto alcuni avvisi di SQL Agent per rilevare eventuali errori di I/O che si verificano durante le query degli utenti. Non so se la logica che ho implementato nei controlli e negli avvisi funzionerà in quanto nessuno dei database che gestisco presenta problemi di danneggiamento. Come è possibile creare problemi di danneggiamento in un database di test in modo da accertare che tutte le impostazioni configurate funzionino in modo corretto? Inoltre, sono disponibili altri metodi per rilevare eventuali errori di I/O?

R In SQL Server® 2000, il vecchio trucco per creare un database danneggiato per il test prevedeva l'eliminazione manuale di una riga dalla tabella sysindexes di un database di test. Tuttavia, con SQL Server 2005, il danneggiamento di una tabella di sistema utilizzando questo metodo risulta alquanto difficoltoso. Il metodo migliore per danneggiare un database di test consiste nel modificare un file di dati mediante l'uso di un editor esadecimale dopo aver arrestato il database. Di seguito viene illustrata la procedura:

• Arrestare il database in modo che i file di dati non vengano bloccati. Fare attenzione, tuttavia, a non disconnettere il database, in quanto se si danneggia la pagina errata, è possibile che non si sia in grado di connetterlo di nuovo.
• Selezionare un valore di offset superiore, ad esempio, a 100 pagine nel file (almeno 819.200 byte), ma assicurarsi che tale valore sia allineato a un limite di 8192 byte (un limite di pagina). Questo consentirà di evitare bitmap di allocazione e pagine di metadati critiche e sarà possibile avviare il database ed eseguire DBCC CHECKDB.
• Scrivere alcuni byte di zeri nel file in corrispondenza dell'offset selezionato. L'utilizzo di questa tecnica garantisce nella maggior parte dei casi l'introduzione di alcuni errori di danneggiamento delle intestazioni di pagina.

Tuttavia, il metodo più rapido per creare un database di test danneggiato consiste nell'utilizzare un database danneggiato già creato da un altro utente. Esempi di database SQL Server 2000 e SQL Server 2005 danneggiati sono disponibili sul mio blog (con descrizioni) all'indirizzo go.microsoft.com/fwlink/?LinkId=115151.

Quanto alla seconda domanda relativa alle eventuali azioni da intraprendere per rilevare errori di I/O, è opportuno attivare i checksum di pagina. Questa funzionalità è stata introdotta in SQL Server 2005 per proteggere un'intera pagina di database da errori generati dal sottosistema di I/O.

In sostanza, quando una pagina viene scritta su disco, l'ultima operazione eseguita da SQL Server consiste nel calcolare un checksum nell'intera pagina di 8 KB e nel contrassegnare il checksum nella pagina. Quando una pagina viene letta dal disco, se include un checksum di pagina, il checksum viene ricalcolato e confrontato con quello memorizzato nella pagina. La mancata corrispondenza tra i due valori indica che la pagina è stata danneggiata da un problema esterno a SQL Server e viene generato l'errore 824. L'errore viene visualizzato nel corso della connessione che ha causato la lettura della pagina e viene registrato nel log degli errori di SQL Server e nel registro eventi applicazioni di Windows®.

I checksum di pagina sono attivi per impostazione predefinita per tutti i database creati in SQL Server 2005 e SQL Server 2008. Tuttavia, è necessario attivarli manualmente per i database aggiornati dalle versioni precedenti di SQL Server. È possibile attivare i checksum di pagina utilizzando il seguente codice:

ALTER DATABASE mydb SET PAGE_VERIFY CHECKSUM;

Suggerimento: modificare la porta SQL Server predefinita

Per impostazione predefinita, la porta configurata delle istanze di SQL Server è 1433. Quando viene utilizzata da un'istanza, questa porta non può essere occupata da un'altra istanza. Pertanto, se si installa una seconda istanza (denominata) in ascolto sulla rete tramite tcp, sarà necessario utilizzare un'altra porta. In alcuni casi, è opportuno che l'amministratore modifichi la porta per motivi di offuscamento (anche se questa forma di offuscamento è secondaria e può essere facilmente interrotta da uno scanner per porta). In tal caso, è necessario configurare il client per l'utilizzo di una porta differente. Esistono tre approcci comuni per l'esecuzione di questa operazione.

Primo, presupponendo che l'amministratore abbia modificato la porta di un'istanza in 5555, è possibile specificare semplicemente il numero di porta dell'istanza nel nome del computer a cui si desidera connettersi utilizzando la sintassi MyServername,5555. Se la porta viene modificata di nuovo, i client dovranno modificare nuovamente anche la relativa sezione connectionStrings.

Un'altra opzione consiste nell'utilizzare gli alias di SQL Server, che sono configurati sul client. Oltre a specificare il nome di un alias, è necessario specificare anche il nome del server, il nome della porta e il protocollo. Una volta configurato, l'alias può essere utilizzato come nome di server per connettersi all'istanza del database. Il vantaggio di questo approccio è rappresentato dal fatto che le modifiche di configurazione del server possono essere distribuite da un amministratore di dominio, in quanto le impostazioni sono memorizzate nel Registro di sistema.

La terza opzione per le istanze denominate, che prevede che l'utente conosca solo il nome dell'istanza e specifichi il nome utilizzando MachineName\InstanceName in connectionString, consiste nell'utilizzare il servizio SQL Server Browser. Il servizio è già implementato in SQL Server 2000 come parte di un servizio in esecuzione. Tuttavia, in SQL Server 2005, il servizio SQL Server Browser è stato creato come servizio separato. Oltre a individuare le istanze per il computer, il servizio è in grado di rispondere alle richieste UDP (User Datagram Protocol) in ingresso sulla porta 1434 con il numero di porta appropriato per l'istanza richiesta, attivando il reindirizzamento per il client e supportando una connessione trasparente.

- Jens K. Suessmeyer, Database Consultant presso Microsoft

D Per rimuovere completamente la frammentazione nel mio database, ho impostato un piano di manutenzione notturno che consente di ricostruire tutti gli indici nel database di produzione, che viene eseguito su SQL Server 2005 Enterprise Edition con SP2. Avendo, tuttavia, notato che questo determina un aumento eccessivo delle dimensioni del database, ho aggiunto un passaggio che prevede la compattazione di tutto lo spazio aggiuntivo in modo da liberare spazio sul disco. Ora sembra che il passaggio di ricostruzione non funzioni in modo corretto. Che cosa è successo?

R Hai sollevato un problema comune a cui molti utenti devono far fronte durante l'impostazione di un piano di manutenzione. Sei entrato in un ciclo che vede l'alternarsi delle operazioni di compattazione-aumento di dimensioni-compattazione-aumento di dimensioni.

Quando un indice viene ricostruito, prima che l'indice esistente venga eliminato, viene creata una nuova copia dell'indice. Questa procedura richiede spazio aggiuntivo nei file di database, in genere la stessa quantità di spazio utilizzata dall'indice corrente. In SQL Server 2000, lo spazio aggiuntivo era necessario anche per l'ordinamento delle righe dell'indice (circa il 20% delle dimensioni dell'indice); tuttavia, per semplificare il processo di ricostruzione dell'indice, questo requisito è stato eliminato in SQL Server 2005.

Talvolta, gli amministratori desiderano rimuovere lo spazio aggiuntivo creato durante la ricostruzione dell'indice e, pertanto, aggiungono un'operazione di compattazione al piano di manutenzione dopo il passaggio di ricostruzione. Non è molto noto, tuttavia, che l'operazione di compattazione causa una frammentazione dell'indice in ragione della natura del relativo algoritmo. Ne consegue che l'indice appena ricostruito e deframmentato viene immediatamente frammentato, annullando l'effetto stesso della relativa ricostruzione.

Dato che le dimensioni del file di database aumenteranno di nuovo la volta successiva in cui viene eseguita la ricostruzione dell'indice, è preferibile non rimuovere dal database lo spazio aggiuntivo ed evitare di eseguire l'operazione di compattazione. Inoltre, l'esecuzione costante di operazioni di compattazione e di aumento di dimensioni sui file di database causa una frammentazione dei file a livello di sistema operativo, determinando, analogamente alla frammentazione dell'indice, un peggioramento delle prestazioni.

Infine, è opportuno ridurre la frequenza con cui viene eseguito il processo di ricostruzione degli indici. È consigliabile utilizzare un metodo alternativo, come lo strumento DBCC INDEXDEFRAG che ho scritto per SQL Server 2000 o la nuova sintassi ALTER INDEX REORGANIZE in SQL Server 2005 e SQL Server 2008.

È disponibile inoltre un articolo molto utile in cui viene illustrata la frammentazione dell'indice e vengono fornite istruzioni su come rimuovere la frammentazione (go.microsoft.com/fwlink/?LinkId=115154). Sebbene questo articolo sia stato scritto per SQL Server 2000, i concetti restano tutti validi.

D Nella mia organizzazione stiamo valutando la strategia di ripristino di emergenza e ritengo che il mirroring del database rappresenti la soluzione ideale per la nostra situazione. Sul server che sto tentando di proteggere vengono eseguiti numerosi database non correlati (il risultato di un precedente progetto di consolidamento dei server) e vorrei utilizzare il mirroring per tutti i database disponibili. La domanda che mi pongo è la seguente: "Quanti database è possibile sottoporre a mirroring prima che le prestazioni inizino a risentirne?

R Non esiste una risposta precisa a questa domanda: il numero di database varia in base al contesto. Le linee guida pubblicate suggeriscono di non eseguire il mirroring di più di 10 database per ciascuna istanza; tuttavia 10 è solo una stima approssimativa del valore che verrà considerato come numero massimo dalla maggior parte degli utenti. Per quanto riguarda la configurazione dell'hardware, è necessario considerare i seguenti fattori:

• Quanta memoria è disponibile nelle istanze principali e mirror? In teoria, la quantità di memoria deve essere identica.
• Qual è la potenza di elaborazione delle istanze principali e mirror? Anche la potenza di elaborazione deve essere identica.
• Quanta larghezza di banda è disponibile nel sottosistema di I/O nell'istanza mirror? La quantità di larghezza di banda deve essere identica a quella dell'istanza principale.
• Quanti log delle transazioni vengono generati dal carico di lavoro su ciascun database?
• Quanta larghezza di banda di rete è disponibile tra le istanze principali e mirror?

Gli ultimi due fattori sono i più critici. Se la larghezza di banda di rete disponibile tra le due istanze non è sufficiente per gestire la frequenza totale di generazione del log delle transazioni al secondo su tutti i database sottoposti a mirroring, le prestazioni subiranno un calo nei database principali. SQL Server 2008 consente di ovviare al problema con la compressione del flusso del log.

Il secondo fattore critico da considerare è rappresentato dai requisiti di memoria e thread per il mirroring. Ciascun database con mirroring richiede un thread e una certa quantità di memoria. Sui server poco potenti l'esecuzione di un numero elevato di database con mirroring potrebbe costituire un carico eccessivo quando combinato con il normale carico di lavoro.

È necessario inoltre considerare la modalità in cui eseguire il mirroring del database. In modalità sincrona, non è possibile eseguire il commit delle transazioni sul database principale fino a quanto tutti i record dei log delle transazioni non sono stati copiati nel log delle transazioni del database con mirroring. Pertanto, qualsiasi ritardo causato da una rete sovraccarica può generare un problema di prestazioni del carico di lavoro sul database principale.

In modalità asincrona, è possibile eseguire il commit delle transazioni sul database principale senza dover attendere, ma un ritardo della rete potrebbe determinare un aumento del numero di log delle transazioni in attesa di essere inviati. Questo può, a sua volta, causare problemi correlati alle dimensioni dei log delle transazioni. Quel che è peggio è che, in caso di errore, tutti i log delle transazioni non inviati verranno persi. Pertanto, più è elevato il numero di log delle transazioni non inviati, maggiore sarà la possibilità di perdita di dati in una situazione di ripristino.

Gli scenari possono variare considerevolmente e, personalmente, ho avuto modo di assistere ad alcuni interessanti esempi in ambienti di produzione reali. Ho esaminato, ad esempio, un ambiente con 150 database, sui quali veniva eseguito un numero piuttosto esiguo di attività e non tutte venivano effettuate contemporaneamente. Il mirroring di tutti i 150 database veniva eseguito senza problemi.

Al contrario, ho esaminato una configurazione che includeva solo tre database con carico elevato, ma senza una connessione di rete efficace. In tale scenario, è possibile eseguire a stento il mirroring di un singolo database prima che la mancanza di larghezza di banda di rete determini un peggioramento delle prestazioni del carico di lavoro.

La chiave per il corretto funzionamento del mirroring del database è eseguire innanzitutto il calcolo della generazione dei log. Se la larghezza di banda di rete disponibile è in grado di supportare il numero di database di cui si desidera eseguire il mirroring, non vi sarà alcun problema. Verificare la configurazione prima di distribuirla nell'ambiente di produzione e assicurarsi di includere tutte le operazioni che possono generare il log delle transazioni, in particolare le operazioni di manutenzione del database eventualmente eseguite.

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. Paul ha scritto il comando DBCC CHECKDB/repair per SQL Server 2005 ed era 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 in SQLskills.com/blogs/paul.

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