Event Notifications - add new server

EN_add_new_server.sql

--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] 
*/