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