Why use SQL Service Broker
It enables you to execute SQL Statements faster and do the real work later. SQL Server is using Service broker for its builtin Mail components since SQL Server 2015.
SQL Server statements are synchronous, 1 statement after another. Each statement waits until it is completed before continuing to the next. It works like that in Triggers, Procedures, Functions, etc.
Often you have situations where you don’t want to wait for a statement to finish all of its internal work before you can continue.
Not waiting for it does not mean you don’t want to be 100% sure it will happen and you also don’t want to wait a long time for it to happen. You want your OLTP processes to do their core thing as fast as possible without waiting for the important side processes to complete.
Yes, of course. But it doesn’t mean a complete code rewrite. The places where you don’t want to wait for the end result you call a procedure and that procedure implements the Service Broker functionality.
A small example:
This trigger does not change if we implement Service Broker.
CREATE TRIGGER tr_u_salary_change FOR UPDATE AS
-- IMPORTANT! Register the salary change, we need to wait for it
INSERT INTO his_salary_change( emp_id, from_salary, to_salary, who_did_it, when_it_happened)
SELECT inserted.emp_id, deleted.salary, inserted.salary, SUSER_NAME(), GETUTCDATE()
FROM inserted inner join deleted on inserted.emp_id=deleted.emp_id
-- trigger is not prepared for bulkupdates!
DECLARE @emp_id int = (SELECT emp_id from inserted)
-- Less important but still very important it happens ASAP
EXEC RecalcSalaryEndOfMonth @emp_id=@emp_id
The procedure RecalcSalaryEndofMonth will be adjusted to implement Service Broker. It will return immediately without processing the whole salary recalculatoin. That will be done later in the procedure you built to process all the salary changes.
In a normal situation, no one is recalcing salaries on every update of course, but it is an easy example:
Good uses of Service Broker
MERGE/TRANSACTIONAL/PEER TO PEER Replication
If you use replication you can use Service Broker also. Replication is so much more than Service Broker so it is not a replacement, but you can easily exchange data between multiple subscribers and a publisher with Service Broker.
When you fill up your history tables during your OLTP process and there is a chain of INSERTS/UPDATES/DELETES and TRIGGERS that need to do the job but slowing down the process, you can now use Service Broker.
If you are writing logging information into tables and many applications are doing that so milliseconds are floating away just waiting to do the inserts, use Service Broker.
Chain of processes
If there is a chain of different processes in place that are necessary to register or inform other systems about changes in systems but slowing down the core OLTP processes, use Service Broker.
You have a data warehouse
You can use Service Broker to have or built a real-time data warehouse without SSIS or other ETL processes that do – enormous- record evaluations what to update.
You want to have a reporting database but do not want to implement Replication, backup or ETL to update it. You can use Service Broker.
SQL Servers anywhere
Secured, not secured, inside and outside domains; You want to have data sent to or from SQL Servers anywhere, you can use Service Broker easily to exchange the data you want to and from any SQL Server inside or outside domain networks with certificates.
There are unlimited possibilities where you can use Service Broker. It is nothing special, it is built into SQL Server, SQL Server itself uses it also for its mail functionality, it doesn’t cost anything extra, there are no agent jobs needed. Just you enable service broker in your database and you write procedures or statements to use it.
SQLTreeo senior DBA’rs have implemented Service Broker to optimize or replace Replication, Chain of processes, Data warehouses, Reporting Databases, Logging and History tables and SQL Servers Anywhere with certificates. If you need hands-on help in your projects with Service Broker implementations, SQLTreeo can help you.