Service Broker Setup for Asynchronous Messaging

By Tom Nonmacher

SQL Server Service Broker provides native support for messaging and queuing applications in the SQL Server Database Engine. This makes it easier to build reliable and scalable applications. The Service Broker is used to send and receive messages from one database to another. Messages can be sent to the same database or to different databases on the same instance or on different instances. In this blog post, we will be discussing how to set up the Service Broker for asynchronous messaging in SQL Server 2016 and SQL Server 2017.

To begin with, you need to enable Service Broker in your database using the ALTER DATABASE command. The NEW_BROKER option will replace any existing Service Broker identifier with a new one. Keep in mind that this should be performed during a maintenance window, as it requires exclusive access to the database. Here is a sample T-SQL command for enabling Service Broker: 


-- Enable Service Broker

ALTER DATABASE YourDatabase SET ENABLE_BROKER WITH ROLLBACK IMMEDIATE;

Next, you need to create a message type. Message types define the name of a message and the validation that Service Broker performs on the message body. Here is an example of how to create a message type: 


-- Create message type

CREATE MESSAGE TYPE YourMessageType VALIDATION = NONE;

The next step is to create a contract. Contracts define the types of messages that are used in a conversation. A contract specifies the message types that the initiating service can send and the message types that the target service can send. Here's an example of creating a contract: 


-- Create Contract

CREATE CONTRACT YourContract (YourMessageType SENT BY ANY);

After the contract is created, you need to create a queue. Queues are used to hold messages sent and received by Service Broker. Here's an example of creating a queue: 


-- Create Queue

CREATE QUEUE YourQueue;

The final step in the setup is to create a service. A service is a group of tasks with specific access permissions. Services are used to send and receive messages. Here's an example of creating a service: 


-- Create Service

CREATE SERVICE YourService ON QUEUE YourQueue (YourContract);

Please note that the MySQL 5.7, DB2 11.1, and Azure SQL technologies do not natively support the Service Broker feature of SQL Server. However, you can implement similar functionality using different methods. For example, in MySQL, you can use stored procedures and triggers together with the EVENT and SCHEDULE features to implement asynchronous messaging. In DB2, you can use IBM MQ, and in Azure SQL, you can use Azure Service Bus for similar functionality.

In conclusion, SQL Server Service Broker is a powerful tool that can greatly simplify the development of distributed and asynchronous applications. It provides a native and efficient way to implement messaging and queuing in the SQL Server Database Engine. With the right setup, it can help you build more reliable and scalable applications.

Check out the latest articles from all our sites:

Privacy Policy for sqlsupport.org

Last updated: Feb 03, 2026

sqlsupport.org respects your privacy and is committed to protecting any personal information you may provide while using this website.

This Privacy Policy document outlines the types of information that are collected and recorded by sqlsupport.org and how we use it.

Information We Collect

  • Internet Protocol (IP) addresses
  • Browser type and version
  • Pages visited
  • Time and date of visits
  • Referring URLs
  • Device type

Cookies and Web Beacons

sqlsupport.org uses cookies to store information about visitors preferences and to optimize the users experience.

How We Use Your Information

  • Operate and maintain our website
  • Improve user experience
  • Analyze traffic patterns
  • Prevent fraudulent activity

Contact

Email: admin@sqlsupport.org




058B74
Please enter the code from the image above in the box below.