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

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

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

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.

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

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

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 :

La console PowerShell est lancée par l’outil sqlps. Il est alors possible d’exécuter directement les scripts PowerShell. L’applet Get-Help permet, comme toujours, de trouver toutes les informations relatives aux applets de commandes. Pour SQL Server, il est possible de mettre en avant Invoke-Sqlcmd qui correspond à l’utilitaire sqlcmd en PowerShell.

Exemple

La gestion des règles

SQL Server propose la notion de gestion par les règles. Ce type de gestion permet de définir des règles d’administration et de les appliquer à une ou plusieurs instances SQL Server. En effet avec la multiplication des instances SQL Server, parfois sur un même poste, il devient nécessaire d’avoir un outil pour simplifier l’administration individuelle des différentes instances. Les règles peuvent également servir à administrer les bases de données utilisateur.

Les règles vont permettre de définir un comportement commun à toutes les bases, instances et serveurs SQL Server en rendant obligatoire ou non l’activation de certains services ou bien en forçant explicitement des règles de nommage par exemple. Les règles vont permettre, entre autres, d’adopter une structure similaire sur des bases distincts ce qui permet une compréhension plus facile des différentes bases et facilite ainsi l’administration car un socle commun est défini.

La gestion par les règles peut être décomposée en trois composants importants :

Le terme règle peut être précisé en utilisant la notion de stratégie...