Une des difficultées dans la gestion du base de données MSSQL est de controller l'espace de stockage et vérifier si une pénurie d'espace disque ou une limite dans la taille de fichier journal ou de données ne va pas entrainer le blocage des transactions.

Les difficultés résident dans plusieurs aspects :

• Une base de données peut avoir plusieurs FILEGROUP de données
• Chaque FILEGROUP peut avoir plusieurs fichiers
• Chaque fichier peut avoir sa propre gestion de stockage : autoextent, taille illimitée ou limitée avec des tailles potentiellement différentes
• Chaque base de données peut avoir plusieurs fichiers journaux (même si un seul est actif à un instant t)
• Les fichiers journaux ou de données peuvent être dispersées sur des volumes différents (disques, partitions, point de montage...) dont les tailles sont différentes.

Comment être alerté avant le blocage avec tous ces paramètres en jeux ?

Je vous propose d'étudier, pour répondre à cette question, les strategies de la base de données SQL Server. Nouveautés de SQL serveur 2008, ces strategies sont très puissantes pour controler des aspects des bases de données et des instances.

Les strategies  de base de données s'appuie sur plusieurs objets :

• Les facettes : ce sont des objets sur lesquels vont porter des conditions
• Les conditions : ce sont des tests qui portent sur une facette
• Les stratégies :  ce sont des conditions appliquées à des cibles (bases, serveurs...) avec ou sans plannification.

 

 

 

 

 

 

 

 

1) Les facettes:

Les facettes sont des objets natifs du SGBD, elles disposent de propriétés. Ces propriétés ont des valeurs qui pourront être testées dans les conditions. Les facettes sont des objets intégrés dans la base de données.

2) Les conditions:

Ce sont les objets les plus complexes dans la gestion par strategies. Ces conditions sont des tests effectués sur une ou plusieurs propriétés d'une facette. Le language de "programmation" dans les conditions est un peu rugueux voir pénible mais il permet de faire quelques opérations simples.

Cet exemple de script dans une condition fait appel au language WQL qui permet de réaliser des demandes au niveau de l'OS. Multiply(100, Divide(Multiply(@VolumeFreeSpace, 1024), ExecuteWql('Numeric', 'root\CIMV2', Concatenate('select Capacity from win32_Volume where drivetype=3 and freespace=', String(Multiply(@VolumeFreeSpace, 1024)))))) Ici on utilise une astuce afin de récupérer le pourcentage d'espace libre dans le volume. Cette astuce (un peu complexe j'en conviens) consiste à  demander la capacity totale du volume en passant en paramètre l'espace libre (@VolumeFreeSpace) en critère de requête. En effet sql server connait le volume d'espace libre lié à chacun de ces fichiers mais il n'y a pas de variable contenant la capacité totale du volume. A moins de vouloir mettre une alerte sur un seuil en Mo restant, la seule variable @VolumeFreeSpace n'est pas suffisant pour connaitre le pourcentage d'espace de stockage restant.

3) Les strategies:

Une strategie va évaluer la condition sur une cible. Cette cible peut, elles-même être définie par une condition permettant de restreindre la cible.
Il  faut ensuite évaluer la strategie et la il y a plusieurs moyens.

 

	La strategie s'ppuie sur une condition appliquée sur des cibles.   Le mode d'évaluation "à la demande" signifie que la stratégie n'est pas automatiquement lancée à horaire fixe mais doit faire l'office d'une demande pour être évaluer.
	La demande d'évaluation peut être faite directement via SSMS mais également par Powershell (cf plus loin).
	Le résultat d'une l'évaluation de la stratégie dans SSMS
	  Il est également possible d'avoir un historique des évaluations

4) Evaluation externe des strategies mssql

Le plus interressant reste d'évaluer la ou les strategies via powershell pour SQL : SQLPS.exe .

Dans le script suivant on liste toutes les strategies ayant la categorie "Availability" et ont les évaluent

1
2
3
4
5
6
7
8
9
10
11
12

# use with SQLPS.exe 
# usage sample 
# C:\"Program Files (x86)\Microsoft SQL Server\100\Tools\Binn"\SQLPS.exe -c D:\shells\ps\Evaluate_Availability_Policies.ps1"   
 
set-location SQLSERVER:\SQLPolicy\V2K3SQLDEV\DTX01\Policies   
 
#Control al the policies in the "Availability" Category 
 
ls | where-object {$_\3.PolicyCategory -eq "Availability"} | Invoke-PolicyEvaluation -TargetServer "V2K3SQLDEV\DTX01"   
 
#exit