Tour d'horizon de la gestion basée sur les stratégies avec SQL Server 2008

L'implémentation des bonnes pratiques sur les serveurs de bases de données est devenue un sujet courant au sein d'une entreprise. Jusqu'à présent, un ensemble de scripts développé par un ou plusieurs administrateurs de bases de données était nécessaire pour vérifier si toutes les instances SQL Server de l'entreprise étaient conformes à l'ensemble des préconisations définies. La gestion basée sur les stratégies facilite le travail des administrateurs par une implémentation et une évaluation plus faciles des préconisations d'entreprise.

La gestion basée sur les stratégies est une nouvelle fonctionnalité proposée avec SQL Server 2008 qui permet de gérer plusieurs instances SQL Server en fonction des stratégies définies par l'entreprise. Toute l'administration de ce système se fait essentiellement par l'outil de gestion client SQL Server Management Studio.

La gestion basée sur les stratégies est composée de cinq concepts différents :

• La cible : représente une entité gérée par la gestion basée sur la stratégie. Une entité peut être un serveur, une base de données, une table, un index, etc. ;
• Les facettes : ce concept est de prime abord abstrait. Une facette est un ensemble logique de propriétés qui détermine le comportement d'une cible. Prenons l'exemple d'un serveur de bases de données. Vous pouvez décider de créer une stratégie par rapport aux performances de ce serveur ou en fonction de ses paramètres de configuration ou encore la gestion de sa sécurité. Les facettes peuvent donc être « Performances du serveur », « paramètres du serveur » et « Sécurité du serveur ». Chaque facette possède un ensemble de paramètres qu'il est possible de contrôler. Il existe un nombre prédéfini de facettes, mais il n'est pas possible d'en ajouter pour le moment ;
• Les conditions : représentent un ensemble d'états booléens des propriétés liées à une facette pour une cible donnée. Reprenons l'exemple du serveur de bases de données. Une des propriétés de la facette Performances du serveur est l'option de configuration MaxDegreeOfParallelism. Nous pouvons déterminer une condition de valeur égale à 1 pour cette propriété, ce qui signifie que nous définissons qu'un serveur de base de données ne doit pas pouvoir générer de plans parallèles par défaut. L'exécution de la stratégie correspondante vérifiera si cette option est correctement paramétrée sur le serveur cible. Dans le cas contraire, une erreur de stratégie sera générée ;
• Les stratégies : elles permettent de définir les conditions et comportements attendus d'une stratégie par exemple le mode d'évaluation, les filtres de cibles ou une planification. Une stratégie ne peut gérer qu'une seule condition à la fois ;
• Les catégories : celles-ci ont deux rôles principaux : tout d'abord elles permettent de gérer plus efficacement les stratégies par un classement défini par l'utilisateur comme par exemple « stratégie serveur », « stratégie base de données » ou encore « stratégie de sauvegarde ». Elles permettent également de pouvoir des définir des abonnements aux stratégies au niveau serveur et au niveau bases de données. Une stratégie placée dans une catégorie dont l'étendue est au niveau serveur peut s'exécuter sur l'ensemble des bases de données tandis qu'une stratégie placée dans une catégorie dont l'étendue est au niveau base de données peut permettre de restreindre l'étendue de cette stratégie aux bases de données abonnées. L'abonnement aux catégories est restreint au propriétaire d'une base de données.

Chaque stratégie peut être évaluée de différentes manières :

• À la demande : dans ce mode une stratégie doit être exécutée manuellement ;
• Selon planification : il est possible de planifier l'exécution d'une stratégie via l'agent SQL Server. Les violations de règles de stratégie sont enregistrées dans le journal des événements Windows ;
• Sur modification : Journal uniquement : la notification d'événement est utilisée dans ce mode. Chaque violation de stratégie est journalisée ;
• Sur modification : Empêcher : ce mode s'appuie sur les triggers DDL empêchant toute violation de stratégie.

Voyons maintenant comment implémenter une stratégie. Pour cela, nous prendrons trois exemples concrets. Ces exemples reflètent une certaine réalité qu'il est possible de rencontrer dans n'importe quelle entreprise. Les stratégies à implémenter doivent être en concordance avec les exigences suivantes :

• les options Autoclose et Autoshrink doivent être activées pour toutes les bases de données en ligne ;
• toute table utilisateur doit posséder une clé primaire ;
• toute procédure stockée doit commencer par psu_

La gestion basée sur les stratégies est accessible via SQL Server Studio Management sous le nœud gestion. On retrouve l'ensemble des concepts présentés au début de l'article sous le nœud Policy Management comme les stratégies (Policies), les conditions (Conditions) et les facettes (Facets).

