Service Broker helps developers build asynchronous, loosely coupled applications in which independent components work together to accomplish a task. These application components exchange messages that contain the information that is required to complete the task. Service Broker's features provide a number of significant benefits to database applications. These features and benefits include:
- Conversations: Service broker is all about messaging, two entities can exchanges messages without caring about the low level communication infrastructure; conversation is a reliable, persistent communication channel that guarantees message delivery.
- Message ordering and coordination: Service Broker queues are integrated into the database which means regular database maintenance and administration also included and handles the most difficult tasks involved in writing messaging applications. These difficult tasks include message coordination, reliable message delivery, locking, and starting queue readers.
- Transactional asynchronous programming: Message delivery between applications is transactional and asynchronous; if your application transaction rolls back, all Service Broker operations in the transaction roll back. In asynchronous delivery, the Database Engine handles delivery while the application continues to run.
- Support for loosely coupled applications: SOA is about messaging and service broker is built using messaging infrastructure so it supports loosely coupled applications. Your applications do not need to run at the same time and do not have to know the physical location or the implementation of the other participants in the conversation.
The following illustration presents a high-level view of Service Broker network communication between two SQL Server instances:
Notice that the conversation is a persistent, logical connection. The conversation can occur over any period of time, and during that period of time, the conversation can use any number of network connections.
Network connections occur between two Service Broker endpoints. These connections use TCP/IP. If the connection is inactive for a short time, SQL Server closes the network connection. To deliver a message, Service Broker holds the message in the transmission queue for the database that sent the message. The recipient delivers the message directly to the queue for the destination service. The queue for the sending service is not involved in the operation.
Let's now but service broker into practical example which is a generic database CRUD auditing. The first thing you will need to do is to enable service broker for your database and this could be done by using the following SQL command:
alter database [DataBaseName] set enable_broker
This option activates Service Broker message delivery, preserving the existing Service Broker identifier for the database. if you have an old database that uses service broker then you will need to generate a new identifier for your service broker:
alter database [DataBaseName] set new_broker
Now it's time to create our service broker objects, primary I will create a template message type to hold the actual data payload for exchange during a certain conversation. A message type defines the name of a message and the validation that Service Broker performs on messages that have that name:
CREATE MESSAGE TYPE [//intellecting.net/Notification] VALIDATION = NONE ;
Note that the message type specifies a validation type of NONE because the message will contain data that is not well-formed XML or it might binary data.
Then I will create service broker contract which defines the name of a specific business task and list the message types used in that task. Service Broker contracts define two different service roles: the initiator and the target. The initiator of a conversation begins the conversation by sending a message to the target. The contract that the conversation uses defines which service role can send messages of a given message type:
CREATE CONTRACT [//intellecting.net/INotification] ([//intellecting.net/Notification] SENT BY INITIATOR);
The above SQL command indicates that only the initiator of the conversation can send messages of the specified message type which means that we defined a one way contract.
Now I will create a service broker Queue which stores messages, when Service Broker receives a message for a service, Service Broker inserts the message into the queue for that service. Service Broker manages queues and presents a view of a queue that is similar to a table:
CREATE QUEUE NotificationQueue WITH STATUS = ON;
A queue may be associated with a stored procedure. In this case, SQL Server activates the stored procedure when there are messages in the queue to be processed. SQL Server can start more than one instance of the stored procedure up to a configured maximum as shown below:
CREATE QUEUE AuditQueue WITH STATUS = ON,
ACTIVATION (
PROCEDURE_NAME = AuditMessage,
MAX_QUEUE_READERS = 20,
EXECUTE AS SELF )
As you can notice from the above SQL code that the activation stored procedure is defined with a maximum of 20 instance and will execute as the current user.
If your auditing database will lay on the same server with the OLTP database then it is preferable to use internal activation with stored procedures as it is a common way to design Service Broker applications and of course your stored procedure will contain the routine needed to audit your data changes.
Finally we will need to create a service which represents a business process as a distinct set of tasks, each contract within the service represents a specific task. Each service uses a queue to store messages, messages sent to the service are delivered to the queue:
CREATE SERVICE NotificationService
ON QUEUE NotificationQueue
([//intellecting.net/INotification])
If your auditing database will be on another SQL server then you will need to configure your service broker instance to use message routing, initially we will create service broker endpoint that exposes our service capabilities to the outside world:
CREATE ENDPOINT AuditBrokerEndpoint
STATE = STARTED
AS TCP ( LISTENER_PORT = 5000 )
FOR SERVICE_BROKER ( AUTHENTICATION = WINDOWS ) ;
After configuring the endpoint, you must set up security between the distributed Service Broker services. By default, Service Broker doesn’t allow two Service Broker services to communicate without configured security. For transport security I will use Windows Authentication especially if the two service broker instances are registered in the same domain and It would even work if you have a trusted relationship between two Windows domains.
You need to run your SQL server instance with a certain domain account, for example intellecting\ahmedamir, you can do this easily through Services MMC snap-in. you now have to create SQL Server logins for the SQL Server that communicates with you. Note that this login represents the service account of the remote SQL Server machine, not the service account of the instance where you’re creating the Service Broker endpoint:
CREATE LOGIN [intellecting\AuditAccount] FROM WINDOWS;
You will need to grant connect and send permission for your service as follows:
GRANT CONNECT ON ENDPOINT::AuditBrokerEndpoint TO [intellecting\AuditAccount];
GRANT SEND ON SERVICE::[NotificationService] TO PUBLIC;
After configuring the endpoint and service security, we need to instruct Service Broker to route our sender messages to a certain SQL server instance, so on the sender service broker instance we will configure our routing as follows:
CREATE ROUTE AuditBrokerService WITH
SERVICE_NAME = 'AuditBrokerService',
ADDRESS = 'TCP://targetserver:5000'
By creating all service broker objects; our messaging infrastructure is ready for message exchange through a single SQL server instance our by using multiple SQL server instances. Now let us try to send message to our service broker, I will create the service initiator; the following stored procedure opens a conversation to our service and submit the XML message to the service queue:
CREATE PROCEDURE [dbo].[AduitMessage] @Message xml
AS
BEGIN
BEGIN TRY
BEGIN TRANSACTION
DECLARE @ch UNIQUEIDENTIFIER
BEGIN DIALOG CONVERSATION @ch
FROM SERVICE NotificationService
TO SERVICE 'NotificationService'
ON CONTRACT [//intellecting.net/INotification] WITH ENCRYPTION = OFF;
SEND ON CONVERSATION @ch MESSAGE TYPE [//intellecting.net/Notification] (@Message);
COMMIT;
END TRY
BEGIN CATCH
ROLLBACK TRANSACTION
END CATCH
END
As can be observed from the above code that no message encryption is used and the procedure sends a message to a local service queue, if you want to send a message to another SQL server instance then you will just use the name of the service in the Service Broker Route defined earlier.
The question now is how to format your audit message with the hooked data and how to use the above stored procedure to send your audit messages to service broker. In order to hook any data changes you will need to create SQL AFTER trigger to capture data changes, you can create three trigger; for insert, Update and delete but I will consolidate them to one trigger as follows:
CREATE TRIGGER [dbo].[tr_Orders_Audit] ON [dbo].[Orders]
FOR INSERT,UPDATE,DELETE
AS
BEGIN
DECLARE @Message XML
DECLARE @TableName varchar(MAX) = 'Orders'
DECLARE @ColumnsUpdated varbinary(MAX) = COLUMNS_UPDATED()
DECLARE @InsertedCount int = (SELECT COUNT(1) FROM INSERTED)
DECLARE @DeletedCount int = (SELECT COUNT(1) FROM DELETED)
DECLARE @ChangedCloumns XML = (SELECT COLUMN_NAME AS Name,DATA_TYPE as Type,
sys.fn_IsBitSetInBitmask(@ColumnsUpdated,COLUMNPROPERTY(
OBJECT_ID(TABLE_SCHEMA + '.' + TABLE_NAME),COLUMN_NAME, 'ColumnID')) as IsUpdate
FROM INFORMATION_SCHEMA.COLUMNS Field WHERE TABLE_NAME = @TableName FOR XML RAW('Column'))
IF @InsertedCount > 0 AND @DeletedCount <= 0 -- SQL Insert
BEGIN
DECLARE @MessageType varchar(MAX) = 'INSERT'
DECLARE @NewData XML = (Select * from INSERTED FOR XML RAW('New'))
END
IF @InsertedCount > 0 AND @DeletedCount > 0 -- SQL Update
BEGIN
SET @MessageType = 'UPDATE'
SET @NewData = (Select * from INSERTED FOR XML RAW('New'))
DECLARE @OldData XML = (SELECT * from DELETED FOR XML Raw('Old'))
END
IF @InsertedCount <= 0 AND @DeletedCount > 0 -- SQL Delete
BEGIN
SET @MessageType = 'DELETE'
SET @ChangedCloumns = (select Column_Name AS Name,Data_Type as Type,0 as IsUpdate
from INFORMATION_SCHEMA.COLUMNS where table_name=@TableName FOR XML RAW('Column'))
SET @OldData = (SELECT * from DELETED FOR XML RAW('Old'))
END
DECLARE @Header XML = (Select SUSER_SNAME() AS [User],@@SPID as Process, HOST_NAME() AS [HostName],GETDATE() AS [Time],
@TableName AS TargetTable,@MessageType as MessageType FOR XML RAW('Header'))
SET @Message = (Select @Header,@ChangedCloumns,@NewData,@OldData for XML RAW('NotificationMessage'))
exec AduitMessage @Message
END
As can be noticed from the above code that our trigger is divided into multiple parts:
-
In the first part, we dynamically detect the changed columns of target table by calling SQL server trigger function 'COLUMNS_UPDATED()' which in turn returns a bitmask of the changed columns according to column order, then we pass this bit mask to system function to get the names of the changed columns and finally we format the result by using XML.
-
In the second part, we detect the trigger action [Insert, Update or Delete] by selecting the number of rows in the INSERTED & DELETED tables and according to the trigger type we format the data changes by using XML.
-
In the third part, we collect information about the user and actions to be our message header and then we combine all the formatted data generated by the first two steps to produce the final message to be audited.
-
In the final part, we just submit the formatted XML message to our audit procedure which in turn sends the message to service broker infrastructure.
Now I can say that we finished the first part of creating our auditing system, the remaining is how to get your XML messages from service broker (in case of distributed brokers) and how to parse them to produce our audit data. I will introduce message receiving and parsing in my next talk soon.
I attached the SQL server database that contains all the code discussed here. DATA.zip (264.13 kb)
Currently rated 5.0 by 2 people
- Currently 5/5 Stars.
- 1
- 2
- 3
- 4
- 5