As a database administrator (DBA), it is crucial to have a system in place to monitor and track changes made to your databases. This not only helps ensure compliance with regulations like Sarbanes-Oxley (SOX), but also provides an auditable trail of actions taken on the system. In this article, we will discuss a homegrown solution for monitoring database changes using SQL Server Trace.

Understanding Database Changes

Before diving into the solution, let’s clarify what we mean by “database changes.” In this context, we are referring to structural changes made to database objects such as tables, views, stored procedures, and functions. We are not concerned with data changes, job alterations, or modifications to users or logins.

The Monitoring System

The monitoring system we will discuss involves using SQL Server Trace to capture specific events and columns related to database changes. By configuring the trace, we can gather detailed information about these events and store them in a central Audit database for reporting purposes.

To implement this system, we need to set up a Monitor Server with SQL Server installed. For each server we want to monitor, we create a linked server and test the connection. The Monitor Server will have a job that triggers the trace on the remote server via the linked server. The trace file will be stored locally on the Monitor Server.

Every hour, a job will start a new trace and generate a trace file for that hour. Another job will then import this trace file into the Audit database for reporting. This allows us to have periodic results without impacting the main server being monitored.

Customization and Reporting

The system allows for customization based on your specific needs. You can choose which events and columns to monitor, filter specific data results, and even purge unnecessary data from the trace files. The Audit database contains various tables and views to store and display the captured information.

Reporting can be done through various means. In the example provided, email reports were initially sent out to relevant parties. However, as the system grew, Reporting Services was implemented to produce more comprehensive reports. These reports can be scheduled and sent via email subscriptions, providing a convenient way to review the database changes.

Ensuring Compliance and Remediation

When it comes to compliance with regulations like SOX, it is essential to not only monitor database changes but also remediate any unauthorized actions. Each change should be justified and associated with a ticket or other Change Control documentation. Non-approved actions should be flagged as potential breaches of the Change Control System.

By having a system in place that tracks and documents all database changes, you can easily provide auditors with a report that shows the history of changes and any remediation actions taken. This helps maintain accountability and transparency in your database environment.

Conclusion

Monitoring database changes is a critical task for DBAs, especially in environments that require compliance with regulations like SOX. By implementing a system using SQL Server Trace, you can track and report on structural changes made to your databases. This provides an auditable trail and helps ensure the integrity and security of your data.