Domande e risposte su SQL Ripristino di emergenza e mirroring del database

  • Articolo

I backup, il ripristino di emergenza e il mirroring del database presentano numerose varianti ideali per moltissimi scenari.

Paul S. Randal

Soluzione temporanea

D. Ho letto un sacco di consigli contraddittori su quanti file di dati sul mio server devo configurare per tempdb per ridurre eventuali conflitti PAGELATCH. Può far luce su questo?

**R.**Tu sei corretto — c'è un sacco di consigli poveri là fuori sulla configurazione del database tempdb. Contesa PAGELATCH in tempdb viene da carichi di lavoro dove molte connessioni simultanee creare ed eliminare tabelle di piccole dimensioni. Queste operazioni richiedono l'allocazione e la deallocazione pagine del file di dati nel database tempdb. Questo a sua volta richiede l'accesso esclusivo a pagine (pagine speciali che fanno notare del file di dati che le pagine sono in uso o non) di file di dati bitmap allocazione in memoria.

Se ci sono molte connessioni simultanee, cercando contemporaneamente di allocare e deallocare, solo una connessione può avere accesso a una bitmap di allocazione in una sola volta. Questo porta alla contesa e una riduzione delle prestazioni.

Un modo per alleviare un po ' di questa contesa è di attivare il flag di traccia 1118 (si può imparare di più su questo sul mio blog all'indirizzo SQLskills.com). Un modo più efficace è quello di creare database tempdb più file di dati. Con la creazione di più file di dati, SQL Server eseguirà allocazioni (e de-allocations) round-robin, il file di dati. In questo modo, aumenta il numero di allocazione bitmap (uno o più per ogni file di dati) e diminuirà la contesa del sistema complessivo.

La domanda è: Quanti file di dati deve creare? Per lungo tempo, i migliori consigli di persone potrebbe dare sono che la posizione ufficiale di Microsoft di creare un database tempdb dati file per ogni core del processore logico (ad esempio, due CPU con quattro core ciascuno e hyper-threading abilitata equivale a otto core logici) non era corretta. Questo approccio può portare a rallentamenti con perdite di memoria sul server con più di otto core. Un'altra diffusa convinzione era che a partire da un quarto alla metà del numero di core del processore era un buon inizio.

Poi alla conferenza al vertice di SQL PASS a fine 2011, Bob Ward dal supporto tecnico Microsoft ha presentato una formula più elegante per determinare il numero di file che è necessario creare. Se il server dispone di meno di otto core logici, utilizzare il numero di core logici come il numero di file di dati del database tempdb. Se il server dispone di più di otto core logici, iniziare con otto file di dati del database tempdb e quindi aggiungere quattro di più in un momento, se il conflitto continua.

Tenete a mente questo è consulenza generalizzata. Ci sono almeno tre occasioni dove server con 64 core hanno bisogno di un file di dati di 128 tempdb — due volte il numero di core — per alleviare la contesa. Il vostro chilometraggio effettivo varierà sicuramente.

Il piano perfetto

D. Recentemente ho rivisto i nostri piani di recupero di disastro e trovato che non stiamo facendo i backup regolari del nostro database di sistema. Consigliate questo? Qual è la cosa peggiore che potrebbe accadere se non lo facciamo?

**R.**È una buona idea di rivedere periodicamente i piani di ripristino di emergenza. È anche meglio per la pratica di tali piani. Una delle cose che vi avrebbero scoperto se è stato eseguito attraverso un ripristino bare-metal di pratica è che ambiente SQL Server non sono tornati alla piena funzionalità, perché mancherebbe i database di sistema.

Molti amministratori di database non considerare quando i database di sistema (master, model, msdb e qualsiasi database di distribuzione di replica) pianificazione o testare una procedura di recupero di disastro. Questo è un grosso errore. Questi database sono fondamentali per le istanze di SQL Server. È necessario proteggere queste e verificare la loro integrità come fare con i database utente.

Non non c'è nessun punto di avere i dati disponibili, se non è possibile connettersi a un'istanza di SQL Server.

