Service Broker - When to use for synchronizing databases

10 January,2019 by Jack Vamvas

Service Broker  is used by SQL developers  to manage applications running across multiple SQL Server instances . It is part of the SQL Server native feature set and is a messaging and queue  service. Asynchronous processing in Service Broker is very effective in supporting decoupled processes for long running tasks. It's very versatile but be warned of attempting to over engineer solutions that could be better served by other features. 

As a very general feature  summary for Service Broker think of  - Guaranteed message delivery , Transactions,Backup,API and asynchronous communication.   

I receive questions regularly about whether  Service Broker should  be used for synchronising databases? Let's take a look at some of the pros\cons of adopting Service Broker for this use case

Pros

>Useful if source and target databases have different schemas. For example - capturing a subset of data and pushing to another database to be consumed as a microservice.

>Decouple database dependencies by managing communication through messages

>Scale application architecture by moving queing to seprate servers if needed

>Flexibility & customasibility particuarly if good T-SQL skills are available. The code used to read\write message is done in SQL

>Service Broker is not a separate repository , therefore messages are integrated into the same HA\DR\Backup as all the data 

 

Cons

>Greater amount if time and effort to implement \ steep learning curve

>Service Broker is complex - as potentially all messages are part of a long-life conversation and must us T-SQL to program against.

>If high volume traffic - a large amount of service broker traffic is generated , which may will require oversight 

 

Conclusion

Difficult to justify using Service Broker for database synchronisation for passing data from one database to another read - only database , compared to Always-On Availability groups,Replication,Log Shipping,Mirroring, or AWS Database Migration Service , Azure SQL Data Sync

With the rise of Cloud \ On-Prem\ Hybrid Solutions there are multiple scenarios to consider. It's tempting to roll-your-own solution , but be careful not to create a monster.

Service_broker

 

 

Read More on Service Broker 

How to enable SQL Server Service Broker 

 

 


Author: Jack Vamvas (http://www.sqlserver-dba.com)


Share:

Verify your Comment

Previewing your Comment

This is only a preview. Your comment has not yet been posted.

Working...
Your comment could not be posted. Error type:
Your comment has been posted. Post another comment

The letters and numbers you entered did not match the image. Please try again.

As a final step before posting your comment, enter the letters and numbers you see in the image below. This prevents automated programs from posting comments.

Having trouble reading this image? View an alternate.

Working...

Post a comment on Service Broker - When to use for synchronizing databases


sqlserver-dba.com | SQL Server Performance Tuning | SQL Server DBA:Everything | FAQ | Contact|Copyright & Disclaimer