Service Broker - Upgrade

Article
05/09/2016

Avec la fin du support de SQL Server 2005 (12 Avril 2016) un grand nombre d’instances chez les clients doivent être migrées.

https://www.microsoft.com/en-us/server-cloud/products/sql-server-2005/

Il existe plusieurs livres blancs et articles sur la migration de SQL Server mais peu d’entre eux évoquent le sujet tant redouté, … Service Broker !

SQL Server Service Broker (SSB) fournit la prise en charge native des applications de messagerie et de mise en file d'attente dans le Moteur de base de données. Construit autour des fonctions de base d'envoi et de réception de messages, il permet entre autres de traiter une file de traitement en asynchrone.

Exemples d’utilisation :

- Envoi de messages de traitement de paiements à une file d'attente qui sera traité durant la nuit lorsque le serveur aura une charge d’utilisation inférieure.
- Envoi de messages sur plusieurs serveurs / processus / Threads pour maximiser la façon dont votre file d'attente est traitée.
- …

Bref, l’utilité de Service Broker n’est plus à démontrer. Il est d’ailleurs utilisé dans de nombreux produits Microsoft comme SCCM, SharePoint, MDS, ...

Dans cet article, j’évoquerai pourquoi il est important de suivre le cycle de supportabilité des versions de SQL Server, je présenterai les nouveautés du service au fil des versions. Pour finir nous verrons les difficultés de la migration du service à travers un exemple concret : La migration d’une base de données SQL Server 2005 à SQL Server 2014 et reprise de la communication Service Broker à partir d’une base de données SQL Server 2016.

Remarque : Dans la suite de l’article j’utiliserai les abréviations suivantes.

- Serveur A : Serveur local avec SQL Server 2016 et la base de données « DatabaseA ».
- Serveur B : VM avec SQL Server 2005 et la base de données « DatabaseB ».
- Serveur C : VM avec SQL Server 2014 et la base de données « DatabaseB » une fois migrée.

1 - Actualisation

La fin de support se réfère à la date à laquelle Microsoft ne fournit plus de corrections, de mise à jour ou encore d’assistances techniques en ligne. Il est donc plus que risqué de ne pas tenir compte de la supportabilité des versions.

Microsoft Support Life Cycle de SQL Server :

La politique d’actualisation de SQL Server a été mi-à jour récemment, il est dorénavant fortement conseillé d’installer les CUs au même titre que les SP : Announcing updates to the SQL Server Incremental Servicing Model (ISM).

Il existe deux stratégies principales pour la mise à niveau :

- Mise à niveau sur place (In Place) : Utilisez le programme d'installation de SQL Server 2014 pour mettre à jour directement une instance de SQL Server 2005, 2008, 2008 R2, ou 2012. L'ancienne instance de SQL Server est remplacée.
- Mise à niveau sur une nouvelle instance (Side-by-side) : Effectuez les étapes pour déplacer tout ou partie des données à partir d'une instance vers une autre. L’instance peut être sur le même serveur ou sur un serveur distant.

Remarque : Je ne présenterai pas les inconvénients, avantages des deux modes d’upgrade dans cet article.

2 – Service Broker : Présentation

SQL Server Service Broker (SSB) a été introduit avec SQL Server 2005, et permet d’envoyer et de recevoir des messages afin de déporter leurs traitements dans une application tierce ou sur une autre base de données d’une même instance ou d’une instance distante.

Un Service Broker se compose des quatre objets suivants :

1. Le type de message : En anglais « Message types », définit le type de données que contient un message (Empty, None, Valid_XML, Well_Formed_XML).

 CREATE MESSAGE TYPE SenderMessageType VALIDATION=NONE
CREATE Message TYPE ReceiverMessageType VALIDATION=NONE

2. Un contrat : En anglais « Contracts », definit les types de messages d’un service Service Broker. C’est un accord entre deux services Service Broker (Un contrat par base de données qui participe à une conversation).

 CREATE Contract SampleContract (
SenderMessageType SENT BY INITIATOR,
ReceiverMessageType SENT BY TARGET
)

3. Une queue : En anglais « Queue », est une file d’attente pour les messages reçus. Lorsque Service Broker reçoit un message, il l’insère dans la file d’attente associée au service cible après l’avoir validé.

 CREATE Queue InitiatorQueue WITH status = ON

4. Un programme de service : En anglais « Service program », traite les messages entrants à partir d’une file d’attente.

 CREATE Service SenderService ON QUEUE InitiatorQueue (SampleContract)

5. Une route, un itinéraire : En anglais « Route », spécifie le réseau sur lequel l'adresse d'un service Service Broker est située.

 CREATE Route RouteA WITH
SERVICE_NAME = 'ReceiverService',
-- BROKER_INSTANCE = 'F6186F80-7DDA-4A7D-B08A-9931A02379D4',
ADDRESS = 'TCP://WIN-ADINKEGG0ER:4022'
GO

Dans la Route ci-dessus, ReceiverService est le service cible du serveur B. Si on ne spécifie par de « BROKER_INSTANCE » alors le service avec un nom similaire sera choisi au hasard par le serveur. Il est donc préférable de spécifier le « service_broker_guid » de la base de données cible (Depuis le serveur B SELECT service_broker_guid FROM sys.databases WHERE name = 'DatabaseA').

Voici les différentes étapes d’un dialogue :

De l'initiation d'un dialogue a la réception en passant par les ou les accusés de réception...

3 – Service Broker : Sécurité

Service Broker fournit deux types de sécurité, la compréhension de ces deux types de sécurité et la façon dont ils travaillent ensemble est primordiale pour concevoir, déployer et administrer des applications Service Broker :

• Transport security : Etablit une connexion réseau authentifiée entre deux instances SQL Server.
• Dialog security : Etablit une communication encryptée entre deux services Service Broker.

Pour mettre en place la sûreté du transport, depuis la base de données « Master » :

 USE master
GO

-- 1. Create a master key for master database.
CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'Romain.Casteres_PulsWeb.fr'
GO

-- 2. Create certificate and End Point that support certificate based authentication 
CREATE Certificate EndPointCertificateA
WITH Subject = 'A.Server.Local',
    START_DATE = '01/01/2006',
    EXPIRY_DATE = '01/01/2018'
    ACTIVE FOR BEGIN_DIALOG = ON;
GO

-- 3. Take a backup of the certificate created and install it into the remote instance by physically copying this certificate to Other Server.
BACKUP CERTIFICATE EndPointCertificateA TO FILE = 'C:\temp\EndPointCertificateA.cer';
GO

-- 4. Create certificate from the certificate backup file copied from the other server.
CREATE CERTIFICATE EndPointCertificateB From FILE = 'C:\temp\EndPointCertificateB.cer';
GO

-- 5. Create login from the certificate in remote server in the current server.
CREATE LOGIN sbLogin FROM CERTIFICATE EndPointCertificateB;
GO

-- 6. Grant the login, connect permissions on the end point.
GRANT CONNECT ON ENDPOINT::ServiceBrokerEndPoint To sbLogin
GO

Pour mettre en place la sûreté du dialogue, depuis la base de données « DatabaseA » :

 Use DatabaseA
GO

-- 1. Create a master key for master DatabaseA.
CREATE Master Key Encryption BY Password = 'Romain.Casteres@PulsWeb.fr'
GO

-- 2. Create a user certificate. 
CREATE Certificate UserCertificateA
 WITH Subject = 'A.Server.Local',
    START_DATE = '01/01/2006',
    EXPIRY_DATE = '01/01/2018'
    ACTIVE FOR BEGIN_DIALOG = ON;
GO

-- 3. Take a backup of the user certificate created and install it into the remote instance.
BACKUP CERTIFICATE UserCertificateA TO FILE='C:\temp\UserCertificateA.cer';
GO

-- 4. Create a user with the same name as the user who has access rights on the other Database.
CREATE User UserB WITHOUT LOGIN
GO

-- 5. Create a user certificate from the user certificate backup file copied from the other server, with authorization to the user created in Step 4.
CREATE CERTIFICATE UserCertificateB 
 AUTHORIZATION UserB
 FROM FILE = 'C:\temp\UserCertificateB.cer';
GO