Lo stesso vale se si non può portare l'istanza in uno stato di lavoro quando il padrone è manca, perché non avete tutte le informazioni di login necessarie. Senza un backup del master, che stai guardando ricreare tutte le informazioni di login per tutti i database prima che le applicazioni possono venire online.

È fondamentale per eseguire il backup del database msdb perché contiene tutti i processi di agente SQL (ad esempio backup e controlli di coerenza), avvisi di SQL Agent (ad esempio errori di elevata gravità e early warning che il sottosistema dei / O sta andando male), i pacchetti SSIS e le tabelle di cronologia di backup. Se avete qualsiasi tipo di sistema automatizzato che genera un set di ripristino istruzioni per facilitare la semplice database di disaster recovery, è probabile che utilizzando le tabelle di cronologia di backup nel database msdb di farlo. Senza una copia di msdb (se il disastro si tirò fuori il vostro intero sottosistema dei / O), sarebbe necessario mettere insieme le istruzioni RESTORE a mano, che è un lavoro noioso che aggiunge all'inattività.

Il database del modello è fondamentale se siete capitati con una configurazione che si desidera replicare in tutti i nuovi database. Ad esempio, se avete un ambiente dove ogni cliente ospitato ha il proprio database, è necessario il modello. Senza di essa, è necessario impostare le opzioni di configurazione nuovamente.

I database di distribuzione di replica sono critici per ristabilire i flussi di dati di replica senza dover effettuare lunghi re-initializations del database di sottoscrizione. Nel complesso, non hai una strategia di ripristino di emergenza a meno che tu stai facendo il backup database di sistema, come pure i database utente.

Per iniziare, check out queste SQL Server Books Online di backup del database di sistema e ripristino:

Cresce, cresce, cresce

D. Stiamo avendo difficoltà a capire un problema dove il CITL continua a crescere, anche se abbiamo compattare manualmente verso il basso. Noi stiamo commettendo il lavoro nelle nostre operazioni interne ed eseguire i backup del log, così perché il log di continuare a crescere?

**R.**Qui il problema sembra essere che gli sviluppatori utilizzano transazioni nidificate nel codice, senza rendersi conto che essi non comportano il loro modo di che guardare. Un flusso di codice di esempio che illustra quello che stai facendo è:

BEGIN TRAN; Do some work … BEGIN TRAN; Do some more work … COMMIT TRAN Continue with more work …

La seconda BEGIN TRAN, che avvia una transazione nidificata, davvero non si avvia un sub-transaction per quanto riguarda il motore di archiviazione. Tutto ciò che fa è incremento @ @ TRANCOUNT di 1. Non c'è niente di scritto nel log delle transazioni che indica che ha iniziato una nuova transazione. Tutto il lavoro fatto dalla transazione nidificata è in realtà parte della transazione iniziale.

Questo significa che quando il COMMIT TRAN è rilasciato per la transazione nidificata, non succede nulla tranne decrementare il @ @ TRANCOUNT, perché non c'è davvero una transazione nidificata. Nulla è impegnata finché la transazione iniziale si impegna, portando @ @ TRANCOUNT indietro fino a zero. Ecco perché sta crescendo il log delle transazioni. Avete ancora una singola transazione lunga.

Inoltre, si non dovrebbero essere eseguendo le operazioni di compattazione registro transazioni regolari. Ogni volta che il log delle transazioni deve crescere, la nuova parte del log deve essere inizializzata su zero. Viene sovrascritto con zeri in quello che in precedenza era quella porzione del volume NTFS. Questo accade in modo da non fallire qualsiasi operazione di recupero successivo crash (vedere mio blog all'indirizzo SQLskills.com per una spiegazione).

Mentre la parte nuova del log delle transazioni viene inizializzata su zero, tutte le attività di registrazione per il database è in pausa. Il carico di lavoro si ferma momentaneamente. Questa pausa potrebbe essere abbastanza lunga se hai impostato l'importo della transazione registro auto-crescita a essere molto grande.

È sempre meglio evitare il log delle transazioni avendo auto-crescere se possibile. Se il log delle transazioni cresce nuovamente ogni volta che si restringerlo, lasciarlo solo. Dovrebbe essere ovvio che deve essere maggiore della dimensione a cui si sta diminuendo.

Specchio specchio

D. Abbiamo appena implementato database mirroring e trovato che non possiamo eseguire ricrea indice per alcuni dei nostri tavoli. L'enorme quantità di log delle transazioni generate Overload rallenta la nostra rete e il mirroring del database. Perché questo accade e come possiamo lavorare intorno ad esso?

**R.**Questo problema viene rilevato da molti che implementa il mirroring del database. Esso deriva dal fatto che le prestazioni e affidabilità test fatto dal vivo con il mirroring del database non include la manutenzione del database regolari.

Molte persone utilizzano il modello di recupero con registrazione di massa quando si eseguono operazioni di rebuild index. Questo limita la quantità di log delle transazioni generate, così il log delle transazioni non cresce durante l'operazione. Mirroring del database consente solo il modello di recupero completo, dove le operazioni di ricostruzione indice sono completamente registrate. Quindi possono generare più volume di registro delle transazioni come la dimensione dell'indice deve essere rigenerata.

La quantità di record del log di transazione aggiuntiva quando indice performante ricostruisce nel modello di recupero completo potrebbe essere davvero grande e saturare il collegamento di rete tra il server principale e mirroring del database. Se ciò accade, una coda di invio può accumularsi sul database principale. Questo potrebbe causare delle transazioni dei ritardi di elaborazione per qualsiasi carico di lavoro di applicazione simultanea.

Ciò significa che, per molte persone, le operazioni di ricostruzione indice non è possibile quando si utilizza il mirroring del database. Questo è vero anche con la compressione dei flussi log incluso in SQL Server 2008 e versioni successive di mirroring del database.

Una strategia di manutenzione indice alternativo è quello di utilizzare ALTER INDEX... RIORGANIZZARE invece di ALTER INDEX... RICOSTRUIRE. La riorganizzazione di un indice solo indirizzi esistenti frammentazione dell'indice. Si può interrompere senza perdere il lavoro già completato. Ricostruzione di un indice, invece, costruisce sempre un nuovo indice indipendentemente dalla misura della frammentazione. Se si interrompe di esso, si ottiene niente. Tutto ciò viene eseguito il rollback.

Per gli indici più grandi che non sono pratici per ricostruire, attenersi alla seguente procedura:

  • **Giorno uno:**Avviare l'esecuzione di ALTER INDEX... RIORGANIZZARE durante la finestra di manutenzione. Lasciar correre per un'ora o così. Uccidere il comando. Esso nulla non rollback e ti hanno fatto qualche progresso in termini di rimozione della frammentazione dall'indice.
  • **Giorno due:**Avviare la riorganizza nuovamente. Non ricorda il primo giorno, ma deve attraversare rapidamente il lavoro che hai fatto sul giorno e iniziare a rimozione della frammentazione nella parte successiva dell'indice. Uccidere ancora dopo un'ora o così.
  • **Dopo giorno due:**Ripetere fino a quando le gocce livello di frammentazione sotto qualsiasi soglia che hai stabilito, o semplicemente continuare indefinitamente il processo giorno per giorno.

Questo consente di limitare la quantità di log delle transazioni generate (e quindi trasmesse utilizzando il mirroring del database) dalla vostra manutenzione regular index. Se si desidera ottenere più avanzata, invece di uccidere il processo riorganizza dopo un certo periodo di tempo, è possibile monitorare quanti log delle transazioni viene generato e ucciderlo una volta raggiunta una certa soglia (vedi mio blog all'indirizzo SQLskills.com per maggiori dettagli).

Paul S. Randal

Paul S. Randal è l'amministratore delegato di SQLskills.com, un direttore regionale Microsoft e MVP per SQL Server. Ha lavorato il team di Microsoft SQL Server Storage Engine dal 1999 al 2007. Egli 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. Randal è un esperto di disaster recovery, alta disponibilità e manutenzione del database ed è un presentatore regolarmente a conferenze in tutto il mondo. Ha Blog at /Blogs/Paul, e lo si può trovare su Twitter a Twitter.com /PaulRandal..

Contenuto correlato