September 17, 2013 at 4:42 pm
I've set up a simple service broker implementation where I have a sender queue on one database and a reciever queue on another database. I've noticed that randomly, about 50% of the messages are dropped and do not show up in either the sender or reciever queues, even after waiting several minutes.
To test this, I run a query that puts four messages in the queue, then shows the contents of the sender and receiver queues. After sending four messages, I never see anything in the sender queue, but I'll see anwhere from 1-4 messages in the receiver queue and there is no pattern to what messages are getting dropped.
I tried tracing all of the broker messages and don't see any indication of errors. Instead, I see this pattern of traces show up for all four messages, regardless of whether they are dropped or not:
STARTED_OUTBOUND
CONVERSING
STARTED_INBOUND
CONVERSING
DISCONNECTED_OUTBOUND
DISCONNECTED_INBOUND
CLOSED
I am running out of ideas of how to get brokering to work right. Any advice?
-e
Here's my test script:
Use SenderDatabase
EXEC [MySignaling].[SendSignal] '<Foo>A1</Foo>'
EXEC [MySignaling].[SendSignal] '<Foo>B1</Foo>'
EXEC [MySignaling].[SendSignal] '<Foo>C1</Foo>'
EXEC [MySignaling].[SendSignal] '<Foo>D1</Foo>'
SELECT * From MySignaling.SignalDefaultSenderQueue
Use ReceiverDatabase
SELECT * From MySignaling.SignalReceiverQueue
Here is the SendSignal SP:
CREATE PROCEDURE [MySignaling].[SendSignal]
(
@signal XML,
@senderService SYSNAME = 'MySignaling_SignalDefaultSenderService'
)
AS
DECLARE @SBDialog UNIQUEIDENTIFIER;
BEGIN DIALOG CONVERSATION @SBDialog
FROM SERVICE @senderService
TO SERVICE 'MySignaling_SignalReceiverService' ON CONTRACT [MySignaling_SignalContract] WITH ENCRYPTION = OFF;
SEND ON CONVERSATION @SBDialog MESSAGE TYPE [MySignaling_Signal] (@signal);
END CONVERSATION @SBDialog;
RETURN;
And here's the setup code for the two databases:
ALTER DATABASE ReceiverDatabase SET ENABLE_BROKER
Go
ALTER DATABASE ReceiverDatabase SET TRUSTWORTHY ON
Go
ALTER DATABASE SenderDatabase SET ENABLE_BROKER
Go
ALTER DATABASE SenderDatabase SET TRUSTWORTHY ON
Go
---------------------------------------------------
-- Set up Receiver
---------------------------------------------------
USE ReceiverDatabase
Go
CREATE SCHEMA [MySignaling];
Go
CREATE MESSAGE TYPE [MySignaling_Signal] VALIDATION = WELL_FORMED_XML;
GO
CREATE CONTRACT [MySignaling_SignalContract] ([MySignaling_Signal] SENT BY INITIATOR);
GO
CREATE QUEUE [MySignaling].[SignalReceiverQueue]
WITH STATUS = ON , RETENTION = OFF , POISON_MESSAGE_HANDLING (STATUS = OFF);
GO
CREATE SERVICE [MySignaling_SignalReceiverService]
ON QUEUE [MySignaling].[SignalReceiverQueue] ([MySignaling_SignalContract]);
Go
---------------------------------------------------
-- Set up Sender
---------------------------------------------------
USE SenderDatabase
Go
CREATE SCHEMA [MySignaling];
Go
CREATE MESSAGE TYPE [MySignaling_Signal] VALIDATION = WELL_FORMED_XML;
GO
CREATE CONTRACT [MySignaling_SignalContract] ([MySignaling_Signal] SENT BY INITIATOR);
GO
CREATE QUEUE [MySignaling].[SignalDefaultSenderQueue]
WITH STATUS = ON , RETENTION = OFF , POISON_MESSAGE_HANDLING (STATUS = OFF);
GO
CREATE SERVICE [MySignaling_SignalDefaultSenderService]
ON QUEUE [MySignaling].[SignalDefaultSenderQueue] ([MySignaling_SignalContract]);
GO
CREATE PROCEDURE [MySignaling].[SendSignal]
(
@signal XML,
@senderService SYSNAME = 'MySignaling_SignalDefaultSenderService'
)
AS
DECLARE @SBDialog UNIQUEIDENTIFIER;
BEGIN DIALOG CONVERSATION @SBDialog
FROM SERVICE @senderService
TO SERVICE 'MySignaling_SignalReceiverService' ON CONTRACT [MySignaling_SignalContract] WITH ENCRYPTION = OFF;
SEND ON CONVERSATION @SBDialog MESSAGE TYPE [MySignaling_Signal] (@signal);
END CONVERSATION @SBDialog;
RETURN;
September 17, 2013 at 6:20 pm
Figured it out: There was another instance of the receiver queue that I did not know about, and SQL Server was load balancing between the instances.
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply