Considérations sur la conception de SQL Server

System Center Operations Manager nécessite un accès à une instance d’un serveur exécutant Microsoft SQL Server pour prendre en charge les bases de données opérationnelles, de l’entrepôt de données et d’audit des services ACS. Les bases de données opérationnelle et de l’entrepôt de données sont obligatoires et créées quand vous déployez le premier serveur d’administration dans votre groupe d’administration, tandis que la base de données des services ACS est créée quand vous déployez un collecteur des services ACS dans votre groupe d’administration.

Dans un environnement de laboratoire ou un déploiement à échelle réduite d’Operations Manager, SQL Server peut être colocalisé sur le premier serveur d’administration du groupe d’administration.

Dans un déploiement distribué à moyenne échelle ou à l’échelle de l’entreprise, l’instance SQL Server doit être située sur un serveur autonome dédié ou dans une configuration à haute disponibilité de SQL Server. Dans les deux cas, SQL Server doit déjà exister et être accessible avant de démarrer l’installation du premier serveur d’administration ou collecteur des services ACS.

Nous vous déconseillons d’utiliser des bases de données Operations Manager à partir d’une instance SQL qui possède d’autres bases de données d’application. Cela permet d’éviter tout problème potentiel lié aux restrictions des E/S et d’autres ressources matérielles.

Important

Operations Manager ne prend pas en charge les instances PaaS (Platform as a Service) de SQL, y compris les produits tels que Azure SQL Managed Instance ou Amazon Relational Database Service (AWS RDS). Utilisez un instance de SQL Server installé sur un ordinateur Windows. La seule exception à cela se trouve dans azure Monitor SCOM Managed Instance, qui utilise Azure SQL MI et n’est pas reconfigurable.

Configuration requise pour SQL Server

Les versions suivantes de SQL Server Entreprise et SQL Server Standard sont prises en charge pour une installation existante de System Center Operations Manager afin d’héberger les bases de données du serveur de rapports, des données opérationnelles, de l’entrepôt de données et des services ACS :

• SQL Server 2022

• SQL Server 2019 avec la mise à jour cumulative 8 (CU8) ou ultérieure, comme indiqué ici

  Notes

  • Operations Manager 2022 prend en charge SQL 2019 avec CU8 ou version ultérieure ; toutefois, il ne prend pas en charge SQL 2019 RTM.
  • Utilisez ODBC 17.3 (ou une version ultérieure) et MSOLEDBSQL 18.2 (ou une version ultérieure).

• SQL Server 2017 et mises à jour cumulatives (voir description)

Avant de mettre à niveau SQL Server, consultez informations de mise à niveau pour 2017 et informations de mise à niveau pour SQL 2019.

Notes

• Chacun des composants SQL Server suivants prenant en charge une infrastructure SCOM doit être dans la même version principale de SQL Server :
  • SQL Server instances de moteur de base de données hébergeant l’une des bases de données SCOM (c’est-à-dire les bases de données OperationManager, OperationManagerDW et SSRS ReportServerServer& ReportServerTempDB).
  • Instance SQL Server Reporting Services (SSRS)
• Le paramètre de classement SQL Server doit être l’un des types pris en charge, comme décrit dans la section Paramètre de classement SQL Server ci-dessous.
• La recherche en texte intégral SQL Server est requise pour toutes les instances du moteur de base de données SQL Server hébergeant l’une des bases de données SCOM.
• Les options d’installation de Windows Server 2016 (Server Core, Server avec expérience utilisateur et Nano Server) prises en charge par les composants de base de données d’Operations Manager sont basées sur les options d’installation de Windows Server prises en charge par SQL Server.

Notes

System Center Operations Manager Reporting ne peut pas être installé côte à côte avec une version précédente du rôle Création de rapports et doit être installé en mode natif uniquement (le mode intégré SharePoint n’est pas pris en charge).

D’autres considérations en matière de logiciels et de matériel s’appliquent lors de la planification de la conception :

• Nous vous recommandons d’exécuter SQL Server sur les ordinateurs avec le format de fichier NTFS.
• Au moins 1 024 Mo d’espace disque libre doivent être disponibles pour les bases de données opérationnelle et de l’entrepôt de données. Elle est appliquée au moment de la création de la base de données et elle augmentera probablement considérablement après l’installation.
• .NET Framework 4 est requis.
• .NET Framework 4.8 est pris en charge par Operations Manager 2022 et versions ultérieures.
• Reporting Server n’est pas pris en charge sur Windows Server Core.

Pour plus d’informations, consultez Configurations matérielle et logicielle requises pour l’installation de SQL Server.

Notes

Bien qu’Operations Manager utilise uniquement Authentification Windows pendant l’installation, le paramètre d’authentification en mode mixte SQL fonctionnera toujours si aucun compte local n’a le rôle db_owner. Les comptes locaux avec le rôle db_owner sont connus pour causer des problèmes avec System Center Operations Manager. Supprimez le rôle db_owner de tous les comptes locaux avant d’installer le produit et n’ajoutez pas le rôle db_owner aux comptes locaux après l’installation.

