Restrictions et règles SQL Server

Environnement: WINDOWS SQL Server 2014
Base de données: AdventureWorks2012

Introduction

En tant qu’administrateur de base de données vous allez être amené à mettre en place un certain nombre de règles et de restrictions sur votre serveur.

• interdiction de commencer une procédure stockes par « sp_ ».
• interdiction de mettre les fichiers DATA ET LOG sur le même volume.
• vérifier qu’une base de données possède l’option autoshrink et
  autoclose.
• vérifier que toutes tables utilisateurs possèdent une clé primaire.

Et bien d’autres règles.

Pour nous aider à mettre cela en place MICROSOFT a mis en place depuis la version 2008 de SQL Server, une gestion basée sur les règles. Celle-ci est accessible via SQL Server Management Studio sous le nœud Gestion/Management. On retrouve l’ensemble des concepts présentés ci-dessus sous le nœud « Policy Management » comme les règles (Policies), les conditions (Conditions) et les facettes (Facets).

Les facettes : objets sur lesquels vont porter des conditions.
Les conditions : tests qui portent sur une facette.
Les règles : conditions appliquées à des cibles (bases, serveurs…) avec ou sans planification.

Restrictions et règles SQL Server – Création d’une règle

Nous allons utiliser la facette procédure stockée pour y mettre une condition qui vérifiera que le nom ne commence pas par « sp_ ».

Ensuite nous créons une règle qui utilise notre condition. Elle sera évaluée à chaque création de procédure stockées et bloquée si besoin.

Essayons maintenant d’ajouter une procédure stockée qui ne respecte pas notre règle :

create procedure sp_test as
begin
   select getdate()
end

Nous avons l’erreur suivante :

Policy ‘VerifNameStoredProcedure’ has been violated by
‘SQLSERVER:\SQL\localhost\DEFAULT\Databases\AdventureWorks2012\StoredProcedures\dbo.sp_test’.
This transaction will be rolled back.
Policy condition: ‘@Name NOT LIKE ‘sp% »
Policy description:  »
Additional help:  » :  »
Statement: ‘create procedure sp_test as
begin
select getdate()
end ‘.
Msg 3609, Level 16, State 1, Procedure sp_syspolicy_dispatch_event, Line 65
The transaction ended in the trigger. The batch has been aborted.

Restrictions et règles SQL Server – Import d’une règle

Microsoft a mis en place quelques règles que nous pouvons importer.
Voici l’écran d’import de la règle qui vérifiera l’autoshrink de nos bases.

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

Conclusion

Grâce à la gestion basée sur les règles, nous allons pouvoir valider que notre serveur de bases de données suit les règles et « best practice » préconisé par Microsoft. De plus nous pourrons ajouter autant de règles que nous le déciderons et qui seront propre à notre métier.