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.

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 

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...

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, REVOKE, DENY 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.

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