Problems with sending Event Notifications to remote service

Hi!

Just set up my first Service Broker test. Wanted to send an Event Notification from one instance to another. Finally, the message arrives but profiler shows that the message is still being continously resent (and rejected as being duplicate). What am I doing wrong?

With best regards,

Artus

--

/* Local Event Source.sql */

USE master
GO

CREATE DATABASE NotificationDB
GO

ALTER DATABASE NotificationDB SET ENABLE_BROKER WITH ROLLBACK IMMEDIATE;
GO

CREATE ENDPOINT BrokerEndpoint
STATE = STARTED
AS TCP
(
LISTENER_PORT = 5554
)
FOR SERVICE_BROKER
(
AUTHENTICATION = WINDOWS,
ENCRYPTION = DISABLED
)
GO

USE NotificationDB
GO

CREATE EVENT NOTIFICATION TestEN
ON DATABASE
FOR CREATE_TABLE
TO SERVICE 'ExpressService', 'AFEDD339-AD3D-4865-AF3C-299B0A0784C6'
GO

CREATE ROUTE ExpressRoute
WITH SERVICE_NAME = 'ExpressService' ,
BROKER_INSTANCE = 'AFEDD339-AD3D-4865-AF3C-299B0A0784C6',
ADDRESS = 'TCP://localhost:5555'
GO

CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'Pa$$w0rd';
GO
ALTER MASTER KEY ADD ENCRYPTION BY SERVICE MASTER KEY;
GO

USE master
GO

SELECT service_broker_guid FROM sys.databases WHERE database_id = DB_ID()
GO

/* Remote Service.sql */

USE master
GO

CREATE DATABASE Test
GO

ALTER DATABASE Test SET ENABLE_BROKER WITH ROLLBACK IMMEDIATE;
GO

CREATE ENDPOINT BrokerEndpoint
STATE = STARTED
AS TCP
(
LISTENER_PORT = 5555
)
FOR SERVICE_BROKER
(
AUTHENTICATION = WINDOWS,
ENCRYPTION = DISABLED
)
GO

USE Test
GO

CREATE QUEUE ExpressQueue
WITH STATUS = ON
GO

CREATE SERVICE ExpressService
ON QUEUE ExpressQueue
(
[http://schemas.microsoft.com/SQL/Notifications/PostEventNotification]
);
GO

GRANT SEND ON SERVICE::ExpressService
TO [public]
GO

CREATE ROUTE ExpressServiceRoute
WITH SERVICE_NAME = 'ExpressService',
BROKER_INSTANCE = 'AFEDD339-AD3D-4865-AF3C-299B0A0784C6',
ADDRESS = 'LOCAL'
GO

CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'Pa$$w0rd';
GO
ALTER MASTER KEY ADD ENCRYPTION BY SERVICE MASTER KEY;
GO

SELECT service_broker_guid FROM sys.databases WHERE database_id = DB_ID()
GO

SELECT * FROM sys.endpoints
GO

On the remote instance you need a route back to the EN service in the [test] database:

CREATE ROUTE NotificationsServiceRoute

WITH SERVICE_NAME = 'http://schemas.microsoft.com/SQL/Notifications/EventNotificationService',

BROKER_INSTANCE = '<service_broker_guid of NotificationDB>',

ADDRESS = 'tcp://localhost:5554'

GO

W/o this route, acknowledgements for messages cannot reach back to the sender, so the sender keeps retrying the same message again and again.

And remove the [ExpressServiceRoute] route from the target [test] database, is not needed.

HTH,
~ Remus

|||

Thank you very much!

I knew I was missing routing information but I simply did not figure out that the destination of (ack)messages from the ExpressService could be. Wasn't clear to me that there is a service

'http://schemas.microsoft.com/SQL/Notifications/EventNotificationService'

Guess I simply missed that in the Eventnotification sample in BOL?

With best regards,

Artus