Blog ENI : Toute la veille numérique !
💥 Un livre PAPIER acheté
= La version EN LIGNE offerte pendant 1 an !
Accès illimité 24h/24 à tous nos livres & vidéos ! 
Découvrez la Bibliothèque Numérique ENI. Cliquez ici
  1. Livres et vidéos
  2. SQL Server 2022
  3. Outils complémentaires
Extrait - SQL Server 2022 Apprendre à administrer un serveur de base de données
Extraits du livre
SQL Server 2022 Apprendre à administrer un serveur de base de données
1 avis
Revenir à la page d'achat du livre

Outils complémentaires

L’audit de l’activité de SQL Server

SQL Server dispose de possibilités de réalisation d’audit de son fonctionnement.

Cet audit peut être défini soit au niveau de l’instance SQL Server, soit au niveau d’une ou de plusieurs bases de données. L’audit va pouvoir suivre des événements et enregistrer la survenue de ces événements dans le journal d’audit. Ce journal d’audit peut être un fichier, le journal des événements de sécurité ou bien le journal des événements d’applications Windows. Quelle que soit la cible de l’audit, le fichier doit être sauvegardé de façon régulière afin de garantir l’espace nécessaire au journal pour enregistrer les événements.

Pour pouvoir réaliser, ou modifier, un audit, il faut être membre du rôle sysadmin. Il est également possible d’auditer chaque modification de l’audit.

Il est à noter que la mise en place d’un audit peut avoir un effet négatif sur les performances, car l’activation des compteurs d’audit consomme des ressources sur le serveur et il y a donc moins de ressources disponibles pour répondre aux demandes des utilisateurs. Donc, de manière générale et afin d’optimiser le fonctionnement du serveur, un audit ne doit être mis en place que si l’on cherche à résoudre un problème.

1. Définir un audit au niveau serveur

La mise en place de ce type d’audit passe tout d’abord par la création et la configuration d’un objet de type SQL Server Audit.

La création d’un objet de type SQL Server Audit est possible depuis le nœud Sécurité - Audit de l’explorateur d’objets. Il est bien sûr nécessaire...

La limitation des ressources utilisées par une requête

Le coût d’une requête correspond à la durée estimée (en secondes) pour l’exécution. L’option query governor cost limit permet de spécifier une limite supérieure pour l’exécution d’une requête.

Par défaut cette option reçoit la valeur 0, ce qui autorise l’exécution de toutes les requêtes. Si une valeur positive, différente de 0 est indiquée, alors l’administrateur de requête n’autorise pas l’exécution de toutes les requêtes dont le coût estimé d’exécution dépasse cette valeur.

Cette limitation est positionnable sur le serveur par l’intermédiaire de sp_configure ou bien sur chaque base de données avec SET QUERY_GOVERNOR_COST_LIMIT

Le paramétrage avec l’option SET n’est valable que pour la période actuelle d’activité de l’instance. Ce paramètre ne sera pas conservé au prochain démarrage de l’instance. Pour conserver cette valeur, il est nécessaire de configurer l’option par sp_configure ou bien par les propriétés du serveur.

set query_governor_cost_limit 20 
 
-- ou 
 
sp_configure 'show advanced options',1 
reconfigure 
 
sp_configure 'query governor cost limit', 20 
reconfigure 

L’instruction RECONFIGURE permet de prendre en compte les nouvelles valeurs des options de configuration sans avoir à redémarrer le serveur.

Depuis SQL Server Management Studio, cette option est paramétrable à partir de la fenêtre des propriétés du serveur, sur la page Connexions en activant la case à cocher Utiliser l’Administrateur de requêtes pour empêcher...

Le magasin de requêtes

Les plans d’exécution évoluent en fonction de nombreux critères, et pour une même requête, il peut y avoir plusieurs plans d’exécution possibles. L’une des difficultés est de sélectionner le plan d’exécution le mieux adapté.

SQL Server propose le magasin de requêtes. Comme son nom l’indique, au sein de chaque base de données, SQL Server se propose de stocker les requêtes et les plans d’exécution associés.

Afin de ne pas surcharger la base de données, cette fonctionnalité est désactivée par défaut, et l’espace maximum qui peut être utilisé par le magasin de requêtes est défini par le paramètre MAX_STORAGE_SIZE_MB. Il est possible de connaître la valeur de ce paramètre en interrogeant la vue sys.database_query_store_options. Sa taille par défaut est de 1 Go et cet espace de stockage est prélevé sur le groupe de fichiers Primary. 

Pour activer le magasin de requêtes, il faut exécuter l’instruction ALTER DATABASE pour basculer à ON la valeur du paramètre QUERY_STORE.

Alter database Gescom set query_store=on; 

