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. Asynchronous 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. Code changes 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. [crayon-5bb7744b24cf2820846296/] 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. History tables 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. Logging 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. Reporting database 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 Supports Service Broker 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.    

The post Why do you want to use SQL Service Broker appeared first on SQLTreeo.