SQL Server: Three Common DDL Change Log Methods

Who is changing your objects (tables, views, stored procedures, functions etc) or creating new one, or who actually deleted one or more objects? These are normal questions when more than one person are working on a same database. Production environment is mostly kept secure for unauthorized access and few known persons are allowed to make changes BUT still you need to keep a track of these changes and if it’s a development database then it is also must to keep a complete log of each change. There are tree major ways in which we can keep track of these changes.

1. DDL Trigger and Event Notifications
2. Extended Events
3. Service Broker and Event Notifications

The DDL Trigger method is most commonly used method, where we write a DLL (after) trigger on each database separately and using information from event notifications, we decide whether to rollback any DDL change or just dump change information to a table.

How to create a DDL Trigger?

Extended Events, is the most advance method, not only for DDL change tracking but it’s going to be next biggest tool for DBAs. SQL Server 2012, introduced three new events for DDL change tracking.

1. object_altered
2. object_created
3. object_deleted

Paul Randal script for extended event creation is good one to follow, but don’t forget to change events.

Service Broker (with event notifications), is the best way I have ever found for DDL Change Tracking before SQL Server 2012. Though its initial steps are bit lengthy, that is why; most people avoid using this method.

Using service broker, you can dump all databases changes data to a single table on an instance, or you can transmit changes information as a message to other instance on internet (if need to create a single point of administration for multiple instances).

(What is Service Broker and what type of objects you need to create, can be found here and here).

Use following simple steps to create DDL Changes Log, for multiple databases on an instance.

/* 
Script By: Aasim Abdullah/Amna Asif for ConnectSQL
Purpose: To create DDL Change Log using Service Broker,
     for multiple databases on a single instance
*/
IF NOT EXISTS (SELECT 1 FROM sys.databases WHERE name = 'ConnectDBA')
	CREATE DATABASE ConnectDBA
GO
USE [ConnectDBA]
GO
/*Create a table to hold change log*/
CREATE TABLE [dbo].DDLChangeLog(
      [EventType] [varchar](250) NULL,
      [PostTime] [datetime] NULL,
      [ServerName] [varchar](250) NULL,
      [LoginName] [varchar](250) NULL,
      [UserName] [varchar](250) NULL,
      [DatabaseName] [varchar](250) NULL,
      [SchemaName] [varchar](250) NULL,
      [ObjectName] [varchar](250) NULL,
      [ObjectType] [varchar](250) NULL,
      [TSQLCommand] [varchar](max) NULL
) ON [PRIMARY]
 
GO
 
--Enable Service Broker
IF EXISTS ( SELECT  *
            FROM    sys.databases
            WHERE   name = 'ConnectDBA'
                    AND is_broker_enabled = 0 ) 
    ALTER DATABASE ConnectDBA SET ENABLE_BROKER ; 
GO
--Create a stored procedure which will hold logic, how to get data from queue and insert to DDLChangeLog table.
CREATE PROCEDURE [ConnectDBA_Queue_EventNotificatier]
    WITH EXECUTE AS OWNER
