Forum aux questions SQL : Récupération d'urgence et mise en miroir de bases de données

  • Article

Sauvegardes, récupération d'urgence et mise en miroir de bases de données offrent des possibilités infinies pour des scénarios tout aussi infinis.

Paul S. Randal

Solution temporaire

Q. J'ai lu beaucoup de conseils contradictoires au sujet de combien les fichiers de données sur mon serveur dois-je configurer pour tempdb afin de réduire la prétention de PAGELATCH. Vous pouvez faire la lumière sur ce ?

**R :**Vous avez raison, il y a beaucoup de mauvais conseils là-bas sur la configuration de tempdb. Prétention PAGELATCH dans tempdb provient des charges de travail où plusieurs connexions simultanées créent et déposer des petites tables. Ces opérations nécessitent l'allocation et de-allocating pages de fichier de données de tempdb. Cela exige à son tour un accès exclusif à l'allocation en mémoire bitmap données fichier pages (pages spéciales qui font remarquer de quel fichier de données pages sont utilisés ou non).

S'il y a plusieurs connexions simultanées essayant simultanément d'allouer et désalloue, qu'une seule connexion peut avoir accès d'un bitmap de répartition à la fois. Ceci conduit à une prétention et une réduction des performances.

Une façon de soulager un peu de cette prétention est à activer l'indicateur de trace 1118 (vous pouvez en savoir plus à ce sujet sur mon blog de SQLskills.com). Un moyen plus efficace est de créer des fichiers de données tempdb multiples. En créant plusieurs fichiers de données, SQL Server interprétera des allocations (et de-allocations) round robin sur les fichiers de données. De cette façon, le nombre de bitmaps allocation augmente (un ou plusieurs par fichier de données) et la prétention globale du système va diminuer.

La question est : Les fichiers de données combien devrait créer ? Pour longtemps, les meilleurs conseils personnes pouvait donner étaient que la position de Microsoft officielle de création de données de tempdb un fichier pour chaque cœur de processeur logique (par exemple, deux processeurs avec chacun quatre carottes et technologie hyper-threading activé équivaut à huit cœurs logiques) était incorrecte. Cette approche peut conduire à relentissements avec les déversements de mémoire sur les serveurs avec plus de huit cœurs. Un autre multiple par la croyance était qu'à partir d'un quart à la moitié du nombre de coeurs de processeurs est un bon début.

Puis à la Conférence au sommet SQL PASS en fin 2011, Bob Ward, de support technique de Microsoft a présenté une formule plus élégante pour déterminer le nombre de fichiers, que vous devez créer. Si votre serveur a moins de huit cœurs logiques, utilisez le nombre de cœurs logiques comme le nombre de fichiers de données tempdb. Si votre serveur dispose de plus de huit cœurs logiques, démarrer avec huit fichiers de données tempdb, puis ajoutez quatre à la fois si la prétention continue.

Gardez à l'esprit, c'est des conseils généraux. Il y a au moins trois reprises où serveurs avec 64 cœurs ont besoin des fichiers de données tempdb 128 — deux fois le nombre de noyaux — pour atténuer la prétention. Votre kilométrage réel variera certainement.

Le Plan parfait

Q. J'ai récemment revu nos plans antisinistres et trouve que nous ne faisons pas des sauvegardes régulières de nos bases de données système. Vous conseiller cela ? Quel est le pire qui pourrait arriver si nous ne le faisons pas ?

**R :**C'est une bonne idée de revoir périodiquement vos plans de récupération après sinistre. C'est encore mieux à la pratique de ces plans. Une des choses que vous aurait découvert si vous avez exécuté par une bare metal restore de pratique est que votre environnement SQL Server ne serait pas a revenir à toutes les fonctionnalités, car vous serait manquer les bases de données système.

De nombreux administrateurs ne considèrent pas les bases de données système (maître, model, msdb et les bases de données de distribution de réplication) quand de planification ou d'essais d'une procédure de récupération après sinistre. C'est une grosse erreur. Ces bases de données sont essentielles pour vos instances de SQL Server. Vous avez besoin de protéger ces et de vérifier leur intégrité, autant que vous faites avec vos bases de données utilisateur.

Il n'y a pas de point à ce que vos données si vous ne peut pas se connecter à une instance de SQL Server.

