[Event Notifications] adding a new box to your environment

Short and sweet - just realized I don't already have this on here.  This makes several assumptions in my environment, and you can modify as needed . Yes, it's ugly.  Yes, security holes due to xp_cmdshell and the like.  You've been warned.

Practically: you run this on the box that already has the repository.  You tell it the name of the new server.  It connects to that server, makes sure each side can reach the other, then enables Event Notifications on that new server.  It ends by making a change, waiting several seconds, then selecting from your repository table.  If it shows, you're golden.  If not, time to roll up sleeves and figure it out.

	--reminder, this runs on the central monitorING box, and uses xp_cmdshell and the like to connect to the "new" box.
	/*
	Event Notification - add server to monitoring. Probably simpler to do by hand, but I like this.
	See http://thebakingdba.blogspot.com/ for more information, look for Event Notifications
	Given a parameter of servername, WHEN RUN ON THE EN "repository", it will
	a) try to determine the servername for the "sender"
	b) get the service_broker_guid from that server for MSDB
	c) get the service_broker_guid from the local host
	d) create a return route on the repository for that host
	e) on the far side, build the endpoint, route, and event notification, and send a test record
	f) verify we are receiving that message
	Please read Version Info for potential issues.
	2012/08/28 mdb - version 1.0
	2012/11/15 mdb - 1.1, deals with machines that already have service broker by using the existing TCP endpoint; more comments
	2013/01/22 mdb - 1.11 fix for 1.1; only SB machines would have worked.
	2013/02/12 mdb - 1.2 adding "test" vs "real" flag to actually deploy, or just tell you what to do; adding authentication perms
	Permissions code from Arshad Ali @ http://www.databasejournal.com/features/mssql/understanding-sql-server-service-broker-authentication.html
	Service code name from Namwar Rizvi http://sqltips.wordpress.com/2007/08/06/retrieve-sql-server-service-account-name-from-registry-through-tsql/
	Please note BOTH MACHINES MUST BE IN THE SAME DOMAIN or there must be trust between the domains, otherwise you can't send messages.
	Also assumes that the service account IS A DOMAIN ACCOUNT.
	2013/02/27 mdb - 1.21 more perms work; if not the same account, then create necessary Login and Grant Connect. Assumes
	2013/03/20 mdb - 1.22 fixed propertyvalue code, which showed that some events weren't being added properly.
	2013/03/21 mdb - 1.25 lots of cleanups. Ping test from Watched Server, checking for Local Auth, etc.
	2013/03/26 mdb - 1.26 added "UPDATE" to fix any problems, found bug with version info. Fixed.
	2014/04/01 mdb - 1.27 added code to deal with AO & fix bug; these days there can be more than one tcp endpoint.
	2014/07/01 mdb - 1.28 adding code
	2014/09/11 mdb - 1.29 adding new group for SQL Server 2014.
	2015/03/30 mdb - 1.30 removing the new group, since it's actually already included under security, so it throws a "dupe" error.
	--note that because we're running XP_cmdshell to ping far side servers, connections must actually work. Trusted for delegation and kerberos and the like
	*/
	/*
	Troubleshooting Service Broker Conversations
	http://rusanu.com/2005/12/20/troubleshooting-dialogs/
	http://rusanu.com/2007/11/28/troubleshooting-dialogs-the-sequel/ (ssbdiagnose)
	http://blog.maskalik.com/sql-server-service-broker/troubleshooting-external-activation
	*/
	--run this code on the server that is your EN repository
	use eventnotificationrec
	go
	declare @servername sysname,
	@distinct_servername sysname,
	@hostname sysname,
	@sql nvarchar(2000),
	@cmd nvarchar(2000),
	@local_guid varchar(36),
	@new_source_GUID varchar(36),
	@port varchar(6),
	@productversion VARCHAR(20),
	@existing_port varchar(6),
	@en_repository_server varchar(20) --name of the server that will receive all the data
	, @deploy VARCHAR(10)
	, @do_permissions BIT
	, @repository_service_name VARCHAR(100)
	, @watched_server_service_name VARCHAR(100)
	if object_id('tempdb..#get_new_server_info') is not null
	DROP TABLE #get_new_server_info
	CREATE TABLE #get_new_server_info (id INT IDENTITY, [OUTPUT] varchar(255))
	SET @en_repository_server = @@SERVERNAME--'WIN-NDTAUPQET7L'
	set @servername = 'newservername' --the server you're adding
	SET @deploy = 'update' --REAL to actually run all the commands; otherwise it just prints out statements.
	--UPDATE to update the rules on boxes, using the current strings. Does NOT change Endpoints, etc.
	--FAKE otherwise
	SET @do_permissions = 1 --1 to do them, 0 to skip. Only needed if service accounts between Repository & Watched Server are different. Code will grab logins for both, and compare.
	--default is 0 because INSTANCES DON"T CURRENTLY WORK. If no instances, 1 is fine. Will fix, but need this now. Probably use a WMI call to get all the instance names.
	-- either way, we check the default instance's name, because we don't want to put it on a server with local authentication
	--------------
	--PING TEST - make sure the watched server can connect to the repository. For one-way routing issues.
	--REQUIRES XP_CMDSHELL be enabled on the watched server; need to add code to turn it on/off as needed
	--------------
	SET @sql = 'xp_cmdshell ''ping ' + @en_repository_server + ''''
	SET @cmd = 'sqlcmd -Q "' + @sql + '" -E -d master -S ' + @servername
	PRINT @cmd
	INSERT INTO #get_new_server_info
	EXEC xp_cmdshell @cmd
	IF (SELECT SUM (CHARINDEX('(0% loss)',[OUTPUT])) FROM #get_new_server_info) >0
	BEGIN
	PRINT 'ping test successful - can reach server'
	END
	ELSE
	BEGIN
	PRINT 'ping test failed - cannot connect! (xp_cmdshell off? login failed?) Setting @deploy to FAKE so it doesn''t run commands'
	IF @deploy = 'real'
	BEGIN
	SET @deploy = 'fake'
	END
	SELECT * FROM #get_new_server_info
	END
	TRUNCATE TABLE #get_new_server_info
	--Retrieve the Service accounts from registry. The person running the script must have Windows admin rights on the box(?)
	IF @deploy = 'real' --changed from checking do_permissions, since we need this to make sure it will succeed
	BEGIN
	--get local SQL service's name; needed so we know whether we're adding perms
	EXECUTE master.dbo.xp_instance_regread
	N'HKEY_LOCAL_MACHINE',
	N'SYSTEM\CurrentControlSet\Services\MSSQLSERVER',
	N'ObjectName',
	@repository_service_name OUTPUT,
	N'no_output'
	--get the service name for the Watched server; if different network, probably won't work.
	-- if same domain, make sure it's given perms.
	select @cmd = 'sqlcmd -Q "declare @repository_service_name varchar(100); EXECUTE master.dbo.xp_instance_regread N''HKEY_LOCAL_MACHINE'', N''SYSTEM\CurrentControlSet\Services\MSSQLSERVER'', N''ObjectName'', @repository_service_name OUTPUT, N''no_output''; select @repository_service_name" -E -S ' + @servername
	INSERT INTO #get_new_server_info
	EXEC xp_cmdshell @cmd
	SELECT @watched_server_service_name = output FROM #get_new_server_info
	WHERE OUTPUT NOT LIKE '(%'
	AND OUTPUT NOT LIKE '--%'
	AND OUTPUT IS NOT NULL
	AND (LTRIM(RTRIM(output))) <> ''
	TRUNCATE TABLE #get_new_server_info
	END
	--come up with a port for the server; use 4022 by default, 4023 if it's an instance.
	if charindex('\',@servername) = 0
	begin
	select @port='4022'
	end
	else
	begin
	select @port = '4023'
	end --need code for more than 1 named instance on a server
	--we need three different names - the service name, a unique name for the route, and the hostname for tcpip
	select @distinct_servername = replace(@servername,'\','__'),
	@hostname = case charindex('\',@servername)
	when 0 then @servername
	when null then @servername
	else left(@servername,charindex('\',@servername)-1)
	end
	--get our Service Broker's GUID so we can add it to their route.
	select @local_guid = service_broker_guid from sys.databases where name = 'EventNotificationRec'
	--trying to get the GUID for the far-side server. 3rd row should be it, but since it's unordered we're doing it this way
	select @cmd = 'sqlcmd -Q "SELECT service_broker_guid FROM sys.databases WHERE name = ''msdb''" -E -S ' + @servername
	INSERT INTO #get_new_server_info
	EXEC xp_cmdshell @cmd
	SELECT @new_source_GUID = [output] FROM #get_new_server_info where [output] like '%-%-%-%-%' and [output] not like '%--%'
	--getting the productversion, so we can add/remove EN groups as needed
	TRUNCATE TABLE #get_new_server_info
	select @cmd = 'sqlcmd -Q "SELECT SERVERPROPERTY(''productversion'')" -E -S ' + @servername
	INSERT INTO #get_new_server_info
	EXEC xp_cmdshell @cmd
	SELECT @productversion = [OUTPUT] FROM #get_new_server_info WHERE ISNUMERIC(LEFT(output,3))= 1
	--last chance to set to fake before we start adding things.
	IF @watched_server_service_name IN ('NT AUTHORITY\NetworkService', 'LocalSystem')
	BEGIN
	PRINT 'Warning - Watched Service is using local authentication.
	Unless you have granted public access, this will not work!
	setting @deploy = ''fake'''
	SET @deploy = 'fake'
	END
	---------------------------------------------------------
	--Find port if TCP endpoint already exists on machine. --
	-- If it does, use it (there can be only one). --
	-- For machines with cross-server SB already in use. --
	-- If not, use the assigned @port --
	---------------------------------------------------------
	select @cmd = 'sqlcmd -Q "set nocount on;SELECT port FROM master.sys.tcp_endpoints where port>0 and type_desc = ''SERVICE_BROKER''" -E -S ' + @servername
	TRUNCATE TABLE #get_new_server_info
	INSERT INTO #get_new_server_info
	EXEC xp_cmdshell @cmd
	SELECT @existing_port = LTRIM(RTRIM([OUTPUT])) FROM #get_new_server_info
	WHERE [OUTPUT] NOT IN ('port ','-----------')
	AND [OUTPUT] IS NOT NULL
	DROP TABLE #get_new_server_info
	--create route on the Repository server first. If new server already has an endpoint, use that, otherwise use the @port
	IF NOT EXISTS(SELECT * FROM sys.routes WHERE name = 'ENAudit_ReturnRoute_' + @distinct_servername)
	BEGIN
	select @sql = 'CREATE ROUTE [ENAudit_ReturnRoute_' + @distinct_servername + ']
	WITH SERVICE_NAME = ''http://schemas.microsoft.com/SQL/Notifications/EventNotificationService'',
	BROKER_INSTANCE = ''' + @new_source_guid + ''',
	ADDRESS = ''TCP://' + @hostname + ':' + COALESCE(@existing_port,@port) + ''';'
	PRINT @sql
	IF @deploy ='REAL'
	BEGIN
	exec sp_executesql @sql --this one runs locally, so we use sp_executesql; WORKS and has been done
	END
	END
	----------------------------------
	--Setup remote "sender" endpoint--
	--uses SQLCMD --
	----------------------------------
	--mdb 2012/01/22 adding ISNULL for @existing_port; prevented NON-service-broker DBs from getting an endpoint. Rookie mistake.
	--mdb 2013/02/05 still not working right with existing SB servers
	--IF @port <> ISNULL(@existing_port,0)
	IF ISNULL(@existing_port,0) = 0 --if there is no existing SB
	BEGIN
	select @sql = null, @cmd = null
	select @sql = 'CREATE ENDPOINT ENAudit_Endpoint STATE = STARTED '
	+ 'AS TCP (LISTENER_PORT = ' + @port + ') '
	+ 'FOR SERVICE_BROKER (AUTHENTICATION = WINDOWS,ENCRYPTION = SUPPORTED)'
	select @cmd = 'sqlcmd -Q "' + @sql + '" -E -d master -S ' + @servername
	PRINT @cmd
	IF @deploy ='REAL'
	BEGIN
	exec xp_cmdshell @cmd
	END
	--------------------------------
	-- Grant login and connect to the foreign endpoint, so they can converse.
	-- Code assumes you're using domain accounts, not machine accounts.
	-- In order to avoid certificates, both must be same domain or domains must be trusted by each other.
	-- If your machines use the same service account, it will work without this.
	-- If the service names are not the same between the Watched & Repository server,
	-- the Repository needs connect perms to the the Watched server & endpoint,
	-- and the Watched service's login needs connect perms to the Repository.
	-- More info at http://msdn.microsoft.com/en-us/library/ms166065(v=sql.90).aspx
	--------------------------------
	IF @do_permissions = 1 AND @repository_service_name <> @watched_server_service_name
	AND @repository_service_name IS NOT NULL AND @watched_server_service_name IS NOT null
	AND CHARINDEX('/', @repository_service_name)>0 AND CHARINDEX('/', @watched_server_service_name)>0 --make sure they are domain accounts
	AND @watched_server_service_name <> 'NT AUTHORITY\NetworkService'
	BEGIN
	PRINT 'updating/adding perms'
	select @sql = null, @cmd = null
	--first do the far side
	select @sql = 'create login [' + @repository_service_name + '] from WINDOWS;'
	select @cmd = 'sqlcmd -Q "' + @sql + '" -E -d master -S ' + @servername
	PRINT @cmd
	IF @deploy ='REAL'
	BEGIN
	exec xp_cmdshell @cmd
	END
	select @sql = 'grant connect on endpoint::ENAudit_Endpoint to [' + @repository_service_name + ']'
	select @cmd = 'sqlcmd -Q "' + @sql + '" -E -d master -S ' + @servername
	PRINT @cmd
	IF @deploy ='REAL'
	BEGIN
	exec xp_cmdshell @cmd
	END
	--Now the Repository
	select @sql = 'create login [' + @watched_server_service_name + '] from WINDOWS;'
	PRINT @sql
	IF @deploy ='REAL'
	BEGIN
	exec sp_executesql @sql --this one runs locally, so we use sp_executesql; WORKS and has been done
	END
	select @sql = 'grant connect on endpoint::ENAudit_Endpoint to [' + @watched_server_service_name + ']'
	PRINT @sql
	IF @deploy ='REAL'
	BEGIN
	exec sp_executesql @sql --this one runs locally, so we use sp_executesql; WORKS and has been done
	END
	END
	END
	-----------------------------------
	-- Set up the Route in MSDB, as --
	-- it has SB enabled by default --
	-----------------------------------
	select @sql = null, @cmd = null
	select @sql = 'CREATE ROUTE [ENAudit_DestinationRoute]'
	+' WITH SERVICE_NAME = ''ENAudit_Service'','
	+' BROKER_INSTANCE = ''' + @local_guid + ''' , '
	+' ADDRESS = ''TCP://' + @en_repository_server + ':4022'';' --that's the listener port on the _Repository_ box
	select @cmd = 'sqlcmd -Q "' + @sql + '" -E -d msdb -S ' + @servername
	PRINT @cmd
	IF @deploy ='REAL'
	BEGIN
	exec xp_cmdshell @cmd --this one runs locally, so we use sp_executesql; WORKS and has been done
	END
	-----------------------------
	-- Now create the actual Event Notification.
	-- Theoretically you can create this using the system tables, but it's different
	-- between versions of SQL and so this is easier and only has to be updated each major SQL release.
	-----------------------------
	select @sql = null, @cmd = null
	--if we're using UPDATE, drop the old one so we can add the new one.
	select @sql = 'DROP EVENT NOTIFICATION [ENAudit_ServerDDLEvents] ON SERVER'
	select @cmd = 'sqlcmd -Q "' + @sql + '" -E -d msdb -S ' + @servername
	PRINT @cmd
	IF @deploy ='Update'
	BEGIN
	exec xp_cmdshell @cmd --this one runs locally, so we use sp_executesql; WORKS and has been done
	END
	select @sql = null, @cmd = null
	--You could just do DDL_SERVER_LEVEL_EVENTS and ALTER_SERVER_CONFIGURATION, but that includes stats.
	--full list of EN groups: http://msdn.microsoft.com/en-us/library/ms180824(v=sql.90).aspx
	--can also add a variety of traces: see http://msdn.microsoft.com/en-us/library/ms187476(v=sql.105).aspx
	select @sql = 'CREATE EVENT NOTIFICATION [ENAudit_ServerDDLEvents] '
	+'ON SERVER '
	--If you want _everything_ (including statistics!), just use the below line. Simpler to type, but more impact.
	-- You could filter them via the enaudit_exclusion_list, but it's still more impact on the initiator;
	-- doubly so if you add the TRC events since they include stats as well.
	--+ 'FOR DDL_EVENTS, ALTER_SERVER_CONFIGURATION '
	-- Note that sp_rename doesn't fire EN!
	--using the chart http://msdn.microsoft.com/en-us/library/ms180824(v=sql.90).aspx which is 2005,
	-- but there's a query to get them all (note it changes each edition): http://msdn.microsoft.com/en-us/library/bb510452(v=sql.105).aspx
	/*
	--for a to-be-written dynamic creation script. Current version works on 2005/2008/r2/2012.
	--Take this query, run it against the sender in order to get all the server-level events.
	-- Exclude DDL_TABLE_VIEW_EVENTS, then add all the subcomponents for DDL_TABLE_VIEW_EVENTS except STATISTICS
	WITH DirectReports(name, parent_type, type, level, sort, type_name) AS
	(
	SELECT CONVERT(varchar(255),type_name), parent_type, type, 1, CONVERT(varchar(255),type_name), type_name
	FROM sys.trigger_event_types
	WHERE parent_type IS NULL
	UNION ALL
	SELECT CONVERT(varchar(255), REPLICATE ('| ' , level) + e.type_name),
	e.parent_type, e.type, level + 1,
	CONVERT (varchar(255), RTRIM(sort) + '| ' + e.type_name),
	e.type_name
	FROM sys.trigger_event_types AS e
	INNER JOIN DirectReports AS d
	ON e.parent_type = d.type
	)
	SELECT parent_type, level, type, name, type_name
	FROM DirectReports
	ORDER BY sort;
	*/
	--Server-level events, including config; SQL Server 2005 and above
	+'FOR DDL_SERVER_LEVEL_EVENTS,'
	--DDL_TABLE_VIEW_EVENTS, minus Statistics which are constantly updated and constantly created, even on temp objects created by 3rd party tools
	+'DDL_TABLE_EVENTS,DDL_VIEW_EVENTS,DDL_INDEX_EVENTS,'
	--and the rest of the DDL_DATABASE_LEVEL_EVENTS
	+'DDL_SYNONYM_EVENTS,DDL_FUNCTION_EVENTS,DDL_PROCEDURE_EVENTS,'
	+'DDL_TRIGGER_EVENTS,DDL_EVENT_NOTIFICATION_EVENTS,DDL_ASSEMBLY_EVENTS,'
	+'DDL_TYPE_EVENTS,DDL_DATABASE_SECURITY_EVENTS,DDL_SSB_EVENTS,'
	+'DDL_XML_SCHEMA_COLLECTION_EVENTS,DDL_PARTITION_EVENTS'
	--and the events added in SQL Server 2008
	IF @productversion NOT LIKE '9.%'
	SET @sql = @sql +
	+',DDL_PLAN_GUIDE_EVENTS,DDL_DEFAULT_EVENTS,DDL_EXTENDED_PROPERTY_EVENTS'
	+',DDL_FULLTEXT_CATALOG_EVENTS,DDL_FULLTEXT_STOPLIST_EVENTS'
	+',DDL_RULE_EVENTS,RENAME'
	--dupes because they are sub-events of ddl_server_level_events
	--+',ALTER_INSTANCE,DDL_DATABASE_EVENTS'
	--+',DDL_ENDPOINT_EVENTS,DDL_EVENT_SESSION_EVENTS,DDL_EXTENDED_PROCEDURE_EVENTS'
	--+',DDL_LINKED_SERVER_EVENTS,DDL_MESSAGE_EVENTS,DDL_REMOTE_SERVER_EVENTS'
	--+',DDL_RESOURCE_GOVERNOR_EVENTS,DDL_SERVER_SECURITY_EVENTS'
	--and the 1 event added in SQL Server 2008 R2; using NOT LIKE to auto-include future versions
	IF @productversion NOT LIKE '9.%' AND @productversion NOT LIKE '10.0%'
	SET @sql = @sql + ',ALTER_SERVER_CONFIGURATION'
	--and the events added in SQL Server 2012; using NOT LIKE to auto-include future versions
	IF @productversion NOT LIKE '9.%' AND @productversion NOT LIKE '10%'
	SET @sql = @sql + ',DDL_SEARCH_PROPERTY_LIST_EVENTS,DDL_SEQUENCE_EVENTS'
	--and now 2014! Only one this time, DDL_DATABASE_AUDIT_EVENTS (oh, the irony); using NOT LIKE to auto-include future versions
	--not needed - database audit events fall under the security group, which is already added.
	--IF @productversion NOT LIKE '9.%' AND @productversion NOT LIKE '10%' AND @productversion NOT LIKE '11%'
	--SET @sql = @sql + ',DDL_DATABASE_AUDIT_EVENTS '
	--trace events - TRC_OBJECTS is just alter/create/drop, added since they have HOSTNAME
	SET @sql = @sql
	+ ',TRC_OBJECTS, ERRORLOG' --EVENTLOG ,TRC_DEPRECATION,.... Deprecation at your own risk. Be CAREFUL.
	+' TO SERVICE ''ENAudit_Service'', ''' + @local_guid + ''';' --central dbid for EventNotification_Receiver
	select @cmd = 'sqlcmd -Q "' + @sql + '" -E -d msdb -S ' + @servername
	PRINT @cmd
	IF @deploy IN ('REAL','UPDATE')
	BEGIN
	exec xp_cmdshell @cmd --this one runs locally, so we use sp_executesql; WORKS and has been done
	END
	WAITFOR DELAY '00:00:05'
	---------------------------------
	--create and drop test objects --
	-- and ensure it's working. --
	---------------------------------
	select @sql = 'create table tempdb.dbo.test_for_audit (field_a int)'
	select @cmd = 'sqlcmd -Q "' + @sql + '" -E -S ' + @servername
	PRINT @cmd
	IF @deploy IN ('REAL','UPDATE')
	BEGIN
	exec xp_cmdshell @cmd --this one runs locally, so we use sp_executesql; WORKS and has been done
	END
	select @sql = 'drop table tempdb.dbo.test_for_audit'
	select @cmd = 'sqlcmd -Q "' + @sql + '" -E -S ' + @servername
	IF @deploy IN ('REAL','UPDATE')
	BEGIN
	exec xp_cmdshell @cmd --this one runs locally, so we use sp_executesql; WORKS and has been done
	END
	ELSE
	BEGIN
	PRINT @cmd
	END
	IF @deploy IN ('REAL','UPDATE')
	BEGIN
	WAITFOR DELAY '00:00:10'
	select top 100 * from ENAudit_Events WHERE PostTime > GETDATE()-.01 AND ServerName = @servername
	END
	/*
	--sqlcmd -Q "CREATE EVENT NOTIFICATION [ENAudit_ServerDDLEvents] ON SERVER FOR DDL_SERVER_LEVEL_EVENTS,DDL_TABLE_EVENTS,DDL_VIEW_EVENTS,DDL_INDEX_EVENTS,DDL_SYNONYM_EVENTS,DDL_FUNCTION_EVENTS,DDL_PROCEDURE_EVENTS,DDL_TRIGGER_EVENTS,DDL_EVENT_NOTIFICATION_EVENTS,DDL_ASSEMBLY_EVENTS,DDL_TYPE_EVENTS,DDL_DATABASE_SECURITY_EVENTS,DDL_SSB_EVENTS,DDL_XML_SCHEMA_COLLECTION_EVENTS,DDL_PARTITION_EVENTS,TRC_OBJECTS, ERRORLOG TO SERVICE 'ENAudit_Service', '5E885B6D-D24D-4FEB-8E64-0F8D1DE16905';" -E -d msdb -S myservera
	--sqlcmd -Q "CREATE EVENT NOTIFICATION [ENAudit_ServerDDLEvents] ON SERVER FOR DDL_SERVER_LEVEL_EVENTS,DDL_TABLE_EVENTS,DDL_VIEW_EVENTS,DDL_INDEX_EVENTS,DDL_SYNONYM_EVENTS,DDL_FUNCTION_EVENTS,DDL_PROCEDURE_EVENTS,DDL_TRIGGER_EVENTS,DDL_EVENT_NOTIFICATION_EVENTS,DDL_ASSEMBLY_EVENTS,DDL_TYPE_EVENTS,DDL_DATABASE_SECURITY_EVENTS,DDL_SSB_EVENTS,DDL_XML_SCHEMA_COLLECTION_EVENTS,DDL_PARTITION_EVENTS,TRC_OBJECTS, ERRORLOG TO SERVICE 'ENAudit_Service', '5E885B6D-D24D-4FEB-8E64-0F8D1DE16905';" -E -d msdb -S myserverb
	THEY MATCH. So we can issue the same command and sync the events of each server.
	sqlcmd -Q "drop EVENT NOTIFICATION [ENAudit_ServerDDLEvents] ON SERVER" -E -d msdb -S myserverb
	sqlcmd -Q "CREATE EVENT NOTIFICATION [ENAudit_ServerDDLEvents] ON SERVER FOR DDL_SERVER_LEVEL_EVENTS,DDL_TABLE_EVENTS,DDL_VIEW_EVENTS,DDL_INDEX_EVENTS,DDL_SYNONYM_EVENTS,DDL_FUNCTION_EVENTS,DDL_PROCEDURE_EVENTS,DDL_TRIGGER_EVENTS,DDL_EVENT_NOTIFICATION_EVENTS,DDL_ASSEMBLY_EVENTS,DDL_TYPE_EVENTS,DDL_DATABASE_SECURITY_EVENTS,DDL_SSB_EVENTS,DDL_XML_SCHEMA_COLLECTION_EVENTS,DDL_PARTITION_EVENTS,TRC_OBJECTS, ERRORLOG TO SERVICE 'ENAudit_Service', '5E885B6D-D24D-4FEB-8E64-0F8D1DE16905';" -E -d msdb -S myserverb
	backup database model to disk ='nul' --testing errorlog
	sqlcmd -Q "SELECT service_broker_guid FROM sys.databases WHERE name = 'msdb'" -E -S VM2
	8B85664B-24AB-4B5A-ABAA-0AE5301D4E2D
	CREATE ROUTE [ENAudit_ReturnRoute_VM2]
	WITH SERVICE_NAME = 'http://schemas.microsoft.com/SQL/Notifications/EventNotificationService',
	BROKER_INSTANCE = '8B85664B-24AB-4B5A-ABAA-0AE5301D4E2D',
	ADDRESS = 'TCP://VM2:4022';
	sqlcmd -Q "CREATE ENDPOINT ENAudit_Endpoint STATE = STARTED AS TCP (LISTENER_PORT = 4022) FOR SERVICE_BROKER (AUTHENTICATION = WINDOWS,ENCRYPTION = SUPPORTED)" -E -d master -S VM2
	sqlcmd -Q "CREATE ROUTE [ENAudit_DestinationRoute] WITH SERVICE_NAME = 'ENAudit_Service', BROKER_INSTANCE = '9CF39735-7E60-490F-92B6-26EBFEC19F5E' , ADDRESS = 'TCP://WIN-NDTAUPQET7L:4022';" -E -d msdb -S VM2
	sqlcmd -Q "CREATE EVENT NOTIFICATION [ENAudit_ServerDDLEvents] ON SERVER FOR DDL_SERVER_LEVEL_EVENTS,DDL_TABLE_EVENTS,DDL_VIEW_EVENTS,DDL_INDEX_EVENTS,DDL_SYNONYM_EVENTS,DDL_FUNCTION_EVENTS,DDL_PROCEDURE_EVENTS,DDL_TRIGGER_EVENTS,DDL_EVENT_NOTIFICATION_EVENTS,DDL_ASSEMBLY_EVENTS,DDL_TYPE_EVENTS,DDL_DATABASE_SECURITY_EVENTS,DDL_SSB_EVENTS,DDL_XML_SCHEMA_COLLECTION_EVENTS,DDL_PARTITION_EVENTS,TRC_OBJECTS TO SERVICE 'ENAudit_Service', '9CF39735-7E60-490F-92B6-26EBFEC19F5E';" -E -d msdb -S VM2
	-------------demo cleanup------------
	--on vm2:
	use master
	go
	drop event notification [ENAudit_ServerDDLEvents] ON SERVER
	go
	use msdb
	go
	drop route [ENAudit_DestinationRoute]
	go
	drop endpoint ENAudit_Endpoint
	-----------------------------------
	------ON LOCAL
	use EventNotificationRec
	drop route [ENAudit_ReturnRoute_VM2]
	*/

view raw EN_add_new_server.sql hosted with ❤ by GitHub