Partager via

Considérations relatives à la conception de SQL Server

  • Article

System Center Operations Manager s’appuie sur Microsoft SQL Server pour prendre en charge ses bases de données d’audit opérationnelles, d’entrepôt de données et ACS. Ces bases de données sont essentielles et sont créées pendant le déploiement du premier serveur d’administration ou 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 des bases de données Operations Manager à partir d’une instance SQL disposant d’autres bases de données d’application pour é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 2019 avec une mise à jour cumulative minimale 8 (CU8) ou une mise à jour ultérieure comme disponible ici
  • SQL Server 2016 et les dernières mises à jour disponibles ici
  • SQL Server 2022 avec une mise à jour cumulative minimale 11 (CU11) ou une mise à jour ultérieure, comme disponible ici
  • SQL Server 2019 avec une mise à jour cumulative minimale 8 (CU8) ou une mise à jour ultérieure comme disponible ici
  • SQL Server 2017 avec la dernière mise à jour disponible ici
  • Mises à jour cumulatives et SQL Server 2017 comme indiqué ici
  • SQL Server 2016 et Service Packs comme indiqué ici

Les versions suivantes de SQL Server Entreprise &Édition Standard sont prises en charge pour une installation nouvelle ou existante de System Center 2016 - Operations Manager pour héberger reporting Server, Opérationnel, Data Warehouse et ACS :

  • SQL Server 2016 et les dernières mises à jour disponibles ici
  • SQL Server 2014 et les dernières mises à jour disponibles ici
  • SQL Server 2012 et les dernières mises à jour disponibles ici

Pilotes SQL Server

Les pilotes OLE DB et ODBC SQL Server doivent être installés sur tous les serveurs d’administration et le serveur de console web, car ces composants interfacent directement avec les bases de données et ces pilotes autorisent l’accès au niveau de l’API à SQL.

Il est recommandé d’utiliser une connexion SQL Server chiffrée ; Dans ce cas, vous devez installer les dernières versions des pilotes SQL :

Vous trouverez plus d’informations sur la configuration du chiffrement de connexion SQL ici : Configurer sql Server Moteur de base de données pour chiffrer les connexions

Si vous n’utilisez pas de connexions SQL chiffrées, utilisez les versions précédentes des pilotes SQL qui n’appliquent pas le chiffrement :

Mises à jour de SQL Server

Chacun des composants SQL Server suivants prenant en charge une infrastructure Operations Manager 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 Operations Manager, notamment :
    • OperationManager
    • OperationManagerDW
    • Bases de données SSRS ReportServer et ReportServerTempDB
  • Instance SQL Server Reporting Services (SSRS)

Mode d’authentification SQL Server

Par défaut, SQL fonctionne dans une configuration d’authentification en mode mixte. Toutefois, Operations Manager utilise uniquement Authentification Windows pour communiquer avec SQL Server. Si elle est laissée par défaut, le paramètre d’authentification en mode mixte SQL fonctionne toujours si aucun compte local n’a le db_owner rôle. Les comptes locaux avec le db_owner rôle sont connus pour provoquer des problèmes avec Operations Manager.

Il est vivement recommandé de supprimer le db_owner rôle de tous les comptes locaux avant d’installer le produit et d’ajouter le db_owner rôle à des comptes locaux après l’installation.

Autres considérations

D’autres considérations matérielles et logicielles s’appliquent à votre planification de conception :

  • Il est recommandé d’avoir des disques SQL au format de fichier NTFS.
  • Vous devez disposer d’au moins 1 Go d’espace disque libre pour la base de données opérationnelle et de l’entrepôt de données, ce qui est appliqué au moment de la création de la base de données. N’oubliez pas que l’utilisation du disque des bases de données augmente considérablement après l’installation, veillez à disposer d’un espace disque libre au-dessus de cette exigence de base.
  • .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.
  • 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.
  • 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 Operations Manager.
  • Les options d’installation de Windows Server (Server Core, Server with Desktop Experience et Nano Server) prises en charge par les composants de base de données Operations Manager sont basées sur les options d’installation prises en charge par SQL Server.

Pour plus d’informations, consultez la section Configuration matérielle et logicielle requise sous la documentation relative à l’installation et à la planification de SQL Server ici : Planifier une installation de SQL Server