C’est par l’intermédiaire de cette même commande ALTER DATABASE mais avec l’option SET QUERY_STORE CLEAR qu’il est possible de libérer l’espace occupé par le magasin de requêtes.

Il est également possible de passer par la page Magasin de requêtes depuis les Propriétés de la base de données, et de modifier la valeur Mode d’opération.

images/12RI06N.png

Le magasin de requêtes propose plusieurs rapports prédéfinis pour répondre aux demandes les plus courantes.

images/12RI07N.png

Le plan de maintenance

Pour les opérations classiques de l’administrateur comme des sauvegardes régulières de la base de données et des journaux de transaction, ou bien les opérations de maintenance des index, il est possible de définir des plans de maintenance. L’exécution de ces différentes tâches va être planifiée lors de la définition du plan de maintenance.

Les plans de maintenance peuvent être définis à l’aide d’un assistant, mais la création manuelle d’un plan de maintenance offre une plus grande richesse de paramétrage à condition d’avoir un minimum de connaissances sur SSIS (SQL Server Integration Service).

Les plans de maintenance sont définis sous forme de package SSIS et c’est bien entendu SQL Server Agent qui se charge d’exécuter le travail qui lance ce package.

La définition d’un nouveau plan de maintenance peut aisément être définie en faisant appel à l’assistant de définition d’un nouveau plan. Cet assistant peut être exécuté depuis le menu contextuel associé au nœud Gestion - Plans de maintenance depuis l’explorateur d’objets.

images/12RI08N.png
images/12RI09N.png
images/12RI10N.png
images/12RI11N.png

À la fin de l’assistant, une tâche planifiée contenant une étape de type package SSIS sera créée au niveau de l’agent SQL Server.

Les déclencheurs DDL

SQL Server propose les déclencheurs de type DDL. Ces déclencheurs (trigger) de base de données fonctionnent comme les déclencheurs associés à une action INSERT,UPDATE ou DELETE qui peut se produire sur une table donnée. L’exécution du déclencheur DDL est associée à l’exécution d’une instruction CREATE, ALTER, DROP, GRANT, REVOKEDENY et UPDATE STATISTICS.

Les déclencheurs DDL sont exécutés après l’instruction DDL à laquelle ils sont associés. Les déclencheurs DDL permettent de suivre les modifications apportées au schéma ou bien d’interdire certaines modifications qui peuvent être apportées au schéma.

Pour interdire une instruction DDL dans le déclencheur associé, il faut annuler la transaction grâce à l’instruction ROLLBACK.

Lors de la définition d’un déclencheur DDL, il faut préciser l’instruction DDL qui permet son exécution, ainsi que sa portée, c’est-à-dire l’endroit où il va être actif. La portée peut correspondre à une base de données particulière ou bien correspondre à la totalité de l’instance.

Pour permettre de suivre au mieux les différentes exécutions des instructions DDL, il est possible d’utiliser la fonction EVENTDATA dans les déclencheurs DDL. Cette fonction permet de capturer les informations relatives à l’exécution du déclencheur. Cette fonction retourne un flux XML. Il est possible d’extraire des informations de ce flux XML, en utilisant une requête XQuery.

Il n’est pas possible de définir des déclencheurs DDL de type instead of.

Les informations relatives à l’exécution...

PowerShell

Ce shell, introduit avec Windows Server 2008, permet de définir de puissants scripts d’administration. Cette version de PowerShell vient s’enrichir d’outils spécifiques à chaque application serveur éditée par Microsoft.

Ce shell permet d’exécuter des instructions de façon directe ou bien sous forme de script.

SQL Server n’échappe pas à la règle et apporte sa liste de commandes PowerShell. Les apports de SQL Server au PowerShell sont :

  • L’intégration d’un lecteur (SQLSERVER...), ceci afin de pouvoir naviguer dans l’arborescence du serveur aussi simplement qu’il est possible de le faire dans un système de fichiers, c’est-à-dire principalement avec les instructions cd et dir.

  • L’ajout de cmdlets afin de pouvoir intégrer et exécuter une action SQL, notamment des scripts Transact SQL.

Il existe deux modules PowerShell pour SQL Server :

  • SQLPS : module utilisé par toutes les tâches de type PowerShell de l’Agent SQL Server. Ce module est ancien et n’est plus mis à jour.

  • SQLSERVER : module reprenant les cmdlets de sqlps et à utiliser dans tous les scripts à exécuter en dehors de SSMS. Ce module doit auparavant être installé à l’aide de la cmdlet install-module.

Une cmdlet de ces modules intéressante à connaître est invoke-sqlcmd. Elle correspond à l’utilitaire sqlcmd mais en PowerShell.

Le développement de scripts en PowerShell ne fait pas parti de cet ouvrage, il convient donc de se former à ce langage avant de pouvoir l’utiliser avec SQL Server.