La gestion des catégories se fait par le menu suivant :

La catégorie « Default » existe par défaut. Celle-ci est associée à l'ensemble des bases de données du serveur (option Mandate Database cochée). Nous reviendrons sur cette notion un peu plus loin.

Voyons maintenant comment implémenter les stratégies relatives aux trois exigences décrites précédemment.

--> Les options Autoclose et Autoshrink doivent être activées pour toutes les bases de données en ligne.

Pour créer la stratégie relative à ce premier exemple, il faut, dans un premier temps, choisir une des facettes associées aux bases de données. Il suffit pour cela de choisir parmi celles proposées par défaut :

Il existe plusieurs facettes associées aux bases de données. Nous choisirons la facette Database Options pour nos besoins. Parmi les propriétés suivantes, AutoClose et AutoShrink nous intéressent plus particulièrement.

Dans un deuxième temps, il faut implémenter la condition qui sera liée à notre stratégie. Pour cela, il faut se rendre sous le nœud condition :

Pour créer une condition, certaines règles doivent être respectées :

• il faut nommer explicitement la condition ;
• il faut choisir la facette sur laquelle les conditions seront appliquées ;
• il faut déterminer l'expression à valider en choisissant les propriétés de la facette et appliquer les valeurs à respecter.

Une fois que la condition est créée, celle-ci apparaît sous le nœud « Conditions » :

Nous devons également créer une deuxième condition pour satisfaire entièrement aux exigences du premier exemple. Pour cela, nous utiliserons la facette Database et la propriété Status :

Une fois nos deux conditions créées, nous pouvons mettre en place la stratégie associée :

Comme nous l'avons expliqué au début de l'article, une stratégie se fait uniquement pour une seule et unique condition. Dans le cas présent, elle correspond à la première condition créée qui concerne les options de bases de données. De plus, une stratégie s'applique à une cible qui est un objet base de données. Une condition existe également pour cette cible : la stratégie ne sera évaluée que pour les bases de données en ligne. Enfin il nous reste à choisir le mode d'évaluation : à la demande (On demand) dans notre cas.

La stratégie Base de données option best pratices étant créée, elle apparaît sous le nœud stratégies :

Rappel : une stratégie est directement associée au groupe default. Celle-ci s'applique donc à l'ensemble des bases de données du serveur.

--> Toute table utilisateur doit posséder une clé primaire

Nous avons vu les actions nécessaires à la création d'une condition. Nous nous attarderons seulement sur les autres actions intéressantes. Pour ce deuxième exemple, nous aurons besoin d'utiliser une expression particulière pour pouvoir vérifier qu'une table possède une clé primaire. Celle-ci sera composée d'une requête TSQL impliquant des objets systèmes comme sys.objects et sys.indexes. Cette requête sera utilisée à son tour avec les fonctions ExecuteSql() et IsNull(). Le résultat final se présentera sous la forme de deux états (1 : la table possède une clé primaire et 0 : la table ne possède pas de clé primaire).

Pour créer l'expression particulière, il faut créer sur le bouton … de la colonne Field :

… et insérer l'expression suivante (cette expression permet de vérifier qu'une table possède une clé primaire) :

Celle-ci devra être utilisée avec la facette table. Le fait d'utiliser cette facette permettra de fixer l'étendue d'évaluation aux objets de type table pour chaque base de données concernée :

Nous voulons également que l'évaluation de la stratégie s'effectue uniquement pour les objets utilisateurs. Pour cela, une deuxième condition devra être créée et permettra de filtrer les tables utilisateurs :

Enfin la stratégie sera créée avec un mode d'évaluation à la demande :

--> Toute procédure stockée doit commencer par « psu_ »

La condition qui sera créée utilisera la facette MultiPartName. Cette facette a la particularité de pouvoir être utilisée avec un mode d'évaluation annulant toute action si celle-ci ne respecte pas les conditions fixées par une stratégie : On change : Prevent. La condition définit que tout objet créé doit être préfixé par « PRD_ ».

La stratégie associée ciblera uniquement les objets de type procédure stockée pour l'ensemble des bases de données. Le mode d'évaluation est ici paramétré à On change : prevent.

Tout comme les travaux SQL Server ou les plans de maintenance, il est possible de planifier l'exécution d'une stratégie. Pour cela, il suffit de se rendre dans les propriétés d'une stratégie et de choisir le mode d'évaluation Selon planification :

Le choix du mode d'évaluation Selon planification fait apparaître un nouveau champ qui permet d'entrer une planification prédéfinie ou personnalisée.

Nous choisirons une planification personnalisée en cliquant sur le bouton New. Le paramétrage d'une planification reste classique.

