[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1020
  • Last Modified:

capturing transaction rollbacks using SQL Server 2008 Audit

SQL Server 2008 Audit allows tracking of specific events and logging them to an audit (file or Application log). It allows tracking of any INSERT, UPDATE or DELETE statement issued against a database for which I define a Database Audit Specification. The events will be logged regardless of whether the DML statements were issued inside of a transaction. How can I tell that a specific DML SQL statement that appears in the Audit log has not been rolled back?
0
GeeCR
Asked:
GeeCR
  • 3
  • 2
1 Solution
 
MikeWalshCommented:
I don't have 2008 Enterprise Edition or Dev edition handy at present. Only standard on this machine but are you sure that Audit is only writing the fact that you tried to do a change?

I would have assumed only committed changes would be written (like in Change Data Capture, a feature introduced at the same time). It would be difficult to audit with rolled back changes.

You can try a simple experiment that I was going to try until I realized I was on standard with this laptop:

1.) Create a new simple table someplace
CREATE TABLE testAudit (ident int, descr varchar(50))

2.) Insert some rows like:
1 'Unchanged'
2 'Unchanged'
3 'Unchanged'

3.) Turn on auditing and verify it looks good
4.) Change one row:
UPDATE testAudit SET descr = 'Changed_NoExplicitTran' WHERE ident = 1
5.) Do you see it in audit? You should.
6.) Update another row in a transaction (that you don't commit):
BEGIN TRAN
UPDATE testAudit SET descr = 'Changed - No Commit') WHERE ident = 2
7.) Wait for your delay of a second or whatever you setup in the audit and check the audit. Is that change there?
8.) If it is then I am wrong and we have to look at options. If it isn't there yet, this is promising perhaps.
9.) Commit that transaction finally and check the audit again. Is it there now? Good
10.) Do the same test with Ident 3 only this time BEGIN TRAN... Update... ROLLBACK --> is it there?

I don't use auditing in production but I would not expect to see rolled back or uncommitted changes there yet since they never really happened yet as far as the ACID properties go. They are in process but they haven't been committed and persisted as factual events.

If the rolled back transactions or transactions pending commits still happen then you have to look at other options.

Not sure what you are trying to do but Change Data Capture is one maybe to investigate. Another is looking at some of the log reading tools out there like APEX SQL Log.
0
 
GeeCRAuthor Commented:
Hello MikeWalsh,
Thank you for you response.

As to step 8) of your instructions, the answer is "Yes, the Update gets logged in the Audit right away (or after the Queue Delay value specified in the Audit definition ...2 seconds for me), and it does get logged a long before the transaction gets committed or rolled back. Once the commit or rollback occurs no additional event seems to be logged into the Audit.  

Sql trace could trap event # 187, 188 but that seems to be out of scope of SQL Server 2008 Audit feature (?)

On the other hand, It looks like the Change Data Capture feature is the way to go for what I need. (track committed changes:-))
 
0
 
MikeWalshCommented:
That surprises me but I guess there is some rationale that I am just not thinking of. Yeah you could use trace to track rollbacks but that could get complicated.

You might look at Change Data Capture (again depending on how you are using this) or look at a tool like Apex SQL Log (or really any SQL Log reader, I haven't used one in awhile. I believe Quest makes one as well, at least they did have one for SQL 2000. Lumigent also makes tools for auditing changes. Sentrigo is another vendor.

0
 
GeeCRAuthor Commented:
Here is the rationale from the source (http://msdn.microsoft.com/en-us/library/dd392015.aspx)

"Another piece is how auditable events are recorded when they occur within a transaction. The simple way to describe this is: They are always audited. Clearly there would be negative security implications if data could be read, but no audit was recorded because a pending transaction was rolled back. So, the recording of audit events occurs irrespective of transaction scope and whether a rollback occurs or not."
0
 
GeeCRAuthor Commented:
Thanks for the pointer ...I completely overlooked the existence of the "Change Data Capture" and "Change Tracking" feature (introduced in SQL Server 2008)
0

Featured Post

A Cyber Security RX to Protect Your Organization

Join us on December 13th for a webinar to learn how medical providers can defend against malware with a cyber security "Rx" that supports a healthy technology adoption plan for every healthcare organization.

  • 3
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now