L’objet de Service Broker, intégré à partir de SQL Server 2005, est de fournir un outil de messagerie de base de données permettant de gérer des flux de données entre serveurs SQL de manière asynchrone, sérialisé et transactionnés.
L’utilisation de Service Broker est assez vaste et repose sur le principe des bases de données distribuées…
Au niveau politique, le but de Service Broker est de replacer le PC au centre de l’entreprise. Les serveurs ayant tendance à grossir tellement que le mot PC (Personal Computer) devient obsolète et les serveurs d’aujourd’hui ressemble de plus en plus aux mainframes d’antan.
Au niveau technique, Service Broker est constitué d’un ensemble d’outils basé sur un principe de Service HTTP et de files d’attente de message. On aura compris que Service Broker permet de véhiculer des informations entres serveurs SQL quelque soit la distance (sur la couche http d’Internet).
Quelques exemples :
- Informatique départementalisée : un serveur de base de données par services (RH, comptabilité, commercial, production…) communiquant leurs informations communes via Service Broker.
- Répartition de charge, lissage de traitements : une batterie de serveurs SQL consomment des messages et les traitent.
- Fiabilisation de système : chaque machine de production (chaîne de fabrication de vaccins humains) est doté d’un serveur SQL qui empile ses message et les envois à un serveur central pour consolidation. En cas d’indisponibilité du serveur cible, comme en cas de panne de réseau, le système continu à produire en toute indépendance.
L’aspect asynchrone se traduit par le fait que le message est placé en file d’attente, ce qui permet au programme de continuer son travail sans attendre la réponse et évite ainsi les goulets d’étranglement. En fait le message sera traité par Service Broker qui assure un véritable service de messagerie, avec la sécurité en plus !
Voyons comment cela fonctionne avec un simple petit exemple que vous pouvez reproduire chez vous…
/********************************************************************
* exemple basique du "service broker" (base de données distribuées) *
* Frédéric Brouard - SQLpro - http://www.sqlspot.com - 2009-02-12 *
* *
* un échange de message au sein de la même base pour montrer *
* l'aspect transactionnel et asynchrone de la chose *
********************************************************************/
/*******************************************************
-- ETAPE n°0 : création de notre base d'essais
*******************************************************/
USE master;
GO
IF EXISTS (SELECT *
FROM sys.databases
WHERE name = 'TEST_SB_1')
DROP DATABASE TEST_SB_1;
GO
CREATE DATABASE TEST_SB_1;
GO
USE TEST_SB_1;
GO
/*******************************************************
-- ETAPE n°1 : mise en place des objets de travail
*******************************************************/
-- création des files d'attentes
CREATE QUEUE Q_1;
CREATE QUEUE Q_2;
-- création des services et initiation de la conversation
CREATE SERVICE SRV_1 ON QUEUE Q_1 ([DEFAULT]);
CREATE SERVICE SRV_2 ON QUEUE Q_2 ([DEFAULT]);
-- quelques options :
-- AUTHORIZATION owner_name :: permet de créer le service pour un autre user SQL
-- ( contract_name | [DEFAULT] [ ,...n ] ) :: permet de définir des contrats associés au service
/*********************************************
-- ETAPE n°2 : expédition
*********************************************/
-- envoie d'un message identifié par un GUID
DECLARE @UID UNIQUEIDENTIFIER;
BEGIN DIALOG @UID
FROM SERVICE SRV_1 TO SERVICE 'SRV_2';
SEND ON CONVERSATION @UID ('Bonjour monde');
-- message lancé
-- interrogation de la file d'attente côté reception (en fait une table d'un type particulier)
SELECT *
FROM Q_2;
-- ? Q_2 est vide... pourquoi ?
-- constatons que Q_1 est aussi vide
SELECT * FROM Q_1;
-- ou le message est-il bloqué ?
SELECT *
FROM sys.transmission_queue;
-- le message est là , mais ...
SELECT transmission_status
FROM sys.transmission_queue;
--=> Par défaut on ne peut pas transmettre en clair ! Il faut crypter
-- création d'une clef de cryptage pour la base
CREATE MASTER KEY
ENCRYPTION BY PASSWORD = 'P@ssw0rd!';
-- envoi d'un 2eme message
DECLARE @UID UNIQUEIDENTIFIER;
BEGIN DIALOG @UID
FROM SERVICE SRV_1 TO SERVICE 'SRV_2';
-- quelques options :
-- LIFETIME = <durée en sec> par défaut 2 milliards de secondes (en fait maxint, soit 68 ans)
-- WITH ENCRYPTION = { ON | OFF } par défaut ON
SEND ON CONVERSATION @UID ('Bonjour monde 2');
-- 2eme message envoyé
-- pour mettre fin à la conversation
-- END CONVERSATION @UID
-- est-il dans la file ?
SELECT *
FROM Q_2;
/*********************************************
* ETAPE n°3 : du côté du destinataire
*********************************************/
-- voyons le message
DECLARE @MSG VARBINARY(MAX);
DECLARE @HDL UNIQUEIDENTIFIER;
-- lecture du message
SELECT TOP(1) @MSG = message_body, @HDL = conversation_handle
FROM Q_2;
-- utilisation du message
PRINT 'Le message transmis est : ' + CAST (@MSG AS VARCHAR(MAX));
-- le message est toujours dans la file. Il n'a pas été "dépilé"
SELECT *
FROM Q_2;
/*********************************************
=> ETAPE n°4 : acquittement
*********************************************/
-- consommons le message
DECLARE @HDL UNIQUEIDENTIFIER;
-- lecture desctructrice
RECEIVE TOP(1) @HDL = conversation_handle
FROM Q_2;
--=> Receive : nouvel ordre Transact SQL comparable au SELECT, mais : LECTURE UNIQUE (DESTRUCTRICE) !
-- en fait dépile le message de la pile consititué par la table Q_2 ou le place dans un statut "lu",
-- en fonction des paramètres de rétention définit lors de la création de la queue
-- le message n'est plus dans la file. Il a été "consommé"
SELECT *
FROM Q_2;
-- envoi de l'acquittement
SEND ON CONVERSATION @HDL ('OK : Bien reçu !');
-- arrêt de la conversation côté destinataire
END CONVERSATION @HDL;
-- Combien de messages dans Q1 ?
SELECT *
FROM Q_1;
--=> le message envoyé en retour et le message indiquant la fin du dialogue :
-- message_type_name "EndDialog" (XML schéma du Service Broker MS)
/*********************************************
* ETAPE n°5 : réception de l'acquittement
*********************************************/
-- lecture du message d'acquittement
DECLARE @MSG VARBINARY(max);
DECLARE @HDL UNIQUEIDENTIFIER;
RECEIVE TOP(1) @MSG = message_body, @HDL = conversation_handle
FROM Q_1;
-- le message est reçut et son "enveloppe" détruite
-- affichage du message d'acuittement côté expéditeur
PRINT 'Le message est : ' + CAST (@MSG AS VARCHAR (max));
-- fin de la conversation côté expéditeur
END CONVERSATION @HDL;
/*********************************************
* Le service broker est-il activé ?
*********************************************/
--voir l'état des bases pour
SELECT name, service_broker_guid, is_broker_enabled
FROM sys.databases;
-- modification du service broker pour la base
ALTER DATABASE TEST_SB_1
SET disable_broker;
-- pour réactiver :
ALTER DATABASE TEST_SB_1
SET enable_broker;
Il nous faudrait encore parler des CONTRACT, des ROUTE et de bien d’autres objets que l’on trouve dans cet outil, mais là c’est une autre affaire…
Quelques liens intéressant pour continuer vos investigations au sujet de Service Broker :
http://sqlserver.itpro.fr/Dossiers-par-Theme/2008/11/6/090013396-Service-Broker.htm
http://www.apsql.com/articles/ServiceBroker.html
http://www.sqlteam.com/article/centralized-asynchronous-auditing-with-service-broker
Pour les différents aspects de la sécurité, voir : http://support.microsoft.com/kb/915852 (merci à Mikaël M.).
Quelques ouvrages plus ou moins spécialisés :
Pro SQL Server 2005 Service Broker – Klaus Aschenbrenner – Apress 2007
The Rational Guide to SQL Server 2005 Service Broker – Roger Wolter – Rational Press 2006
SQL Server 2005 Practical Troubleshooting : The Database Engine – Ken Henderson – Addison-Wesley 2006
—
Frédéric BROUARD, Spécialiste modélisation, bases de données, optimisation, langage SQL.
Le site sur le langage SQL et les S.G.B.D. relationnels : http://sqlpro.developpez.com/
Expert SQL Server http://www.sqlspot.com : audit, optimisation, tuning, formation
* * * * * Enseignant au CNAM PACA et à l’ISEN à Toulon * * * * *