SQL Auditing

Question

SQL Auditing

Admingod

SSCertifiable

Points: 5997
More actions
November 8, 2018 at 10:43 am

#380803

I’ve been looking into solutions for change tracking of our SQL data. Any idea about CDC? Any thoughts on that solution or other recommendations?
https://docs.microsoft.com/en-us/sql/relational-databases/track-changes/about-change-data-capture-sql-server?view=sql-server-2017

Viewing 10 posts - 1 through 9 (of 9 total)

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

Joe Torre SSChampion Points: 10248 More actions · Answer 1

I think temporal (system versioned) tables are better for a number of reasons if you really want to audit DML changes, but it is a feature of 2016+.

Admingod SSCertifiable Points: 5997 More actions · Answer 2

Admingod

SSCertifiable

Points: 5997

November 13, 2018 at 10:45 am

#2013361

Could you please provide more details?

Thanks

Steve Jones - SSC Editor SSC Guru Points: 736478 More actions · Answer 3

What are you trying to accomplish with tracking changes? What's the purpose?

Admingod SSCertifiable Points: 5997 More actions · Answer 4

I would like to Audit tables so looking the best possible solution, Is it C2 Audit mode or trigger or any other best possible solution you can advise?

Thanks

Steve Jones - SSC Editor SSC Guru Points: 736478 More actions · Answer 5

There isn't a best solution. Every solution has tradeoffs, and depending on what you audit, this can be a tremendous amount of data.

If you use SQL Audit or C2, this is stored in a format that isn't easily readable. It's a file that SSMS can read, and you can load this into a table, but it's cumbersome. This can be a lot of data, and it takes some practice and time to set up.

You can roll your own auditing with triggers, this this can also be hard, same for temporal tables. Plus, these get you changes, but not reads.

You can also use Extended Events to read any access, again, cumbersome to set up.

Auditing isn't a thing. You need to define what information you want to audit and how much data you're willing to capture. If you run an extended events or profiler default session and look at all the activity for SQL batches, you'll realize this is a lot. Decide what's important and what you need, then it's easier to find ways to solve the problem you have.

Admingod SSCertifiable Points: 5997 More actions · Answer 6

Thanks for the information, at this point i have to Audit only one table. Based on the information you provided i would think trigger or extended events would be a better option. Could you please provide me some examples or point me where i can get the help to setup audit for single table? Thanks and appreciated!

Steve Jones - SSC Editor SSC Guru Points: 736478 More actions · Answer 7

What do you need to audit? You still haven't defined this.

Admingod SSCertifiable Points: 5997 More actions · Answer 8

Admingod

SSCertifiable

Points: 5997

November 14, 2018 at 10:56 am

#2013519

A table with few columns.

Steve Jones - SSC Editor SSC Guru Points: 736478 More actions · Answer 9

That's only a part of what auditing is. Auditing is deciding what information to capture, when, and for this objects. You have the last one, but what information needs to be captured based on what events. Are you capturing only SELECTs (read) queries? Writes? What data do you need? The last value that existed, all history?

You should think through the purpose of auditing and then what data supports that. What questions are asked? Then write down what needs to be done. We, as technical people, need to be specific here in order to actually get the solution designed.