Forum aux questions SQL
Les transactions distribuées, les compteurs de performance et les sauvegardes SQL
Paul s. Randal
Question : Nous utilisons un grand nombre de transactions distribuées et nous are maintenant étudier une haute disponibilité à l'une de nos bases de données critiques de la mise en miroir de base de données. Au cours des nos tests, nous avons découvert que les transactions distribuées échouent parfois après que nous essayons basculement de la base de données miroir. Pouvez-vous expliquer ce qui se passe ?
Réponse : Il s'agit de réellement une limitation documentée de l'utilisation des transactions distribuées. La limitation existe lorsque la mise en miroir d'une base de données ou l'envoi de journaux : pratiquement n'importe quelle technologie où le nom du serveur Windows est différent après l'exécution d'un basculement sur incident.
Lors de l'utilisation de transactions Microsoft Distributed Transaction Coordinator (MSDTC), le coordinateur de transactions local possède un ID de ressource identifie le serveur sur lequel il est en cours d'exécution. En cas de basculement sur incident mise en miroir, la base de données principale devient hébergé sur un serveur différent (le partenaire de mise en miroir) et l'ID de ressource de coordinateur de transactions est donc différent.
Si une transaction distribuée est active, le coordinateur de transactions sur la mise en miroir partenaire essaie de vérifier l'état de la transaction et ne peut pas car il a l'ID de ressource incorrect ; MSDTC n'est pas reconnaître comme il n'a pas été initialement impliqué dans la transaction distribuée. Dans ce cas, la transaction distribuée doit se terminer, le comportement que vous voyez.
Un problème similaire peut se produire avec les transactions entre bases de données (met à une simple transaction impliquant jour de plusieurs bases de données). Si une des bases de données impliqués est mis en miroir et il n'est pas, il est possible pour la validation dans les deux bases de données de la transaction entre bases de données. Si un basculement de mise en miroir forcé se produit (lorsque l'entité de sécurité et le miroir ne sont pas synchronisés et un basculement manuel, ce qui permet de perte de données, est effectué), la transaction validée dans la base de données en miroir peut-être être perdue : rompre l'intégrité de la transaction entre bases de données.
Cela peut se produire si la base de données en miroir n'est pas synchronisé (voir la Colonne de juin 2009 où j'explique ceci plus) et donc les enregistrements du journal pour la transaction entre bases de données validée n'ont pas encore été envoyées vers le miroir. Après le basculement forcé, la transaction n'existe pas dans la nouvelle base de données principale et l'intégrité de la transaction entre bases de données est interrompue.
Question : Récemment, je WAS contrôle certains compteurs de performance pour comprendre un problème avec notre stockage de base de données. Tout en effectuant cela, j'ai remarqué quelque chose de très étrange : il était déjà rien passe dans la base de données, s'est toujours activité d'écriture se produisant dans les fichiers de base de données. Ceci s'est produit pour à la fois les données et les fichiers journaux. J'ai fait même ne comportait aucune connexion à SQL Server, mais il toujours de suite. Comment à peut y avoir d'activité d'e/S lorsqu'il n'y a aucune connexion ?
Réponse : SQL Server dispose d'un certain nombre d'opérations de conservation de la maison qui doivent s'exécuter ; il s'agit des tâches en arrière-plan. Un ou plusieurs de ces sont quelque chose sur votre système et à l'origine des e/S. Voici une liste rapide des causes possibles :
Nettoyage de fantôme : Une opération de suppression marque uniquement les enregistrements comme supprimé, comme une optimisation des performances au cas où l'opération est annulée ; elle ne zéro pas physiquement l'espace. Une fois l'opération de suppression a été validée, quelque chose a réellement supprimer les enregistrements supprimés de la base de données. Pour cela, la tâche de nettoyage de fantôme. En savoir plus sur lui dans Cette annonce sur mon blog, qui explique également comment vérifier si la tâche de nettoyage de fantôme est en cours d'exécution.
La réduction automatique : Il s'agit d'une tâche que vous pouvez activer pour supprimer automatiquement les espaces vides dans une base de données. Il fonctionne en déplaçant des pages de la fin des fichiers de données vers le début, l'espace libre à la fin de la consolidation et puis tronquer les fichiers. Bien que vous pouvez l'activer, vous absolument ne devez pas : il provoque des problèmes de fragmentation d'index (entraînant une baisse des performances) et utilise beaucoup de ressources. Généralement, vous aurez la croissance automatique activée pour une base de données, trop, par conséquent, vous pouvez obtenir en un cycle réduction-accroissement-réduction-accroissement qui effectue la plupart du travail pour aucun gain. Vous pouvez vérifier l'état de toutes les bases de données avec cette requête :
SELECT name, is_auto_shrink_on FROM sys.databases;
Différé-déplacer : Cette tâche est responsable de la réalisation du travail nécessaire pour déplacer ou de tronquer les tables et index (la liste déroulante index peut être dû à une opération de reconstruction d'index, le nouvel index est construit et ensuite l'est supprimé). Pour les petites tables et index, la suppression d'allocation s'effectue immédiatement. Pour plus de tables et index, la désallocation s'effectue dans des lots par une tâche d'arrière-plan. Cela est de s'assurer que tous les verrous nécessaires peuvent être acquises sans manquer de mémoire. Vous pouvez utiliser les compteurs de performance différée-déplacer différents pour surveiller cette tâche, comme décrit dans la documentation en ligne ici.
Écritures différées : Cette tâche est responsable de la suppression des anciennes pages à partir du cache en mémoire (appelé le pool de tampons). Lorsque le serveur est sous pression de mémoire, pages peut-être être supprimé même s'ils possèdent des modifications sur ceux-ci. Dans ce cas, la page modifiée doit être écrite sur le disque avant de pouvoir être supprimé de la mémoire. Vous pouvez utiliser le “ écritures différées / s ” compteur de performance à surveiller cette tâche, comme décrit dans la documentation en ligne ici.
Tous ces éléments sont susceptibles d'apporter des modifications à la base de données. Tous les utilise de transaction pour apporter les modifications et chaque fois qu'une transaction est validée, les enregistrements du journal des transactions générées par la transaction doivent être écrite à la partie du journal de la base de données sur disque. Régulièrement lorsque des modifications sont en cours apportées à la base de données, un point de contrôle doit avoir lieu également de vider les données modifiées pages du fichier sur le disque. En savoir plus sur cette dans mon article dans le numéro de février 2009 de TechNet Magazine de sur Journalisation de compréhension et de récupération dans SQL Server.
Comme vous pouvez le constater, il vous suffit car il n'existe aucune connexion active à SQL Server, qui ne signifie pas nécessairement que le processus est passive : il peut être occupé par une ou plusieurs des tâches en arrière-plan. Si l'activité des e/S se poursuit de temps après toute votre activité de base de données est terminée, il convient également vérifier les tâches planifiées en cours d'exécution.
Question : Je suis un DBA involontaire et je 've essayé à différentes choses à venir opérationnel. L'administrateur de base de données précédente configuré travaux pour effectuer des sauvegardes dans un fichier mais je Cannot sais pas comment les restaurer. Existe-t-il un moyen pour voir les sauvegardes dans le fichier ? Et comment Can I les restaurer correctement ?
Réponse : Bien qu'il soit possible d'ajouter des sauvegardes vers le même fichier, la plupart des gens placer chaque sauvegarde dans un fichier séparé, avec un nom significatif (et généralement une combinaison de date/timestamp). Cela peut permettre d'éviter le problème que vous vous prenez et effectuer d'autres tâches plus facilement :
- Copie des sauvegardes pour des raisons de sécurité est facile lorsque chaque sauvegarde est dans son propre fichier. Si toutes les sauvegardes sont dans un seul fichier, une copie de la dernière sauvegarde seulement sont possibles en copiant le fichier de sauvegarde entière.
- Suppression des anciennes sauvegardes n'est pas possible lorsque toutes les sauvegardes sont dans un seul fichier.
- Écrasement accidentellement sur des sauvegardes existantes est peu probable si chaque sauvegarde dispose d'un fichier nommé séparément.
Malheureusement, qui ne permet pas de votre situation : vous disposez déjà d'un fichier avec plusieurs sauvegardes à l'intérieur de celui-ci. Toutefois, il existe deux façons vous pouvez restaurer les sauvegardes : manuellement ou à l'aide de SQL Server Management Studio (SSMS).
Pour voir les sauvegardes dans le fichier, vous pouvez utiliser SSMS pour créer une nouvelle unité de sauvegarde qui fait référence au fichier. Une fois la référence est créée, vous pouvez afficher les détails de sauvegarde pour ce qui se trouve dans cette unité de sauvegarde. Ou bien, vous pouvez utiliser la commande RESTORE HEADERONLY. Ces deux examine l'unité de sauvegarde et fournissent une seule ligne de sortie décrivant chaque sauvegarde du fichier. SSMS identifie les types de sauvegarde avec un nom convivial. À l'aide de la syntaxe correcte, vous devez déterminer quel type de sauvegarde chaque l'une consiste à utiliser les informations fournies dans l'entrée documentation en ligne de commande (voir ici pour la version de SQL Server 2008) afin que vous pouvez utiliser la commande RESTORE appropriée pour restaurer la sauvegarde.
Vous devez également travailler hors quelle sauvegarde que vous souhaitez restaurer. Ceci est un peu difficile car le nom de colonne de sortie dont vous avez besoin à partir de RESTORE HEADERONLY ne correspond pas à l'option que vous permet de le restaurer. Les sauvegardes dans le fichier sont numérotées de 1, avec 1 étant le plus ancien, et le numéro se trouve dans la colonne appelée position. Pour restaurer cette sauvegarde, vous devez utiliser le numéro dans la FILE WITH = < nombre > partie d'une commande RESTORE. Voici un exemple :
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;
Et ainsi de suite. Vous devez démarrer la séquence de restauration avec une sauvegarde de base de données, puis restaurez zéro ou plusieurs différentielle de base de données et/ou des sauvegardes du journal des transactions. Détail supplémentaire n'est pas abordée dans cette colonne, mais vous pouvez lire que plus d'informations sur Restaurer les séquences et les autres options de restauration sur que vous pouvez avoir besoin dans mon article du numéro de novembre 2009 Récupération à partir d'urgence à l'aide de sauvegardes.
À l'aide de SSMS, vous spécifiez le fichier de sauvegarde dans l'Assistant de restauration de base de données et il sera automatiquement vous montrer toutes les sauvegardes dans le fichier et vous permet de sélectionner ceux que vous souhaitez. Un exemple vous en est présenté à la figure 1.
.jpg)
Figure 1 utilisation de l'Assistant de SSMS restaurer la base de données pour afficher plusieurs sauvegardes dans un fichier.
Quelle que soit l'option choisie, il est essentiel que vous exercer à exécution de restaurations vers un autre emplacement avant d'avoir faire véritablement lorsqu'à la reprise après un sinistre. Une de mes favorites principes est “ vous n'avez pas une sauvegarde, jusqu'à ce que vous avez fait une restauration réussie. ”
Question : Je disposer d'une base de données assez grande que je dois pour copier toutes les semaines dans un environnement de développement. Mon problème est que la taille de la base de données a été récemment augmenté en prévision des données plus entrant et maintenant il est trop grande lors de la restauration dans l'environnement de développement. Comment CAN me procurer qu'il soit plus petite lorsque je le restaurer ?
Réponse : Il s'agit d'une question assez courante pour lequel il n'existe pas, malheureusement, une bonne réponse.
Une sauvegarde d'une base de données ne modifie pas la base de données en aucune façon — elle juste de lire toutes les parties utilisées de la base de données et les inclut dans la sauvegarde, ainsi que certaines du journal des transactions (voir Cette annonce sur mon blog pour obtenir une explication de pourquoi et le volume). Une restauration à partir d'une sauvegarde de base de données crée simplement les fichiers, écrit ce qui se trouvait dans la sauvegarde, puis exécute récupération sur la base de données. En fait, la vous aviez dans la base de données est ce que vous obtenez lorsque vous restaurez. Il n'existe aucune option pour réduire une base de données pour la restauration, la fragmentation d'index adresse sur restauration, mise à jour des statistiques sur la restauration ou toute autre opération personnes souhaiterez peut-être effectuer.
C'est le cas comment pour parvenir à ce que vous souhaitez ? Vous disposez de trois options, selon votre scénario précis.
Tout d'abord, vous pourriez effectuer une opération de réduction sur la base de données de production pour récupérer l'espace vide. Cela rendrait la copie restaurée de la base de données de la même que la production et sans l'espace perdu, mais à un coût potentiel élevé. La base de données de production devrait être cultivées à nouveau, et l'opération de réduction peut être extrêmement coûteux (en termes de processeur, e/S et du journal de transactions) et provoquer la fragmentation d'index. La fragmentation d'index devrait être adressée, prendre davantage de ressources. Il s'agit pas de l'option à utiliser. (Pour plus d'informations informations des aléas des mobilité de l'utilisation d'une réduction de fichier de données, reportez-vous à la section Cette annonce sur mon blog.) Vous pouvez envisager de uniquement supprimer l'espace libre à la fin du fichier (DBCC SHRINKFILE WITH TRUNCATEONLY) mais cela ne peut pas vous donner la réduction taille que vous avez besoin.
Deuxièmement, si vous devez uniquement restaurer la base de données de production qu'une seule fois dans le développement, vous devez disposer de suffisamment d'espace pour restaurer la base de données en taille réelle et réduire pour récupérer l'espace. Après cela, vous devez décider de la fragmentation créée par l'opération de réduction d'adresses.
Si vous avez l'intention d'exécuter des requêtes pour les tests de performances ou pour la création de rapports, la fragmentation peut provoquer une chute importante dans l'exercice de ces requêtes. Si vous n'êtes pas le cas, vous ne devrez pas peut-être supprimer la fragmentation du tout. Pour faire face à la fragmentation, vous ne pouvez pas reconstruire les index (à l'aide de la commande ALTER INDEX … REBUILD) comme qui nécessite de l'espace supplémentaire et provoquent la croissance à nouveau de la base de données, vous devrez peut-être réorganiser les index (à l'aide de la commande ALTER INDEX … REORGANIZE).
Si vous décidez de supprimer la fragmentation, prenez soin de ne changer de la base de données dans le modèle de récupération SIMPLE pour le journal des transactions n'augmente pas la taille de tous les enregistrements de journal de transaction générés par la réorganisation. Si vous laissez la base de données dans le modèle de récupération FULL, le journal continue à croître à moins d'effectuer des sauvegardes du journal — un élément dont vous voulez probablement éviter de traiter une copie de développement de la base de données.
Enfin, si vous avez besoin de restaurer la base de données de production plusieurs fois au cours de développement, vous n'allez pas à répéter les étapes de l'option 2 plusieurs fois. Dans ce cas, il serait préférable suivre les étapes de l'option 2, puis créer une sauvegarde supplémentaire de la base de données réduite (et peut-être défragmentée).
Cette deuxième sauvegarde permet ensuite d'effectuer plusieurs restaurations de la base de données de production au minimum taille.
Pour résumer, il est inutile de facile pour déplacer une base de données de production dispose de beaucoup d'espace libre à un environnement de développement sans espace libre thSQLat qu'il soit exigé pour la restauration initiale.
Grâce à Kimberly l. Tripp de SQLskills.com pour fournir une analyse technique de mois-ci ce !
Paul s. Randal est l'administrateur délégué de SQLskills.com, un directeur régional Microsoft et un serveur de SQL Server MVP. Il a travaillé sur l'équipe de moteur de stockage de SQL Server de Microsoft de 1999 à 2007. Randal a écrit DBCC CHECKDB/réparation pour SQL Server 2005 et était responsable de Core Storage Engine pendant le développement de SQL Server 2008. Il est un expert de reprise après sinistre, de la haute disponibilité et de la maintenance de base de données et un présentateur régulier à des conférences dans le monde entier. Blogs HE à SQLskills.com/blogs/paul, et vous pouvez lui trouver sur Twitter à Twitter.com/PaulRandal.