Paramètre de classement SQL Server

Les classements SQL Server et Windows suivants sont pris en charge par System Center Operations Manager.

Notes

Pour éviter tout problème de compatibilité lors de la comparaison ou de la copie d’opérations, nous vous recommandons d’utiliser le même classement pour SQL et la base de données Operations Manager.

Classement SQL Server

• SQL_Latin1_General_CP1_CI_AS

Classement Windows

• Latin1_General_100_CI_AS
• French_CI_AS
• French_100_CI_AS
• Cyrillic_General_CI_AS
• Chinese_PRC_CI_AS
• Chinese_Simplified_Pinyin_100_CI_AS
• Chinese_Traditional_Stroke_Count_100_CI_AS
• Japanese_CI_AS
• Japanese_XJIS_100_CI_AS
• Traditional_Spanish_CI_AS
• Modern_Spanish_100_CI_AS
• Latin1_General_CI_AS
• Cyrillic_General_100_CI_AS
• Korean_100_CI_AS
• Czech_100_CI_AS
• Hungarian_100_CI_AS
• Polish_100_CI_AS
• Finnish_Swedish_100_CI_AS

Si votre SQL Server instance n’est pas configuré avec l’un des classements pris en charge répertoriés précédemment, la nouvelle configuration de l’installation d’Operations Manager échouera. Toutefois, les mises à niveau en place se termineront correctement.

Configuration du pare-feu

Operations Manager utilise SQL Server pour héberger ses bases de données et une plateforme de création de rapports pour analyser et présenter les données opérationnelles historiques. Les rôles serveur d’administration, opérations et console web doivent être en mesure de communiquer correctement avec SQL Server, et il est important de comprendre le chemin de communication et les ports afin de configurer correctement votre environnement.

Si vous concevez un déploiement distribué qui nécessite sql Always On groupes de disponibilité pour fournir des fonctionnalités de basculement pour les bases de données Operations Manager, des paramètres de configuration de pare-feu supplémentaires doivent être inclus dans votre stratégie de sécurité de pare-feu.

Le tableau suivant vous permet d’identifier les ports de pare-feu requis par SQL Server qui doivent être autorisés au minimum afin que les rôles de serveur dans le groupe d’administration Operations Manager puissent communiquer correctement.

Scénario	Port	Direction	Rôle Operations Manager
Serveur SQL Server hébergeant les bases de données Operations Manager	TCP 1433 *	Entrant	Serveur d’administration et console Web (pour Application Advisor et Diagnostic d’application)
Service SQL Server Browser	UDP 1434	Entrant	Serveur management
Connexion admin dédiée SQL Server	TCP 1434	Entrant	Serveur management
Ports supplémentaires utilisés par SQL Server - Appels de procédure distante Microsoft (MS RPC) - Windows Management Instrumentation (WMI) - Microsoft Distributed Transaction Coordinator (MS DTC)	TCP 135	Entrant	Serveur management
Écouteur de groupe de disponibilité SQL Server AlwaysOn	Port configuré par l’administrateur	Entrant	Serveur management
SQL Server Reporting Services hébergeant Operations Manager Reporting Server	TCP 80 (par défaut)/443 (SSL)	Entrant	Serveur d’administration et console Opérateur

* Alors que le port TCP 1433 est le port standard pour l’instance par défaut du moteur de base de données, quand vous créez une instance nommée sur un serveur autonome SQL Server ou avez déployé un groupe de disponibilité SQL Always On, un port personnalisé est défini et doit être documenté pour référence afin que vous puissiez configurer correctement vos pare-feu et entrer ces informations lors de la configuration.

Pour obtenir une vue d’ensemble plus détaillée de la configuration requise du pare-feu pour SQL Server, consultez Configurer le Pare-feu Windows pour autoriser l’accès à SQL Server.

Considérations relatives à la capacité et au stockage

Base de données OperationsManager

La base de données Operations Manager est une base de données SQL Server qui contient toutes les données nécessaires à Operations Manager pour une surveillance quotidienne. Le dimensionnement et la configuration du serveur de base de données sont essentiels pour les performances globales du groupe d’administration. La ressource la plus importante utilisée par la base de données Operations Manager est le sous-système de stockage, mais le processeur et la mémoire RAM sont également importants.

Les facteurs qui influencent la charge sur la base de données Operations Manager sont notamment les suivants :