Paramètre de classement SQL Server

Les classements SQL Server et Windows suivants sont pris en charge dans 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 se termine 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 utilise des groupes de disponibilité SQL Always On, 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 identifie les ports de pare-feu requis par SQL Server pour que les serveurs d’administration communiquent avec les bases de données :

Scénario Port Sens Rôle Operations Manager
SQL Server hébergeant des bases de données Operations Manager TCP 1433 * Entrante serveur d’administration et console web (pour Application Advisor et Diagnostics d’application)
SQL Server Browser Service UDP 1434 Entrante Serveur management
Connexion d’administrateur dédié SQL Server TCP 1434 Entrante Serveur management
Autres ports 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 Entrante Serveur management
Écouteur de groupe de disponibilité Always On SQL Server Port configuré par l’administrateur Entrante Serveur management
SQL Server Reporting Services hébergeant Operations Manager Reporting Server TCP 80 (par défaut)/443 (SSL) Entrante serveur d’administration et console Opérateur

Remarque

Bien que TCP 1433 soit le port standard de l’instance par défaut du Moteur de base de données, lorsque vous créez une instance nommée sur un serveur SQL Server autonome ou que vous 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 configuriez correctement vos pare-feu et entrez ces informations lors de l’installation.

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.
    • Le taux de collecte de données opérationnelles est influencé par des facteurs tels que le nombre de packs d’administration importés, le nombre d’agents ajoutés et le type d’ordinateur surveillé. Par exemple, un agent qui surveille un ordinateur de bureau critique pour l’entreprise collecte moins de données par rapport à un agent qui surveille un serveur exécutant SQL Server avec plusieurs bases de données.
  • Taux de modification de l’espace d’instance.
    • La mise à jour des données existantes dans la base de données Operations Manager est intensive par rapport à l’écriture de nouvelles données opérationnelles. En outre, lorsqu’il existe des modifications dans les données d’espace d’instance, les serveurs d’administration doivent effectuer davantage de requêtes à la base de données pour calculer la configuration et regrouper les modifications. Le taux de modification de l’espace d’instance augmente lors de l’importation de nouveaux packs d’administration ou l’ajout de nouveaux agents au groupe d’administration.
  • Le nombre de consoles Opérateur et d’autres connexions du Kit de développement logiciel (SDK) exécutées simultanément affecte également la charge sur la base de données.
    • 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 pour vérifier si le répartiteur est déjà activé, indiqué par un résultat de 1 (un) dans le is_broker_enabled champ :

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

  2. Si la valeur affichée dans le is_broker_enabled champ est 0 (zéro), exécutez l’instruction SQL suivante pour activer le répartiteur :

    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 Operations Manager Data Warehouse

Remarque

L’entrepôt de données Operations Manager est également appelé base de données « Entrepôt de données de création de rapports » ou simplement « Entrepôt de données » dans une documentation.

System Center - Operations Manager insère des données dans l’entrepôt de données en quasi-temps 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. Comme pour la base de données Operations Manager, la ressource la plus critique sur l’entrepôt de données 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 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 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 sont les suivants :

  • Taux de collecte de données opérationnelles.
    • L’entrepôt de données effectue des calculs et stocke des données agrégées, ainsi qu’une quantité limitée de données brutes, pour permettre des rapports plus efficaces. Par conséquent, le coût de collecte des données opérationnelles dans l’entrepôt de données est légèrement plus élevé par rapport à la base de données Operations Manager. Toutefois, ce coût est compensé par le coût de traitement réduit des données de découverte dans l’entrepôt de données 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.
    • Chaque utilisateur de création de rapports peut ajouter une charge importante sur le système, car les rapports résument fréquemment de grands volumes de données. Les besoins de capacité globaux sont influencés par le nombre de rapports exécutés simultanément et le type de rapports en cours d’exécution. 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 :

  • Choisissez un sous-système de stockage approprié.
    • Étant donné que l’entrepôt de données 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 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 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.
  • 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 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 pour bénéficier de tout avantage. Les fichiers de données de la base de données Et de l’entrepôt de données Operations Manager peuvent partager le même volume physique tant que le volume fournit une capacité et des performances d’E/S de disque adéquates n’affectent pas négativement la fonctionnalité de surveillance et de création de rapports.
  • Envisagez de placer l’entrepôt de données 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 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 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 Operations Manager Data Warehouse 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é sur 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.

