Dot.Blog

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

SQL Server 2008 et le type FileStream - résumé de la conférence DAT304 des TechEd 2007

Gérer des données "raw" tels que des fichiers multimédia, de la documation, etc, dans une base de données est un sujet qui divise les développeurs depuis longtemps. SQL Server 2008 va (enfin) mettre fin à cette dispute de principe !

DAT304 - Managing Unstructured Data in SQL Server 2008: Introducing the Filestream Datatype

Je n'ai suivi que partiellement cette conférence, il faudra d'ailleurs que je profite de la diffusion en ligne des vidéos pour les participants aux TechEd pour la regarder en totalité. Ce qui m'intéressait c'était l'info elle-même qui se résume à un nouveau type champ dans SQL Server. Mais c'est une avancée de taille, je vais vous expliquer pourquoi en quelques lignes...

Le duel blob vs file system pour les données raw

En effet, il y a d'une côté les tenants du "tout file system" c'est à dire le stockage des fichiers en dehors de la base de données avec juste le stockage des noms de fichiers dans la base elle-même. Pour: la simplicité, la gestion des flux du file sytem généralement plus performante que les blobs. Contre: le manque cruel de consistence, pas de contexte transactionnel, backups à faire séparément, etc, etc. Je fais partie des "anti" d'ailleurs.

De l'autre côté il y a ceux qui préfèrent le stockage en blob. Pour : consistence des données, contexte transactionnel, backup unique, etc. Je pour pour cette solution en général. Contre : les blobs sont moins rapide en lecture / écriture de flux que le file system, certains SGBD imposent des limites à la taille des blobs. Si on fait abstraction de ce dernier argument (il suffit d'utiliser une base n'ayant pas cette limite, par exemple SQL Server 2005 ou même Firebird/Interbase), le léger inconvénient de la rapidité (qui reste modeste et peu gênant dans la plupart des cas) est largement, à mon avis et par expérience, compensé par les avantages de cette technique. Reste qu'on peut faire mieux...

Mélanger le meilleur des deux solutions 

C'est justement ce que propose SQL Server 2008 avec le nouveau type FileStream qui est une extension de VARBINARY(MAX) qui s'en distingue par un attribut lors de la création du champ.

Le principe est simple : on marie le meilleur des deux solutions existantes. On prend la souplesse (gestion des quotas par ex) et la rapidité du file system (NTFS obligatoirement) et on l'associe à la cohérence des données de la solution blob. En gros, SQL Server 2008 stocke les fichiers dans le file system mais assure l'accès à ces fichiers comme à n'importe quel autre champ ce qui permet la gestion transactionnelle, le backup unique et centralisé.

Conclusion 

Une solution simple et performante à un problème de plus en plus crucial, les utilisateurs devant de plus en plus gérer des données lourdes (photos, documents digitalisés, vidéos...) en synchronisme parfait avec les bases de données. Une fichier client peut comporter une photo, une fiche article une vidéo de présentation, tout cela n'est plus "exotique", cela devient une contrainte légitime d'exploitation.

Pour l'instant SQL Server 2008 est en bêta, mais comme son nom l'indique il devrait être bientôt sur le marché...

Encore une bonne idée, ingénieuse et simple à mettre en oeuvre. Je trouve que les équipes de dev de MS ont vraiment l'âme créative depuis qu'on est entré dans ce que j'appelle "l'ére .NET". Souhaitons que ça dure le plus longtemps possible !

A+ pour un nouveau billet. Stay tuned !

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