• Vitesse de collecte des données opérationnelles. Les données opérationnelles regroupent l’ensemble des événements, alertes, changements d’état et données de performances collectées par les agents. La plupart des ressources utilisées par la base de données Operations Manager permettent d’écrire ces données sur le disque à leur arrivée dans le système. La vitesse à laquelle les données opérationnelles sont collectées tend à augmenter à mesure que d’autres packs d’administration sont importés et des agents supplémentaires sont ajoutés. Le type d’ordinateur surveillé par un agent est également un facteur important utilisé pour déterminer la vitesse globale de collecte de données opérationnelles. Par exemple, un agent qui surveille un poste de travail stratégique pour l’entreprise peut s’attendre à collecter moins de données qu’un agent qui surveille un serveur exécutant une instance de SQL Server avec un grand nombre de bases de données.
• Taux de modifications de l’espace d’instances. La mise à jour de ces données dans la base de données Operations Manager est coûteuse par rapport à l’écriture de nouvelles données opérationnelles. En outre, quand les données de l’espace d’instances changent, les serveurs d’administration effectuent des requêtes supplémentaires dans la base de données Operations Manager afin de calculer les modifications de configuration et de groupe. Le taux de modifications de l’espace d’instances augmente à mesure que vous importez des packs d’administration supplémentaires dans un groupe d’administration. L’ajout de nouveaux agents à un groupe d’administration augmente aussi temporairement le taux de modifications de l’espace d’instances.
• Nombre de consoles Opérateur et autres connexions de SDK exécutées simultanément. Chaque console Opérateur lit les données à partir de la base de données Operations Manager. L’interrogation de ces données utilise des quantités potentiellement importantes de ressources d’E/S de stockage, de temps processeur et de mémoire RAM. Les consoles Opérateur qui affichent de grandes quantités de données opérationnelles dans l’affichage des événements, l’affichage des états, l’affichage des alertes et l’affichage des données de performances ont tendance à produire la plus grande charge sur la base de données.

La base de données Operations Manager étant une source unique de défaillance pour le groupe d’administration, elle peut être rendue hautement disponible en utilisant des configurations de basculement prises en charge, telles que les groupes de disponibilité SQL Server AlwaysOn ou les instances de cluster de basculement.

Vous pouvez configurer et mettre à niveau des bases de données Operations Manager avec une configuration SQL Always On sans avoir besoin d’apporter des modifications post-configuration.

Activer SQL Broker sur une base de données Operations Manager

System Center Operations Manager dépend de SQL Server Service Broker pour implémenter toutes les opérations de la tâche. Si SQL Server Service Broker est désactivé, toutes les opérations de la tâche seront affectées. Le comportement qui en découle peut varier en fonction de la tâche lancée. Par conséquent, il est important de case activée l’état de SQL Server Service Broker chaque fois qu’un comportement inattendu est observé autour d’une tâche dans System Center Operations Manager.

Pour activer SQL Server Service Broker, procédez comme suit :

1. Exécutez la requête SQL suivante : .

   SELECT is_broker_enabled FROM sys.databases WHERE name='OperationsManager'
   

2. Ignorez cette étape si la valeur affichée dans le champ is_broker_enabled est is_broker_enabled (un). Sinon, exécutez les requêtes SQL suivantes :

   ALTER DATABASE OperationsManager SET SINGLE_USER WITH ROLLBACK IMMEDIATE
   ALTER DATABASE OperationsManager SET ENABLE_BROKER
   ALTER DATABASE OperationsManager SET MULTI_USER
   

Base de données de l'entrepôt de données Operations Manager

System Center - Operations Manager insère des données dans l’entrepôt de données de création de rapports en quasi temps réel. Il est important de disposer d’une capacité suffisante sur ce serveur qui prend en charge l’écriture de toutes les données collectées dans l’entrepôt de données de création de rapports. Comme avec la base de données Operations Manager, la ressource la plus importante sur l’entrepôt de données Reporting est le sous-système d’E/S de stockage. Sur la plupart des systèmes, les charges sur l’entrepôt de données Reporting sont semblables à la base de données Operations Manager, mais elles peuvent varier. En outre, la charge de travail placée sur l’entrepôt de données Reporting par la création de rapports est différente de celle placée sur la base de données Operations Manager par l’utilisation de la console Opérateur.

Les facteurs qui influencent la charge sur l’entrepôt de données Reporting sont notamment les suivants :

• Vitesse de collecte des données opérationnelles. Pour permettre une création de rapports plus efficace, l’entrepôt de données Reporting calcule et stocke les données agrégées en plus d’une quantité limitée de données brutes. Ce travail supplémentaire signifie que la collecte des données opérationnelles pour l’entrepôt de données Reporting peut être légèrement plus coûteuse que pour la base de données Operations Manager. Ce coût supplémentaire est généralement équilibré par le coût réduit du traitement des données de détection par l’entrepôt de données Reporting par rapport à la base de données Operations Manager.
• Nombre d’utilisateurs de rapports simultanés ou génération de rapport planifiée. Étant donné que les rapports condensent fréquemment de gros volumes de données, chaque utilisateur de rapports peut ajouter une charge importante sur le système. Le nombre de rapports exécutés simultanément et le type des rapports exécutés affectent les besoins en termes de capacité globale. En règle générale,les rapports qui interrogent de grandes plages de dates ou un nombre important d’objets nécessitent davantage de ressources système.

