Anyone used SQL Trace (server side) as a permanent auditing solution?

Question

Anyone used SQL Trace (server side) as a permanent auditing solution?

TheGreenShepherd

SSCertifiable

Points: 6979
More actions
September 18, 2013 at 10:21 am

#278029

I have some very strict auditing requirements on my current project, and SQL Server Audit has some serious flaws. CDC won't work in entirety, either. I'm considering setting up server side traces, as the data that SQL Trace is able to gather would be sufficient. I know that it has a much smaller performance impact than Profiler, but I'm still unsure about how it would hold up over the long term. Has anyone tried to use this as a permanent auditing solution? If so, what were your results? Pros/cons?

Viewing 6 posts - 1 through 5 (of 5 total)

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

jasona.work SSC Guru Points: 50083 More actions · Answer 1

Where I work has similar requirements, and actually provides the script to create the trace. Downside is, they write the trace to disk and default it to 1GB files and keeping 500 files...

So about once or so a month, I have to go in and delete trace files to avoid a full disk. I'd set it to enough files to last a month, but how quickly it fills up and rolls over to a new file is variable depending on the month and the server.

Otherwise, works pretty much OK.

What we use is available publicly here: http://iase.disa.mil/stigs/index.html

Look at the STIG for SQL2005, check DG0145 (I think, might be DG0029)

TheGreenShepherd SSCertifiable Points: 6979 More actions · Answer 2

So you don't have to provide any sort of reporting on the trace files? No requirements on retention?

jasona.work SSC Guru Points: 50083 More actions · Answer 3

With this particular trace and the rules, no. The goal behind it is if something needs to be investigated later, we're able to dig into the trace files.

Between the nightly file system backups, and the time those backups are kept, that covers the retention requirements.

I believe too, you can configure these traces to go to a table, rather than files, which if you need to run reports against them would make that significantly easier...

TheGreenShepherd SSCertifiable Points: 6979 More actions · Answer 4

Thanks for your reply. Actually, you can't have these go directly to table, they must go to a file first. Unfortunately.

jasona.work SSC Guru Points: 50083 More actions · Answer 5

Oops. I was (partially) confusing server side traces with Profiler Traces...