Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase ««123»»

What, When and who? Auditing 101 Expand / Collapse
Author
Message
Posted Monday, November 28, 2011 6:58 PM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Saturday, February 02, 2013 8:21 AM
Points: 283, Visits: 268
When SQL 2005 came out, I cringed at the way SQL Notification services worked and basically threw it out the window as an option for change tracking. However, I re-tooled the same concept using SQL Service Broker, and using a simple, generic Trigger, was able to audit ANY table's changes (real-time, with both previous and new values) asynchronously and we were able to audit all databases into one or many audit databases if we wished. Views were created against the meta-data to make "pseudo" tables which resembled the originals, so comparisons could be made. Anyway, it's worked for years and was completely scalable. If we wanted to move the auditing database(s) to another server, it was as easy as "changing the channel" on the service broker, no messy Trigger or Proc changes.


Post #1212848
Posted Tuesday, November 29, 2011 6:02 AM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Thursday, March 13, 2014 9:27 PM
Points: 3,283, Visits: 6,670
codebyo (11/28/2011)
Just one small correction: ALLOW_SNAPSHOT_ISOLATION is not needed for Change Tracking but it's the probably the safer way to use it with.

Best regards,

Andre, what you stated here is true. You do NOT need SNAPSHOT ISOLATION on but if you do not, you will need to do some extra work to make sure that all requirements are met. You can read here to get more details regarding how it should be done without snapshot isolation.

EDIT : Regarding CHANGE_TRACKING_CONTEXT, it will work fine if all data changes are done only using applications. Then you have control of it and you get more context of the data change. It is a good info to pass to everyone. Thanks. I missed that in the article.


-Roy
Post #1213142
Posted Tuesday, November 29, 2011 6:09 AM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Thursday, March 13, 2014 9:27 PM
Points: 3,283, Visits: 6,670
Thanks David for the compliment.
To agree to the definition of an Audit is difficult. Everyone has their own take on it. Your definition is easier to understand as well. Thanks for sharing it.


-Roy
Post #1213146
Posted Tuesday, November 29, 2011 6:11 AM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Thursday, March 13, 2014 9:27 PM
Points: 3,283, Visits: 6,670
Evil Kraig F (11/28/2011)
Roy, an excellent walkthrough on the mechanics of something I've basically avoided. You highlighted its difficulties and its uses quite well.

Thanks for the article.

Thanks Craig. When I look back, I realized that I could have added a bit more in depth information.


-Roy
Post #1213149
Posted Tuesday, November 29, 2011 8:42 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Tuesday, April 01, 2014 11:31 AM
Points: 13, Visits: 226
Good article, thanks for the summary.

The biggest issues I've encountered, save for a server side trace, is catching the originating hostname for the incoming connection. To me this is a critical aspect of "who" and without it, it can be difficult in tracking down an individual in the situation where a common SQL login is utilized by multiple individuals (or even a service account implemented on several servers). In this, SQL Server Audit is woefully lacking. Sure we *wish* we could always avoid folks using a SQL login but life is rarely perfect especially when you've inherited someone else's mess to straighten out. To me this is the whole point of auditing is catching non-standard activity but without capturing *correct* hostname you can't easily complete the picture of *who* (all hostname captured information in SQL Audit is the actual host SQL server or blank and not the host of the originating connection).

This of course is Microsoft's issue to resolve, not the author's.
Post #1213324
Posted Tuesday, November 29, 2011 8:55 AM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Thursday, March 13, 2014 9:27 PM
Points: 3,283, Visits: 6,670
Thanks Zero one.
Auditing in SQL Server is still in infancy state. It will take couple of more releases to get all the required information for auditing I guess.


-Roy
Post #1213336
Posted Friday, December 02, 2011 9:06 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Yesterday @ 4:00 PM
Points: 35,967, Visits: 30,258
tymberwyld (11/28/2011)
When SQL 2005 came out, I cringed at the way SQL Notification services worked and basically threw it out the window as an option for change tracking. However, I re-tooled the same concept using SQL Service Broker, and using a simple, generic Trigger, was able to audit ANY table's changes (real-time, with both previous and new values) asynchronously and we were able to audit all databases into one or many audit databases if we wished. Views were created against the meta-data to make "pseudo" tables which resembled the originals, so comparisons could be made. Anyway, it's worked for years and was completely scalable. If we wanted to move the auditing database(s) to another server, it was as easy as "changing the channel" on the service broker, no messy Trigger or Proc changes.


That would make one heck of a nice article. Any chance of you taking that on?


--Jeff Moden
"RBAR is pronounced "ree-bar" and is a "Modenism" for "Row-By-Agonizing-Row".

First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column."

"Change is inevitable. Change for the better is not." -- 04 August 2013
(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1215765
Posted Friday, December 02, 2011 9:09 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Yesterday @ 4:00 PM
Points: 35,967, Visits: 30,258
Roy Ernest (11/29/2011)
Thanks David for the compliment.
To agree to the definition of an Audit is difficult. Everyone has their own take on it. Your definition is easier to understand as well. Thanks for sharing it.


Heh... I've found that a lot of people define "Audit" as "Keeping historical data so we know who to blame when something goes wrong."

Nicely done on the article. Good order of revelation with nice, simple examples. Well done, Roy.

As a side bar, I'm going to use your article to justify why we don't actually want to use CT when we finally get to 2008 at the new company I'm working at. Of course, if they wait a bit longer, we can jump to 2k12.


--Jeff Moden
"RBAR is pronounced "ree-bar" and is a "Modenism" for "Row-By-Agonizing-Row".

First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column."

"Change is inevitable. Change for the better is not." -- 04 August 2013
(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1215766
Posted Monday, December 05, 2011 5:09 AM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Thursday, March 13, 2014 9:27 PM
Points: 3,283, Visits: 6,670
Hey Jeff,

Thanks Jeff. The second part will come out hopefully soon. Are there too many negatives on CT for your company to use CT?



-Roy
Post #1216203
Posted Monday, December 05, 2011 5:16 AM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Yesterday @ 4:00 PM
Points: 35,967, Visits: 30,258
Kind of, Roy. It's not actually negatives with CT... I'd be more likely to call it a lack of positives. CT doesn't appear to actually audit, in my feeble ol' mind. If I understand the article correctly, it only keeps one historical row back.

Like I said... good article. The fact that CT doesn't appear to do what I think of as "auditing" has no bearing on the fact that this is an excellent article about what CT does and doesn't do. Thanks for writing it.


--Jeff Moden
"RBAR is pronounced "ree-bar" and is a "Modenism" for "Row-By-Agonizing-Row".

First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column."

"Change is inevitable. Change for the better is not." -- 04 August 2013
(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1216208
« Prev Topic | Next Topic »

Add to briefcase ««123»»

Permissions Expand / Collapse