Service Broker--between databases of same instance

Databases: Adata and Bdata


USE Adata
GO
CREATE MASTER KEY ENCRYPTION BY PASSWORD='password'
GO
ALTER DATABASE Adata
SET ENABLE_BROKER
GO
/* in this simple example, you can set TRUSTWORTHY OFF.
TRUSTWORTHY is used fof Database modules (for example, user-defined functions or stored procedures) that use an impersonation context can access resources outside the database.
*/
ALTER DATABASE Adata
SET TRUSTWORTHY ON
GO
CREATE MESSAGE TYPE [initMessage] VALIDATION=NONE;
GO
CREATE MESSAGE TYPE [respMessage] VALIDATION=NONE;
GO
CREATE CONTRACT mContract
( [initMessage] sent by Initiator,
[respMessage] sent by target)
go
CREATE QUEUE mQueue WITH STATUS=ON,RETENTION=ON
GO
CREATE SERVICE mService ON QUEUE mQueue
(mContract);
GO



USE Bdata
GO
CREATE MASTER KEY ENCRYPTION BY PASSWORD='password'
GO
ALTER DATABASE Bdata
SET ENABLE_BROKER
GO
ALTER DATABASE Bdata
SET TRUSTWORTHY OFF
GO
CREATE MESSAGE TYPE [initMessage] VALIDATION=NONE;
GO
CREATE MESSAGE TYPE [respMessage] VALIDATION=NONE;
GO
CREATE CONTRACT mContract
( [initMessage] sent by Initiator,
[respMessage] sent by target)
go
CREATE QUEUE pQueue WITH STATUS=ON,RETENTION=ON
GO
CREATE SERVICE pService ON QUEUE pQueue
(mContract);
GO



USE Adata
GO
SET NOCOUNT ON
DECLARE @id uniqueidentifier
BEGIN TRAN
BEGIN DIALOG @id
FROM SERVICE mService
TO SERVICE 'pService'
ON CONTRACT [mContract]
WITH LIFETIME=600;
SEND ON CONVERSATION @id
MESSAGE TYPE initMessage (N'service is good.')
COMMIT
GO


USE Bdata
GO
SET NOCOUNT ON
DECLARE @id uniqueidentifier;
DECLARE @body nvarchar(max);
DECLARE @type sysname;
BEGIN TRAN
WAITFOR
(RECEIVE TOP (1)
@type=message_type_name,
@id=conversation_handle,
@body=message_body
FROM [pQueue]
),TIMEOUT 60;
PRINT @body;
SEND ON CONVERSATION @id
MESSAGE TYPE [respMessage] (N'what matters?');
END CONVERSATION @id;
COMMIT
GO

/*
SELECT * FROM Bdata.dbo.pQueue
SELECT * FROM Adata.dbo.mQueue
*/

/* you don't need to set up the route and route binding. It is local service. */

Service Broker--Within a database

Database Name: Adata

USE Adata
GO
/* Master Key must be created. */
CREATE MASTER KEY ENCRYPTION BY PASSWORD='password'
GO
ALTER DATABASE Adata SET ENABLE_BROKER
GO
CREATE MESSAGE TYPE [initMessage] VALIDATION=NONE;
GO
CREATE MESSAGE TYPE [respMessage] VALIDATION=NONE;
GO
CREATE CONTRACT mContract
( [initMessage] sent by Initiator,[respMessage] sent by target )
GO

USE Adata
GO
CREATE QUEUE mQueue WITH STATUS=ON,RETENTION=ON
GO
CREATE QUEUE pQueue WITH STATUS=ON, RETENTION=ON;
GO
CREATE SERVICE mService ON QUEUE mQueue(mContract);
GO
CREATE SERVICE pService ON QUEUE pQueue(mContract);
GO

/* CODE BLOCK (1) */

USE Adata
GO
SET NOCOUNT ON
DECLARE @id uniqueidentifier
BEGIN TRAN
BEGIN DIALOG @id
FROM SERVICE mService TO SERVICE 'pService'
ON CONTRACT [mContract] WITH LIFETIME=600;

SEND ON CONVERSATION @id
MESSAGE TYPE initMessage
(N'service is good.')
COMMIT

GO


/* CODE BLOCK (2) */

USE Adata
GO
SET NOCOUNT ON
DECLARE @id uniqueidentifier;
DECLARE @body nvarchar(max);
DECLARE @type sysname;
BEGIN TRAN
WAITFOR(
RECEIVE TOP (1)
@type=message_type_name,
@id=conversation_handle,
@body=message_body
FROM [pQueue]
),TIMEOUT 60;

SEND ON CONVERSATION @id
MESSAGE TYPE [respMessage]
(N'what matters?');

END CONVERSATION @id;

COMMIT

GO

Understand the message queue
Run code block (1)
SELECT conversation_handle,service_name FROM mQueue
SELECT conversation_handle,service_name FROM pQueue

Both mQueue and pQueue include the same conversation_handle. Each queue includes many records, because the message has not been retrieved.

Modifying the CODE BLOCK (2) as following:
/*SEND ON CONVERSATION @id
MESSAGE TYPE [respMessage]
(N'what matters?');*/

Run the code block (2)

The pQueue is empty because all messages are retrieved and END CONVERSATION @id statement. However, messages are still in the queue until timeout.

To avoid the piling up of the message in a queue, END CONVERSATION @id statement should be added in code block (1).

END CONVERSATION @id in CODE BLOCK (2) does not delete the entry in sending queue (mQueue).

If you can retrieve the conversation_handle a queue, you can run:

SEND ON CONVERSATION @id
MESSAGE TYPE [respMessage]
(N'what matters?');

Both mQueue and pQueue have conversation_handle.

When you send a message and run the END CONVERSATION @id statement, the entry in sending queue is deleted.

But the entry is still in receiving queue. When you run the CODE BLOCK (2) and run the END CONVERSATION @id, the entry in receiving queue is deleted.