Dot.Blog

C#, XAML, Xamarin, UWP/Android/iOS

Pister les modifications de schéma (SQL Server)

Maintenir une base de données est toujours délicat notamment en raison des modifications de schéma parfois nécessaires durant le développement d'une application ou sa maintenance.

De même il peut s'avérer utile de pister, chez un client/utilisateur, toute modification du schéma qui pourrait destabiliser l'application (tout en rendant celle-ci coupable des problèmes éventuels, donc engageant votre responsabilité...).

Reconstruire l'historique des modifications de schéma pour ensuite les centraliser dans un script de mise à jour de la base de données peut aussi être un plus très appréciable.

Mais comment pister et conserver ces modifications et où les stocker de façon sûres ?

Avec SQL Server la réponse tient en un simple trigger et en l'utilisation habile du type de donnée XML !

Vous l'avez compris le suivi des modifications sera stocké dans la base elle-même, pratique et fiable cela évite la présence de fichiers annexes risquant d'être perdus.

Etape 1 : Créer la table pour stocker les modifications

CREATE TABLE [Audit].[ModifSchema](

[EventID] [int] IDENTITY(1,1) NOT NULL,
[EventData] [xml] NULL,

PRIMARY KEY CLUSTERED
(
   [EventID]
ASC
) WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]

Etape 2 : Créer le trigger

CREATE TRIGGER [Trig_AuditModifSchema]
ON
DATABASE
FOR
DDL_DATABASE_LEVEL_EVENTS
AS
INSERT
INTO Audit.ModifSchema(EventData
)
SELECT EVENTDATA
()
GO
ENABLE TRIGGER [Trig_AuditModifSchema] ON DATABASE

Etape 3 : il n'y a pas d'étape 3 c'est FINI !

Désormais, lors de toute modification du schéma de la base de donnée un événement sera enregistré dans la table et il sera possible de consulter, sous la forme d'une entrée XML facilement lisible et analysable par tout outil ou portion de code, l'ensemble des modifications. Voici un exemple : 

<EVENT_INSTANCE>
    <
EventType>ALTER_TABLE</EventType
>
    <
PostTime>2007-06-03T20:12:05.813</PostTime
>
    <
SPID>55</SPID
>
    <
ServerName>CHI100906</ServerName
>
    <
LoginName>MYDOMAIN\myusername</LoginName
>
    <
UserName>dbo</UserName
>
    <
DatabaseName>Sales</DatabaseName
>
    <
SchemaName>dbo</SchemaName
>
    <
ObjectName>Products</ObjectName
>
    <
ObjectType>TABLE</ObjectType
>
    <
TSQLCommand
>
        <
SetOptions ANSI_NULLS="ON" ANSI_NULL_DEFAULT="ON" ANSI_PADDING="ON"      QUOTED_IDENTIFIER="ON" ENCRYPTED="FALSE"
/>
        <
CommandText>
ALTER TABLE dbo.Products
    DROP COLUMN testremove
       </CommandText
>
  </
TSQLCommand
>
</
EVENT_INSTANCE>

A noter, la fonction EVENTDATA() est fournie par SQL Server à l'intérieur d'un trigger DLL et renvoie toutes les données sous la forme d'un document XML.

Cette idée astucieuse a été publiée par plusieurs auteurs sous différentes formes, je ne m'en attribue pas la paternité. On la retrouve par exemple sur le Richard's C# blog, un blog riche que je conseille pour ceux qui lisent l'anglais, of course.

 

kick it on DotNetKicks.com

Nouvelle rentrée, nouveau blog !

Bienvenue à toutes et à tous !

"Vous mé réconnéssé ? " Laughing

Pour ceux qui ne savent pas qui je suis, en deux mots : dirigeant de e-naxos, auteur de plusieurs ouvrages sur Delphi et C# chez Eyrolles, partenaire Borland de longue date (même si depuis le tournant Codegear nos voies se sont séparées), partenaire Microsoft. Ceux qui fréquentent ces milieux m'ont reconnu.

Ce nouveau blog, installé sur mon propre serveur, va me permettre de m'exprimer avec le maximum de liberté.

Non pas que le blog que je possède de longue date sur Developpez soit spécialement censuré, mais vous savez ce que c'est, il ne faut pas froisser untel, ou tel autre qui prend la mouche parce qu'on ne dit pas que du bien de tel produit ou éditeur ... Censure réelle ou auto-censure, le résultat est le même.

J'avais donc besoin d'un espace autogéré, j'ai trouve BlogEngine, un très beau moteur de blog open source en C#, et voilà comment est né Dot.Blog ! Blog avant tout technique, mes humeurs n'y seront pas donc forcément absentes mais Dot.Blog ne sera pas un blog d'opinion. 

C#, le framework .NET, Visual Studio, SQL Serveur, Silverlight, Ajax, Linq, sont autant de sujets qui me viennent à l'esprit et qui feront bientôt l'objet de billets ici.

Alors marquez cette page et revenez souvent y lire et y commenter les billets !

A très bientôt,

Olivier