Il en va de même si vous ne peut pas amener l'instance dans un état de travail, lorsque le maître est manquant, parce que vous n'avez pas toutes les informations de connexion nécessaires. Sans une sauvegarde du maître, vous cherchez à recréer toutes les informations de connexion pour toutes vos bases de données avant que les applications peuvent venir en ligne.

Il est essentiel de sauvegarder la base de données msdb, parce qu'elle contient tous vos travaux d'Agent SQL (par exemple les sauvegardes et les contrôles de cohérence), alertes de l'Agent SQL (par exemple les erreurs de haute intensité et premiers avertissements que votre sous-système d'e/S va mal), vos packages SSIS et vos tables d'historique des sauvegardes. Si vous avez tout type de système automatisé qui génère un ensemble de restauration des déclarations afin de faciliter faciles de base de reprise après sinistre, il est probablement utilisant les tables d'historique de sauvegarde dans msdb du pour faire. Sans une copie de msdb (si la catastrophe a votre tout sous-système d'e/S), vous auriez besoin de reconstituer les déclarations de restauration à la main, qui est fastidieux travail qui ajoute au temps d'arrêt.

La base de données model est essentiel si vous venez avec une configuration que vous souhaitez répliquer à toutes les nouvelles bases de données. Par exemple, si vous avez un environnement où chaque client hébergé a sa propre base de données, vous auriez besoin du modèle. Sans cela, vous devrez définir ces options de configuration à nouveau.

Bases de données de réplication distribution sont cruciales pour rétablir le flux de données de réplication sans avoir à effectuer de longues re-initializations de bases de données de souscription. Dans l'ensemble, vous n'avez pas une stratégie de récupération après sinistre sauf si vous avez sauvegarder vos bases de données système ainsi que vos bases de données utilisateur.

Pour démarrer, vérifiez ces SQL Server Books Online sur la sauvegarde de la base de données système et de restauration :

Croissance, croissance, croissance

Q. Nous allons faire difficulté à comprendre un problème où le journal des transactions continue de croître, même si nous le rétractable manuellement vers le bas. Nous allons commettre le œuvre dans nos opérations internes et des sauvegardes du journal, donc pourquoi le journal de poursuivre sa croissance ?

**R :**Ici, le problème semble être que vos développeurs utilisent les transactions imbriquées dans le code, sans se rendre compte qu'ils ne comportent pas la façon dont ils regardent. Un flux de code d'exemple qui illustre ce que vous faites est :

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

Le deuxième BEGIN TRAN, qui démarre une transaction imbriquée, ne commence pas vraiment une sub-transaction pour ce qui est le moteur de stockage est. Elle ne fait l'accroissement @ @TRANCOUNT de 1. Rien n'est écrit dans le journal de transaction indiquant qu'une nouvelle transaction a débuté. Tout le travail effectué par la transaction imbriquée est réellement une partie de la transaction initiale.

Cela signifie quand le COMMIT TRAN est délivré pour la transaction imbriquée, rien ne se passe sauf décrémentant la @ @TRANCOUNT, parce qu'il n'existe pas vraiment une transaction imbriquée. Rien n'est engagé jusqu'à la transaction initiale s'engage, apportant @ @TRANCOUNT retour à zéro. C'est pourquoi votre journal des transactions est en croissance. Vous avez encore une transaction unique et de longue durée.

Aussi, vous ne devrait pas des opérations de rétractable pour le journal de transactions régulières. Chaque fois que le journal des transactions doit se développer, la nouvelle partie du journal doit être initialisé à zéro. Il est remplacé par des zéros dans ce qui était auparavant la partie du volume NTFS. C'est le cas pour toute opération de récupération suite accident n'est pas échouer (voir mon blog SQLskills.com pour une explication).

Tandis que la nouvelle portion du journal des transactions est initialisé à zéro, toutes les activités d'exploitation forestière pour cette base de données sont en pause. Votre charge de travail s'arrête momentanément. Cette pause peut être assez longue si vous avez défini le montant de croissance auto journal transaction à être assez grande.

Il est toujours préférable d'éviter le journal des transactions ayant auto-croissance si possible. Si le journal des transactions croît à nouveau chaque fois que vous tendre, laissez-le seul. Il devrait être évident, qu'elle doit être supérieure à la taille à laquelle vous êtes le rétrécissement.

Miroir miroir

Q. Nous avons juste mis en place de base de données mise en miroir et trouve que nous pouvons n'est plus effectuer des reconstructions d'index pour certains de nos tables. L'énorme volume du journal de transactions générées surcharges de notre réseau et mise en miroir de base de données ralentit. Pourquoi cela se produit-il et comment nous pouvons travailler autour d'elle ?

**R :**Ce problème est rencontré par de nombreux qui mettent en œuvre la mise en miroir de base de données. Il découle du fait que le rendement et la fiabilité test direct fait avec la mise en miroir de base de données ne comprend pas la maintenance régulière de la base de données.

Beaucoup de gens utilise le modèle de récupération journalisée en bloc lors de l'exécution des opérations de reconstruction des index. Cela limite le montant de la transaction journal généré, pour le journal des transactions ne pousse pas au cours de l'opération. Mise en miroir de base de données permet uniquement le modèle de récupération complète, où les opérations de reconstruction des index sont entièrement consignées. Alors qu'ils peuvent générer autant volume du journal des transactions à la taille de l'index en cours de reconstruction.

La quantité d'enregistrements du journal des transactions supplémentaires lors de la scène index reconstruit dans le modèle de récupération complète pourrait être vraiment grande et saturer la liaison réseau entre les serveurs de base de données principale et miroir. Si cela se produit, une file d'attente d'envoi peut construire sur la base de données principale. Cela peut entraîner la transaction des délais de traitement pour toute charge application concomitante.

Cela signifie que, pour beaucoup de gens, des opérations de reconstruction indice ne possibles lors de l'utilisation de base de données mise en miroir. Cela est vrai même avec la base de données mise en miroir de la compression de flux journal incluse avec SQL Server 2008 et versions ultérieures.

Une stratégie de maintenance autre indice est d'utiliser ALTER INDEX... RÉORGANISER au lieu de ALTER INDEX... REMISE À NEUF. Réorganisation d'un index traite uniquement la fragmentation des index existants. Vous pouvez l'interrompre sans perdre le travail déjà effectué. Une reconstruction d'index, d'autre part, crée toujours un nouvel indice quel que soit le degré de fragmentation. Si vous interrompez l'il, vous obtenez rien. Tout est restaurée.

Pour les plus gros index qui ne sont pas pratiques pour reconstruire, effectuez les opérations suivantes :

  • **Première journée :**Démarrer Exécuter ALTER INDEX... RÉORGANISATION au cours de votre fenêtre de maintenance. Laissez-le pendant une heure. Tuer la commande. Il ne rien faire reculer et vous serez ont fait quelques progrès en ce qui concerne la suppression de la fragmentation de l'index.
  • **Jour deux :**Démarrer la réorganiser une fois de plus. Il ne se souvienne pas de la première journée, mais devrait traverser rapidement le travail à jour et suppression de la fragmentation de la partie suivante de l'index de départ. Il tuer après une heure.
  • **Après le jour deux :**Répétez jusqu'à ce que les gouttes niveau de fragmentation quelque seuil que vous avez créé ou juste indéfiniment le processus de jour en jour.

Cela vous permet de limiter la quantité de journal de transactions générées (et donc transmis à l'aide de la mise en miroir de base de données) par votre entretien régulier indice. Si vous souhaitez obtenir plus de pointe, au lieu de tuer le processus de réorganiser après un certain laps de temps, vous pouvez contrôler combien journal des transactions est généré et tuer une fois qu'il atteint un certain seuil (voir mon blog SQLskills.com pour plus de détails).

Paul S. Randal

Paul S. Randal est le directeur général de SQLskills.com, un directeur régional Microsoft et MVP SQL Server. Il a travaillé sur l'équipe du moteur de stockage SQL Server de Microsoft de 1999 à 2007. Il a écrit de réparation DBCC CHECKDB pour SQL Server 2005 et a été responsable pour le moteur de stockage de noyau au cours du développement de SQL Server 2008. Expert de la récupération d'urgence, de la haute disponibilité et de la maintenance de base de données, il anime fréquemment des conférences. Blogs d'a à SQLskills.com/blogs/paul, et vous pouvez lui trouver sur Twitter à twitter.com/PaulRandal.

Contenu associé