Forum aux questions SQL : Sauvegarde et restauration
SQL Server est une plateforme puissante, mais qui exige néanmoins un degré de finesse élevé en matière de paramétrage des journaux de transaction et d’autres questions de configuration.
Paul s. Randal
Journaux des transactions XXXL
Q : Notre produit utilise SQL Server pour stocker des données. Régulièrement, nous publions une nouvelle version de notre produit inclut un script de mise à niveau pour s'exécuter sur la base de données. Comme nous avons tester notre script dernière mise à niveau sur une base de données de test représentatif, le fichier journal des transactions est passé à plus de 40 Go. Nous souhaitons empêcher le fichier journal de grandir de si grande. Quelles sont nos options ? Nous devons tenir avec le modèle de récupération complète pour la récupération après incident.
R : Commence, c'est génial que vous testez par rapport aux données de client représentatif. C'est le cas de nombreux cas, je vois des fournisseurs d'applications en couches pour ces types de scripts sur les petits jeux de données de test et en les libérant à leurs clients qui exécutent ensuite dans toutes sortes de problèmes de production. Je vais répondre à votre question comme si vous êtes l'utilisateur. Vous pouvez ensuite traduire dans le contexte de vos clients.
Vous vous qu'avez besoin pour rester avec le modèle de récupération complète. Ceci implique que vous remettez déjà transaction les sauvegardes du journal et vous ne disposez pas des problèmes généraux avec le journal des transactions en pleine croissance du contrôle. C'est parfait, comme prise de sauvegardes du journal des transactions est la seule opération que le journal des transactions vous permet d'effacer une fois que les transactions sont validées. (Pour l'arrière-plan de cela, reportez-vous à la section technet.microsoft.com/magazine/2009.02.logging , qui explique comment fonctionne le journal des transactions et de la façon dont les différents modèles de récupération affectent son comportement.)
Ceci dit, la fréquence à laquelle vous effectuez des sauvegardes du journal des transactions est une chose qui détermine la vitesse à laquelle le journal des transactions peut effacer et augmente pas. Par exemple, si votre opération de sauvegarde régulière effectue une sauvegarde du journal des transactions toutes les 30 minutes, le fichier journal des transactions doit être suffisant pour accueillir la plus grande quantité de données de journal de transaction peuvent être générées dans un délai de 30 minutes. Dans le cas contraire, il va croître.
Si votre script de mise à niveau s'exécute dans les 60 minutes, qui peut fonctionner à 20 Go du journal des transactions généré toutes les 30 minutes, de sorte que le fichier journal des transactions aura de 20 Go. Qui est probablement trop importante, afin que vous avez à effectuer des sauvegardes du journal des transactions plus fréquemment pendant que le script de mise à niveau est en cours d'exécution. Ceci permettra le journal des transactions à effacer plus fréquemment et l'empêcher de devenir très volumineuses. Nous avait une situation semblable à l'emplacement du client et de fin d'exécution d'une sauvegarde du journal des transactions une fois par minute pendant plusieurs heures si un script similaire a été exécuté sur une grande base de données.
Une chose à retenir est que ces transactions “ supplémentaires ” sauvegardes font partie de la chaîne de sauvegarde de journal vous connecter et sont nécessaires pour la récupération d'urgence. Assurez-vous qu'ils ont tous des noms parlants et ne sont pas supprimés.
Il existe un autre facteur à considérer : Quelle est la plus grande transaction unique qui se produit dans le cadre du processus de mise à niveau que vous avez conçu ? Le journal des transactions peut effacer uniquement si les enregistrements de journal proviennent les transactions validées (qui peut être un peu oversimplifying, consultez l' article de susmentionnées, pour plus d'informations). Cela signifie que d'une transaction de longue durée d'exécution empêchera le journal d'effacer, même si le journal des transactions ne sauvegarde sauvegarde le journal des transactions générées.
Si votre script de mise à niveau contient une transaction nécessitant 15 Go d'espace du journal, le fichier journal des transactions doivent être au moins 15 Go pour accueillir toute la transaction jusqu'à ce qu'il valide. Si tel est le cas, quel que soit la fréquence à laquelle vous effectuez une sauvegarde du journal des transactions, le journal des transactions ne sont pas effacer. Dans ce cas, la seule solution est de fractionner la transaction de grande en petites, si possible.
Gardez ceci à l'esprit : la taille du journal des transactions requis pour exécuter votre script de mise à niveau sera déterminée par la fréquence des sauvegardes du journal des transactions et la taille de la transaction de plus grande unique que vous créez.
Configuration énigme
Q : Nous avons mise en service un nouveau stockage DAS pour l'un de nos serveurs de base de données et que vous souhaitez vous assurer que nous allons prendre connaissance de tous nos options et obtenir la configuration correcte. Pouvez-vous expliquer les différents paramètres de configuration, que nous devons être conscients de, ce qui concerne SQL Server ?
R : Il existe une multitude de paramètres et options de configuration lors de la mise en service de stockage, afin que je préfère impliquent un administrateur de stockage dédié. Il n'y a sans aucun doute certaines options avec lesquelles un administrateur SQL Server doit être concerné et assurez-vous de définir de manière appropriée.
Le premier est le niveau RAID sous-jacent. Les différents niveaux RAID ont différents compromis dans la mesure où concernent les performances et la redondance. Par exemple, la moins chère configuration RAID qui offre toujours une redondance est RAID-5, mais cette configuration peut uniquement prendre en charge par une défaillance de disque unique (à moins que l'utilisation de RAID-6, ou configuré de disques de remplacement à chaud) et peut parfois affecter les performances des charges de travail comportant beaucoup de l'écriture, en fonction du nombre de lecteurs est dans le tableau.
RAID 10 offre la meilleure redondance, mais il est plus coûteux. La capacité totale du tableau est au maximum la moitié de la capacité totale des disques constituants. Trouverez une bonne présentation des différents niveaux de RAID est présentée dans l'annexe A du livre blanc TechNet “ Création de stockage physique de base de données . ”
Les principaux autres facteurs à prendre en compte sont la taille d'agrégats par bandes RAID, la taille d'unité NTFS allocation (taille de cluster) et l'alignement de la partition de disque. Tous ces facteurs peuvent réduire considérablement les performances si la valeur incorrecte. Le plus important est l'alignement des partitions disque pour les volumes de disque créées à l'aide de Windows Server 2003. Cette méthode utilise un alignement 31,5 Ko par défaut, ce qui ne correspond pas à des tailles en agrégat par bandes RAID standard de 64 Ko (ou un multiple de celle-ci). Par conséquent, chaque e/S a essentiellement lire ou écrire deux bandes RAID pour répondre à l'E/S. Évidemment, cela provoque une dégradation considérable des performances.
Windows Server 2008 utilise un alignement de 1 Mo par défaut. Tous les volumes créés sur Windows Server 2003 et mis à niveau pour l'hébergement de Windows Server 2008 n'ont pas leur alignement modifié, afin qu'ils peuvent être affectées. Résolution de ce problème, en reformatant les volumes, mais qui les gains de performance rendent souvent une étape couronnés de succès.
Une étude approfondie de ces est vraiment dépasse le cadre de cette colonne, mais il y a plus d'informations (et des liens pour plus d'informations) plus dans mon billet de blog, “ sont des offsets votre partition de disque, RAID agrègent par bande les tailles et les unités d'allocation NTFS correctement défini? ”
Lors de la mise en service un nouveau stockage, il est conseillé de test de stress et de test de performances avant de démarrer une charge de travail de production. Les tests de stress vous permet de nettoyer tous les problèmes de configuration peuvent entraîner une perte de données ou des interruptions de service. Vous permet de vérifier que les nouvelles capacités de stockage fournit la capacité d'e/S de votre charge de travail de test des performances requiert. Microsoft dispose d'outils gratuits pour vous aider à ce que vous pouvez en apprendre plus sur dans le livre blanc “ Pre-Deployment recommandées pour l'E/S . ”
Miroir, miroir
Q : Je ne sais pas un peu la nature de serveur témoin lors du paramétrage de la mise en miroir de base de données. La puissance du serveur témoin doit-il être ? Il dépende du nombre de bases de données pour laquelle c'est le cas avec basculement ? Il est important dans le centre de données, vous placez le serveur témoin ? Je veux vous assurer d'obtenir la plus haute disponibilité pour les bases de données en miroir.
R : Le rôle de serveur témoin est un des aspects plus mal comprises de toute base de données système de mise en miroir. Le serveur témoin dans une configuration de la mise en miroir de base de données synchrone existe uniquement pour aider à organiser un basculement automatique dans le cas où le serveur principal n'est plus disponible.
Le serveur principal envoie en permanence les enregistrements du journal des transactions sur le serveur miroir, ne jamais le serveur témoin. Les serveurs d'entité de sécurité, de mise en miroir et de rappel commande ping sur l'autre par seconde dans le cadre du mécanisme de détection automatique des pannes. Si le serveur miroir détermine qu'il ne peut pas communiquer avec le serveur principal pour une raison quelconque, il ne peut pas initier un basculement automatique, à moins que le serveur témoin reconnaît qu'il ne peut pas également communiquer avec le serveur principal. Si les deux serveurs, qui constitue un quorum et le serveur miroir déclenche un basculement automatique. Si un serveur témoin n'est pas présent, il ne peut pas être un quorum et un basculement automatique n'est pas possible.
Par conséquent, le serveur témoin existe uniquement pour aider à former un quorum. Il n'est pas initier des basculements ou jouez une partie dans la base de données en miroir d'hébergement. En général le quorum existe entre les serveurs principal et miroir.
Comme le serveur témoin ne fait aucun traitement en tant que tel, il n'a pas besoin d'être un puissant serveur. Il peut héberger n'importe quelle édition de SQL Server, y compris gratuit SQL Server Express Edition. Il n'y a également pas de limite quant au nombre de sessions pour lequel une instance particulière de SQL Server peut agir comme rappel de la mise en miroir de base de données.
Le serveur témoin est mieux placé dans un centre de données distincte à partir des serveurs de mise en miroir ou de l'entité de sécurité. Toutefois, la plupart des entreprises n'ont pas trois centres de données, afin que la question est le serveur témoin doit être placé avec le serveur miroir ou avec le serveur principal.
Lorsqu'il existe seulement deux centres de données, le serveur témoin doit toujours être placé avec le serveur principal. La raison est à former un quorum. Si le rappel et de mise en miroir les serveurs sont hébergée et supprime de la liaison réseau au serveur principal, un quorum constitue alors entre le rappel et la mise en miroir et le serveur miroir va initier un basculement.
Le serveur principal, ce qui peut avoir pas du tout des problèmes, mettez la base de données principale hors connexion lorsqu'il perd le quorum. Il suppose que la mise en miroir effectuera un basculement dans ce cas. Pour éviter cela, une implantation commune des serveurs principal et de rappel permet l'entité de sécurité gérer le quorum avec le rappel au cas où une défaillance réseau. La base de données principale reste disponible.
Le serveur témoin est entièrement facultatif, mais il n'y a aucune possibilité d'un basculement automatique — et par conséquent, la haute disponibilité de la base de données mis en miroir, n'en comporte aucune. La mise en miroir de base de données fonctionne de la même dans tous les autres moyens. Si un serveur témoin est configuré mais n'est pas disponible pour une raison quelconque, il existe sans perte de fonctionnalités à l'exception la possibilité d'effectuer un basculement automatique de la mise en miroir.
Vous pouvez également avoir des deux témoins une base de données de session mise en miroir. La seule manière d'ajouter davantage de redondance pour le rôle de serveur témoin est à l'instance de SQL Server de rappel hébergé sur un cluster avec basculement. Vous pouvez obtenir plus d'informations sur la base de données de la mise en miroir de configurations dans le TechNet livre blanc “ mise en miroir de base de données dans SQL Server 2005 . ”
.jpg)
Paul s. Randal est le directeur général de SQLskills.com, un directeur régional Microsoft et SQL Server MVP. Il a travaillé dans l'équipe SQL Server Storage Engine chez Microsoft depuis 1999 à 2007. Il a écrit DBCC CHECKDB/réparation pour SQL Server 2005 et était responsable du Core Storage Engine pendant le 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. Il blogs à SQLskills.com/blogs/paul et que vous en trouverez lui sur Twitter à Twitter.com/PaulRandal.