En fonction de ces facteurs, il existe plusieurs pratiques recommandées à envisager lors du dimensionnement de l’entrepôt de données Reporting :

• Choisissez un sous-système de stockage approprié. Étant donné que l’entrepôt de données Reporting fait partie intégrante du flux de données global par le biais du groupe d’administration, le choix d’un sous-système de stockage approprié pour l’entrepôt de données Reporting est important. Comme avec la base de données Operations Manager, RAID 0 + 1 est souvent le meilleur choix. En général, le sous-système de stockage pour l’entrepôt de données Reporting doit être semblable au sous-système de stockage pour la base de données Operations Manager, et les instructions qui s’appliquent à celle-ci sont également valables pour l’entrepôt de données Reporting.
• Envisagez le positionnement approprié des journaux de données et des journaux de transactions. Comme pour la base de données Operations Manager, la séparation des journaux de transactions et de données SQL est souvent un choix approprié quand vous effectuez un scale-up du nombre d’agents. Si la base de données Operations Manager et l’entrepôt de données Reporting se trouvent tous deux sur le même serveur et que vous souhaitez séparer les journaux de transactions et de données, vous devez placer les journaux de transactions pour la base de données Operations Manager sur des piles de disques et un volume physique distincts de l’entrepôt de données Reporting pour en retirer un quelconque bénéfice. Les fichiers de données de la base de données Operations Manager et de l’entrepôt de données reporting peuvent partager le même volume physique tant que le volume fournit une capacité suffisante et que les performances d’E/S disque n’affectent pas négativement la fonctionnalité de surveillance et de création de rapports.
• Envisagez de placer l’entrepôt de données Reporting sur un serveur distinct de la base de données Operations Manager. Bien que les déploiements à plus petite échelle puissent souvent consolider la base de données Operations Manager et l’entrepôt de données de création de rapports sur le même serveur, il est avantageux de les séparer lorsque vous effectuez un scale-up du nombre d’agents et du volume de données opérationnelles entrantes. Quand l’entrepôt de données Reporting et le serveur de rapports se trouvent sur un serveur distinct de la base de données Operations Manager, vous obtenez de meilleures performances de création de rapports.

La base de données de l’entrepôt de données Operations Manager étant une source unique de défaillance pour le groupe d’administration, elle peut être rendue hautement disponible en utilisant des configurations de basculement prises en charge, telles que les groupes de disponibilité SQL Server AlwaysOn ou les instances de cluster de basculement.

SQL Server AlwaysOn

Les groupes de disponibilité SQL Server AlwaysOn prennent en charge des environnements de basculement pour un ensemble discret de bases de données utilisateur (bases de données de disponibilité). Chaque ensemble de bases de données de disponibilité est hébergé par un réplica de disponibilité.

Avec System Center 2016 et supérieur - Operations Manager, SQL AlwaysOn est préféré au clustering de basculement pour assurer la haute disponibilité des bases de données. Toutes les bases de données peuvent être hébergées dans un groupe de disponibilité AlwaysOn, à l’exception de l’installation de Reporting Services en mode natif qui utilise deux bases de données pour distinguer les besoins en termes de stockage persistant et temporaire des données.

Avec Operations Manager 2022, vous pouvez configurer et mettre à niveau des bases de données Operations Manager avec une configuration SQL Always On sans avoir besoin d’apporter des modifications post-configuration.

Pour configurer un groupe de disponibilité, vous devez déployer un cluster de clustering de basculement Windows Server (WSFC) pour héberger les réplica de disponibilité et activer Always On sur les nœuds du cluster. Vous pouvez ensuite ajouter la base de données SQL Server Operations Manager comme base de données de disponibilité.

• En savoir plus sur les prérequis Always On.
• En savoir plus sur la configuration d’un cluster WSFC pour Always On groupes de disponibilité.
• En savoir plus sur la configuration d’un groupe de disponibilité.

Notes

Après avoir déployé Operations Manager sur les nœuds SQL Server qui participent à SQL Always On, pour activer Sécurité CLR stricte, exécutez le script SQL sur chaque base de données Operations Manager.

Chaîne à plusieurs sous-réseaux

Operations Manager ne prend pas en charge les mots clés chaîne de connexion (MultiSubnetFailover=True). Dans la mesure où un groupe de disponibilité a un nom d’écouteur (connu sous le nom de réseau ou de point d’accès client dans le Gestionnaire de cluster WSFC) qui dépend de plusieurs adresses IP de différents sous-réseaux, comme quand vous déployez dans une configuration de basculement entre sites, les demandes de connexion client entre les serveurs d’administration et l’écouteur du groupe de disponibilité dépassent le délai de connexion.