Pour configurer un groupe de disponibilité, vous déployez 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é.

Conseil

À compter d’Operations Manager 2022, vous pouvez configurer et mettre à niveau des bases de données Operations Manager avec une configuration SQL Always On existante sans avoir besoin de modifier la configuration après la configuration.

Pour configurer un groupe de disponibilité, vous déployez 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é.

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 avec les nœuds de serveur de groupe de disponibilité déployés dans un environnement multi-sous-réseau consiste à :

  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 une récupération et une résolution plus rapides du nom du cluster avec la nouvelle adresse IP lors du basculement vers un nœud dans un autre sous-réseau.

Exécutez les commandes PowerShell suivantes sur l’un des nœuds SQL pour modifier ces 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.

Les commandes PowerShell suivantes peuvent être exécutées 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 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, 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.

Optimiser SQL Server

Les expériences de support ont montré 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 ; plutôt que le problème est 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 - DiskSpd, PowerShell et performances de stockage : mesurer les E/S, le débit et la latence pour les disques locaux et les partages de fichiers SMB.

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 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é d’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, assurez-vous qu’il n’est pas inférieur à 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 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 pouvez atteindre 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.

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 par TempDB à la taille 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 disques 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, ce paramètre peut 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 montrent 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 sys.sysprocesses requête.

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 -T1118 de trace 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

Conseil

Pour connaître les meilleures pratiques et recommandations les plus récentes de l’équipe SQL Server, reportez-vous à leur documentation ici : Définir l’option max degree of parallelism pour des performances optimales

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 non 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 n’appartenant pas à l’unité d’accès à la mémoire (NUMA) 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 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 d’essayer de coder en dur la valeur de ce paramètre, ce qui peut avoir des conséquences négatives lorsque la requête est exécutée.

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

    Conseil

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

  • Pour les serveurs configurés par NUMA, MAXDOP ne doit pas dépasser le nombre de processeurs affectés à chaque nœud NUMA.

  • Pour les serveurs sur utilisant l’hyperthreading activé, la valeur MAXDOP ne doit pas dépasser le nombre de processeurs physiques.

  • Pour les serveurs avec NUMA configurés et hyperthreading activés, la valeur MAXDOP ne doit pas dépasser le nombre de processeurs physiques par nœud NUMA.

Vous pouvez surveiller le nombre de workers parallèles en interrogeant select * from sys.dm_os_tasks.

Dans cet exemple, la configuration matérielle du serveur était un HP Blade G6 avec 24 processeurs de cœurs et 196 Go de RAM. L’instance hébergeant la base de données Operations Manager avait un paramètre MAXMEM de 64 Go. Après avoir effectué les optimisations suggérées dans cette section, les performances ont été améliorées. Toutefois, un goulot d’étranglement du parallélisme de requête persiste. Après avoir testé différentes valeurs, les performances les plus optimales ont été trouvées en définissant MAXDOP=4.

Dimensionnement initial de la base de données

La tentative d’estimer la croissance future des bases de données Operations Manager, en particulier les bases de données opérationnelles et d’entrepôt de données, dans les premiers mois après le déploiement n’est pas un exercice simple. Bien que l’Assistance de dimensionnement Operations Manager soit raisonnable dans l’estimation de la croissance potentielle basée sur la formule dérivée du groupe de produits de leur test dans le laboratoire, elle ne tient pas compte de plusieurs facteurs, ce qui peut influencer la croissance à court terme et à long terme.

La taille initiale de la base de données, comme suggéré par l’assistant de dimensionnement, doit être allouée à une taille prédite pour réduire la fragmentation et la surcharge correspondante, qui peut être spécifiée au moment de l’installation pour les bases de données Opérationnelles et Data Warehouse. Si, pendant l’installation, l’espace de stockage insuffisant est disponible, les bases de données peuvent être développées ultérieurement à l’aide de SQL Management Studio, puis réindexées par la suite pour défragmenter et optimiser en conséquence. Cette recommandation s’applique également à la base de données ACS.

