Synchronizing data between SQL Server instances with Service Broker

By: Diana Moldovan | Updated: 2018-01-24 | Comments (1) | Related: > Service Broker

Problem

I'm thinking about using the Service Broker component for a SQL Server table synchronization project, but it seems that there are not many examples relevant and detailed enough for me to use. Can you describe a real-life case of using Service Broker to copy data between SQL Server instances?

Solution

The best way to become familiar with Service Broker (if you’re not already) is to get some hands-on experience with a simple use case. However, there are a few definitions and general facts you must know before you start working. Therefore, you should take the time and explore these tips:

This tip is based on a working implementation I’ve done a few months ago. My customer still runs on SQL 2008 R2, but I’ve re-done everything for you on a more recent edition.

I won’t do another overview of the Service Broker component. There are many good introduction articles and I’ve listed a few of them above. Instead I’ll describe in detail how I’ve implemented a simple table synchronization between two SQL Server instances.

My goal is to “replicate” the records inserted in a table on the “initiator” instance to an identical table on the “target” instance. There are fewer than 100 inserts each day in the source table and the tables don’t need to be tightly coupled. Therefore, I chose to implement a Service Broker solution.

Test Environment Specifications

I’ve tested this process in the past with SQL 2008 R2 and in this tip I will be using SQL Server 2014 SP2 instances:

Initiator

Windows 10 Enterprise version 1703 OS Build 15063.7026 (my dev physical machine)
SQL 2014 SP2 version 12.0.5207.0
Ports 1433 and 4023 are open
TCP/IP protocol is enabled

Target

Windows Server 2012 R2 Standard (virtual machine)
SQL 2014 SP2 version 12.0.5000.0
Ports 1433 and 4022 are open
TCP/IP protocol is enabled

On each instance I’ve created a user database which hosts the appropriate Service Broker objects. I need the records in the SBSource.dbo.SourceTable to be replicated to the SBTarget.dbo.TargetTable on the “target” instance. Please find the appropriate scripts SB_Source_user_db.sql and SB_Target_user_db.sql in the attached archive.

Setting up the Network Access

If the instances are installed on machines from the same domain or from trusted domains the network access between instances can be based on Windows authentication. Since my virtual machines were not part of any domain, I used certificate based authentication.

In the master database of each instance I created the elements that secure the access between instances:

Database master key which protects the private keys of certificates
Certificate I’ll use as the authentication option for the Service Broker endpoint
Service Broker endpoint
Login that will have the right to connect to the endpoint

Notice that each certificate is backed up to a file on disk and exchanged between instances.

You can find the script for both instances (SB_Source_master.sql and SB_Target_master.sql) in the attached archive.

Creating the Service Broker Specific Objects

SB_Source_objects.sql and SB_Target_objects.sql create the Service Broker specific objects in each database:

message type with a “well-formed xml” validation
contract for this message type
queue
service for the above queue and contract

In the “source” script I included a trigger (trgSendItemData) created for the table where the new data will be inserted:

INSERT INTO dbo.SourceTable(ID, RegDay, RegTime, Code)
VALUES(1, CAST(CURRENT_TIMESTAMP AS date), CAST(CURRENT_TIMESTAMP AS time), 'A123')

The trigger calls the dbo.SendItemData procedure (SendItemData.sql), which only sends the new data “on conversation“ to the target, i.e. stores a XML fragment in the initiator queue and moves it to the target queue. The new data is formatted as XML:

SET@item_data = (SELECT ID, RegDay, RegTime, Code FROM inserted AS t FOR XML AUTO, ELEMENTS);

This involves less work in the current transaction than another insert on the target side over the network.

Each script contains a “debug” section.

Processing the Message on the Target Side

Up to this point, if all worked well and you query the target queue you should be able to find the message you’ve already sent:

--the message is stored in binary format in the queue
SELECT CAST(message_body AS xml) FROM dbo.ItemQueue2014

The result should look like this:

<t>
  <ID>1</ID>
  <RegDay>2017-12-15</RegDay>
  <RegTime>14:38:40.3170000</RegTime>
  <Code>A123</Code>
</t>

However, the other half of the story is still missing. The goal of this implementation is to replicate the record inserted in the “source” table to the target table. The XML fragment will be “picked” from the target queue, parsed and the data will end up in the SBTarget.dbo.TargetTable. This work will be done by the target queue’s activation stored procedure. InsertDataAtTarget.sql is the activation stored procedure for the target queue – it will execute each time when a message is inserted in the queue.

An activation stored procedure can’t take any parameter. It only processes the content of the queue’s message_body column. If everything went well, this message is the XML fragment sent from the initiator instance. In our case the activation stored procedure parses this fragment and inserts the data in the target table.

Notice that this kind of procedure follows a template – the message from the queue is received, afterwards it is processed (in this case parsed and inserted in the target table) and if the queue is empty (@@ROWCOUNT = 0) it exits. I’ve added a 5 second WAITFOR for each message, i.e. a message will be processed every 5 seconds.

Troubleshooting Conversation Problems

It’s easy to make mistakes when setting up a Service Broker conversation. You need a detailed oriented attitude and a lot of attention. Drop and create the objects (service, queue, contract, message type) taking into consideration their dependency. Be extra careful when typing the service names and broker IDs in T-SQL code, especially when there are literals enclosed in quotes. For example, you receive no error message if you put the wrong target service name in the dbo.SendItemData code.

If all goes well and the message (XML fragment) reaches its destination and is processed by the transmission queue and ItemQueue should remain empty both at the initiator and at the target. If, however, there are messages stuck in these queues, you should start investigating what’s going on.

sys.dm_transmission_queue

According to the documentation, the transmission_status column of this catalog view holds a description of the reason why the message is still here. This is obvious when the column really holds an error message. For example, suppose that you’ve misspelled the target service name in the SendItemData stored procedure. This time the transmission_status message is clear: “The target service name could not be found. Ensure that the service name is specified correctly and/or the routing information has been supplied.”. If you did not enable broker at the initiator, the message will be “The broker is disabled in the sender's database.”.

The message_body column holds the XML fragment you send in binary format. You can still retrieve the content in a readable format:

SELECT CAST(message_body AS xml) FROM sys.transmission_queue

However, many times you’ll find the transmission_status column empty. In this case follow Remus Rusanu’s advice and run a SQL Trace at the target machine. I’ve included the script for this trace (SB_trace.sql) and for a similar extended events session (SB_xevents.sql) in the script archive.

Here are a few examples where I had to run this trace to find out what was wrong.

I did not define a route back to the initiator at the target side. Even if there is no requirement for a message back to the initiator, the Service Broker still needs to send back an “acknowledgement” and therefore a route must be defined from target to initiator. The .trc file will contain a Broker: Message Undeliverable event class with the text data “This message could not be delivered because it is a duplicate.”.
I misspelled the sender’s contract name. The message is “This message could not be delivered because the <contract name> contract could not be found or the service does not accept conversations for the contract.”.
The target queue was disabled. The message is “This message could not be delivered because the destination queue has been disabled. Queue ID: <QueueID>.”.

SSBDiagnose utility

SSBDiagnose is a command line application that will let you know if there is anything wrong with the Service Broker specific objects you’ve created at the user database level. The location of the SSBDiagnose.exe file depends on the SQL Server edition, for a SQL Server 2014 SP2 Developer Edition instance it is located at C:\Program Files\Microsoft SQL Server\120\Tools\Binn.

Here is a usage example:

cd C:\Program Files\Microsoft SQL Server\120\Tools\Binn
ssbdiagnose configuration from service tcp://<initiator ip number>:<initiator port>/SBSource/item_list/ItemDataSender2014 -S <instance name> -d SBSource to service tcp://<target ip number>:<target port>/SBTarget/item_list/ItemDataReceiver2014 -S <instance name> -U sa -P <password> -d SBTarget on contract //item_list/ItemContract2014 encryption off

My target queue is disabled and SSBDiagnose will let me know this by displaying an error message “Queue dbo.ItemQueue2014 is disabled”.

sys.conversation_endpoints

Since the conversation started in dbo.SendItemData never ends, sys.conversation_endpoint will hold one record for this conversation and the dbo.Conversation.DialogId is the sys.conversation_endpoint.conversation_handle. If you end up with several “DISCONNECTED_INBOUND” records you should prune them.

Next Steps

Download all of the scripts for this tip.
I’ve tried to be as detailed oriented as I could in this tip, but this is only a basic example. From now on you’ll need to use more advanced techniques to make the most of this powerful component. The best Service Broker resource remains Remus Rusanu’s blog. You’ll find there an encyclopedia of facts that will soon become your permanent companion. The information is still useful, even if many articles have been written for SQL 2005. Mr. Rusanu has a significant contribution to the creation of the Service Broker.
The sqlteam.com series helped me to better understand how to solve my problem with the Service Broker. Even if the code may be no longer available for download, it was still a good starting point.
Finally, here is an interesting usage of the Service Broker as a replacement for SQL Agent: http://www.davewentzel.com/content/service-broker-tickling-or-how-run-procedure-asynchronously-every-x-seconds. The whole series about Service Broker is a valuable resource.

About the author

Diana Moldovan is a DBA and data centric applications developer with 6 years of experience covering SQL 2000, SQL 2005 and SQL 2008.

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips

Article Last Updated: 2018-01-24