L’approche recommandée pour contourner cette limitation lorsque vous avez déployé des nœuds de serveur dans le groupe de disponibilité dans un environnement à plusieurs sous-réseaux consiste à effectuer les opérations suivantes :

1. Définissez le nom réseau de votre écouteur de groupe de disponibilité pour inscrire une seule adresse IP active dans DNS.
2. Configurez le cluster pour qu’il utilise une faible valeur de durée de vie pour l’enregistrement DNS inscrit.

Ces paramètres permettent, lors du basculement sur un nœud d’un autre sous-réseau, une récupération et une résolution plus rapides du nom de cluster avec la nouvelle adresse IP.

Exécutez les commandes PowerShell suivantes sur l’un des nœuds SQL pour modifier ses paramètres :

Import-Module FailoverClusters
Get-ClusterResource "Cluster Name"|Set-ClusterParameter RegisterAllProvidersIP 0
Get-ClusterResource "Cluster Name"|Set-ClusterParameter HostRecordTTL 300
Stop-ClusterResource "Cluster Name"
Start-ClusterResource "Cluster Name"
Start-ClusterGroup "Cluster Name"

Si vous utilisez Always On avec un nom d’écouteur, vous devez également apporter ces modifications de configuration sur l’écouteur. Pour plus d’informations sur la configuration d’un écouteur de groupe de disponibilité, consultez la documentation ici : Configurer un écouteur de groupe de disponibilité - SQL Server Always On

Exécutez les commandes PowerShell suivantes sur le nœud SQL hébergeant actuellement l’écouteur pour modifier ses paramètres :

Import-Module FailoverClusters
Get-ClusterResource <Listener Cluster Resource name> | Set-ClusterParameter RegisterAllProvidersIP 0
Get-ClusterResource <Listener Cluster Resource name> | Set-ClusterParameter HostRecordTTL 300
Stop-ClusterResource <Listener Cluster Resource name>
Start-ClusterResource <Listener Cluster Resource name>
Start-ClusterGroup <Listener Cluster Group name>

Lorsqu’une instance SQL en cluster ou Always On est utilisée pour la haute disponibilité, vous devez activer la fonctionnalité de récupération automatique sur vos serveurs d’administration, afin d’éviter le redémarrage du service d’accès aux données Operations Manager à chaque basculement entre les nœuds. Pour obtenir des informations sur la manière de configurer cela, consultez l’article suivant de la Base de connaissances : Le service Administration de System Center cesse de répondre après la mise hors connexion d’une instance SQL Server.

Optimisation de SQL Server

En général, l’expérience de déploiement précédente avec les clients montre que les problèmes de performances ne sont généralement pas dus à une utilisation élevée des ressources (c’est-à-dire, processeur ou mémoire) avec SQL Server elle-même ; ils sont directement liés à la configuration du sous-système de stockage. Les goulots d’étranglement des performances sont généralement attribués au non-respect des instructions de configuration recommandées concernant le stockage provisionné pour l’instance de base de données SQL Server. En voici quelques exemples :

• Allocation insuffisante de piles pour que les LUN puissent remplir les conditions d’E/S d’Operations Manager.
• Hébergement des journaux de transactions et des fichiers de base de données sur le même volume. Ces deux charges de travail ont des caractéristiques d’E/S et de latence différentes.
• La configuration de TempDB est incorrecte en ce qui concerne le placement, le dimensionnement, etc.
• Partition de disque incorrectement des volumes hébergeant les journaux des transactions de base de données, les fichiers de base de données et TempDB.
• En négligeant la configuration SQL Server de base, comme l’utilisation d’AUTOGROW pour les fichiers journaux de base de données et de transactions, le paramètre MAXDOP pour le parallélisme des requêtes, la création de plusieurs fichiers de données TempDB par cœur de processeur, etc.

La configuration du stockage est l’un des composants essentiels d’un déploiement de SQL Server pour Operations Manager. Les serveurs de base de données ont tendance à être très étroitement liés aux E/S en raison du caractère rigoureux de l’activité de lecture/écriture de base de données et du traitement des journaux de transactions. En règle générale, le modèle de comportement d’E/S d’Operations Manager représente 80 % d’opérations d’écriture et 20 % d’opérations de lecture. Par conséquent, une configuration incorrecte des sous-systèmes d’E/S peut entraîner des performances et un fonctionnement des systèmes SQL Server médiocres, et devenir visible dans Operations Manager.

Il est important de tester la conception SQL Server en effectuant des tests de débit du sous-système d’E/S avant de déployer SQL Server. Assurez-vous que ces tests sont en mesure d’atteindre vos exigences d’E/S avec une latence acceptable. Utilisez l’utilitaire Diskspd pour évaluer la capacité d’E/S du sous-système de stockage prenant en charge SQL Server. L’article de blog suivant, créé par un membre de l’équipe serveur de fichiers du groupe de produits, fournit des conseils détaillés et des recommandations sur la façon d’effectuer des tests de stress à l’aide de cet outil avec du code PowerShell et de capturer les résultats à l’aide de PerfMon. Vous pouvez également vous reporter à Operations Manager Sizing Helper pour obtenir vos premières directives.

