Synchronizing data between SQL Server instances with Service Broker

By:Diana MoldovanLast Updated: 2018-01-24CommentsRelated Tips:MoreService Broker

Im 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?

The best way to become familiar with Service Broker (if youre 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 Ive done a few months ago. My customer still runs on SQL 2008 R2, but Ive re-done everything for you on a more recent edition.

I wont do another overview of the Service Broker component. There are many good introduction articles and Ive listed a few of them above. Instead Ill describe in detail how Ive 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 dont need to be tightly coupled. Therefore, I chose to implement a Service Broker solution.

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

On each instance Ive 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 scriptsSB_Source_user_db.sqlandSB_Target_user_db.sqlin theattached archive.

If the instances are installed on machines from the same domain or from trusted domains the network access between instances can be based onWindows 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 Ill use as the authentication option for the 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.sqlandSB_Target_master.sql) in theattached archive.

SB_Source_objects.sqlandSB_Target_objects.sqlcreate the Service Broker specific objects in each database:

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

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:

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

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

t ID1/ID RegDay2017-12-15/RegDay RegTime14:38:40.3170000/RegTime CodeA123/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 queues activation stored procedure.InsertDataAtTarget.sqlis 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 cant take any parameter. It only processes the content of the queues 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. Ive added a 5 second WAITFOR for each message, i.e. a message will be processed every 5 seconds.

Its 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 whats going on.

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 youve 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 beThe broker is disabled in the senders database..

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

However, many times youll find the transmission_status column empty. In this case followRemus Rusanus adviceand run a SQL Trace at the target machine. Ive included the script for this trace (SB_trace.sql) and for a similar extended events session (SB_xevents.sql) in thescript 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 senders 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..

SSBDiagnoseis a command line application that will let you know if there is anything wrong with the Service Broker specific objects youve 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.

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 messageQueue dbo.ItemQueue2014 is disabled.

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 shouldprune them.

Ive tried to be as detailed oriented as I could in this tip, but this is only a basic example. From now on youll need to use more advanced techniques to make the most of this powerful component. The best Service Broker resource remainsRemus Rusanus blog. Youll 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.

Finally, here is an interesting usage of the Service Broker as a replacement for SQL Agent: The wholeseries about Service Brokeris a valuable resource.

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

Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*** NOTE *** – If you want to include code from SQL Server Management Studio (SSMS) in your post, please copy the code from SSMS and paste the code into a text editor like NotePad before copying the code below to remove the SSMS formatting.

I agree by submitting my data to receive communications, account updates and/or special offers about SQL Server from MSSQLTips and/or its Sponsors. I have read theprivacy statementand understand I may unsubscribe at any time.

I agree by submitting my data to receive communications, account updates and/or special offers about SQL Server from MSSQLTips and/or its Sponsors. I have read theprivacy statementand understand I may unsubscribe at any time.

Copyright (c) 2006-2019Edgewood Solutions, LLCAll rights reserved

Some names and products listed are the registered trademarks of their respective owners.