Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I've been working on getting several servers to feed Event Notifications to a central server. Had everything working yesterday, then managed to break it. Dropping and recreating the endpoint/route/event notification on the "sending" server (testserver1 below) doesn't fix it.

The only changes I made were a slight modification to the stored procedure to not insert some events, and I added a trace event, TRC_DEPRECATION.

On the sending server, I see nothing in sys.transmission_queue.

On the receiving server, I see nothing in the SB queue, nor in sys.transmission_queue.

When I run a profile trace, I get the following message:

A previously existing connection with the same peer was found after DNS lookup. This connection will be closed. All traffic will be redirected to the previously existing connection. This is an informational message only. No user action is required. State 116.

Looking online, I see someone who had the exact same problem; his fix was to restart the service on the initiator. While doable, I'd rather try and solve the underlying issue. Thanks in advance.

Here's the ack route on the receiving (enrepository):

CREATE ROUTE [ENAudit_ReturnRoute_testserver1] 
WITH SERVICE_NAME = 'http://schemas.microsoft.com/SQL/Notifications/EventNotificationService',
BROKER_INSTANCE = '316C0E13-F1AD-4CFD-AA4E-006E8AD5EF3C', 
ADDRESS = 'TCP://testserver1:4022';

Here's the code on the sending server:

CREATE ENDPOINT ENAudit_Endpoint STATE = STARTED 
AS TCP (LISTENER_PORT = 4022) 
FOR SERVICE_BROKER (
    AUTHENTICATION = WINDOWS
    , ENCRYPTION = SUPPORTED
);

CREATE ROUTE [ENAudit_DestinationRoute] 
WITH SERVICE_NAME = 'AuditService'
, BROKER_INSTANCE = '5E885B6D-D24D-4FEB-8E64-0F8D1DE19605'
, ADDRESS = 'TCP://enrepository:4022';

CREATE EVENT NOTIFICATION [ENAudit_ServerDDLEvents] ON SERVER FOR 
    ALTER_SERVER_CONFIGURATION
    , DDL_SERVER_LEVEL_EVENTS
    , DDL_ASSEMBLY_EVENTS
    , DDL_DATABASE_SECURITY_EVENTS
    , DDL_EVENT_NOTIFICATION_EVENTS
    , DDL_FUNCTION_EVENTS
    , DDL_PARTITION_EVENTS
    , DDL_PROCEDURE_EVENTS
    , DDL_PLAN_GUIDE_EVENTS
    , DDL_SSB_EVENTS
    , DDL_SYNONYM_EVENTS
    , DDL_TRIGGER_EVENTS
    , DDL_TYPE_EVENTS
    , DDL_XML_SCHEMA_COLLECTION_EVENTS
    , DDL_INDEX_EVENTS
    , DDL_TABLE_EVENTS
    , DDL_VIEW_EVENTS
    , TRC_DEPRECATION 
TO SERVICE 'AuditService', '5E885B6D-D24D-4FEB-8E64-0F8D1DE19605';
share|improve this question
    
Have you explored this on Connect? They may have something there. –  swasheck Aug 30 '12 at 14:27
    
Tried, no luck. My thought was that I'm not dequeueing things properly, so using Remus Rusanu's code I've changed how I catch and handle messages. That doesn't fix it. Oddly, on a second machine it's sending just the TRC_DEPRECATION messages, though I'm trapping for a lot of others. –  mbourgon Aug 30 '12 at 14:58
1  
Unfortunately, I don't have a better solution, but dropping all the objects on both sides (queues, routes, endpoints) fixed it. While not optimal, it's a different solution than "Restart the service", which is the other solution I've seen. –  mbourgon Aug 31 '12 at 17:35
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Browse other questions tagged or ask your own question.