AS 
    DECLARE @message_body XML 
 
    WHILE ( 1 = 1 )
        BEGIN
            BEGIN TRANSACTION
      -- Receive the next available message FROM the queue
            WAITFOR ( RECEIVE TOP ( 1 ) -- just handle one message at a time
                  @message_body = CONVERT(XML, CONVERT(NVARCHAR(MAX), message_body))
                FROM dbo.[ConnectDBA_EventNotificationQueue] ), TIMEOUT 1000  
                -- if the queue is empty for one second, give UPDATE and go away
      -- If we didn't get anything, bail out
            IF ( @@ROWCOUNT = 0 ) 
                BEGIN
                    ROLLBACK TRANSACTION
                    BREAK
                END 
            INSERT  INTO DDLChangeLog
                SELECT  @message_body.value('(/EVENT_INSTANCE/EventType)[1]',
                                            'varchar(128)') AS EventType,
                        CONVERT(DATETIME, @message_body.value('(/EVENT_INSTANCE/PostTime)[1]', 'varchar(128)'))
                        AS PostTime,
                        @message_body.value('(/EVENT_INSTANCE/ServerName)[1]',
                                            'varchar(128)') AS ServerName,
                        @message_body.value('(/EVENT_INSTANCE/LoginName)[1]',
                                            'varchar(128)') AS LoginName,
                        @message_body.value('(/EVENT_INSTANCE/UserName)[1]',
                                            'varchar(128)') AS UserName,
                        @message_body.value('(/EVENT_INSTANCE/DatabaseName)[1]',
                                            'varchar(128)') AS DatabaseName,
                        @message_body.value('(/EVENT_INSTANCE/SchemaName)[1]',
                                            'varchar(128)') AS SchemaName,
                        @message_body.value('(/EVENT_INSTANCE/ObjectName)[1]',
                                            'varchar(128)') AS ObjectName,
                        @message_body.value('(/EVENT_INSTANCE/ObjectType)[1]',
                                            'varchar(128)') AS ObjectType,
                        @message_body.value('(/EVENT_INSTANCE/TSQLCommand/CommandText)[1]',
                                            'nvarchar(max)') AS TSQLCommand
            
            COMMIT TRANSACTION
        END
GO 
--Create Queue To Catch Messages
CREATE QUEUE [ConnectDBA_EventNotificationQueue]
   WITH ACTIVATION -- Setup Activation Procedure
 ( STATUS= ON, PROCEDURE_NAME = DBO.[ConnectDBA_Queue_EventNotificatier],-- Procedure to execute
   MAX_QUEUE_READERS = 2, -- maximum concurrent executions of the procedure
   EXECUTE AS OWNER) -- account to execute procedure under
GO
--Create Service
CREATE SERVICE [ConnectDBA_EventNotificationService] 
AUTHORIZATION dbo ON QUEUE dbo.ConnectDBA_EventNotificationQueue
    ([http://schemas.microsoft.com/SQL/Notifications/PostEventNotification]) 
GO 
 
/*==========ON TARGET DATABASE===================*/
-- Enable service broker on target database
IF EXISTS ( SELECT  *
            FROM    sys.databases
            WHERE   name = 'NotifierChekcerDB'
                    AND is_broker_enabled = 0 ) 
    ALTER DATABASE YourDBNameHere SET ENABLE_BROKER ; 
GO 
--
USE YourDBNameHere
GO
--Create event notification
CREATE EVENT NOTIFICATION [ConnectDBA_NotifierFor_DDL_DATABASE_LEVEL_EVENTS] ON
    DATABASE FOR --DDL_DATABASE_LEVEL_EVENTS, -- uncomment to get all type of events information
    CREATE_TABLE,ALTER_TABLE,DROP_TABLE,CREATE_VIEW,ALTER_VIEW,DROP_VIEW,CREATE_INDEX,
	ALTER_INDEX,DROP_INDEX,CREATE_FUNCTION,ALTER_FUNCTION,DROP_FUNCTION,CREATE_PROCEDURE,
	ALTER_PROCEDURE,DROP_PROCEDURE,CREATE_TRIGGER,ALTER_TRIGGER,DROP_TRIGGER,CREATE_SCHEMA,
	ALTER_SCHEMA,DROP_SCHEMA 
    TO SERVICE 'ConnectDBA_EventNotificationService' 
, '709CD726-2F34-4B67-9AAF-05D0DB31D5A8' ---- service_broker_guid of ConnectDBA DB
-- SELECT service_broker_guid FROM SYS.DATABASES WHERE name = 'ConnectDBA'
GO 
 
----------How to get DDL Change Log information--------
--USE ConnectDBA
--GO
--SELECT * FROM DBO.DDLChangeLog