Un travail planifié est créé et associé à la stratégie :

Une fois que le travail planifié est exécuté, il est possible de visualiser les violations des règles de l'évaluation concerné dans le journal d'événements Windows de la manière suivante :

Nous avons utilisé deux modes d'évaluation lors de la création de nos trois stratégies qui sont : À la demande et On change : prevent. Nous commencerons par évaluer les stratégies utilisant le mode d'évaluation A la demande :

--> Stratégie : Base de données options best pratices

Le résultat de l'évaluation est le suivant :

L'évaluation de la stratégie sur l'ensemble des bases de données du serveur montre qu'une des cibles (base de données test) n'est pas conforme aux conditions définies dans cette même stratégie. Pour connaître les conditions non respectées, il suffit de cliquer sur le lien correspondant :

Le détail de la nouvelle fenêtre fait apparaître que l'option auto shrink est activée sur cette base de données.

Lorsque cela est possible, la gestion basée sur les stratégies permet de corriger à la demande les conditions non respectées. Le bouton Apply est activé lorsqu'une éventuelle correction d'erreur est possible :

Une fois la modification effectuée, la cible devient conforme :

La visualisation de la fenêtre du détail de l'évaluation montre effectivement que la propriété @AutoShrink est égale à False.

--> Stratégie : toute table utilisateur doit posséder une clé primaire

Le scénario d'évaluation reste le même que précédemment. Cependant nous allons voir ici les avantages d'utilisation des catégories lors de l'évaluation d'une stratégie, qui permettent d'une part une classification précise des stratégies et, d'autre part, le ciblage des stratégies sur une ou plusieurs bases de données.

Nous allons créer la catégorie Table :

Il faut entrer le nom de la catégorie :

L'option Mandate Database est ici décochée, ce qui signifie que pour une stratégie appartenant à la catégorie Table, il faudra abonner explicitement une base de données pour qu'elle soit évaluée.

Nous associerons ensuite la stratégie Table design best pratices à la catégorie Table.

Dans le menu Description, il faut choisir la catégorie correspondante :

Maintenant, il faut abonner une base de données présente sur le serveur pour que celle-ci puisse être évaluée par notre stratégie. Dans le cadre de l'article, j'ai choisi une base de données nommée test présente sur mon instance par défaut :

L'option Subscribed doit être cochée pour abonner la base de données test à la catégorie Table.

Ceci étant fait, nous pouvons dès à présent évaluer la stratégie Table design best pratices sur la base de données test :

La liste des stratégies apparaît et nous devons choisir la stratégie adéquate :

Le résultat de l'évaluation est le suivant :

On constate que l'évaluation concerne uniquement la base de données test et que certaines tables ne satisfont pas aux conditions de notre stratégie. Cependant il ne sera pas possible de résoudre automatiquement le problème dans ce cas. L'administrateur devra implémenter lui-même les corrections.

--> Stratégie : toute procédure stockée doit commencer par « psu_ »

Pour évaluer cette stratégie, il faut créer une procédure stockée que nous nommerons proc_test :

USE test;
GO
 
CREATE PROCEDURE proc_test
AS
 
SELECT * FROM dbo.test;

La création de cette procédure stockée donne lieu à l'erreur suivante :

Msg 3609, Level 16, State 1, Procedure sp_syspolicy_dispatch_event, Line 65
The transaction ended in the trigger. The batch has been aborted.

La stratégie a annulé la création de la procédure stockée, car celle-ci ne respectait pas les conditions de nommage imposées par celle-ci.

La définition des stratégies est stockée dans la base de données msdb. Cependant il est possible de l'exporter dans un fichier de type XML. Cela permet de sauver la définition d'une stratégie sur le système de fichiers d'une part et de l'importer sur un autre serveur d'autre part. Nous verrons également que nous pouvons nous servir de ce fichier XML comme modèle d'évaluation des stratégies sur d'autres serveurs.

Le menu Export Policy d'une stratégie existante permet d'exporter sa définition dans un fichier XML :

L'écran d'import d'une stratégie est le suivant :