-- 6. Grant connect permissions to the user.
GRANT CONNECT TO UserB;

-- 7. Grant send permissions to the user on the local service.
GRANT SEND ON SERVICE::SenderService To UserB;
GO

-- 8)Create a Remote Service Binding with the user created.
CREATE REMOTE SERVICE BINDING ServiceBindingB TO SERVICE 'ReceiverService' WITH USER = UserB

Remarques : Si communication entre deux bases de données d'une même instance la sécurité du transport n'est pas nécessaire.

3 – Service Broker : Nouveautés

Voici les nouveautés du Service Broker par versions de SQL Server :

- SQL Server 2008 :
o GUI
o Priorities Service Broker messages and diagnostic tool
o Allow disabling poison message handling but this can result in an infinite loop of failures and it is not recommended
- SQL Server 2012 :
o Service Broker Statistics Reports
o Messages can be sent to multiple target services (multicast) : The syntax of the SEND (Transact-SQL) statement has been extended to enable multicast by supporting multiple conversation handles.
o Queues expose the message enqueued time : Queues have a new column, message_enqueue_time, that shows how long a message has been in the queue.
o Poison message handling can be disabled : The CREATE QUEUE (Transact-SQL) and ALTER QUEUE (Transact-SQL) statements now have the ability to enable or disable poison message handling by adding the clause, POISON_MESSAGE_HANDLING (STATUS = ON | OFF). The catalog view sys.service_queues now has the column is_poison_message_handling_enabled to indicate whether poison message is enabled or disabled.
o AlwaysOn support in Service Broker : For more information, see Service Broker with AlwaysOn Availability Groups (SQL Server).
- SQL Server 2014 :
o More detailed support in SQL Server Management Studio (SSMS)
o Windows System Monitor objects and counters
o Tutorials to help you get started with Service Broker
o More flexible activation options
o SQL Server 2014 no longer supports the Itanium-based architecture.

4 – Service Broker : Upgrade Démo

Voici le scénario de la démonstration : La base de données « DatabaseB » du serveur B situé dans l’instance SQL Server 2005 doit être migrée sur une autre instance SQL Server 2014 du serveur C. Après la migration, le serveur A doit pouvoir communiquer avec la base de données restaurée.

Définitions des différentes étapes :

- Initialisation.
- Backup Database "DatabaseB" depuis l’instance SQL Server 2005 et restauration sur l’instance SQL Server 2014.
- Configuration de la sécurité (Transport security et Dialog security).
- Test de la communication.

4.1 - Initialisation

Étapes pour créer une application Service Broker Distribué :

1. Création des bases du Service Broker (messages types, contracts, services, queues).

2. Création du Transport Security:

a. Création de la Master Key de la base de données Master
b. Création du certificat et de l’End Point prennent en charge l'authentification basée sur les certificats.
c. Backup du certificat créé et installation de celui ci sur l’instance distante.
d. Création d’un certificat à partir du backup copié de l’autre instance.
e. Création d’un Login à partir du certificat créé à l’étape précédente
f. Grant des permissions Connect sur le End Point
Certaines étapes sont à effectuer sur les deux serveurs.

3. Set up Dialog Security:

a. Création d’une Master Key sur la base donnée utilisateur (celle utilisée par votre application)
b. Création d’un certificat utilisateur
c. Création d’un Backup du certificate utilisateur et installation de celui-ci sur l’instance distante.
d. Créez un utilisateur avec le même nom que l'utilisateur qui a des droits d'accès sur l'autre base de données.
e. Create a user with the same name as the user who has access rights on the other Database.
f. Créer un certificat à partir du fichier de sauvegarde de certificat utilisateur copié sur l'autre serveur. (Cela permet l'autorisation de l'utilisateur créé à l'étape précédente)
g. Grant Connect autorisations à l'utilisateur.
h. Grant Send des autorisations à l'utilisateur sur le service local
i. Créer un service distant avec l'utilisateur créé.

Certaines étapes sont à effectuer sur les deux serveurs.

4. Send Messages & Receive Messages

Voici les deux scripts T-SQL pour initier l’architecture : Lien.

4.2 – Backup & Restore

