SQL Server vu par Christian Robert

SQL Server : Query Notification ou comment être notifié de modifications de données côté application (SqlDependency)

Cette fonctionnalité à vue le jour dans Ado.Net 2.0 et s'appuie sur SQL Server 2005 (et plus) même si elle fonctionne avec SQL Server 2000. Le principe de fonctionnement côté applicatif est assez simple, on fournit une requête et lorsque le résultat de celle-ci change on souhaite être notifié par l'appel d'une fonction (callback).

Ce que vous connaissez peut être moins c'est comment fonctionne ce système. Il repose, sous SQL Server 2005, sur le Service Broker et un système de notification basé sur les vues indexées. Sur SQL Server 2000, c'est un système de pooling d'une table alimentée par des triggers.

Dans les 2 cas vous aurez 2 connexions ouvertes vers la base de données. La première sert à créer la notification et malheureusement nécessite des privilèges important (de création d'objet dans la base de données, et de certains privilèges serveurs sous SQL Server 2005). Le seconde sert à la notification et ne nécessite peu de privilèges, mais vous n'aurez pas de contrôle sur cette dernière sauf à utiliser une classe de plus bas niveau (voir plus loin).

Avant de continuer sur le sujet, voici les limites de ce système :

• Repose sur le système des vues indexées, il a donc les même restrictions : pas de SELECT *, non du schéma obligatoire, pas de LEFT ou de RIGHT JOIN, etc. La liste complète des restrictions : http://msdn.microsoft.com/en-us/library/aewzkxxh.aspx ou en français : http://msdn.microsoft.com/fr-fr/library/aewzkxxh.aspx
• Et a aussi les même implications que les vues indexées en termes de performance, je ne serais trop vous recommander de ne pas mettre 2000 notifications sur la même table… D'une manière générale le système monte « mal » en charge
• Nécessite des privilèges élevés pour les classes de plus haut niveau (SqCacheDependency et SqlDependency) au sein de la base de données et certains privilèges serveur.
• Ouvre une nouvelle connexion, chaque notification maintien donc une connexion vers le serveur jusqu'à ce qu'elle soit levée ou passe en TimeOut (Méthode Stop, voir plus loin dans l'exemple)
• Vous ne savez pas quel type d'opération génère la notification ni quelles sont les données modifiées, ce qui vous oblige à ré exécuter la requête pour avoir la totalité des nouvelles données (on en profite pour réamorcer la notification).
• Peut passer en timeout ce qui oblige à réamorcer la notification. Ce réamorçage est aussi nécessaire à chaque déclenchement de cette dernière, il faut le prévoir dans le code.

En allant plus loin, vous découvrirez que 3 classes sont à votre disposition (de celle de plus haut niveau à celle de plus bas niveau) :

• SqlCacheDependency
  • Celle-ci est en relation avec l'objet Cache de l'ASP.Net et permet de piloter le cache de sortie d'ASP.Net en fonction de la modification des données.
• SqlDependency
  • C'est la plus courante, on fournit juste la connexion, la commande SQL et la fonction de Callback. En option il est possible de fournir le nom de la file d'attente du Broker à monitorer.
• SqlNotificationRequest
  • C'est la plus complexe, mais aussi la plus flexible. On s'attend à ce que toute la mécanique du Service Broker soit déjà en place, seule la notification est créée. C'est aussi de votre responsabilité que de créer le thread de monitoring.

On passe maintenant aux exemples, mais avant toute chose assurez-vous d'avoir le service broker activé dans votre base de données ou sinon exécutez :

ALTER DATABASE MaBase SET NEW_BROKER

Exemple avec la classe SqlDepedency

SqlConnection cnx = new SqlConnection(connectionString);

// Démarre la connexion qui crée le Service Borker et sa file d'attente

SqlDependency.Start(connectionString);

cnx.Open();

SqlCommand cmd = new SqlCommand("SELECT ShipperID, CompanyName, Phone FROM dbo.Shippers", connection);

// Ici on défnie la dépendance sur la requête

SqlDependency dependency = new SqlDependency(cmd);

// Et la méthode de Callback

dependency.OnChange += new OnChangeEventHandler(OnDataChange);

// On execute la requête qui est le signal de démarrage de la surveillance de la requête

cmd.ExecuteReader();

cnx.Close();

Et le code de Callback :

// Le squelette du Callback, on est pas obligé de réaliser le nettoyage avec Stop ici

static void OnDataChange(object sender, SqlNotificationEventArgs e)

{

// ... on regarde entre autre (TimeOut, autre) ce qu'on a dans e.Info

// On détruit les objets du Broker

SqlDependency.Stop(connectionString);

}

Exemple avec la classe SqlNotificationRequest.

Je pars du principe que j'ai déjà un service existant côté base de données et une méthode qui a appel

// Création de la notification

SqlNotificationRequest not = new SqlNotificationRequest();

not.UserData = Guid.NewGuid().ToString();

not.Options = "Service=MonService";

not.Timeout = 100000;

// On l'associé à la requête

SqlCommand cmd = new SqlCommand("SELECT a FROM dbo.TabA", cnx);

cmd.Notification = not;

// Ouvre la connecion et execute la requête

cnx.Open();

cmd.ExecuteReader();

cnx.Close();

J'assume aussi le fait que vous ayez un thread séparé qui exécute la commande SQL suivante :

WAITFOR (RECEIVE * FROM MaFileDAttente);

Ma préférence va à la seconde solution car vous avez entièrement la main sur ce qui se passe côté base de données, ce qui est important en termes de permissions et pour d'autres éléments que je vous présenterais dans d'autres billets.

Un point important à garder en tête, ce système est particulièrement difficile à manipuler à cause des restrictions nombreuses, ne baissez pas les bras et lisez la liste des restrictions avec attention ! Plus la requête est simple, plus elle a de chances de passer.

Maintenant que l'on connait le fonctionnement interne, on peut imaginer des solutions tordues comme :

• Query Notification côté serveur uniquement avec SQLCLR à « Trigger asynchrone »
• Query Notification côté serveur pour notifier l'application
• Imitation du système via un trigger en bonus en lui fournissant les données !
• Fonctionnement conjoint du Query Notification avec d'autres fonctionnalités de 2008

Qui a dit que SQL Server n'était pas fun pour faire du développement .Net ?

Bonne notification…