Results 1 to 4 of 4
  1. #1
    Join Date
    Jun 2012
    Posts
    5

    Unanswered: Database Change Management Tools - Monitoring Changes

    Does anyone have audit requirements that require that all schema changes to certain database are tracked and monitored. Does anyone know of any tools that can monitor these changes and log them for audit review?

  2. #2
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    It depends on whether you need a canned package to satisfy the auditor or if you can "grow your own" and use that.

    If you need to purchase a package, there are several packages that will do the job without a lot of work but at considerable expense. These range from simple detection of changes after the fact to full blown who/what/when/where/how auditing, and as a general rule the complexity, overhead, and cost correlate directly to the level of monitoring that your auditor demands.

    If you can "grow your own" you need to find out EXACTLY what your auditors NEED to know, then you ought to be able to satisfy them pretty quickly and easily. The exact mechanics of this differ slightly depending on their requirements and how much load you can place on your production systems, but it ought to be relatively straightforward.

    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

  3. #3
    Join Date
    Jun 2012
    Posts
    5
    Thanks Pat. To make this easy, I am in the auditor .

    We were utilizing a snapshot report taken at the beginning of each month and at the end of each month and using a comparison tool to determine all schema changes that occurred during that month (adds, deletions, and modifications to schemas).

    We essentially want to do this same thing, but using a better tool that doesn't require so much DBA time.

    So essentially, to answer your question, we are looking to see the time that objects were changed, the type of change, and who made the change. That's about all the data we need. Also, when I say changes, I mean strictly schema changes and not data changes.

    What are some of these tools you have seen used?

  4. #4
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Prior to SQL 2008, to get that level of detail you need to run a filtered SQL Profiler Trace. Set the trace filter to capture the events and the information that you need, store them in a file or a table on another server, and you're good!

    If you have SQL 2008 or later, you can use SQL Server Extended Events which have access to far more information and also have far less impact on the server than a Profile Trace requires. This is definitely the best answer if your SQL Server supports it.

    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •