Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

HELP! Service Broker is dropping messages Expand / Collapse
Author
Message
Posted Tuesday, September 17, 2013 4:42 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Thursday, September 25, 2014 11:38 AM
Points: 14, Visits: 34
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;




Post #1495723
Posted Tuesday, September 17, 2013 6:20 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Thursday, September 25, 2014 11:38 AM
Points: 14, Visits: 34
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.
Post #1495731
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse