Article Topics

All Articles

Performance Tuning

Audit

Business Intelligence

Clustering

Reporting Services

Developer

General DBA

ASP.NET / ADO.NET

Write for Us

Share your SQL Server knowledge with others and raise your profile in the community More...

Audit Data Modifications

By : TJay Belt
Apr 15, 2008

Page 4 / 4

Jobs
[_Audit - Data Modifications / Client]
I feel impressed to justify my naming convention. I use the underscore to keep these jobs at the top of the list of jobs, sorted alphabetically. This is a strange habit I picked up years ago. Since the job is an Audit job, I have labeled it as such with the term ‘Audit’. ‘Data Modifications / Client’ processes the Data Modification by Client. The first incarnation of this job had 1 step. But with the implementation of the Client/ServerType options, I added as many steps as I had for that combination. This allows the job to continue processing, even if a single Client/ServerType combination failed.

Each step calls the proc [sp_StartAllDataModificationTraces] and passed in specific Client and ServerType params. Some forgo the ServerType altogether, and simply call the Client param. Each step has been given the ability to continue to the next step on success or failure, to ensure it process all it can, even if it receives individual failures.

[_Audit - Process Files / Client]
This job will call the process procedures for each Client and ServerType you want. Multiple steps, like the job above has, are needed to ensure proper calling to each combination that you may have in your system. The last step of this job is to call the Purge Data Modification proc. This will clean up any unneeded data that was imported.

The Rest of the Process
The rest is up to you. All other steps that you take from this point are not dictated by system jobs or timetables, other than the ones you indicate in your job requirements as a DBA. You will now have data being stored in an audit database. You can act upon it or not. It’s your choice. Since we have a third party reading this information, we do not act upon it as it comes into the system. We get daily reports from this individual that describes the items that have no explanation. They may be a DBA’s actions or otherwise. We can research the data in these tables to determine what happened, who did it, when, etc. And then act upon that information.

The important fact is that there is now detailed data describing modifications in your monitored systems, sitting, waiting for you to do something. You or someone ‘must’ remediate them. Regardless of the reality of your solution, you need to document why these modifications occurred. Who did what, when it was done, and what was done. Mark this item as resolved and give it good notes. You will find yourself referring to this information in the future, when you need too. Why did so-and-so make X modification? You can now research it, and even mine data on its past modifications. Now, when the Auditor’s come knocking on your door, you have a solution in place that allows you too easily provide them a report that shows all Data Modifications, and any remediation actions taken. The sample script for this article can be found here.

<< Prev Page