Après avoir finalisé toutes les discussions en cours, effectuer un backup de la base de données DatabaseB :

 SELECT * FROM sys.transmission_queue
SELECT * FROM sys.conversation_endpoints

ALTER DATABASE DatabaseB SET DISABLE_BROKER WITH ROLLBACK IMMEDIATE;
  
BACKUP DATABASE [DatabaseB] TO  DISK = N'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Backup\DatabaseB.bak' WITH NOFORMAT, NOINIT,  NAME = N'DatabaseB-Full Database Backup', SKIP, NOREWIND, NOUNLOAD,  STATS = 10

Sur l’instance cible, le serveur C sous SQL Server 2014 :

 USE [master]
GO

RESTORE DATABASE [DatabaseB] FROM  DISK = N'C:\Program Files\Microsoft SQL Server\MSSQL12.MSSQLSERVER\MSSQL\Backup\DatabaseB.bak' WITH  FILE = 2,  MOVE N'DatabaseB' TO N'C:\Program Files\Microsoft SQL Server\MSSQL12.MSSQLSERVER\MSSQL\DATA\DatabaseB.mdf',  MOVE N'DatabaseB_log' TO N'C:\Program Files\Microsoft SQL Server\MSSQL12.MSSQLSERVER\MSSQL\DATA\DatabaseB_log.LDF',  NOUNLOAD,  STATS = 5

Remarques :

- La configuration du Service Broker dans la base de données « DatabaseB » est conservée après sa restauration, pas besoin de recréer les Messages Types, les Contracts, les Queues, les Services, les Routes ou encore le Bindings.
- Le Service Broker Guid de la base de données n’a pas changé.

 SELECT service_broker_guid FROM sys.databases WHERE name = 'DatabaseB'

4.3 - Transportation Security

Pour reconfigurer le Transportation Security, il faut :

- Sur l’instance A :

o Actualiser la Route
o Importer un certificat de la nouvelle instance
o Re-Créer le Login à partir du certificat
o Autoriser celui sur le Endpoint

 ALTER DATABASE DatabaseA SET DISABLE_BROKER WITH ROLLBACK IMMEDIATE;
ALTER ROUTE RouteA WITH ADDRESS = 'TCP://192.168.0.39:4022';
ALTER DATABASE DatabaseA SET ENABLE_BROKER WITH ROLLBACK IMMEDIATE;

USE [master];
GO

DROP LOGIN sbLogin
DROP CERTIFICATE EndPointCertificateB

CREATE CERTIFICATE EndPointCertificateB From FILE = 'C:\temp\EndPointCertificateB.cer';
GO

CREATE LOGIN sbLogin FROM CERTIFICATE EndPointCertificateB;
GO

GRANT CONNECT ON ENDPOINT::ServiceBrokerEndPoint To sbLogin
GO

- Sur l’instance B :

o Créer un certificat et le Backuper
o Créer le Endpoint
o Restaurer le certificat de l’instance A
o Créer le Login à partir du certificat
o Autoriser celui sur le Endpoint

 USE [master];
GO

Create Certificate EndPointCertificateB
WITH Subject = 'B.Server.Local',
START_DATE = '01/01/2006',
EXPIRY_DATE = '01/01/2018'
ACTIVE FOR BEGIN_DIALOG = ON;
GO

CREATE ENDPOINT ServiceBrokerEndPoint
STATE=STARTED
AS TCP (LISTENER_PORT = 4022)
FOR SERVICE_BROKER (
AUTHENTICATION = CERTIFICATE EndPointCertificateB,
ENCRYPTION =DISABLED
);

BACKUP CERTIFICATE EndPointCertificateB TO FILE = 'C:\Temp\EndPointCertificateB.cer'

CREATE CERTIFICATE EndPointCertificateA FROM FILE = 'C:\Temp\EndPointCertificateA.cer';
GO

CREATE LOGIN sbLogin FROM CERTIFICATE EndPointCertificateA;
GO

GRANT CONNECT ON ENDPOINT::ServiceBrokerEndPoint To sbLogin
GO

SELECT * FROM sys.certificates;
SELECT * FROM sys.services;
SELECT * FROM sys.endpoints;

