Préconfiguration des optimisations des bases de données
En raison du rôle critique que joue SQL Server dans n’importe quel environnement BizTalk Server, il est d’une importance capitale que SQL Server être configurés/paramétrés pour des performances optimales. Si SQL Server n’est pas réglé pour fonctionner correctement, les bases de données utilisées par BizTalk Server deviennent un goulot d’étranglement et les performances globales de l’environnement BizTalk Server en pâtiront. Cette rubrique décrit plusieurs optimisations des performances SQL Server qui doivent être suivies avant d’installer BizTalk Server et de configurer les bases de données BizTalk Server.
Définir l’unité d’allocation de fichiers NTFS
SQL Server stocke ses données dans Les étendues, qui sont des collections de huit pages 8 000 contiguës physiquement, soit 64 Ko. Par conséquent, pour optimiser les performances du disque, définissez la taille de l’unité d’allocation NTFS sur 64 Ko, comme décrit dans « Meilleures pratiques de configuration de disque » dans Meilleures pratiques d’E/S de prédéploiement.
Considérations relatives à la version et à l’édition de SQL Server
Différentes versions et éditions de SQL Server fournissent différentes fonctionnalités qui peuvent affecter les performances de votre environnement BizTalk Server. Par exemple, dans des conditions de charge élevée, le nombre de verrous de base de données disponibles pour la version 32 bits de SQL Server peut être dépassé, ce qui nuit aux performances de la solution BizTalk. Envisagez de loger votre base de données MessageBox sur une version 64 bits de SQL Server si vous rencontrez des erreurs « hors verrou » dans votre environnement de test. Le nombre de verrous disponibles est nettement supérieur sur cette version.
Tenez compte du tableau suivant lorsque vous décidez des fonctionnalités du moteur de base de données dont vous aurez besoin pour votre environnement BizTalk. Pour les solutions à grande échelle au niveau de l’entreprise qui nécessitent une prise en charge clustering, BizTalk Server la prise en charge de la copie des journaux ou la prise en charge d’Analysis Services, vous devez SQL Server Entreprise Edition pour héberger les bases de données SQL Server.
Pour obtenir la liste complète des fonctionnalités prises en charge par les éditions SQL Server, consultez Éditions SQL Server et fonctionnalités prises en charge.
Considérations relatives à la planification de la base de données
Nous vous recommandons d’héberger vos bases de données SQL Server sur un stockage rapide (par exemple, des disques SAN rapides ou des disques SCSI rapides). Nous recommandons RAID 10 (1+0) au lieu de RAID 5, car raid 5 est plus lent à l’écriture. Les disques SAN plus récents ont des caches de mémoire très volumineux. Dans ces cas, la sélection raid n’est pas aussi importante. Pour améliorer les performances, les bases de données et leurs fichiers journaux peuvent résider sur différents disques physiques.
Envisagez également de régler la profondeur de la file d’attente de la carte de bus hôte (HBA) si vous utilisez un réseau de zone de stockage (SAN). Cela peut avoir un impact significatif sur le débit d’E/S et les valeurs prêtes à l’emploi peuvent être insuffisantes pour SQL Server. Des tests sont nécessaires pour déterminer la valeur optimale, bien que la profondeur de la file d’attente de 64 soit généralement acceptée comme un bon point de départ en l’absence de recommandations spécifiques du fournisseur
Installez les dernières mises à jour du Service Pack et cumulatives pour SQL Server
Installez les derniers Service Packs et les dernières mises à jour cumulatives pour SQL Server ainsi que les derniers Service Packs .NET Framework.
Installer les Service Packs SQL et les mises à jour cumulatives sur les BizTalk Server et les SQL Server
Lors de l’installation des Service Packs ou des mises à jour cumulatives pour SQL Server, installez également le Service Pack ou la mise à jour cumulative sur l’ordinateur BizTalk Server. BizTalk Server utilise des composants SQL Client mis à jour par SQL Server Service Packs et des mises à jour cumulatives.
Envisagez d’utiliser un disque SSD rapide pour héberger le SQL Server tembdb
Envisagez d’utiliser un ou plusieurs disques SSD pour héberger tempDB. Les disques SSD offrent des avantages significatifs en matière de performances par rapport aux disques durs traditionnels et sont rapidement en baisse à mesure qu’ils entrent sur les marchés traditionnels. Étant donné que les performances tempDB sont souvent un facteur clé pour les performances globales SQL Server, le coût initial supplémentaire des lecteurs est souvent rapidement récupéré par l’augmentation globale des performances SQL Server, en particulier lors de l’exécution d’applications d’entreprise pour lesquelles SQL Server performances sont essentielles.
Envisagez d’implémenter le Data Warehouse collecteur de données et de gestion SQL Server 2008 R2
SQL Server 2008 R2 permet d’utiliser la nouvelle Data Warehouse collecte et gestion des données pour collecter des données relatives aux performances de l’environnement et de la base de données pour les tests et l’analyse des tendances. Le collecteur de données conserve toutes les données collectées dans le Data Warehouse de gestion spécifié. Bien qu’il ne s’agit pas d’une optimisation des performances, cela sera utile pour l’analyse des problèmes de performances.
Accordez le compte utilisé pour SQL Server le privilège Windows Lock Pages in Memory
Accordez le privilège Pages de verrouillage Windows en mémoire au compte de service SQL Server. Cela doit être effectué pour empêcher le système d’exploitation Windows de paginer la mémoire du pool de mémoires tampons du processus SQL Server en verrouillant la mémoire allouée pour le pool de mémoires tampons dans la mémoire physique.
Dans notre environnement de laboratoire, l’option Verrouiller les pages dans la mémoire de stratégie Windows a été activée par défaut. Consultez Activer l’option Verrouiller les pages dans la mémoire.
Important
Certaines limitations s’appliquent lors de l’octroi au compte de service SQL Server le privilège Pages de verrouillage Windows dans la mémoire. Consultez les rubriques suivantes :
Accorder au SE_MANAGE_VOLUME_NAME droit au compte de service SQL Server
Vérifiez que le compte exécutant le service SQL Server dispose du privilège Windows « Effectuer des tâches de maintenance en volume » ou vérifiez qu’il appartient à un groupe qui en dispose. Cela permet une initialisation instantanée des fichiers garantissant des performances optimales si une base de données doit se développer automatiquement.
Définir la mémoire minimale et maximale du serveur
Les ordinateurs exécutant SQL Server qui hébergent les bases de données BizTalk Server doivent être dédiés à l’exécution de SQL Server. Lorsque les ordinateurs exécutant des SQL Server qui hébergent les bases de données BizTalk Server sont dédiés à l’exécution de SQL Server, nous recommandons que les options « mémoire minimale du serveur » et « mémoire maximale du serveur » de chaque SQL Server instance soient définies pour spécifier la quantité fixe de mémoire à allouer à SQL Server. Dans ce cas, vous devez définir la « mémoire minimale du serveur » et la « mémoire maximale du serveur » sur la même valeur (égale à la quantité maximale de mémoire physique que SQL Server utiliserez). Cela permettra de réduire la surcharge qui serait utilisée par SQL Server gérer dynamiquement ces valeurs. Exécutez les commandes T-SQL suivantes sur chaque ordinateur exécutant SQL Server pour spécifier la quantité fixe de mémoire à allouer à SQL Server :
sp_configure ‘Max Server memory (MB)’,(max size in MB)
sp_configure ‘Min Server memory (MB)’,(min size in MB)
Avant de définir la quantité de mémoire pour SQL Server, déterminez le paramètre de mémoire approprié en soustrayant la mémoire requise pour Windows Server de la mémoire physique totale. Il s’agit de la quantité maximale de mémoire que vous pouvez affecter à SQL Server.
Notes
Si les ordinateurs exécutant SQL Server qui hébergent les bases de données BizTalk Server hébergent également le serveur secret principal Sign-On d’entreprise, vous devrez peut-être ajuster cette valeur pour vous assurer que la mémoire disponible est suffisante pour exécuter le service d'Sign-On d’entreprise. Il n’est pas rare d’exécuter un instance cluster du service d'Sign-On unique d’entreprise sur un cluster SQL Server afin de fournir une haute disponibilité pour le serveur secret maître. Consultez Clustering the Master Secret Server
Fractionnez la base de données tempdb en plusieurs fichiers de données de taille égale sur chaque SQL Server instance utilisé par BizTalk Server
Il est essentiel de s’assurer que les fichiers de données utilisés pour tempdb sont de taille égale, car l’algorithme de remplissage proportionnel utilisé par SQL Server est basé sur la taille des fichiers de données. Si des fichiers de données sont créés avec des tailles inégales, l’algorithme de remplissage proportionnel utilise davantage le fichier le plus volumineux pour les allocations GAM au lieu de répartir les allocations entre tous les fichiers, ce qui va à l’échec de l’objectif de création de plusieurs fichiers de données. Le nombre optimal de fichiers de données tempdb dépend du degré de contention du verrou observé dans tempdb. En règle générale, le nombre de fichiers de données doit être égal au nombre de cœurs de processeur/processeurs où le nombre de processeurs est inférieur ou égal à 8. Pour les serveurs avec plus de 8 processeurs, créez des fichiers de données pour la moitié du nombre de processeurs (là encore, vous seul avez une contention de verrou).
Dans notre environnement de laboratoire, nous avons utilisé le script ci-dessous pour créer 8 fichiers de données TempDB dont chacun avait une taille de fichier de 1 024 Mo avec une croissance de 100 Mo et un fichier journal de 512 Mo avec une croissance de 100 Mo. Les fichiers de données sont déplacés vers le lecteur H : et le fichier journal est déplacé vers le lecteur I :.
Important
Ce script est fourni « en l’état », est destiné uniquement à des fins de démonstration ou à des fins éducatives, et doit être utilisé à vos propres risques. L’utilisation de ce script n’est pas prise en charge par Microsoft et Microsoft n’offre aucune garantie quant à l’adéquation de ce script.
--<<<<<<<<<<----------------------------------------------------------------->>>>>>>>>>--
-- Use of included script samples are subject to the terms specified at
-- http://www.microsoft.com/info/cpyright.htm
--<<<<<<<<<<----------------------------------------------------------------->>>>>>>>>>--
--***Instructions***
-- 1. If running the script from a remote server, change the context in SSMS to target instance
-- 2. Enable SQLCMD mode (add & click toolbar button or toggle by clicking Query > SQLCMD Mode)
-- 3. Commence execution of scripts (recommend running statements discretely to more easily remedy potential problems)
-- 4. Examine servername & temp configuration
-- 5. If necessary, 1) Replace instance name in path to reflect target instance *all throughout script*
-- 2) Modify root drives to reflect drives designated for data & log (folder creation *and* ALTER DB statements)
-- 6. Resume script execution
-- 7. If necessary, create new folders
-- 8. Modify/Add data & log files
-- 9. Recycle SQL service using sqlservermanager10.msc
--10. Examine results & if appropriate, delete original tempdb data log files
--(if they were "moved", the original files aren't automatically deleted)
--<<<<<<<<<<----------------------------------------------------------------->>>>>>>>>>--
--1. If running the script from a remote server, change the context in SSMS to target instance
--2. Enable SQLCMD mode (add & click toolbar button or toggle by clicking Query > SQLCMD Mode)
--3. Commence execution of scripts (recommend running statements discretely to more easily remedy potential problems)
--4. Examine servername & temp configuration
SELECT @@SERVERNAME
EXEC dbo.sp_helpdb tempdb
--tempdev 1 C:\tempdb.mdf PRIMARY 8192 KB Unlimited 10% data only
--templog 2 C:\templog.ldf NULL 512 KB Unlimited 10% log only
GO
--5. If necessary, 1) Replace instance name in path to reflect target instance *all throughout script*
-- 2) Modify root drives to reflect drives designated for data & log (folder creation *and* ALTER DB statements)
--6. Resume script execution
--7. If necessary, create new folders
--!!md H:\MSSQL10.<instance>
--!!md H:\MSSQL10.<instance>\MSSQL
--!!md H:\MSSQL10.<instance>\MSSQL\DATA
GO
-- 8. Modify/Add data & log files
--note: even if the out-of-box mdf is already where it needs to be,
--the first command is necessary to modify size & filegrowth
ALTER DATABASE tempdb MODIFY FILE (NAME = tempdev , FILENAME = 'H:\tempdb.mdf' , SIZE = 1024MB , FILEGROWTH = 100MB)
ALTER DATABASE tempdb ADD FILE (NAME = tempdat2 , FILENAME = 'H:\tempdat2.ndf' , SIZE = 1024MB , FILEGROWTH = 100MB)
ALTER DATABASE tempdb ADD FILE (NAME = tempdat3 , FILENAME = 'H:\tempdat3.ndf' , SIZE = 1024MB , FILEGROWTH = 100MB)
ALTER DATABASE tempdb ADD FILE (NAME = tempdat4 , FILENAME = 'H:\tempdat4.ndf' , SIZE = 1024MB , FILEGROWTH = 100MB)
ALTER DATABASE tempdb ADD FILE (NAME = tempdat5 , FILENAME = 'H:\tempdat5.ndf' , SIZE = 1024MB , FILEGROWTH = 100MB)
ALTER DATABASE tempdb ADD FILE (NAME = tempdat6 , FILENAME = 'H:\tempdat6.ndf' , SIZE = 1024MB , FILEGROWTH = 100MB)
ALTER DATABASE tempdb ADD FILE (NAME = tempdat7 , FILENAME = 'H:\tempdat7.ndf' , SIZE = 1024MB , FILEGROWTH = 100MB)
ALTER DATABASE tempdb ADD FILE (NAME = tempdat8 , FILENAME = 'H:\tempdat8.ndf' , SIZE = 1024MB , FILEGROWTH = 100MB)
GO
ALTER DATABASE tempdb MODIFY FILE (NAME = templog , FILENAME = 'I:\templog.ldf', SIZE = 512MB , FILEGROWTH = 100MB)
GO
--8b. Modify log file: modify drive & instance name to reflect designated destination for tempdb log
--!!md I:\MSSQL10.<instance>
--!!md I:\MSSQL10.<instance>\MSSQL
--!!md I:\MSSQL10.<instance>\MSSQL\DATA
GO
-- 9. Recycle SQL service in SQL Server Services node of sqlservermanager10.msc
--note, if running script from a UNC share, SSMS will report an error,
--but SQL Server Configuration Manager will open if its location is in %path%
!!sqlservermanager10.msc
--10. Examine results & if appropriate, delete original tempdb data log files
--(if they were "moved", the original files aren't automatically deleted)
EXEC dbo.sp_helpdb tempdb
--!!del C:\tempdb.mdf
--!!del C:\templog.ldf
GO
Utilisez le moniteur d’activités SQL Server 2008 ou les rapports de tableau de bord de performances SQL Server 2005 décrits dans Surveillance SQL Server performances pour identifier les problèmes liés à la contention des verrous.
Définir manuellement SQL Server affinité de processus
L’option Affinité de processus peut fournir des améliorations de performances dans les environnements de SQL Server haut de gamme au niveau de l’entreprise qui s’exécutent sur des ordinateurs non NUMA avec 16 processeurs ou plus. Cela est particulièrement vrai dans les environnements BizTalk à haut débit où vous avez des conflits sur des tables partagées dans la base de données MessageBox. Étant donné que les ordinateurs SQL Server utilisés dans notre environnement de laboratoire n’étaient pas compatibles avec NUMA et disposaient de 16 cœurs, afin d’optimiser les performances, nous avons utilisé les commandes ci-dessous pour définir l’affinité de processus :
Pour définir manuellement l’affinité de processus SQL Server de 0 à 15
ALTER SERVER CONFIGURATION
SET PROCESS AFFINITY CPU = 0 to 15
Pour plus d’informations, consultez ALTER SERVER CONFIGURATION (Transact-SQL).
Configurer MSDTC
Pour faciliter les transactions entre SQL Server et BizTalk Server, vous devez activer Microsoft Distributed Transaction Coordinator (MS DTC). Pour configurer MSDTC sur SQL Server, consultez la rubrique Instructions générales pour l’amélioration des performances du système d’exploitation.
Activez l’indicateur de trace T1118 comme paramètre de démarrage pour toutes les instances de SQL Server
L’implémentation de l’indicateur de trace -T1118 permet de réduire les conflits entre les instances SQL Server en supprimant presque toutes les allocations de page unique. Pour plus d’informations, consultez Kb 328551 : PRB : Améliorations de concurrence pour la base de données tempdb.
Ne modifiez pas les paramètres de SQL Server par défaut pour le degré maximal de parallélisme, les statistiques SQL Server ou les reconstructions d’index de base de données et la défragmentation
Si une SQL Server instance héberge BizTalk Server bases de données, certains paramètres SQL Server ne doivent pas être modifiés. Plus précisément, le SQL Server degré maximal de parallélisme, les statistiques SQL Server sur la base de données MessageBox et les paramètres de la reconstruction et de la défragmentation de l’index de base de données ne doivent pas être modifiés. Consultez SQL Server paramètres qui ne doivent pas être modifiés.