Note: Adding a connection simply allows you to browse the database schema. DDL Events are not been tracked at this stage.
DDL events are tracked using event notifications. It's possible to create an event notification at the server level to capture events scoped at the server level (e.g. CREATE_DATABASE, DROP_LOGIN etc) as well as database level events (e.g. ALTER TABLE, CREATE PROCEDURE) for every database on your SQL Server instance. Events are organized in a hierarchical structure (see here
for more info), which makes it easy to specify the events you are interested in auditing. By default the application will capture the DDL_EVENTS
event group when creating an event notification on the server and the DDL_DATABASE_LEVEL_EVENTS
event group when creating a notification on an individual database, but you can configure this to suite your own requirements.
Please note that if you want to capture event notifications at the database level, the service broker needs to be enabled on that database. The application will prompt you about this, but you will need to run the command yourself in management studio. The application doesn't run the command for you as it requires exclusive access to the database to run or you can forcibly disconnect user sessions from the database.
It's possible to create event notifications at the server and database level. In this situation it's possible to configure overlapping events so they are been captured at both the server and database level resulting in a duplication of the events captured. Event notifications are created using the "WITH FAN_IN" clause which is used to prevent this duplication so it doesn't matter if events overlap.
- Right click on a server or database node that you want to configure event notifications for.
- If this is the first time you've done this, you will be prompted to create a new database called "zzCodeHistory". This will be used to store the captured DDL Events.
- Change the location of the MDF/LDF files if required or leave the settings as default.
- Click OK to create the database.
- You should get a message that the database was created successfully, click OK.
- You should now be prompted with a "Configure DDL Event Notification" dialog.
- The "Add/Replace Event Notification" option is selected by default. This will create an event notification called "zzCodeHistory_DDLEvent_Notification". If an event with this name already exists, it will be dropped and re-created.
Note: Select the "Remove Event Notification" if you want to remove an existing event notification without creating a new one in it's place.
- The Event Types hyperlink can be used to configure what events you want to audit in your database. By default, this will be the "DDL_EVENTS" event group for server level event notifications and DDL_DATABASE_LEVEL_EVENTS for database level event notifications. If an event notification already exists it will default to the value previously specified.
Click the hyperlink to select the events to audit if required.
- The preview button can be used to see the TSQL command that will be executed.
Note: If you need to customize the command, it copy the text and run in in management studio.
- Click OK to create the event notification.
DDL Events are now been tracked for your specified databases, providing you with information about who changed what and when. It's worth noting that if someone alters a stored procedure at this stage you will be able to see the ALTER PROCEDURE command, when it was done and who issued the command but you won't have the original CREATE PROCEDURE text to use as a comparison to see what the user has changed.
If DDL Event tracking is enabled from the start of your project, this isn't an issue as the original CREATE PROCEDURE event would have been captured but in most cases you will be tracking events for existing databases. Also, if a second ALTER PROCEDURE command is issued, you will be able to compare this event with the previous ALTER PROCEDURE command. To overcome this initial limitation of the tool, it's possible to create a DDL snapshot of your database - a point in time copy of your database schema. This allows you to compare the ALTER PROCEDURE command against the snapshot to see what the user has changed when no previous DDL events are available.
Creating a DDL Snapshot
As mentioned previously, it can be a good idea to create an initial DDL snapshot for each database after enabling event tracking as it provides you with a point in time reference of your database schema. You can use the DDL snapshot to compare with DDL events when no previous DDL event is available for comparison. You might also decide to create snapshots at particular milestones in your project or at regular intervals. In the initial release, this is done through the GUI, but future versions will likely support command line automation.
- Click on the database node in the tree that you want to create a snapshot for.
- Click the snapshot button on the toolbar.
- You will be prompted to enter some metadata about the snapshot. You can leave the default or edit as required.
- Click the "Create Snapshot" button to create the snapshot.
Note: This process might take some time depending on the number of objects in your database. It's possible to minimize the process and continue using the tool.
- After the snapshot has been completed, it will be available in the DDL Snapshots folder. You will also see a "Snapshot History" tab when you browse to a specific object in the tree for that database.
Event Notifications vs DDL Triggers
It's also possible to audit DDL Events using DDL Triggers instead of event notifications and you might be wondering why event notifications were chosen over DDL Triggers. The main difference between the two methods is that event notifications are asynchronous and DDL triggers fire synchronously. DDL Triggers fire after a DDL event occurs as part of the same transaction as the event that fired the trigger (Similar to an AFTER DML trigger). This means it's possible to run some logic within the trigger and rollback the transaction if you desire. It also means that the connection that issued the DDL event has to wait for whatever logic you have configured in the trigger to complete in addition to the DDL command that they issued.
Event notifications are asynchronous and run outside the transaction that fired the DDL event. The transaction that fired the DDL event doesn't need to wait for you auditing code to complete so there is much lower performance overhead associated with event notifications. It also means that it's not possible to rollback the DDL event using an event notification, but in this we just want to audit DDL events - not to prevent them. If you just want to audit events, event notifications are a generally a better option than DDL Triggers.
Older versions of the tool (not released to community) were configured to use DDL Triggers instead of event notifications. DDL triggers worked well for auditing, but there are also other differences that make event notifications a better choice.
The application used to store DDL Events in the same database in which they are generated instead of using a separate database. There are some advantages to this, but overall a separate database is likely to be a better option for most people. A separate database is a less invasive option as it requires little or no changes to the databases you are tracking DDL Events for. Also, it allows questions to be asked at a higher level such as "What has changed on my server in the last 24hrs" instead of "What has change in my database in the last 24hrs".
DDL Triggers become more difficult to use when cross-database access is required. Erland Sommarskog wrote an excellent article
on this which helped me setup the application to use a shared database, granting permissions via certificate signed stored procedures. This worked well, but it was a messy solution that left private keys and certificate files on the file system. Event notifications are actually a lot easier to setup and require fewer objects to be created in the monitored databases. The DDL Trigger solution required a DDL Trigger, schema, certificate and stored procedure (+ signature) to be added to be added to the monitored database when using database level DDL Triggers. A database level event notification only requires an event notification to be created in the monitored database (providing the service broker is already enabled).
It's also worth noting that DDL triggers were first introduced in SQL Server 2005 but it wasn't possible to create a server level DDL trigger to capture database level events. It is possible to create a server level event notification in SQL Server 2005 to track database level event notifications though so this is another advantage to using event notifications in SQL Server 2005. Microsoft improved DDL Triggers in SQL 2008 and you can now create server level triggers to capture database level events.
So it's possible to audit DDL events using either DDL Triggers or event notifications and both options can work very well. For auditing purposes, event notifications are a better solution in my opinion. Although the application no longer supports the use of DDL Triggers, it's possible to set this up yourself manually if you prefer. Simply insert the XML Event data into the zzCodeHistory.EventData table and a trigger on this table will take care of the rest.