28 November 2018

Database Drift – Monitoring Schema Changes

by SQL Monitor Team

3,724 0 Security

Metrics install automatically if you have Redgate Monitor installed.

If you are using Redgate’s SQL Server monitoring tool, Redgate Monitor, you can instantly install and run this metric on your servers.

Reports the number database object changes (create, alter or drop) in last 30 mins.

First, create the following Extended Event session on the monitored SQL Server instance:

IF EXISTS --if the session already exists, then delete it. We are assuming you've changed something
    (
    SELECT *
      FROM sys.server_event_sessions
      WHERE server_event_sessions.name = 'WhoChangedWhat'
    )
    DROP EVENT SESSION WhoChangedWhat ON SERVER;
  GO 

--create a new session. you can change a session with the ALTER syntax. This
  -- preserves the data already collected. In this case we don't need the old data
  -- All these EVENT classes we've chosen occur as two events. The first event 
  -- indicates the Begin phase. The second event indicates the Rollback or Commit phase.
  -- we just want the commit phase.
  CREATE EVENT SESSION [WhoChangedWhat] ON SERVER 
  -- Object Altered. The Object:Altered event class indicates that an object has been
  -- altered; for example, by an ALTER INDEX, ALTER TABLE, or ALTER DATABASE statement.
  ADD EVENT sqlserver.object_altered(SET collect_database_name=(1)--we need this name
      ACTION(sqlserver.client_app_name,sqlserver.server_principal_name,
             sqlserver.session_id,sqlserver.sql_text)--we definitely want this data
      WHERE ([package0].[equal_uint64]([ddl_phase],(1)) 
        AND [database_name]<>N'tempdb' AND [database_name]<>N'master' 
        AND ([package0].[not_equal_uint64]([object_type],(21587))))),--no STATISTICS
  --The Object:Created event class indicates that an object has been created, for 
  --example, by the CREATE INDEX, CREATE TABLE, or CREATE DATABASE statements.
  ADD EVENT sqlserver.object_created(SET collect_database_name=(1)
      ACTION(sqlserver.client_app_name,sqlserver.server_principal_name,
             sqlserver.session_id,sqlserver.sql_text)
      WHERE ([package0].[equal_uint64]([ddl_phase],(1)) 
        AND [database_name]<>N'tempdb' AND [database_name]<>N'master' 
        AND ([package0].[not_equal_uint64]([object_type],(21587))))),--no STATISTICS
  --The Object:Deleted event class indicates that an object has been deleted 
  ADD EVENT sqlserver.object_deleted(SET collect_database_name=(1)
      ACTION(sqlserver.client_app_name,sqlserver.server_principal_name,
             sqlserver.session_id,sqlserver.sql_text)
      WHERE ([package0].[equal_uint64]([ddl_phase],(1)) 
        AND [database_name]<>N'tempdb' AND [database_name]<>N'master'
        AND ([package0].[not_equal_uint64]([object_type],(21587)))))--no STATISTICS
  --we specify a ring-buffer target
  ADD TARGET package0.ring_buffer
  WITH (MAX_MEMORY=4096 KB,EVENT_RETENTION_MODE=ALLOW_SINGLE_EVENT_LOSS,
        MAX_DISPATCH_LATENCY=30 SECONDS,MAX_EVENT_SIZE=0 KB,
        MEMORY_PARTITION_MODE=NONE,TRACK_CAUSALITY=OFF,STARTUP_STATE=ON)
  GO
 --and now we start the session 
ALTER EVENT SESSION WhoChangedWhat ON SERVER STATE = START;

IF EXISTS --if the session already exists, then delete it. We are assuming you've changed something

(

SELECT *

FROM sys.server_event_sessions

WHERE server_event_sessions.name = 'WhoChangedWhat'

)

DROP EVENT SESSION WhoChangedWhat ON SERVER;

--create a new session. you can change a session with the ALTER syntax. This

-- preserves the data already collected. In this case we don't need the old data

-- All these EVENT classes we've chosen occur as two events. The first event

-- indicates the Begin phase. The second event indicates the Rollback or Commit phase.

-- we just want the commit phase.

CREATE EVENT SESSION [WhoChangedWhat] ON SERVER

-- Object Altered. The Object:Altered event class indicates that an object has been

-- altered; for example, by an ALTER INDEX, ALTER TABLE, or ALTER DATABASE statement.

ADD EVENT sqlserver.object_altered(SET collect_database_name=(1)--we need this name

ACTION(sqlserver.client_app_name,sqlserver.server_principal_name,

sqlserver.session_id,sqlserver.sql_text)--we definitely want this data

WHERE ([package0].[equal_uint64]([ddl_phase],(1))

AND [database_name]<>N'tempdb' AND [database_name]<>N'master'

AND ([package0].[not_equal_uint64]([object_type],(21587))))),--no STATISTICS

--The Object:Created event class indicates that an object has been created, for

--example, by the CREATE INDEX, CREATE TABLE, or CREATE DATABASE statements.

ADD EVENT sqlserver.object_created(SET collect_database_name=(1)

ACTION(sqlserver.client_app_name,sqlserver.server_principal_name,

sqlserver.session_id,sqlserver.sql_text)

WHERE ([package0].[equal_uint64]([ddl_phase],(1))

AND [database_name]<>N'tempdb' AND [database_name]<>N'master'

AND ([package0].[not_equal_uint64]([object_type],(21587))))),--no STATISTICS

--The Object:Deleted event class indicates that an object has been deleted

ADD EVENT sqlserver.object_deleted(SET collect_database_name=(1)

ACTION(sqlserver.client_app_name,sqlserver.server_principal_name,

sqlserver.session_id,sqlserver.sql_text)

WHERE ([package0].[equal_uint64]([ddl_phase],(1))

AND [database_name]<>N'tempdb' AND [database_name]<>N'master'

AND ([package0].[not_equal_uint64]([object_type],(21587)))))--no STATISTICS

--we specify a ring-buffer target

ADD TARGET package0.ring_buffer

WITH (MAX_MEMORY=4096 KB,EVENT_RETENTION_MODE=ALLOW_SINGLE_EVENT_LOSS,

MAX_DISPATCH_LATENCY=30 SECONDS,MAX_EVENT_SIZE=0 KB,

MEMORY_PARTITION_MODE=NONE,TRACK_CAUSALITY=OFF,STARTUP_STATE=ON)

--and now we start the session

ALTER EVENT SESSION WhoChangedWhat ON SERVER STATE = START;

Metric definition

Name

ObjectsChanged

Description

Reports the number database object changes (create, alter or drop) in last 30 mins.

The T-SQL query that will collect data


		
			
			
Transact-SQL
			
			SELECT Count(*) AS ObjectsAltered
  FROM sys.dm_xe_session_targets AS xet
    INNER JOIN sys.dm_xe_sessions AS xes
      ON xes.address = xet.event_session_address
    CROSS APPLY
    (SELECT Cast(xet.target_data AS XML)) AS target_data_xml(xml)
    CROSS APPLY target_data_xml.xml.nodes('//RingBufferTarget/event') AS xed(event_data)
  WHERE xes.name = 'WhoChangedWhat'
    AND xet.target_name = 'ring_buffer'
    AND DateDiff
      (MINUTE,
      Convert(DATETIME2,
              SwitchOffset(
Convert(DATETIMEOFFSET,xed.event_data.value('(@timestamp)[1]', 'datetime2')),
                DateName(TzOffset,SysDateTimeOffset()))
            ),
      GetDate()) < 30; --metadata changes within the last thirty minutes
			
				
					
				
					1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
				
						SELECT Count(*) AS ObjectsAltered
  FROM sys.dm_xe_session_targets AS xet
    INNER JOIN sys.dm_xe_sessions AS xes
      ON xes.address = xet.event_session_address
    CROSS APPLY
    (SELECT Cast(xet.target_data AS XML)) AS target_data_xml(xml)
    CROSS APPLY target_data_xml.xml.nodes('//RingBufferTarget/event') AS xed(event_data)
  WHERE xes.name = 'WhoChangedWhat'
    AND xet.target_name = 'ring_buffer'
    AND DateDiff
      (MINUTE,
      Convert(DATETIME2,
              SwitchOffset(
Convert(DATETIMEOFFSET,xed.event_data.value('(@timestamp)[1]', 'datetime2')),
                DateName(TzOffset,SysDateTimeOffset()))
            ),
      GetDate()) < 30; --metadata changes within the last thirty minutes

Instances to collect from

Default

Databases to collect from

master

Collection frequency

1 min

Use collected or calculated values

Leave the Use a calculated rate of change between collections check box unchecked

Metric collection

Enabled

Alert definition

Alert name

Database Drift has occurred

Description

Someone made unauthorized changes to database objects..

Raise an alert when the metric value goes

Above the defined threshholds

Default threshold values

High:	1
Medium:
Low:

Raise an alert when the threshold is passed for

collections

Alert is

Enabled

3,724 Rate

Click to rate this post!

[Total: 0 Average: 0]

Database Drift – Monitoring Schema Changes

Metric definition

Name

Description

The T-SQL query that will collect data

Instances to collect from

Databases to collect from

Collection frequency

Use collected or calculated values

Metric collection

Alert definition

Alert name

Description

Raise an alert when the metric value goes

Default threshold values

Raise an alert when the threshold is passed for

Alert is

What are custom metrics?

What is Redgate Monitor?

Featured custom metrics

Total database file size

Number of active backups and restores

Full backup to odd path

Find metrics