Understanding Service Broker to perform asynchronous operations – (Part-2)
Author: Basit A. Farooq
Creating queues
Next, you create the queue using the CREATE QUEUE statement. You must specify queue_name as a fully-qualified name unless you are creating it in the current database using your default schema.
The syntax is:
CREATE QUEUE
{
[ database_name. [ schema_name ] . | schema_name. ]
queue_name
}
::=
{
[ database_name. [ schema_name ] . | schema_name. ]
stored_procedure_name
}
The STATUS setting determines whether the queue is active. It is ON by default. The RETENTION setting determines whether the messages should be kept in the queue until the conversation has ended. It is OFF by default. You might want to turn retention on to create an audit trail before you close the conversation. However, leaving retention on degrades performance.
You use the ACTIVATION keyword to associate the queue with a stored procedure that is activated automatically when a message is placed in the queue. You use the fully qualified name if you need to use a stored procedure that isn’t in your default schema.
You can define the maximum number of queue readers SQL Server should create and a security context for executing the stored procedure.
The queue is essentially a table. Therefore, you can specify a filegroup other than the default as the storage destination.
To create a queue named inventoryQ that calls the spCheckInventory stored procedure, you execute:
CREATE QUEUE inventoryQ
WITH STATUS=ON,
ACTIVATION(PROCEDURE_NAME=spCheckInventory,
MAX_QUEUE_READERS = 5, EXECUTE AS OWNER)
Creating services
You create a service using the CREATE SERVICE statement. It has the syntax:
CREATE SERVICE service_name
[ AUTHORIZATION owner_name ]
ON QUEUE [ schema_name. ]queue_name
[ ( contract_name | [DEFAULT] [ ,…n ] ) ]
[ ; ]
The queue name and contract name are case-sensitive. The queue is required. If the service acts only as an initiator, you don’t need to specify a contract. You must specify at least one contract if the service acts as the target. The DEFAULT contract uses the DEFAULT message type in ANY direction.
You must have REFERENCES permission on the queue and all contracts to execute this statement. Members of the db_owner, ddl_admin, and sysadmin roles have the REFERENCES permission by default.
To create a service named InventoryService that uses the inventoryQ queue and the inventoryContract contract, you execute:
CREATE SERVICE InventoryService
ON QUEUE dbo.inventoryQ
(inventoryContract)
Spanning multiple servers
You can use Service Broker to pass messages between services in:
- The same database
- A different database on the same instance of SQL Server
- A distributed application
Transmission management
Service Broker implements its own protocol which manages message sequencing and delivery. The protocol has two components:
- Dialog protocol — Ensures EOIO delivery for the messages.
- Adjacent broker protocol — Manages message fragmentation.
The adjacent broker protocol interfaces with the transport protocol, usually TCP/IP.
Service Broker addresses a network transmission as an endpoint. Each SQL Server instance that participates in a remote Service Broker conversation must have an endpoint. Server Broker stores messages en route to their destination in a built-in queue, the sys.transmission_queue. Figure below shows how a message moves from one service to another. The Dialog is a logical connection and exists for the life of the conversation.
You can view conversation endpoints in the sys.conversation_endpoints catalog view. These are not the same as the Service Broker endpoints depicted in the figure below.
When both services are on and there are no network problems, the transmission occurs as follows:
- An object initiates a conversation by sending the first message.
- The conversation begins and the service forwards the message to the sys.transmission_queue.
- Service Broker takes the message from the sys.transmission_queue and sends the message out through the Service Broker endpoint.
- The message travels from the Service Broker endpoint on the local instance to the Service Broker endpoint on the remote instance.
- Service Broker forwards the message to the queue associated with the service on the remote computer. In this case, the Inventory queue.
- The queue reader receives the message from the queue.
If the remote instance of SQL Server doesn’t respond, the message stays in the sys.transmission_queue on the local computer until Service Broker can deliver it.
If the remote instance of SQL Server accepts the message, but the queue status is off, the message is stored in the sys.transmission_queue queue of the remote SQL Server until the service’s queue is turned on.
Creating a Service Broker endpoint
An instance of SQL Server can host only a single Service Broker endpoint. You use the CREATE ENDPOINT statement to create a Service Broker endpoint. When creating a Service Broker endpoint, the statement has the following syntax:
CREATE ENDPOINT endPointName [ AUTHORIZATION login ]
[ STATE = { STARTED | STOPPED | DISABLED } ]
AS { TCP } (
)
FOR { TSQL | SERVICE_BROKER | DATABASE_MIRRORING } (
)
::=
AS TCP (
LISTENER_PORT = listenerPort
[ [ , ] LISTENER_IP = ALL | ( 4-part-ip ) | ( “ip_address_v6” ) ]
)
::=
FOR SERVICE_BROKER (
[ AUTHENTICATION = {
WINDOWS [ { NTLM | KERBEROS | NEGOTIATE } ]
| CERTIFICATE certificate_name
| WINDOWS [ { NTLM | KERBEROS | NEGOTIATE } ] CERTIFICATE certificate_name
| CERTIFICATE certificate_name WINDOWS [ { NTLM | KERBEROS | NEGOTIATE } ]
} ]
[ [ , ] ENCRYPTION = { DISABLED | { { SUPPORTED | REQUIRED }
[ ALGORITHM { RC4 | AES | AES RC4 | RC4 AES } ] }
]
[ [ , ] MESSAGE_FORWARDING = { ENABLED | DISABLED } ]
[ [ , ] MESSAGE_FORWARD_SIZE = forward_size ]
)
When you create the Service Broker endpoint, you must identify:
- endPointName – Used when modifying or deleting the endpoint.
- State – Whether the endpoint should be started, stopped, or disabled after it is created.
- Listener port – Port Service Broker will listen on. The standard port is 4022.
You use the AUTHENTICATION option to specify how Service Broker authenticates the computer attempting the connection. If you don’t specify an authentication method, Service Broker uses Windows authentication with negotiate enabled. Negotiate means that it uses Kerberos where supported and NTLM if Kerberos isn’t supported. You can choose from the following authentication options:
- Windows only
- Certificate only
- Try Windows first, then certificate
- Try certificate first, then Windows
Encryption using the RC4 algorithm is required by default. You can disable encryption, or configure it as supported but not required. You can also select a different encryption algorithm. A discussion of encryption algorithms is beyond the scope of this article series.
You use the MESSAGE_FORWARDING option to configure the end point to forward the message if it is for a different instance of SQL Server. To use certificates, the local computer must have the certificate with the private key. The remote computer must have a certificate with a matching public key.
To create a Service Broker endpoint that listens on port 4022 and uses default authentication and encryption settings, execute as follows:
CREATE ENDPOINT AdventureWorks_sb_end
STATE = STARTED
AS TCP(LISTENER_PORT = 4022)
FOR SERVICE BROKER()
To create a Service Broker endpoint that uses a certificate for authentication if Windows authentication fails, you execute:
CREATE ENDPOINT AdventureWorks_sb_end
STATE = STARTED
AS TCP(LISTENER_PORT = 4022)
FOR SERVICE_BROKER
(AUTHENTICATION = WINDOWS CERTIFICATE sb_cert)
You can view the endpoints on an instance of SQL Server by querying sys.endpoints.
Conversation routing
Service Broker determines message routing to a server based on information contained in two tables:
- sys.routes table in the local database — Stores routing information for dialogs that originate in the local database.
- msdb.sys.routes — Stores routing information for dialogs that come from an external instance.
The information used to determine conversation routing is shown in the table:
Item |
Description |
|
name |
Name of the route. |
|
remote_service_name |
Name of the service. |
|
broker_instance |
Instance of the service. Contains a guid. You can retrieve the guid for Service Broker from the service_broker_guid column in sys.databases.
You might have the same service running on multiple instances of SQL Server to provide fault tolerance. |
|
lifetime |
Determines the date the route expires. If NULL, the route never expires. |
|
address |
Network path to the instance of SQL Server. LOCAL defines a service in the current instance. Use the syntax: tcp://server_name:port/. |
|
mirror_address |
Address to the mirror, if any. If no mirror exists, contains NULL. |
All databases include a default route named AutoCreatedLocal that references the LOCAL address. Service Broker tries to find the closest match and sends the request to that route.