Taille de l’unité d’allocation NTFS

L’alignement du volume, communément appelé alignement du secteur, doit être effectué sur le système de fichiers (NTFS) chaque fois qu’un volume est créé sur un périphérique RAID. Ne pas le faire peut entraîner une dégradation significative des performances et est le plus souvent le résultat d’un mauvais alignement de partition avec des limites d’unités de bande. Cela peut également entraîner un alignement de cache matériel incorrect, ce qui donne lieu à une utilisation inefficace du cache de la baie. Lors de la mise en forme de la partition qui sera utilisée pour SQL Server fichiers de données, il est recommandé d’utiliser une taille d’unité d’allocation de 64 Ko (soit 65 536 octets) pour les données, les journaux et tempdb. Sachez toutefois que l’utilisation de tailles d’unités d’allocation supérieures à 4 Ko entraîne l’impossibilité d’utiliser la compression NTFS sur le volume. Bien que SQL Server prend en charge les données en lecture seule sur les volumes compressés, il n’est pas recommandé.

Réservation de mémoire

Notes

La plupart des informations contenues dans cette section proviennent de Jonathan Kehayias dans son billet de blog De quelle quantité de mémoire mon SQL Server a-t-il réellement besoin ? (sqlskills.com).

Il n’est pas toujours facile d’identifier la quantité appropriée de mémoire physique et de processeurs à allouer pour SQL Server pour la prise en charge de System Center Operations Manager (ou pour d’autres charges de travail en dehors de ce produit). La calculatrice de dimensionnement fournie par le groupe de produits fournit des conseils basés sur l’échelle de la charge de travail, mais ses suggestions sont basées sur les tests effectués dans un environnement de labo qui peuvent ou non s’aligner sur votre charge de travail et votre configuration réelles.

SQL Server vous permet de configurer la quantité minimale et maximale de mémoire qui doit être réservée et utilisée par ses processus. Par défaut, SQL Server peut modifier ses besoins en mémoire dynamiquement en fonction des ressources système disponibles. Le paramètre par défaut de l'option mémoire minimale du serveur est 0 et le paramètre par défaut de l'option mémoire maximale du serveur est 2 147 483 647 Mo.

Des problèmes de performances et de mémoire peuvent survenir si vous ne définissez pas une valeur appropriée pour mémoire maximale du serveur. De nombreux facteurs influencent la quantité de mémoire à allouer à SQL Server afin de garantir que le système d’exploitation peut prendre en charge d’autres processus exécutés sur ce système, tels que la carte HBA, les agents de gestion et l’analyse antivirus en temps réel. Si la mémoire disponible n’est pas suffisante, le système d’exploitation et SQL enverront une notification par radiomessagerie au disque. Cela peut entraîner l’augmentation des E/S du disque, en diminuant davantage les performances et en créant un effet d’ondulation dans le cas où il sera perceptible dans Operations Manager.

Nous vous conseillons de préciser au moins 4 Go de RAM pour mémoire minimale du serveur. Vous devez le faire pour chaque nœud SQL hébergeant une des bases de données Operations Manager (opérationnelle, de l’entrepôt de données, ACS).

Pour mémoire maximale du serveur, nous vous recommandons de réserver initialement un total de :

• 1 Go de RAM pour l’OS
• 1 Go de RAM par 4 Go de RAM installés (jusqu’à 16 Go de RAM)
• 1 Go de RAM par 8 Go de RAM installés (plus de 16 Go de RAM)

Après avoir paramétré ces valeurs, surveillez ensuite le compteur Mémoire\Mégaoctets disponibles dans Windows pour déterminer si vous pouvez augmenter la mémoire disponible pour SQL Server. Windows signale que la mémoire physique disponible est faible à 96 Mo. Dans l’idéal, le compteur ne doit donc pas s’exécuter en dessous d’environ 200 à 300 Mo pour vous assurer que vous disposez d’une mémoire tampon. Pour les serveurs avec 256 Go de RAM ou plus, vous devez probablement vous assurer qu’il ne s’exécute pas en dessous de 1 Go.

N’oubliez pas que ces calculs supposent que vous souhaitez que SQL Server soit en mesure d’utiliser toute la mémoire disponible, sauf si vous les modifiez pour prendre en compte d’autres applications. Tenez compte des exigences spécifiques en matière de mémoire pour votre système d’exploitation, d’autres applications, la pile de threads SQL Server et d’autres allocations multipages. Une formule standard serait ((total system memory) – (memory for thread stack) – (OS memory requirements) – (memory for other applications) – (memory for multipage allocators)), où la mémoire pour la pile de threads = ((max worker threads) (stack size)). La taille de la pile est de 512 Ko pour les systèmes x86, 2 Mo pour les systèmes x64 et 4 Mo pour les systèmes IA64, et vous pouvez trouver la valeur des thread de travail max dans la colonne max_worker_count de sys.dm_os_sys_info.

Ces considérations s’appliquent également aux besoins en mémoire pour que SQL Server s’exécutent sur une machine virtuelle. Étant donné que SQL Server est conçu pour mettre en cache des données dans le pool de mémoires tampons, et qu’il utilisera généralement autant de mémoire que possible, il peut être difficile de déterminer la quantité de RAM idéale. Lors de la réduction de la mémoire allouée à une instance SQL Server, vous devez finalement atteindre un point où une allocation de mémoire inférieure est négociée pour un accès d’E/S disque plus élevé.

Pour configurer la mémoire SQL Server dans un environnement qui a été surprovisionné, commencez par surveiller l’environnement et les indicateurs de performances actuels, y compris l'espérance de vie d’une page du gestionnaire de tampons SQL Server et le nombre de pages lues/s et les valeurs lectures de disque/s du disque physique. Si l’environnement dispose de mémoire excédentaire, l’espérance de vie d’une page augmente d’une unité chaque seconde sans aucune baisse de la charge de travail, en raison de la mise en cache ; le nombre de pages lues/s du gestionnaire de tampons SQL Server sera faible après l’augmentation du cache, et les lectures disque/s du disque physique restera également faible.

Une fois que vous avez compris la base de référence de l’environnement, vous pouvez réduire la mémoire maximale du serveur de 1 Go, puis voir quel est l’impact sur vos compteurs de performance (après les sous-volets de vidage du cache initial). Si les mesures restent acceptables, réduisez-les par un autre 1 Go, puis recommencez l’opération en répétant autant de fois que vous le souhaitez jusqu’à ce que vous déterminiez une configuration idéale.

Pour en savoir plus, consultez les Options de configuration de la mémoire du serveur.

Optimisation de TempDB

La taille et le positionnement physique de la base de données tempdb peuvent affecter les performances d’Operations Manager. Par exemple, si la taille définie pour tempdb est trop petite, une partie de la charge de traitement système peut être absorbée par l’ajustement automatique de tempdb à la taille nécessaire à la gestion de la charge de travail chaque fois que vous redémarrez l’instance de SQL Server. Pour optimiser les performances de tempdb, il est recommandé de lui appliquer la configuration suivante dans un environnement de production :

• Affectez au mode de récupération de tempdb la valeur SIMPLE. Ce mode récupère automatiquement l’espace strictement nécessaire au journal.
• Préallouez l’espace de tous les fichiers de tempdb en définissant leur taille avec une valeur suffisamment élevée pour assumer la charge de travail habituelle de l’environnement. Cela évite que tempdb ne se développe trop fréquemment au détriment des performances. La base de données tempdb peut être définie de façon à autoriser la croissance automatique, mais celle-ci doit être utilisée pour augmenter l’espace disque en cas d’exceptions non prévues.
• Créez autant de fichiers que nécessaire pour optimiser la bande passante disque. L’utilisation de plusieurs fichiers réduit les contentions de stockage de tempdb et améliore la scalabilité. Toutefois, ne créez pas trop de fichiers, car cela peut réduire les performances et augmenter la surcharge de gestion. À titre d’instruction générale, créez un fichier de données pour chaque processeur logique du serveur (en tenant compte des paramètres du masque d’affinité), puis augmentez ou diminuez le nombre de fichiers en fonction des besoins. En règle générale, si le nombre de processeurs logiques est inférieur ou égal à 8, utilisez le même nombre de fichiers de données que de processeurs logiques. Si le nombre de processeurs logiques est supérieur à 8, utilisez huit fichiers de données. Si la contention persiste, augmentez le nombre de fichiers de données par multiples de 4 (jusqu’au nombre de processeurs logiques) jusqu’à ce que la contention soit réduite à des niveaux acceptables, ou apportez des changements à la charge de travail/au code. Si la contention n’est pas réduite, vous devrez peut-être augmenter le nombre de fichiers de données.
• Faites en sorte que chaque fichier de données soit de la même taille, ce qui permet d’optimiser les performances de remplissage proportionnel. Une taille égale pour les fichiers de données est essentielle, car l’algorithme de remplissage proportionnel est basé sur la taille des fichiers. Si les fichiers de données sont créés avec différentes tailles, l’algorithme de remplissage proportionnel tente d’utiliser le plus grand fichier davantage pour les allocations GAM au lieu de propager les allocations entre tous les fichiers, ce qui va à l’encontre de l’objectif de créer plusieurs fichiers de données.
• Placez la base de données tempdb dans un sous-système d’E/S rapide avec des disques SSD pour des performances optimales. Si plusieurs disques sont directement attachés, utilisez l’agrégation de disques.
• Placez la base de données tempdb sur des disques différents de ceux employés par les bases de données utilisateur.

