Hello,

I have some questions on Service Broker settings:

1) Could you clarify the following settings for creating conversation
between two local databases?

In order to create such conversation you prescribe to set the TRUSTWORTHY
option to ON or to have certificates.

Actually, these settings are not required for creating conversation between
two remote databases.

What is the difference between these two cases?

2) Why do not these settings (TRUSTWORTHY option to ON or to have
certificates) are not required for Event Notification for to local
databases?

As I understand, Event Notification is similar to create conversation
action.

3) We have a problem with Event Notification for two remote databases - sent
messages stay at the transmission queue.

Could you possibly clarify the problem.

For example, I create the following Service Broker objects for Event
Notification.

Then I execute a notification action.

/*Run this script on the server #1*/

USE DB_2

CREATE QUEUE [dbo].[Quest_Event_Notification_Queue_w2k3-7_DB_2_1] WITH
STATUS = ON, RETENTION = OFF ON [DEFAULT]

/*Run this script on the server #1*/

USE DB_2

CREATE SERVICE [Quest_Event_Notification_Service_w2k3-7_DB_2_1]
AUTHORIZATION [dbo] ON QUEUE
[dbo].[Quest_Event_Notification_Queue_w2k3-7_DB_2_1] (
[http://schemas.microsoft.com/SQL/Notifications/PostEventNotification] )

/*Run this script on the server #2*/

USE DB_1

GRANT RECEIVE ON [dbo].[EventNotificationErrorsQueue] TO [dbo]

GRANT SEND ON
SERVICE::[http://schemas.microsoft.com/SQL/Notifications/EventNotificationService]
TO [public]

/*Run this script on the server #1*/

USE DB_2

GRANT RECEIVE ON [dbo].[Quest_Event_Notification_Queue_w2k3-7_DB_2_1] TO
[dbo]

GRANT SEND ON SERVICE::[Quest_Event_Notification_Service_w2k3-7_DB_2_1] TO
[public]

/*Run this script on the server #2*/

USE DB_1

CREATE ROUTE
[Quest_4efb1caf-622d-424f-8ae0-7e01019ed9a3_Quest_Event_Notification_Service_w2k3-7_DB_2_1]
WITH SERVICE_NAME = 'Quest_Event_Notification_Service_w2k3-7_DB_2_1',
BROKER_INSTANCE = '4EFB1CAF-622D-424F-8AE0-7E01019ED9A3', ADDRESS =
'TCP://W2K3-7:4022'

/*Run this script on the server #1*/

USE DB_2

CREATE ROUTE
[Quest_9f3a9be7-0d81-4cc7-bb86-8c2bfbc32c50_http://schemas.microsoft.com/SQL/Notifications/EventNotificationService]
WITH SERVICE_NAME =
'http://schemas.microsoft.com/SQL/Notifications/EventNotificationService',
BROKER_INSTANCE = '9F3A9BE7-0D81-4CC7-BB86-8C2BFBC32C50', ADDRESS =
'TCP://W2K3-6:4022'

/*Run this script on the server #2*/

USE DB_1

CREATE EVENT NOTIFICATION [EN] ON DATABASE FOR CREATE_TABLE, ALTER_TABLE,
DROP_TABLE TO SERVICE 'Quest_Event_Notification_Service_w2k3-7_DB_2_1'
,'4efb1caf-622d-424f-8ae0-7e01019ed9a3';

-- Then execute the notification action:

Use DB_1

create table Tab_1 (col1 int)

Result:

Notification message delivered to the target queue
[Quest_Event_Notification_Queue_w2k3-7_DB_2_1] BUT the same message still
existed in transmission queue with Empty status.

I tried to trace the process by SQL Profiler. It represented the following:

This message could not be delivered because another instance of this service
program has already started conversing with this endpoint.

Note: all the following messages for this event are not delivered to the
target queue. They all stayed in the transmission queue with empty status.
When I drop Event Notification, all messages from transmission queue are
delivered to the target queue.