Considérations relatives à la conception de SQL Server

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

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

Dans un déploiement distribué à l’échelle de l’entreprise moyen à l’échelle de l’entreprise, l’instance SQL Server doit se trouver sur un serveur autonome dédié ou dans une configuration de haute disponibilité SQL Server. Dans les deux cas, SQL Server doit déjà exister et est accessible avant de démarrer l’installation du premier serveur d’administration ou du collecteur ACS.

Nous vous déconseillons d’utiliser les 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 E/S et à d’autres restrictions de 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 qu’Azure SQL Managed Instance ou Amazon Relational Database Service (AWS RDS). Utilisez une instance de SQL Server installée sur un ordinateur Windows. La seule exception à ceci se trouve dans Azure Monitor SCOM Managed Instance, qui utilise Azure SQL MI et n’est pas reconfigurable.

impératifs SQL Server

Les versions suivantes de SQL Server Entreprise &Édition Standard sont prises en charge pour une installation existante de la version de System Center Operations Manager pour héberger la base de données Reporting Server, Operational, Data Warehouse et ACS :

• SQL Server 2022

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

  Remarque

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

• Mises à jour cumulatives et SQL Server 2017 comme indiqué ici

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

Remarque

• Chacun des composants SQL Server suivants prenant en charge une infrastructure SCOM doit être à la même version principale de SQL Server :
  • Instances du moteur de base de données SQL Server hébergeant l’une des bases de données SCOM (autrement dit, OperationManager, OperationManagerDW et bases de données SSRS ReportServer &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.

Remarque

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

Des considérations matérielles et logicielles supplémentaires s’appliquent à votre planification de conception :

• Nous vous recommandons d’exécuter SQL Server sur des ordinateurs au format de fichier NTFS.
• Il doit y avoir au moins 1024 Mo d’espace disque libre pour la base 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.

Remarque

Bien que Operations Manager utilise uniquement Authentification Windows pendant l’installation, le paramètre d’authentification en mode mixte SQL fonctionne toujours si aucun compte local n’a le rôle db_owner. Les comptes locaux avec le rôle db_owner sont connus pour provoquer 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 à l’un des 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.

Remarque

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 la base de données SQL et 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 instance SQL Server n’est pas configurée avec l’un des classements pris en charge répertoriés précédemment, l’exécution d’une nouvelle configuration de l’installation d’Operations Manager échoue. Toutefois, une mise à niveau sur place s’effectue correctement.

Configuration du pare-feu

Operations Manager dépend de SQL Server pour héberger ses bases de données et une plateforme de création de rapports pour analyser et présenter des données opérationnelles historiques. Le serveur d’administration, les opérations et les rôles de 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 des groupes de disponibilité SQL Always On pour fournir des fonctionnalités de basculement pour les bases de données Operations Manager, il existe des paramètres de configuration de pare-feu supplémentaires qui doivent être inclus dans votre stratégie de sécurité de pare-feu.

Le tableau suivant vous aide à identifier les ports de pare-feu requis par SQL Server qui devront être autorisés au minimum afin que les rôles serveur de votre groupe d’administration Operations Manager communiquent correctement.

Scénario	Port	Sens	Rôle Operations Manager
SQL Server hébergeant des bases de données Operations Manager	TCP 1433 *	Entrant	serveur d’administration et console web (pour Application Advisor et Diagnostics d’application)
SQL Server Browser Service	UDP 1434	Entrant	Serveur management
Connexion d’administrateur dédié 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é Always On SQL Server	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 des exigences de 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 la surveillance quotidienne. Le dimensionnement et la configuration du serveur de base de données sont essentiels aux performances globales du groupe d’administration. La ressource la plus critique utilisée par la base de données Operations Manager est le sous-système de stockage, mais le processeur et la RAM sont également importants.

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

• Taux de collecte de données opérationnelles. Les données opérationnelles se composent de tous les événements, alertes, modifications 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 sont utilisées pour écrire ces données sur disque à mesure qu’elles entrent dans le système. Le taux de données opérationnelles collectées tend à augmenter à mesure que des packs d’administration supplémentaires sont importés et des agents supplémentaires sont ajoutés. Le type d’ordinateur qu’un agent surveille est également un facteur important utilisé pour déterminer le taux global de collecte de données opérationnelles. Par exemple, un agent qui surveille un ordinateur de bureau critique pour l’entreprise peut être censé 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 modification de l’espace d’instance. 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, lorsque les données d’espace d’instance changent, les serveurs d’administration effectuent des requêtes supplémentaires à la base de données Operations Manager afin de calculer la configuration et de regrouper les modifications. Le taux de modifications de l’espace d’instance augmente lorsque vous importez des packs d’administration supplémentaires dans un groupe d’administration. L’ajout de nouveaux agents à un groupe d’administration augmente temporairement le taux de modifications de l’espace d’instance.
• Nombre de consoles Opérateur et d’autres connexions du Kit de développement logiciel (SDK) en cours d’exécution simultanément. Chaque console Opérateur lit les données de la base de données Operations Manager. L’interrogation de ces données consomme potentiellement de grandes quantités de ressources d’E/S de stockage, de temps processeur et de RAM. Les consoles d’exploitation qui affichent de grandes quantités de données opérationnelles dans l’affichage des événements, l’affichage d’état, la vue Alertes et la vue de données de performances ont tendance à entraîner la plus grande charge sur la base de données.

La base de données Operations Manager est une source unique d’échec pour le groupe d’administration. Elle peut donc être rendue hautement disponible à l’aide de configurations de basculement prises en charge, telles que les groupes de disponibilité Always On SQL Server 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 la 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 tâche. Si SQL Server Service Broker est désactivé, toutes les opérations de tâche sont affectées. Le comportement résultant peut varier en fonction de la tâche lancée. Par conséquent, il est important de vérifier 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 is_broker_enabled champ est 1 (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 temps quasi réel, il est important d’avoir 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 critique 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 similaires à la base de données Operations Manager, mais elles peuvent varier. En outre, la charge de travail mise sur l’entrepôt de données reporting par la création de rapports est différente de la charge mise sur la base de données Operations Manager par utilisation de la console Opérateur.

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

• Taux de collecte de 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. Cette opération supplémentaire signifie que la collecte des données opérationnelles dans l’entrepôt de données de création de rapports 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écouverte par l’entrepôt de données reporting par rapport à la base de données Operations Manager.
• Nombre d’utilisateurs de création de rapports simultanés ou de génération de rapports planifiée. Étant donné que les rapports résument fréquemment de grands volumes de données, chaque utilisateur de création de rapports peut ajouter une charge importante sur le système. Le nombre de rapports exécutés simultanément et le type de rapports en cours d’exécution affectent les besoins de capacité globaux. En règle générale, les rapports qui interrogent de grandes plages de dates ou un grand nombre d’objets nécessitent des ressources système supplémentaires.

En fonction de ces facteurs, il existe plusieurs pratiques recommandées à prendre en compte 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 via le groupe d’administration, le choix d’un sous-système de stockage approprié pour l’entrepôt de données de création de rapports 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 de l’entrepôt de données Reporting doit être similaire au sous-système de stockage de la base de données Operations Manager, et les instructions qui s’appliquent à la base de données Operations Manager s’appliquent également à l’entrepôt de données Reporting.
• Envisagez le placement approprié des journaux de données par rapport aux journaux des transactions. Quant à la base de données Operations Manager, la séparation des données SQL et des journaux des transactions est souvent un choix approprié lorsque vous augmentez le nombre d’agents. Si la base de données Operations Manager et l’entrepôt de données Reporting se trouvent sur le même serveur et que vous souhaitez séparer les données et les journaux des transactions, vous devez placer les journaux des transactions pour la base de données Operations Manager sur un volume physique et des spindles de disque distincts de l’entrepôt de données reporting pour recevoir tous les avantages. 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é adéquate et que les performances des E/S de 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 Reporting sur le même serveur, il est avantageux de les séparer lorsque vous augmentez le nombre d’agents et le volume de données opérationnelles entrantes. Lorsque l’entrepôt de données de création de rapports et le serveur de rapports se trouvent sur un serveur distinct de la base de données Operations Manager, vous bénéficiez de meilleures performances de création de rapports.

La base de données de l’entrepôt de données Operations Manager est une source unique de défaillance pour le groupe d’administration. Elle peut donc être rendue hautement disponible à l’aide de configurations de basculement prises en charge, telles que les groupes de disponibilité Always On SQL Server ou les instances de cluster de basculement.

SQL Server AlwaysOn

Les groupes de disponibilité Always On SQL Server prennent en charge les 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 versions ultérieures - Operations Manager, SQL Always On est préféré au clustering de basculement pour fournir une haute disponibilité pour les bases de données. Toutes les bases de données, à l’exception de l’installation de Reporting Services en mode natif, qui utilise deux bases de données pour séparer le stockage persistant des exigences de stockage temporaire, peuvent être hébergées dans un groupe de disponibilité AlwaysOn.

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 WSFC (Clustering de basculement Windows Server) pour héberger le 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 en tant que 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 les groupes de disponibilité Always On.
• En savoir plus sur la configuration d’un groupe de disponibilité.

Remarque

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

Chaîne multisubnet

Operations Manager ne prend pas en charge les mots clés chaîne de connexion (MultiSubnetFailover=True). Étant donné qu’un groupe de disponibilité a un nom d’écouteur (appelé nom réseau ou point d’accès client dans le Gestionnaire de cluster WSFC) en fonction de plusieurs adresses IP provenant de différents sous-réseaux, par exemple lorsque vous déployez dans une configuration de basculement intersite, les demandes de connexion client des serveurs d’administration vers l’écouteur du groupe de disponibilité atteignent un délai d’expiration de connexion.

L’approche recommandée pour contourner cette limitation lorsque vous avez déployé des nœuds serveur dans le groupe de disponibilité dans un environnement multi-sous-réseau 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 utiliser une valeur de durée de vie faible pour l’enregistrement DNS inscrit.

Ces paramètres permettent, lors du basculement vers un nœud dans un autre sous-réseau, de récupérer et de résoudre plus rapidement le nom du 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 suivante : Configurer l’écouteur de groupe de disponibilité - SQL Server Always On

Exécutez les commandes PowerShell suivantes sur le nœud SQL qui héberge 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 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 pour éviter le redémarrage du service d’accès aux données Operations Manager chaque fois qu’un basculement entre les nœuds se produit. Pour plus d’informations sur la configuration de ce paramètre, consultez l’article de la base de connaissances suivant : le service System Center Management cesse de répondre après qu’une instance de SQL Server soit hors connexion.

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 causés par une utilisation élevée des ressources (autrement dit, processeur ou mémoire) avec SQL Server lui-même ; il est plutôt directement lié à la configuration du sous-système de stockage. Les goulots d’étranglement des performances sont généralement attribués à ne pas suivre les instructions de configuration recommandées avec le stockage approvisionné pour l’instance de base de données SQL Server. Ces exemples sont les suivants :

• Allocation insuffisante des broches pour les numéros logiques pour prendre en charge les exigences d’E/S d’Operations Manager.
• Hébergement des journaux des transactions et des fichiers de base de données sur le même volume. Ces deux charges de travail présentent 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, et ainsi de suite.
• Partition de disque incorrectement désalignement 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 de base de données et de journaux 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 d’UC, etc.

La configuration du stockage est l’un des composants critiques d’un déploiement SQL Server pour Operations Manager. Les serveurs de base de données ont tendance à être fortement liés aux E/S en raison d’une activité de lecture et d’écriture de base de données rigoureuse et du traitement du journal des transactions. Le modèle de comportement des E/S d’Operations Manager est généralement de 80 % d’écritures et de 20 % de lectures. Par conséquent, une configuration incorrecte des sous-systèmes d’E/S peut entraîner des performances et un fonctionnement médiocres des systèmes SQL Server et devient visible dans Operations Manager.

Il est important de tester la conception de 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 dans le 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 appareil RAID. L’échec de cette opération 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é de bande. Elle peut également entraîner une mauvaise alignement du cache matériel, ce qui entraîne une utilisation inefficace du cache de tableau. Lors de la mise en forme de la partition qui sera utilisée pour les fichiers de données SQL Server, il est recommandé d’utiliser une taille d’unité d’allocation de 64 Ko (autrement dit, 65 536 octets) pour les données, les journaux et tempdb. Sachez toutefois que l’utilisation de tailles d’unité d’allocation supérieures à 4 Ko entraîne l’incapacité à utiliser la compression NTFS sur le volume. Même si SQL Server prend en charge les données en lecture seule sur les volumes compressés, il n’est pas recommandé.

Réserver la mémoire

Remarque

La plupart des informations contenues dans cette section proviennent de Jonathan Kehayias dans son billet de blog Combien de mémoire mon SERVEUR 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 à 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 en fonction de l’échelle de la charge de travail, mais ses recommandations sont basées sur les tests effectués dans un environnement lab qui peut 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 sera réservée et utilisée par son processus. Par défaut, SQL Server peut modifier dynamiquement ses besoins en mémoire en fonction des ressources système disponibles. Le paramètre par défaut pour la mémoire minimale du serveur est 0 et le paramètre par défaut pour la mémoire maximale du serveur est de 2 147 483 647 Mo.

Les problèmes liés aux performances et à la mémoire peuvent survenir si vous ne définissez pas de valeur appropriée pour la mémoire maximale du serveur. De nombreux facteurs influencent la quantité de mémoire que vous devez allouer à SQL Server pour vous assurer que le système d’exploitation peut prendre en charge d’autres processus s’exécutant sur ce système, tels que la carte HBA, les agents de gestion et l’analyse en temps réel antivirus. Si la mémoire suffisante n’est pas définie, le système d’exploitation et SQL pagent sur le disque. Cela peut entraîner l’augmentation des E/S de disque, la diminution des performances et la création d’un effet d’entraînement où il devient visible dans Operations Manager.

Nous vous recommandons de spécifier au moins 4 Go de RAM pour la mémoire minimale du serveur. Cette opération doit être effectuée pour chaque nœud SQL hébergeant l’une des bases de données Operations Manager (opérationnelles, entrepôt de données, ACS).

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

• 1 Go de RAM pour le système d’exploitation
• 1 Go de RAM toutes les 4 Go de RAM installées (jusqu’à 16 Go de RAM)
• 1 Go de RAM toutes les 8 Go de RAM installées (au-dessus de 16 Go de RAM)

Une fois que vous avez défini ces valeurs, surveillez le compteur Memory\Available MBytes 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 plus bas qu’environ 200 à 300 Mo pour vous assurer que vous disposez d’une mémoire tampon. Pour les serveurs avec une RAM de 256 Go ou une version ultérieure, vous souhaiterez probablement vous assurer qu’il ne s’exécute pas moins de 1 Go.

N’oubliez pas que ces calculs supposent que SQL Server peut utiliser toutes les mémoires disponibles, sauf si vous les modifiez pour prendre en compte d’autres applications. Tenez compte des besoins en mémoire spécifiques de votre système d’exploitation, d’autres applications, de la pile de threads SQL Server et d’autres allocateurs multipage. Une formule classique serait ((total system memory) – (memory for thread stack) – (OS memory requirements) – (memory for other applications) – (memory for multipage allocators)), où la mémoire de 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 pour les threads de travail maximum dans la colonne max_worker_count de sys.dm_os_sys_info.

Ces considérations s’appliquent également aux exigences en mémoire pour que SQL Server s’exécute 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 utilise généralement autant de mémoire que possible, il peut être difficile de déterminer la quantité idéale de RAM nécessaire. Lorsque vous réduisez la mémoire allouée à une instance SQL Server, vous atteindrez finalement un point où l’allocation de mémoire inférieure est échangée pour un accès d’E/S disque supérieur.

Pour configurer la mémoire SQL Server dans un environnement surprovisionné, commencez par surveiller l’environnement et les métriques de performances actuelles, y compris l’espérance de vie de page du Gestionnaire de mémoires tampons SQL Server et les lectures de pages/s et les valeurs de disque de disque physique lues/s. Si l’environnement a un excès de mémoire, l’espérance de vie des pages augmente d’une valeur d’une seconde par seconde sans aucune diminution de la charge de travail, en raison de la mise en cache ; la valeur de la page du Gestionnaire de mémoires tampons SQL Server est faible après la montée en puissance du cache ; et les lectures de disque physique /s restent également faibles.

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 comment cela a un impact sur vos compteurs de performances (après tout vidage initial du cache subsides). Si les métriques restent acceptables, réduisez d’un autre 1 Go, puis surveillez à nouveau, répétez comme vous le souhaitez jusqu’à ce que vous déterminiez une configuration idéale.

Pour plus d’informations, consultez les options de configuration de la mémoire du serveur.

Optimiser 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 du système peut être prise en charge automatiquement avec la taille de tempdb requise pour prendre en charge la charge de travail chaque fois que vous redémarrez l’instance de SQL Server. Pour obtenir des performances tempdb optimales, nous vous recommandons la configuration suivante pour tempdb dans un environnement de production :

• Définissez le modèle de récupération de tempdb sur SIMPLE. Ce modèle récupère automatiquement l’espace journal pour maintenir la taille de l’espace requis.
• 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. Il empêche tempdb de se développer trop fréquemment, ce qui peut affecter les performances. La base de données tempdb peut être définie sur la croissance automatique, mais elle doit être utilisée pour augmenter l’espace disque pour les exceptions non planifiées.
• Créez autant de fichiers que nécessaire pour optimiser la bande passante du disque. L’utilisation de plusieurs fichiers réduit la contention de stockage tempdb et génère une scalabilité améliorée. Toutefois, ne créez pas trop de fichiers, car il peut réduire les performances et augmenter la surcharge de gestion. En règle générale, créez un fichier de données pour chaque processeur logique sur le serveur (en tenant compte des paramètres de masque d’affinité), puis ajustez le nombre de fichiers vers le haut ou vers le bas si nécessaire. 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, puis si la contention persiste, augmentez le nombre de fichiers de données par plusieurs de 4 (jusqu’au nombre de processeurs logiques) jusqu’à ce que la contention soit réduite à des niveaux acceptables ou apportez des modifications à la charge de travail/code. Si la contention n’est pas réduite, vous devrez peut-être augmenter davantage 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. Le dimensionnement égal des fichiers de données est essentiel, car l’algorithme de remplissage proportionnel est basé sur la taille des fichiers. Si des fichiers de données sont créés avec des tailles inégales, l’algorithme de remplissage proportionnel tente d’utiliser davantage de fichiers pour les allocations GAM au lieu de répartir les allocations entre tous les fichiers, ce qui permet de vaincre l’objectif de créer plusieurs fichiers de données.
• Placez la base de données tempdb sur un sous-système d’E/S rapide à l’aide de lecteurs ssd pour obtenir les performances les plus 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 d’attente peut apparaître sous la forme « 2:1:1 » (page PFS) ou « 2:1:3 » (page Carte d’allocation globale partagée). Selon le degré de contention, cela peut également entraîner l’apparition de SQL Server sans réponse pendant 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 les ressources tempdb et ont des valeurs similaires comme en surbrillance 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 de 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 étendues complètes à chaque objet de base de données, éliminant ainsi la contention sur les pages SGAM.

Remarque

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

Degré maximal de parallélisme

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

L’option de configuration Max Degree of Parallelism (MAXDOP) de Microsoft SQL Server contrôle le nombre de processeurs utilisés pour l’exécution d’une requête dans un plan parallèle. Cette option détermine les ressources de calcul et de thread 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 symétrique multiprocesseur (SMP), un ordinateur NUMA (non uniforme memory access) ou des processeurs hyperthreading activés, vous devez configurer l’option max degree of parallelism de manière appropriée.

Quand SQL Server s’exécute sur un ordinateur avec plusieurs microprocesseurs ou processeurs, il détecte le meilleur degré de parallélisme, autrement dit, le nombre de processeurs utilisés pour exécuter une seule instruction, pour chaque exécution de plan parallèle. Par défaut, sa valeur pour cette option est 0, ce qui permet à SQL Server de déterminer le degré maximal de parallélisme.

Les procédures stockées et requêtes prédéfinis 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 d’essayer 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.

Remarque

L’option de configuration max degree of parallelism ne limite pas le nombre de processeurs que SQL Server utilise. Pour configurer le nombre de processeurs utilisés par SQL Server, utilisez l’option de configuration du 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 vers N

  Remarque

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