Pour configurer tempdb, vous pouvez exécuter la requête suivante ou modifier ses propriétés dans Management Studio.

USE [tempdb]
GO
DBCC SHRINKFILE (N'tempdev' , 8)
GO
USE [master]
GO
ALTER DATABASE [tempdb] MODIFY FILE ( NAME = N'tempdev', NEWNAME = N'tempdb', SIZE = 2097152KB , FILEGROWTH = 512MB )
GO
ALTER DATABASE [tempdb] ADD FILE ( NAME = N'tempdb2', FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\DATA\tempdb2.mdf' , SIZE = 2097152KB , FILEGROWTH = 512MB )
GO

Exécutez la requête SELECT * from sys.sysprocesses T-SQL pour détecter la contention d’allocation de pages pour la base de données tempdb. Dans la sortie de la table système, la ressource wait peut s’afficher sous la forme « 2:1:1 » (page PFS) ou « 2:1:3 » (page SGAM). Selon le degré de contention, il est également possible que SQL Server ne réponde pas sur de courtes périodes. Une autre approche consiste à examiner les vues de gestion dynamique [sys.dm_exec_request ou sys.dm_os_waiting_tasks]. Les résultats indiquent que ces requêtes ou tâches attendent des ressources tempdb et ont des valeurs similaires à celles mises en évidence précédemment lorsque vous exécutez la requête sys.sysprocesses .

Si les recommandations précédentes ne réduisent pas considérablement la contention d’allocation et que la contention se trouve sur les pages SGAM, implémentez l’indicateur de trace -T1118 dans les paramètres de démarrage pour SQL Server afin que l’indicateur de trace reste en vigueur même après le recyclage de SQL Server. Sous cet indicateur de trace, SQL Server alloue des extensions complètes à chaque objet de base de données et élimine ainsi la contention dans les pages SGAM.

Notes

Cet indicateur de trace affecte chaque base de données sur le instance de SQL Server.

Degré maximal de parallélisme

La configuration par défaut de SQL Server pour les déploiements de taille réduite à moyenne d’Operations Manager est appropriée pour la plupart des besoins. Toutefois, lorsque la charge de travail du groupe d’administration est mise à l’échelle vers le haut vers un scénario de classe entreprise (généralement plus de 2 000 systèmes gérés par agent et une configuration de supervision avancée, qui inclut la surveillance au niveau du service avec des transactions synthétiques avancées, la surveillance des périphériques réseau, multiplateforme, etc.), il est nécessaire d’optimiser la configuration de SQL Server décrite dans cette section du document. MAXDOP est une option de configuration qui n’a pas été abordée dans les instructions précédentes.

L’option de configuration Degré maximal de parallélisme (MAXDOP) de Microsoft SQL Server contrôle le nombre de processeurs employés pour exécuter une requête dans un plan parallèle. Cette option détermine les ressources de calcul et de thread qui sont utilisées pour les opérateurs de plan de requête qui effectuent le travail en parallèle. Selon que SQL Server est configuré sur un ordinateur multitraitement symétrique (SMP), un ordinateur NUMA (non uniforme à la mémoire) ou des processeurs avec hyperthreading, vous devez configurer l’option degré maximal de parallélisme de manière appropriée.

Quand SQL Server s’exécute sur un ordinateur doté de plusieurs microprocesseurs ou processeurs, il détecte le degré de parallélisme optimal, qui correspond au nombre de processeurs employés pour exécuter une seule instruction, pour chaque exécution d’un plan parallèle. Par défaut, la valeur de cette option est égale à 0, ce qui permet à SQL Server de déterminer le degré maximal de parallélisme.

Les procédures stockées et les requêtes prédéfinies dans Operations Manager en ce qui concerne la base de données opérationnelle, l’entrepôt de données et même la base de données d’audit n’incluent pas l’option MAXDOP, car il n’existe aucun moyen pendant l’installation d’interroger dynamiquement le nombre de processeurs présentés au système d’exploitation, ni de coder en dur la valeur de ce paramètre, ce qui peut avoir des conséquences négatives lors de l’exécution de la requête.

Notes

L’option de configuration max degree of parallelism ne limite pas le nombre de processeurs que le SQL Server utilise. Pour configurer le nombre de processeurs utilisés par SQL Server, utilisez l’option de configuration de masque d’affinité.

• Pour les serveurs qui utilisent plus de huit processeurs, utilisez la configuration suivante : MAXDOP=8
• Pour les serveurs qui utilisent huit processeurs ou moins, utilisez la configuration suivante : MAXDOP=0 à N

  Notes

  Dans cette configuration, N représente le nombre de processeurs.