Remarque : Pour une restauration de la base de données sur une même instance, la Transportation Security n’est pas à actualiser. Seul le Dialog Security est à reconfigurer.

4.4 - Dialog Security

Lors de la restauration d’une base de données le service broker de celle-ci est désactivé. Pour le réactiver :

 ALTER DATABASE DatabaseB SET ENABLE_BROKER WITH ROLLBACK IMMEDIATE;

Pour réencrypter les certificats avec la Master Key de la nouvelle instance :

 USE DatabaseB
GO

OPEN MASTER KEY DECRYPTION BY PASSWORD = '45Gme*3^&fwu';
ALTER MASTER KEY ADD ENCRYPTION BY SERVICE MASTER KEY;
CLOSE MASTER KEY;

4.5 - Test

Voici un script de test permettant d’envoyer un message et de le recevoir.

Depuis l’instance A :

 -- Send Messages
DECLARE @InitDlgHandle UNIQUEIDENTIFIER;
DECLARE @RequestMsg NVARCHAR(100);
BEGIN TRANSACTION;
BEGIN DIALOG @InitDlgHandle FROM SERVICE SenderService TO SERVICE 'ReceiverService' ON CONTRACT SampleContract WITH ENCRYPTION = OFF;
SELECT @RequestMsg = N'Message for Target service.';
SEND ON CONVERSATION @InitDlgHandle MESSAGE TYPE SenderMessageType (@RequestMsg);
SELECT @RequestMsg AS SentRequestMsg;
COMMIT TRANSACTION;
GO

-- Receive Response
DECLARE @RecvReplyMsg NVARCHAR(100);
DECLARE @RecvReplyDlgHandle UNIQUEIDENTIFIER;
BEGIN TRANSACTION;
WAITFOR
( RECEIVE TOP(1)
    @RecvReplyDlgHandle = conversation_handle,
    @RecvReplyMsg = message_body
  FROM InitiatorQueue
), TIMEOUT 1000;
END CONVERSATION @RecvReplyDlgHandle;
SELECT @RecvReplyMsg AS ReceivedReplyMsg;
COMMIT TRANSACTION;
GO

SELECT * FROM sys.transmission_queue
SELECT * FROM sys.conversation_endpoints

Depuis l’instance B :

 -- Send Response
DECLARE @RecvReqDlgHandle UNIQUEIDENTIFIER;
DECLARE @RecvReqMsg NVARCHAR(max);
DECLARE @RecvReqMsgName sysname;
BEGIN TRANSACTION;
Print 'Started Receiving ';
WAITFOR
( RECEIVE TOP(1)
    @RecvReqDlgHandle = conversation_handle,
    @RecvReqMsg = message_body,
    @RecvReqMsgName = message_type_name
  FROM TargetQueue
), TIMEOUT 1000;
SELECT @RecvReqMsg AS ReceivedRequestMsg;
IF @RecvReqMsgName = 'SenderMessageType'
BEGIN
     DECLARE @ReplyMsg NVARCHAR(100);
     SELECT @ReplyMsg = N'Message is received';
     SEND ON CONVERSATION @RecvReqDlgHandle MESSAGE TYPE ReceiverMessageType (@ReplyMsg);
     END CONVERSATION @RecvReqDlgHandle;
END
SELECT @ReplyMsg AS SentReplyMsg;
COMMIT TRANSACTION;
GO

SELECT * FROM sys.transmission_queue
SELECT * FROM sys.conversation_endpoints

5 – Troobleshooting

Et parce que rien n’est toujours aussi simple que dans un article… Voici quelques outils pour le Troubleshooting :