Il faut choisir le fichier XML à importer en précisant son chemin. Lors de l'import de la stratégie, il est également possible de décider de son état. On peut décider de préserver l'état de la stratégie définie dans ce fichier. On peut également choisir d'activer ou de désactiver cette stratégie. On laissera l'état par défaut pour notre exemple (Préservation de l'état). Enfin il est possible de remplacer une stratégie si celle existe déjà.

SQL Server fournit plusieurs DMV relatives à la gestion basée sur les stratégies situées dans la base de données msdb. Les principales sont les suivantes :

• syspolicy_management_facets ;
• syspolicy_configuration ;
• syspolicy_conditions ;
• syspolicy_policies ;
• syspolicy_policy_categories ;
• syspolicy_policy_execution_history ;
• syspolicy_policy_execution_history_details ;
• syspolicy_policy_category_subscriptions ;

… avec quelques exemples d'application :

-- Visualisation de la configuration de la gestion basée sur les stratégies
SELECT *
FROM syspolicy_configuration;
 
-- Visualisation des différents paramètres de la gestion basée sur les stratégies
SELECT 
    p.name AS [policy name],
    CASE p.execution_mode 
        WHEN 0 THEN 'On demand'
        WHEN 1 THEN 'On change : prevent'
        WHEN 2 THEN 'On change : log only'
        ELSE 'On schedule'
    END AS execution_mode,
    p.is_enabled,
    c.name AS [condition name],
    c.facet AS [facet used],
    c.expression,
    ca.name AS [category name]
FROM syspolicy_policies AS p
INNER JOIN syspolicy_conditions AS c
ON p.condition_id = c.condition_id
LEFT JOIN syspolicy_policy_categories AS ca
ON ca.policy_category_id = p.policy_category_id;
 
-- Historique d'exécution des stratégies
SELECT 
    p.name,
    CASE p.execution_mode 
        WHEN 0 THEN 'On demand'
        WHEN 1 THEN 'On change : prevent'
        WHEN 2 THEN 'On change : log only'
        ELSE 'On schedule'
    END AS execution_mode,
    h.result,
    h.exception,
    h.exception_message,
    hd.execution_date,
    hd.exception AS [exception details],
    hd.exception_message AS [exception message details],
    hd.target_query_expression,
    hd.result,
    CAST(hd.result_detail AS XML) AS result_detail
FROM syspolicy_policies AS p
INNER JOIN syspolicy_policy_execution_history AS h
ON p.policy_id = h.policy_id
INNER JOIN syspolicy_policy_execution_history_details AS hd
ON h.history_id = hd.history_id
ORDER BY hd.execution_date DESC;
 
-- Visualisation des abonnements de base aux catégories
SELECT 
    c.name,
    s.target_object,
    s.target_type,
    c.mandate_database_subscriptions
FROM syspolicy_policy_category_subscriptions AS s
INNER JOIN syspolicy_policy_categories AS c
ON s.policy_category_id = c.policy_category_id;
 
-- Visualisation des possibilités d'utilisation
-- des modes d'évaluation associés aux facettes
SELECT 
    *
FROM
(
    SELECT 
    facet.name as 'Facet Name', 
    CASE facet.execution_mode & 4 
      WHEN 4 THEN 'X' 
    END AS 'On Demand', 
    CASE facet.execution_mode & 2 
      WHEN 2 THEN 'X' 
    END AS 'OnChangeLogOnly', 
    CASE facet.execution_mode & 1 
      WHEN 1 THEN 'X' 
    END AS 'OnChangePrevent' 
    FROM syspolicy_management_facets facet
) AS T;

Jusqu'à présent, nous avons vu qu'il était possible d'évaluer des stratégies sur une instance cible, mais il serait plus intéressant pour une entreprise de l'étendre à l'ensemble de ses instances SQL Server. La gestion centralisée des serveurs permet de réaliser cela.

IX-A. La gestion centralisée de serveurs▲

La gestion centralisée de serveurs permet la gestion simultanée d'un ensemble d'instances SQL Server. Il est possible, entre autres, d'exécuter une requête ou d'évaluer une stratégie sur plusieurs instances de serveurs à la fois, à la condition que ces instances possèdent au moins une version 2000.

Pour utiliser cette fonctionnalité, il faut tout d'abord inscrire un serveur qui jouera le rôle de serveur de gestion centralisée sur lequel sera stockée la liste d'instances à gérer. Cette liste peut contenir un ou plusieurs groupes de serveurs. Ces groupes permettent d'étendre l'exécution d'une action à l'ensemble des instances appartenant à ce groupe. Le serveur de gestion centralisée doit être obligatoirement une instance SQL Server 2008 et celui-ci peut également faire partie de la liste des instances à évaluer. Il est important de noter que l'authentification intégrée est le seul protocole pris en charge avec la gestion centralisée de serveurs.

Dans le cadre de l'article, j'utiliserai une instance par défaut et deux instances nommées, ce qui est suffisant pour présenter l'ensemble des concepts liés à la gestion centralisée des serveurs et des stratégies. La démarche peut être évidemment étendue à un contexte d'entreprise où cohabiteraient plusieurs serveurs et instances SQL Server avec des versions différentes.

Commençons par inscrire l'instance par défaut en tant que serveur de gestion centralisée. L'accès à la console de gestion centralisée se fait par la vue d'affichage Serveurs inscrits.

La fenêtre d'inscription demande le nom du serveur ainsi que la méthode d'authentification :

Nous allons créer ensuite un groupe de serveurs nommé test :

Nous devons entrer un nom de groupe et éventuellement une description :

Le groupe apparaît sous le serveur de gestion centralisée :

Nous allons maintenant inscrire la première instance nommée dans le groupe TEST :

Il faut entrer le nom de l'instance nommée. Remarquez que seule l'authentification Windows est possible.

De la même manière, nous inscrirons la deuxième instance INSTANCE3.

IX-B. Évaluer une stratégie pour l'ensemble des serveurs d'un groupe▲

Voyons maintenant comment évaluer une stratégie à l'ensemble des serveurs du groupe TEST :

Il faut d'abord choisir la source sur laquelle se trouve la stratégie à évaluer. Cette source peut être un fichier XML exporté ou bien un serveur sur lequel sont stockées des stratégies.

Nous choisirons le serveur de gestion des stratégies qui est également notre instance par défaut :

Nous évaluerons la stratégie Base de données option best pratices sur l'ensemble des serveurs du groupe TEST :

Le résultat d'évaluation de la stratégie est le suivant :

On constate qu'il existe une violation des règles de la stratégie pour une base de données sur l'instance nommée INSTANCE2. Le détail de l'erreur est le suivant :

L'option « Auto Shrink » de la base de données TEST_INSTANCE2 est activée. De la même manière que pour une instance locale, il est possible de corriger cette violation de règle sur une instance distante à condition que le compte de connexion utilisé possède les droits adéquats.

La gestion centralisée de serveurs associée à la gestion basée sur les stratégies présente un réel avantage lorsqu'il s'agit d'étendre une politique d'évaluation des préconisations ou de bonnes pratiques à l'ensemble des instances SQL Server d'une entreprise. Cependant aucun processus d'historisation concernant l'évaluation des stratégies d'un ensemble d'instances n'existe à l'origine ce qui empêche toute forme de Reporting. Heureusement il existe plusieurs méthodes d'exportation des résultats d'évaluation permettant d'ouvrir la voie à d'éventuelles solutions d'importation et d'extraction de données.

X-A. Exportation manuelle des résultats▲

Une première solution consiste à exporter manuellement les résultats de l'évaluation d'une stratégie via la console d'administration sur un groupe de serveurs dans un fichier XML. Il faudra cependant développer une solution complémentaire d'importation et d'exploitation de ces fichiers.

Voici un extrait du contenu du fichier XML :

X-B. Utilisation de PowerShell▲

sl "SQLSERVER:\SQLPolicy\WIN-4SPCKAEGSXA\DEFAULT\Policies"
Get-Item "Base de données options best pratices" | Invoke-PolicyEvaluation -TargetServerName "192.168.0.15\INSTANCE2 " 
-OutputXML > c:\database_options_policy_instance2_evaluation.xml

X-C. Entreprise Policy Management Framework (EPM)▲

Les choses étant bien faites, Microsoft nous a livré une solution de Reporting d'état des évaluations de stratégie en automatisant leur évaluation sur des instances SQL Server 2000 à 2008 et en centralisant l'historique dans une base de données dédiée.

Ce Framework utilise les composants suivants :

• une instance jouant le rôle de serveur de gestion centralisée ;
• une instance de stockage des stratégies ;
• une instance exécutant des scripts power Shell ;
• une instance gérant une base de données d'historisation des résultats des stratégies ;
• une instance Reporting Services permettant de gérer les rapports de gestion des stratégies.

Note : Il est nécessaire d'installer le cumulatif de mise à jour 3 du service pack 1 de SQL Server 2008 pour fixer un bug concernant des problèmes de compatibilité descendante survenant lors de l'exécution des évaluations des stratégies.

Une configuration possible d'architecture utilisant ce Framework :

Un extrait des possibilités de Reporting du framework EPM :

Encore une fois, SQL Server 2008 offre des fonctionnalités d'administrations supplémentaires très intéressantes. La gestion basée sur les stratégies associée à la gestion centralisée des serveurs permet de répondre à deux axes très importants en entreprise : la standardisation et industrialisation des processus. Une fonctionnalité à implémenter sans modération !!!

• MSDN : Gestion basée sur les stratégies
• Framework EPM

• MCTS Self-Paced Training Kit (70-432)

Je remercie ma fiancée et Fadace pour le temps qu'ils ont passé pour lire cet article.