SQL Q & A: sans trace
Les processus comme la sauvegarde, la restauration et le contrôle de cohérence peuvent se comporter de manière imprévisible, mais ils ont un sens.
Paul S. Randal
La rigueur de la restauration
Q. Je travaille les exigences en matière de temps d'arrêt pour certains de nos instances de SQL Server dans le cadre de la planification de la récupération après incident. Est-il suffisant de considérer seulement le temps que nécessaire à la restauration des sauvegardes ?
**R :**Non, il y a quelques autres choses à prendre en compte. Tout d'abord, considérez le temps total que nécessaire pour restaurer toutes les sauvegardes nécessaires. Cela inclut votre plus récente sauvegarde complète, la sauvegarde différentielle la plus récente et toutes les sauvegardes du journal des transactions. Toujours présumer le pire des cas, lorsque la base de données est détruit juste avant de prendre la prochaine sauvegarde complète, donc, vous avez le plus grand nombre de sauvegardes du journal.
Ensuite, examiner le temps supplémentaire qu'il faudra pour restaurer la sauvegarde complète initiale pour créer les fichiers journaux de transactions et de données, si elles n'existent pas déjà. Si vous avez activé l'initialisation instantanée des fichiers, fichiers de données seront créés presque instantanément. Toutefois, le fichier journal des transactions, doit être initialisées à zéro.
Si vous avez un gros fichier qui est plus de centaines de gigaoctets, une restauration peut prendre plusieurs heures. Si vous devez restaurer une sauvegarde différentielle, qui sera à nouveau complètement zéro-initialiser le fichier journal des transactions. Vous devrez tenir compte de ce temps. S'il n'y a aucune transaction supplémentaire des fichiers journaux qui ont été temporairement ajoutée (mais pas supprimé) vous aurez à zéro initialiser ces ainsi, potentiellement deux fois.
La dernière phase de la procédure de restauration de base de données consiste à exécuter une récupération sur incident. Le temps nécessaire pour cela va dépendre de combien enregistrements du journal des transactions que vous deviez faire reculer. Ils font partie des transactions non validées au moment de la sauvegarde de journal final. Si vous avez des transactions de longue durée dans votre base de données, supposer le pire. Supposons que vous aurez à faire reculer la quasi-totalité des transactions plus long possible. Vous devez ajouter cette année-là à l'équation.
Enfin, également considérer combien de temps il faut le serveur physique pour arriver au point où vous pouvez démarrer la restauration des sauvegardes. En d'autres termes, combien de temps cela prend-il le serveur au démarrage (exécution POST, vérifications de mémoire et ainsi de suite) et le démarrage Windows ? Cela pourrait également ajouter à l'interruption de service.
Si l'on considère toutes ces choses au pire des cas, cela vous donnera un temps d'arrêt possible maximal. Vous pourriez être surpris lorsque vous ajoutez tout vers le haut.
Ne pas interrompre
Q. Récemment, je suis tombé sur un problème intéressant. J'ai essayé d'interrompre un processus DBCC CHECKDB qui prenait plus de temps que d'habitude. J'ai trouvé je ne pouvait pas interrompre et a dû attendre longtemps pour la fin du processus. Pouvez-vous expliquer ce qui se passait ?
**R :**Ce comportement est prévisible, mais pas intuitif du tout. Lorsque DBCC CHECKDB démarre, il crée un instantané de base de données masquées. L'instantané de base de données est tenu de fournir de DBCC CHECKDB avec une vue cohérente et invariable de la base de données. De cette façon, DBCC CHECKDB sait qu'il vérifie la cohérence d'une base de données statique qui ne devrait pas avoir des corruptions.
Le processus crée un instantané de base de données de premier point de contrôle la base de données. Ensuite, il crée la capture instantanée de base de données vide et utilise le journal des transactions de la base de données pour lancer la récupération après incident sur l'instantané de base de données. En d'autres termes, il annule toutes les transactions actives dans l'instantané de base de données sans affecter réellement la véritable base de données. L'instantané de base de données devient donc cohérente.
Le temps que nécessaire pour lancer la récupération après incident alors que la création de l'instantané de base de données est proportionnelle à la quantité et la durée des transactions non validées dans la base de données lors du démarrage de la capture instantanée de base de données. S'il y a une transaction de longue durée, il peut prendre du temps pour faire reculer. Cela signifie la création de l'instantané de base de données et le processus de DBCC CHECKDB prend plus de temps.
Dans les cas extrêmes, lors de la création de l'instantané de base de données prend beaucoup plus longtemps que la normale et que vous décidez de tuer le processus de DBCC CHECKDB, rien ne se passera tout de suite. Vous devez attendre pour la récupération sur incident capture instantanée de base de données avant que le processus répond au signal kill. Vous ne pouvez pas interrompre la récupération sur incident, et il n'y a aucun distinction dans le code de récupération de crash dans SQL Server entre récupération sur incident réel après un arrêt inattendu et une récupération après incident pour un instantané de base de données.
La seule solution dans ce cas est de redémarrer l'instance de SQL Server, qui va supprimer l'instantané de base de données masquées. Cela ne fonctionne pas dans le cas d'une récupération sur incident vraie base de données normale. Dans ces cas, récupération après incident se poursuivra après le redémarrage de l'instance.
Il existe plusieurs façons vous pouvez éviter ce scénario. Essayez de n'exécuter que DBCC CHECKDB lorsque vous savez qu'il n'y a aucune transaction de longue durée active dans la base de données. Vous auriez d'avoir ces roulés dans le cadre de la création de l'instantané de base de données masquées DBCC CHECKDB. Vous pouvez également utiliser un mécanisme de vérification de cohérence, qui est de restaurer la base de données vers un autre serveur et ensuite la copie restaurée de vérification de cohérence. On évite ainsi la possibilité de transactions de longue durée au total.
Trouver le bon moment
Q. La semaine dernière, j'ai eu à restaurer les sauvegardes pour récupérer une table quelqu'un accidentellement tombé. La trace par défaut avait déjà perdu les informations sur lorsque la table a été supprimée, donc c'était un processus fastidieux pour trouver la position de sauvegarde auquel j'avais besoin de restaurer. Y a-t-il un moyen de trouver le bon point dans le temps auquel je dois restaurer ?
**R :**Chaque fois que vous essayez de déterminer quand une table est supprimée, vérifiez la trace par défaut. Cela rend la note des événements de langage de définition de données (DDL). Vous pouvez lire plus sur le trace par défaut sur la documentation en ligne de SQL Server.
Le seul problème avec la trace par défaut est que c'est une taille finie. Il a également été déconseillée en faveur d'événements étendus SQL Server 2012. Alors s'il y a beaucoup d'activités qui se produisent sur le serveur, le dossier de la quelle la table a été supprimée peut existe dans la trace plus.
Cela signifie que la seule façon de trouver où la table a été supprimée est de faire ce que j'appelle « inching via le journal de transactions. » Restaurez une copie de la base de données à un moment où la table a été connue. Puis à plusieurs reprises effectuer des restaurations de point-à-temps en utilisant les options WITH STOPAT et WITH STANDBY. Déplacer légèrement vers l'avant à chaque fois. Lorsque vous trouvez le temps quand la table n'existe plus, restaurez la base de données juste avant ce moment et vous pouvez récupérer les données de la table.
Ce processus est très complexe et peut prendre un certain temps. Chaque fois que vous restaurez la base de données à l'aide de WITH STANDBY, toutes les transactions non validées à ce moment-là sont annulées dans un fichier d'annulation. La prochaine restauration du processus annule l'annulation, un peu plus, restaurations et nouveau annule les transactions non validées dans le fichier d'annulation. Vous devez répéter ce processus jusqu'à ce que vous trouvez l'heure correcte.
Il y a une autre manière soignée pour ce faire. Analyser les enregistrements du journal dans les sauvegardes de journaux de transactions pour rechercher les transactions appelées DROPOBJ. Pour ce faire avec une fonction non documentée de table appelée fn_dump_dblog. Cela se comporte de la même manière que les fn_dblog plus largement connus, dont les enregistrements de journal de vidages du journal des transactions actif, utilisez une sauvegarde de base de données.
Vous pouvez utiliser cette fonction pour trouver la transaction que le passé de l'objet qui vous intéresse. Vous pouvez alors utiliser la transaction Log Sequence Number (ou LSN) pour exécuter une restauration WITH STOPBEFOREMARK = ' lsn : < le LSN de la transaction >'. Cela permettra de restaurer le journal des transactions jusqu'à, mais excluant, la transaction qui a chuté de la table. Faire de cette façon vous évite d'avoir à « pouces à travers le journal, » tel que décrit précédemment. Vous pouvez lire plus sur cette fonction et comment l'utiliser sur mon blog.
Filtrage d'événements
Q. Maintenant que SQL Trace a été déconseillée dans SQL Server 2012, je voudrais savoir plus sur les événements étendus. Pouvez-vous expliquer comment les événements étendus sont censés pour être plus léger que SQL Trace ?
**R :**La raison principale pour la performance différentielle entre les deux mécanismes est la manière dont les événements sont filtrés. Lorsque vous définissez une session de trace ou un événement, vous pouvez filtrer les événements dans les deux cas selon différents critères d'événement. Filtrage sur l'activité dans une certaine base de données est un bon exemple de cela.
Avec SQL Trace, les événements sont générés tout le temps. Le consommateur d'événements effectue le filtrage. Cela signifie que SQL Server est grevé de générer tous les événements, même si certains ne consommer. Ce processus est très inefficace.
Avec les événements étendus, le moteur d'événements étendus SQL Server exécute un filtrage des événements. Le moteur d'événements étendus évalue les prédicats spécifiés lors de la session d'événements. Cela signifie que lorsque l'événement se déclenche, il faut seulement un minimum de travail pour collecter les données d'événement de base. Cela permet au moteur d'événements évaluer le prédicat. Si le prédicat prend la valeur false, l'événement est immédiatement jeté. Le moteur d'événements n'effectue aucun traitement ultérieur. Cela réduit les performances de collecte des événements par rapport à SQL Trace.
En outre, SQL Trace recueille toutes les colonnes associées à un événement et ignore toutes les colonnes non requis. Les événements étendus, en revanche, ne recueille que colonnes et autres données spécifiées. Cela limite encore l'effort requis pour déclencher un événement.
Bien que les événements étendus est un mécanisme bien supérieur pour la collecte des données de dépannage, il peut toujours nuire performance SQL Server si la session d'événements n'est pas soigneusement construite. Si une session d'événements doit produire une pile des appels T-SQL chaque fois qu'un événement très commun se produit (par exemple l'acquisition d'un verrou ou une attente de thread), cela sera évidemment affecter les performances.
Avec deux mécanismes, vous devez tester la collecte d'événements avant de le mettre en production. Vous devez vous assurer que les performances de charge de travail ne soit compromis.
.jpg)
Paul S. Randal est le directeur général de SQLskills.com, directeur régional Microsoft et MVP SQL Server. Il a travaillé sur l'équipe SQL Server Storage Engine chez Microsoft de 1999 à 2007. Il a écrit DBCC CHECKDB/repair pour SQL Server 2005 et était responsable de Core Storage Engine pendant le développement de SQL Server 2008. Randal est un expert de récupération après incident, haute disponibilité et de maintenance de base de données et présente fréquemment à des conférences dans le monde entier. Il blogs à SQLskills.com/blogs/paul, et vous pouvez le retrouver sur Twitter à Twitter.com/PaulRandal.