Une surveillance proactive de la croissance de la base de données opérationnelle et de l’entrepôt de données doit être effectuée sur un cycle quotidien ou hebdomadaire. Cela est nécessaire pour identifier les poussées de croissance inattendues et significatives et commencer à résoudre les problèmes afin de déterminer la causalité, qu’il s’agisse d’un bogue dans un flux de travail du pack d’administration (autrement dit, règle de découverte, règle de collecte d’événements ou règle de collecte d’événements, ou de surveillance ou de règle d’alerte) ou d’un autre symptôme avec un pack d’administration qui n’a pas été identifié pendant la phase de test et d’assurance qualité du processus de gestion des mises en production.

Croissance automatique de la base de données

Lorsque la taille de fichier des bases de données réservées est pleine, SQL Server peut augmenter automatiquement la taille d’un pourcentage ou d’une quantité fixe. De plus, une taille de base de données maximale peut être configurée pour empêcher le remplissage de l’espace disponible sur le disque. Par défaut, la base de données Operations Manager n’est pas configurée avec la croissance automatique activée ; seules les bases de données Data Warehouse et ACS sont.

Reposez uniquement sur la croissance automatique comme une urgence pour une croissance inattendue. La croissance automatique introduit une pénalité de performances qui doit être prise en compte lors de la gestion d’une base de données transactionnelle hautement transactionnelle. Les pénalités de performances sont les suivantes :

  • Si vous ne fournissez pas d’incrément de croissance approprié, la fragmentation du fichier journal ou de la base de données peut se produire.
  • Si vous exécutez une transaction qui nécessite plus d’espace journal que disponible et que la croissance automatique est activée pour le journal des transactions de cette base de données, le temps nécessaire à la fin de la transaction inclut le temps nécessaire au journal des transactions pour augmenter d’après le montant configuré.
  • Si vous exécutez une transaction volumineuse qui nécessite que le journal augmente, d’autres transactions nécessitant une écriture dans le journal des transactions doivent également attendre que l’opération de croissance se termine.

Si les options autogrow et autoshrink sont combinées, cela peut créer une surcharge inutile. Assurez-vous que les seuils qui déclenchent les opérations de croissance et de réduction ne provoquent pas de changements fréquents de taille de taille supérieure et descendante. Par exemple, vous pouvez exécuter une transaction qui entraîne la croissance du journal des transactions de 100 Mo au moment où elle est validée ; Un certain temps après ce démarrage automatique et réduit le journal des transactions de 100 Mo. Ensuite, vous exécutez la même transaction et le journal des transactions augmente de 100 Mo. Dans cet exemple, vous créez une surcharge inutile et potentiellement une fragmentation du fichier journal, dont l’une peut affecter négativement les performances.

Configurez ces deux paramètres avec soin. La configuration particulière dépend vraiment de votre environnement. La recommandation générale est d’augmenter la taille de la base de données d’une quantité fixe afin de réduire la fragmentation du disque. Consultez, par exemple, la figure suivante, où la base de données est configurée pour augmenter de 1 024 Mo chaque fois que la croissance automatique est requise.

Stratégie de basculement de cluster

Le clustering de basculement Windows Server est une plateforme à haute disponibilité qui surveille constamment les connexions réseau et l’intégrité des nœuds d’un cluster. Si un nœud n’est pas accessible sur le réseau, l’action de récupération est effectuée pour récupérer et mettre en ligne des applications et des services sur un autre nœud du cluster. Les paramètres par défaut sont optimisés pour les défaillances où une perte complète d’un serveur est considérée comme une défaillance « difficile ». Il s’agit de scénarios d’échec irrécupérables, tels que l’échec d’un matériel ou d’une alimentation nonredondants. Dans ces situations, le serveur est perdu et l’objectif est que le clustering de basculement détecte rapidement la perte du serveur et récupère rapidement sur un autre serveur du cluster. Pour effectuer cette récupération rapide à partir d’échecs durs, les paramètres par défaut pour la surveillance de l’intégrité du cluster sont assez agressifs. Toutefois, ils sont entièrement configurables pour permettre une flexibilité pour différents scénarios.

