Post reply

Auditing System questions- no personal experience

  • January 27, 2020 at 5:40 pm

    #3720714

    Good Morning All -I have used triggers to do some basic auditing, but I have moved to a new company and they have this system that basically does the following:

    Prod access is restricted to the DBA Group for updates/ddl (outside our application).

    Developer gets a helpdesk ticket, goes to a website and clicks to request access with a description of why they need access and how long to grant access for, email is sent to the dba group, if we approve it they get elevated rights in prod and it starts a extended event session tied to the user that captures all events they do, once they click they are finished (or time runs out) it removes the access.  extended event session is stopped(it writes the event to a file share), and marks that it is ready to be processed.  Every night at 7:30 a job runs and finds everything that needs to be processed and inserts it into a table and presented in a report to be reviewed.

     

    Problem is that its capturing thousands of events we do not need, partial SQL statements (Set nocount on, set statements, etc).  The database has bloated to over 244 Million records, and filtering on the varchar column to delete the unneeded data takes about 6-8 minutes to delete 10k records.  So its always growing - Would anyone be able to suggest a solution they are familiar with that is HIPPA compliant and would meet our needs if you have ever used one, I'm not opposed to a 3rd party option dependent on price, obviously.

     

    Or does someone have a way to filter a large dataset so that I can get much faster deletes on the Varchar column, which I'm not sure is possible.

     

    Thanks

  • January 27, 2020 at 6:07 pm

    #3720720

    multiple things.

    • start with archiving the data onto a different server - partitioned by year/month - will help querying in some ways (not so in others) and will make deletes faster
    • when loading from the .xel file filter the data at this point before inserting into the final audit table
    • Extented Events may also be setup in a way that it excludes particular statements

  • January 28, 2020 at 1:38 pm

    #3720905

    Filtering on your extended events would be the biggest boost. Don't capture what you don't need. Most stringent filters first in order (maximizes the reduction in overhead).

    "The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
    - Theodore Roosevelt

    Author of:
    SQL Server Execution Plans
    SQL Server Query Performance Tuning

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply