Domande e risposte su SQL
Transazioni distribuite, contatori delle prestazioni e di backup SQL
Paul s. Randal
Domanda: Utilizziamo una grande quantità di transazioni distribuite e abbiamo ora sta attualmente esaminando il mirroring del database per garantire un'elevata disponibilità a uno dei nostri database critici. Durante il nostro test abbiamo scoperto che le transazioni distribuite non riuscire a volte dopo che si tenta di failover al database mirror. È possibile spiegare cosa sta succedendo?
Risposta: Si tratta effettivamente una limitazione dell'utilizzo di transazioni distribuite documentata. La limitazione presente quando si utilizza il mirroring del database o la distribuzione dei log, fondamentalmente una tecnologia in cui il nome del server Windows è diverso dopo l'esecuzione di un failover.
Quando si utilizzano transazioni Microsoft Distributed Transaction Coordinator (MSDTC), il coordinatore delle transazioni locale dispone di un ID di risorsa che identifica il server su cui è in esecuzione. Quando si verifica un failover del mirroring, il database principale diventa ospitato su un altro server (il partner di mirroring) e pertanto l'ID di risorsa di coordinatore delle transazioni è diverso.
Se una transazione distribuita è attiva, il coordinatore delle transazioni sul mirroring partner tenta di verificare lo stato della transazione e non è possibile perché ha MSDTC; l'ID di risorsa errato non lo riconosca come non era originariamente coinvolti nella transazione distribuita. In tal caso, deve terminare la transazione distribuita, ovvero il comportamento della visualizzazione.
Un problema simile può verificarsi con le transazioni tra database (una semplice transazione che coinvolge gli aggiornamenti in più di un database). Se uno dei database coinvolti viene eseguito il mirroring e uno non è disponibile, è possibile che la transazione tra database eseguire il commit in entrambi i database. Se si verifica un failover del mirroring forzato (quando non sono sincronizzati principale e mirror e viene eseguito un failover manuale, consentendo di perdita di dati), la transazione di cui è stato eseguito il commit nel database con mirroring vengano persa, ovvero compromettere l'integrità delle transazioni tra database.
Questo problema può verificarsi se il database mirror non è SYNCHRONIZED (vedere la colonna giugno 2009 in cui è illustrata più) e in modo che i record del log per il commit delle transazioni tra database non sono ancora stati inviati al server mirror. Dopo il failover forzato la transazione non esiste nel nuovo database principale e pertanto l'integrità delle transazioni tra database è interrotto.
Domanda: Recentemente era monitoraggio alcuni contatori delle prestazioni di scoprire un problema con l'archiviazione dei database. Mentre in questo modo ho notato qualcosa di molto strano: Sebbene non esisteva alcuna operazione in corso del database, si è verificato ancora attività di scrittura nei file di database in corso. Questo non è disponibile per i file di dati e di log. Ho anche apportato che non erano Nessuna connessione a SQL Server, ma comunque continuata. Come può essere presente l'attività quando non vi sono connessioni?
Risposta: SQL Server dispone di un numero di operazioni di mantenimento di servizi di accesso a terze parti che è necessario eseguire; questi sono denominati attività in background. Uno o più di questi eseguendo un'operazione nel sistema e causare I/o. Ecco un elenco di possibili culprits rapido:
Pulitura fantasma: Record di un'operazione di eliminazione solo contrassegnato come eliminato, come un'ottimizzazione delle prestazioni nel caso in cui l'operazione viene annullata, non fisicamente zero fuori dello spazio. Dopo l'operazione di eliminazione è stato eseguito il commit, qualcosa ha effettivamente rimuovere i record eliminati dal database. Questa operazione viene eseguita dall'attività di pulitura fantasma. Per ulteriori informazioni sull'oggetto stesso in questo post sul mio blog, che inoltre spiegato come verificare se l'attività di pulitura fantasma è in esecuzione.
Compattazione automatica Si tratta di un'attività che è possibile attivare per rimuovere automaticamente lo spazio vuoto in un database. Funziona spostando le pagine dalla fine del file di dati per l'avvio consolidamento dello spazio disponibile alla fine e quindi troncando i file. Anche se è possibile attivarla, è assolutamente necessario non, ovvero viene causa problemi di frammentazione indice (causando una riduzione delle prestazioni) e utilizza molte risorse. In genere si disporrà di aumento automatico delle dimensioni attivato per un database, troppo, pertanto, è possibile ottenere in un ciclo di compattazione crescere-compattazione-aumento, che esegue una grande quantità di lavoro per alcun vantaggio. È possibile controllare lo stato di tutti i database con la query seguente:
SELECT name, is_auto_shrink_on FROM sys.databases;
Rilascio rinviata Questa attività è responsabile per svolgere il lavoro necessario per eliminare o troncare le tabelle e gli indici (l'indice della selezione potrebbe essere dovuto a un'operazione di ricostruzione dell'indice, viene creato il nuovo indice e quindi quello precedente viene interrotta). Per gli indici e tabelle di piccole dimensioni, la deallocazione della avviene immediatamente. Per le tabelle più grandi e gli indici, la deallocazione della avviene in batch di attività in background. Si tratta di garantire l'acquisizione di tutti i blocchi necessari senza l'esaurimento della memoria. È possibile utilizzare i vari contatori delle prestazioni discesa rinviato per monitorare questa attività, come descritto nella documentazione in linea qui .
Scritture Lazy: Questa attività è responsabile della rimozione delle vecchie pagine dalla cache in memoria (denominata pool di buffer). Quando il server è sotto pressione della memoria, le pagine potrebbero essere necessario rimuovere anche se le modifiche apportate su di essi. In tal caso, la pagina modificata deve essere scritto su disco prima di rimuovere dalla memoria. È possibile utilizzare il “ scritture Lazy / sec ” contatore delle prestazioni per monitorare questa attività, come descritto nella documentazione in linea qui .
Tutti questi sono potenzialmente in grado di apportare modifiche al database. Tutte utilizzano le transazioni per apportare le modifiche e ogni volta che una transazione viene vincolata, il record del log delle transazioni generati dalla transazione deve essere scritto la parte di log del database sul disco. Tanto in tanto alle modifiche apportate al database, un checkpoint deve verificarsi anche per svuotare i dati modificati pagine a file su disco. È possibile leggere ulteriori informazioni, vedere l'articolo nel numero di febbraio 2009 di TechNet Magazine in informazioni sulla registrazione e il ripristino in SQL Server.
Come si può vedere, semplicemente perché non esistono connessioni attive a SQL Server, che non significa necessariamente che il processo è statica, potrebbe essere occupato con uno o più attività in background. Se l'attività di I/O molto tempo dopo tutte le attività del database è stata completata, è consigliabile verificare la presenza di processi pianificati che potrebbero essere in esecuzione.
Domanda: Sono un amministratore del database involontari e si sono state tentando di out operazioni diverse a un esperto. L'amministratore del database precedente impostare processi per eseguire il backup in un file, ma è impossibile determinare la procedura per ripristinarli. Esiste un modo per visualizzare le copie di backup del file? E come posso ripristinare tali correttamente?
Risposta: Sebbene sia possibile accodare i backup nello stesso file, la maggior parte degli utenti inserire ogni backup in un file separato con un nome significativo (e in genere una combinazione di data/timestamp). Questo consente di evitare il problema sta affiancate e rendere più semplice eseguire altre attività:
- Copia di backup per sicurezza è facile quando ogni backup è in un file specifico. Se tutti i backup sono in un unico file, è possibile effettuare una copia di backup più recente solo copiando l'intero file di backup.
- Eliminazione vecchie copie di backup non è possibile quando tutti i backup sono in un unico file.
- Sovrascrittura accidentale di backup esistenti non è probabile se ogni backup dispone di un file denominato separatamente.
Sfortunatamente, che non consentono la situazione specifica, si dispone già di un file con più copie di backup in essa contenuti. Esistono, tuttavia, è possibile ripristinare i backup in due modi: manualmente o utilizzando SQL Server Management Studio (SSMS).
Per visualizzare le copie di backup del file, è possibile utilizzare SSMS per creare una nuova periferica di backup che fa riferimento al file. Una volta creato il riferimento, è possibile visualizzare i dettagli backup di questa periferica di backup. In alternativa, è possibile utilizzare il comando RESTORE HEADERONLY. Entrambi questi verranno esaminare la periferica di backup e fornire una riga di output che descrive ogni backup del file. SSMS identifica tipi di backup con un nome descrittivo. Utilizzando la sintassi corretta, è necessario scoprire il tipo di backup ogni uno è utilizzando le informazioni fornite nella documentazione in linea voce per il comando (vedere qui per la versione di SQL Server 2008) in modo che sia possibile utilizzare il comando RESTORE appropriato per ripristinare il backup.
Sarà inoltre necessario lavorare fuori i backup da ripristinare. Questo è un po' complesso perché il nome della colonna di output che necessari da RESTORE HEADERONLY non corrisponde per ripristinarla è necessario utilizzare l'opzione. I backup del file sono numerati da 1, con 1 venga meno recente, e il numero può essere presente nella colonna denominata posizione. Per ripristinare tale backup, è necessario utilizzare il numero di WITH FILE = < numero > parte di un comando RESTORE. Di seguito è riportato un esempio:
RESTORE DATABASE test FROM DISK = 'C:\SQLskills\test.bak' WITH FILE = 1, NORECOVERY;RESTORE LOG test FROM DISK = 'C:\SQLskills\test.bak'
WITH FILE = 2, NORECOVERY;
E così via. È necessario avviare la sequenza di ripristino con un backup del database e quindi ripristinare zero o più backup differenziali del database e/o i backup del log delle transazioni. Dettaglio non rientra nell'ambito di questa colonna, ma è possibile leggere che ulteriori informazioni su ripristino sequenze e le altre opzioni di ripristino che potrebbe essere necessario nel mio articolo nel numero di novembre 2009 in ripristino da guasti utilizzo dei backup.
Utilizzando SQL Server Management Studio, è possibile specificare il file di backup nella procedura guidata Ripristina Database e verranno automaticamente visualizzati tutti i backup nel file e consentono di selezionare quelli desiderati. Un esempio è illustrato nella Figura 1.
.jpg)
Figura 1 mediante la procedura guidata Ripristina Database SQL Server Management Studio per visualizzare più copie di backup in un file.
Indipendentemente dall'opzione scelta, è estremamente importante mettere in pratica eseguire ripristini in un'altra posizione prima che sia sufficiente ad reale durante il ripristino d'emergenza. Uno dei miei preferiti è principi “ non è necessario un backup fino a quando non è stato eseguito un ripristino. ”
Domanda: Dispone di un database di grandi dimensioni piuttosto che è necessario copiare ogni due settimane in un ambiente di sviluppo. Il problema è che il database di recente è stato raggiunto in previsione di ulteriori dati in entrata e ora è troppo grande quando è possibile ripristinare nell'ambiente di sviluppo. Come posso ottenere che risulti più piccoli quando si ripristina?
Risposta: Questa è una domanda piuttosto comune per il quale non esiste, purtroppo, una risposta valida.
Un backup di un database non altera in alcun modo il database, ovvero solo legge tutte le parti utilizzate del database e li include nel backup, più alcuni dei log delle transazioni (vedere il post sul mio blog per una spiegazione del motivo e la relativa quantità). Un ripristino da un backup del database appena vengono creati i file, scrive cosa è stato del backup e quindi esegue un ripristino sul database. In sostanza, era necessario nel database is what you get quando si esegue il ripristino. Non è disponibile alcuna opzione per compattare un database di ripristino, ripristino, update statistics su ripristino o di una delle altre operazioni utenti potrebbe essere necessario eseguire la frammentazione dell'indice indirizzo.
Così come per ottenere ciò che si desidera? Sono disponibili tre opzioni, a seconda dello scenario esatto.
Innanzitutto, era possibile eseguire un'operazione di compattazione del database di produzione per recuperare lo spazio vuoto. Questo renderebbe la copia del database ripristinata lo stesso esempio produzione e senza lo spazio inutilizzato, ma a costi potenzialmente elevato. Sarebbe necessario raggiunto nuovamente il database di produzione e l'operazione di compattazione può essere estremamente costoso (in termini di CPU, I/O e del log delle transazioni) e causare la frammentazione dell'indice. La frammentazione degli indici dovrebbe essere risolto ulteriori risorse. Non è l'opzione che si desidera utilizzare. (Per una spiegazione più approfondita di perils dell'utilizzo di un file di dati di compattazione, vedere il post sul mio blog) È possibile solo rimuovendo lo spazio disponibile alla fine del file (DBCC SHRINKFILE WITH TRUNCATEONLY), ma questo potrebbe non produrre la riduzione della dimensione che è necessario.
In secondo luogo, è necessario ripristinare il database di produzione una sola volta in fase di sviluppo, sarà necessario disporre di spazio sufficiente per ripristinare il database a dimensioni e compattazione per recuperare lo spazio. Dopo che è necessario decidere se risolvere la frammentazione è stata creata con l'operazione di compattazione.
Se si prevede di eseguire query per il test delle prestazioni o per la creazione di report, la frammentazione può provocare una distanza grande nelle prestazioni di queste query. Se non si è certi, non potrebbe essere necessario rimuovere la frammentazione affatto. Per risolvere la frammentazione, è Impossibile ricostruire gli indici (utilizzando il comando ALTER INDEX … REBUILD) che richiede spazio aggiuntivo e causerà un aumento delle dimensioni nuovamente il database, sarà necessario riorganizzare gli indici (utilizzando il comando ALTER INDEX … REORGANIZE).
Se si decide di rimuovere la frammentazione, prestare attenzione passare al database nel modello di recupero con registrazione minima, in modo non aumenta il log delle transazioni da tutti i record del log delle transazioni generati dalla riorganizzazione. Se si lascia il database nel modello di recupero completo, il registro continuerà ad aumentare a meno che non si esegua il backup del log, ovvero un'operazione è consigliabile evitare di gestione di una copia di sviluppo del database.
Infine, se è necessario ripristinare il database di produzione più volte in fase di sviluppo, non sarà necessario ripetere i passaggi della seconda opzione più volte. In questo caso, sarebbe preferibile attenersi alla procedura descritta nella seconda opzione e quindi creare un ulteriore backup del database compattato (e forse deframmentato).
Questo secondo backup consente quindi di eseguire più ripristini del database di produzione con dimensioni minime.
Per riepilogare, non esiste un metodo semplice per spostare un database di produzione che contiene una grande quantità di spazio libero per un ambiente di sviluppo senza thSQLat spazio libero necessario per il ripristino iniziale.
Grazie a Kimberly l. Tripp di SQLskills.com per fornire un'analisi tecnica di mese questo!
Paul s. Randal è di la gestione dei director di SQLskills.com un direttore regionale Microsoft e SQL Server MVP. Ha lavorato nel team SQL Server Storage Engine in Microsoft dal 1999 al 2007. Randal 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. È un esperto di ripristino di emergenza, alta disponibilità e la manutenzione del database e un regolare relatore a conferenze in tutto il mondo. Il suo blog all'indirizzo SQLskills.com/blogs/pauled è possibile trovare lui Twitter in Twitter.com/PaulRandal.