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';