Service Broker - When to use for synchronizing databases

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.

Read More on Service Broker 

How to enable SQL Server Service Broker