- SQL Server Profiler : Il permet d’analyser un certain nombre d’événements (Catégorie d'événements Broker : https://technet.microsoft.com/fr-fr/library/ms186347(v=sql.120).aspx)

- Quelques DMV SQL Server : sys.dm_broker_activated_tasks, sys.dm_broker_connections, sys.dm_broker_forwarded_messages, sys.dm_broker_queue_monitors, …

- Compteur de performances : Service Broker a plusieurs compteurs de performance que vous pouvez capturer pour surveiller la santé et la performance de vos applications. Pour plus de détails : https://msdn.microsoft.com/en-us/library/ms190382.aspx

- Utilitaire ssbdiagnose : Disponible depuis SQL Server 2008, il permet de résoudre des problèmes dans la conversation ou dans la configuration entre deux services Service Broker : https://msdn.microsoft.com/en-us/library/bb934450.aspx

Exemple, depuis une fenêtre de commande DOS :

 ssbdiagnose -E CONFIGURATION FROM SERVICE SenderService -S ROMAINCA -d DatabaseA TO SERVICE ReceiverService -S 192.168.0.39 -d ECHO ON CONTRACT SampleContract

- Les dernières erreurs de SQL Server :

 EXEC xp_ReadErrorLog 0, 1, NULL, NULL, NULL, NULL, 'DESC'

- Redemarrage de l’Endpoint :

 USE master
GO
ALTER ENDPOINT ServiceBrokerEndPoint STATE = STOPPED;
GO
ALTER ENDPOINT ServiceBrokerEndPoint STATE = STARTED;
GO

- Script T-SQL de suppression de toutes les conversations : [A UTILISER AVEC PRECAUTION !]

 declare @conversation uniqueidentifier
while exists (select 1 from sys.transmission_queue )
begin
   set @conversation = (select top 1 conversation_handle from sys.transmission_queue )
 end conversation @conversation with cleanup
end

declare @conversationHandle uniqueidentifier
select top 1 @conversationHandle = conversation_handle from sys.conversation_endpoints
while @@rowcount = 1
begin
     end conversation @conversationHandle with cleanup
     select top 1 @conversationHandle = conversation_handle from sys.conversation_endpoints
end

6 – Conclusion

En conclusion, Service Broker permet de créer une couche asynchrone pour vos applications et/ou permet de déporter le traitement de données dans une autre application. La migration du service peut s’avérer compliquée, mais elle ne doit pas être un frein à l’évolution de version de vos plateformes. En respectant certaines étapes listées dans cet article, vos migrations « devraient » bien se passer !

Voici quelques liens et ressources complémentaires :

- An Introduction to SQL Server Service Broker: https://msdn.microsoft.com/en-us/library/ms345108.aspx
- Le livre le plus complet sur Service Broker “Pro SQL Server 2008 Service Broker” ecrit par by Klaus Aschenbrenner
- Building Reliable, Asynchronous Database Applications Using Service Broker: https://msdn.microsoft.com/en-us/library/ms345113.aspx
- SQL Server Service Broker : https://msdn.microsoft.com/library/bb522893.aspx
- SQL Server 2014 Upgrade technical guide
- Supported Version and Edition Upgrades : https://msdn.microsoft.com/en-us/library/ms143393(v=sql.120).aspx

- How to troubleshoot Service Broker problems : https://www.sqlteam.com/article/how-to-troubleshoot-service-broker-problems
- Service Broker: Performance and Scalability Techniques : https://msdn.microsoft.com/en-us/library/dd576261.aspx
- SQL Server: Service Broker Tips and Tricks : https://myadventuresincoding.wordpress.com/2007/11/22/sql-server-service-broker-tips-and-tricks/
- Understanding SQL Server Service Broker Authentication : https://www.databasejournal.com/features/mssql/understanding-sql-server-service-broker-authentication.html
- Using multiple routes in Service Broker : https://blogs.msdn.microsoft.com/sql_service_broker/2008/07/14/using-multiple-routes-in-service-broker/
- Blog de Dave Wentzel : https://www.davewentzel.com/taxonomy/term/343
- Script de Troubleshooting Service Broker (SSB) : https://www.sqlservercentral.com/scripts/Maintenance+and+Management/31867/
- SQL Server Service Broker Team Blog : https://blogs.msdn.microsoft.com/sql_service_broker/
[/list]

Je tiens à remercier Nicolas Soukoff (PFE SQL Server), Olivier Aupetit (Support Escalade Eng.) et Sarah Bessard (DBA chez DCube) pour leurs aides sur le sujet. Merci à Érik Delepeleire (PFE SQL Server) pour la relecture de cet article.

Romain Casteres

Premier Field Engineer – SQL & BI & Big Data