Ces paramètres par défaut offrent le meilleur comportement pour la plupart des clients ; Toutefois, étant donné que les clusters sont étendus de pouces à des kilomètres à l’écart, le cluster peut devenir exposé à plus, et potentiellement non fiable, composants réseau entre les nœuds. Un autre facteur est que la qualité des serveurs de produits de base augmente constamment, couplée à la résilience augmentée par le biais de composants redondants (tels que l’alimentation double, l’association de cartes réseau et les E/S multi-chemins), le nombre de défaillances matérielles non redondantes peut potentiellement être assez rare. Étant donné que les défaillances matérielles peuvent être moins fréquentes, certains clients peuvent souhaiter paramétrer le cluster pour les défaillances temporaires, où le cluster est plus résilient aux brefs échecs réseau entre les nœuds. En augmentant les seuils d’échec par défaut, vous pouvez réduire la sensibilité aux problèmes réseau brefs qui durent une courte période.

Il est important de comprendre qu’il n’y a pas de réponse appropriée ici, et que le paramètre optimisé peut varier en fonction de vos besoins métier spécifiques et des contrats de niveau de service.

Virtualisation de SQL Server

Dans les environnements virtuels, pour des raisons de performances, il est recommandé de stocker la base de données opérationnelle et la base de données de l’entrepôt de données sur un stockage attaché direct, et non sur un disque virtuel. Vous pouvez utiliser l’utilitaire Operations Manager Sizing Helper publié pour Operations Manager 2012 pour estimer les E/S par seconde requises et tester les contraintes de vos disques de données pour vérifier. Les performances du stockage peuvent être testées avec l’utilitaire DiskSpd. Consultez également la prise en charge de la virtualisation d’Operations Manager pour obtenir des conseils supplémentaires sur l’environnement Operations Manager virtualisé.

Modèle De récupération et Always On

Bien qu’elle ne soit pas strictement une optimisation, une considération importante concernant le groupe de disponibilité Always On est le fait que, par conception, cette fonctionnalité exige que les bases de données soient définies dans le modèle de récupération « Complet ». Cela signifie que les journaux des transactions ne sont jamais ignorés tant qu’une sauvegarde complète n’est pas effectuée ou uniquement dans le journal des transactions. Pour cette raison, une stratégie de sauvegarde n’est pas facultative, mais une partie requise de la conception AlwaysOn pour les bases de données Operations Manager. Sinon, avec le temps, les disques contenant les journaux de transactions se remplissent.

Une stratégie de sauvegarde doit tenir compte des détails de votre environnement. Une planification de sauvegarde classique est donnée dans le tableau suivant.

Type de sauvegarde Planification
Journal des transactions uniquement Toutes les heures
COMPLET Hebdomadaire, dimanche à 3h00

Optimisation de SQL Server Reporting Services

L’instance de Reporting Services agit comme proxy pour l’accès aux données dans la base de données de l’entrepôt de données. Il génère et affiche des rapports basés sur des modèles stockés dans les packs d’administration.

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

En arrière-plan de Reporting Services, il existe une instance de base de données SQL Server qui héberge les bases de données ReportServer et ReportServerTempDB. Les recommandations générales relatives au réglage des performances de cette instance s’appliquent.

Remarque

À partir de SQL Server Reporting Services (SSRS) 2017 version 14.0.600.1274 et ultérieures, les paramètres de sécurité par défaut n’autorisent pas les chargements d’extensions de ressources. Cela conduit à des exceptions ResourceFileFormatNotAllowedException dans Operations Manager pendant le déploiement de composants de création de rapports.

Pour résoudre ce problème :

  1. Ouvrez SQL Management Studio.
  2. Connectez-vous à votre instance Reporting Services.
  3. Cliquez avec le bouton droit sur l’instance de serveur dans la fenêtre Explorateur d’objets.
  4. Sélectionnez Propriétés.
  5. Sélectionnez Avancé dans la barre latérale gauche.
  6. Ajoutez *.* à la liste pour AllowedResourceExtensionsForUpload.

Vous pouvez également ajouter la liste complète des extensions de création de rapports d’Operations Manager à la liste verte dans SSRS. La liste est décrite dans « Résolution 2 » ici : les rapports Operations Manager ne peuvent pas être déployés

Étapes suivantes

Pour comprendre comment configurer l’hébergement de l’entrepôt de données (Reporting) derrière un pare-feu, consultez Connecter l’entrepôt de données (Reporting) sur un pare-feu.