June 11, 2013 at 4:45 pm
SQL PLATFORM: 2008r2 Enterprise 64Bit
SIMPLE AUDITING = Capture the name and date/time for each row that is inserted or updated in a table. This is just INSERT & UPDATE since a DELETE action would leave no row to store the info.
DETAIL AUDITING = Capturing the Name of the user, the date/Time of the command (UPDATE, INSERT and DELETE)
As of current I can implement Simple Auditing and Detail Auditing via the use of table triggers and additional audit tables (tables that store info from the DELETED table anytime an UPDATE or DELETE DML statement is executed). However I'm looking for a better, more robust solution, ideally one that’s native to SQL Server if 2008R2 has such a thing.
My company has been using SQL Server 2000 and more recently 2005 for management of our primary accounting database which is form a third party vendor that specializes in vertical accounting markets. We recently moved over to 2008R2 and I'm hoping that this will open up some options for us like better auditing.
Does anyone know if SQL 2008R2 natively has table level auditing that stores the info either in the table where the change has been made (SIMPLE AUDITING ) or in a backup or secondary audit table when you want to capture not only who and when but the "what changed" as well? From what I’ve read about SQL SERVER AUDIT you don't have the option to save this info to a table and that’s important for us. In fact I don't know why someone would prefer to use windows logs except for when it comes to performance and trying to avoid adding to the DB Server workload and or size by storing the audit info within the DB. I had hoped that SQL 2008 R2 might natively offer a new data type that when you set a column to it, the thing would auto-record the login info of the user account/login that the DML statement is being executed under.
Then again maybe I'm the odd one for thinking windows logs are not the best pace to store audit info. I just seems logical to me that you'd want to store that info a normalized fashion so you can use T-SQL to qeury/mine the audit info.
In any event, if you know of a third party product (perhaps something by Red-Gate) that can do this please share that in a post.
Thanks
Kindest Regards,
Just say No to Facebook!June 12, 2013 at 7:45 am
Based on your requirements I thin Change Data Capture (CDC) would be a decent place to start. For an accounting system where you not only want to know if a row changed, but also how it changed, CDC gives you that information, it's baked into the database engine and has tight hooks into T-SQL and SSIS.
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
June 12, 2013 at 8:27 am
opc.three (6/12/2013)
Based on your requirements I thin Change Data Capture (CDC) would be a decent place to start. For an accounting system where you not only want to know if a row changed, but also how it changed, CDC gives you that information, it's baked into the database engine and has tight hooks into T-SQL and SSIS.
Does CDC capture the name of the user that made the change and the type of transaction (insert/update/delete)?
June 12, 2013 at 8:38 am
Michael Valentine Jones (6/12/2013)
opc.three (6/12/2013)
Based on your requirements I thin Change Data Capture (CDC) would be a decent place to start. For an accounting system where you not only want to know if a row changed, but also how it changed, CDC gives you that information, it's baked into the database engine and has tight hooks into T-SQL and SSIS.Does CDC capture the name of the user that made the change and the type of transaction (insert/update/delete)?
Type, yes, definitely, that's baked into the CDC results. Not sure if it can be setup to value-add the user that made the change because I haven't needed that and it does not look like it at a glance to the docs. On insert and update if you had an audit column in your table you could store the user making the update and that could carry through to when you pickup the CDC results. For deletes, if it's important to know, then soft-deletes might be the way to go. CDC aside, most accounting software I have run across uses soft-deletes partly for the reason of keeping the audit trail intact.
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
June 26, 2013 at 10:14 am
Thanks for the CDC recomendation but unfortunately it does not capture the Identity of user which the DML statement was run under and so while CDC captures the changes it doesn't capture the other key piece, the who. In my opinion auditing should capture:
1) What happened
2) When it happened
3) Who did it
CDC doesn't include all 3 and sadly it looks like the SQL Server 2008 R2 'SQL Server Audit' function is not as DBA frinedly because while it does capture these items it stores that info to a Windows Log and not a table so if you then want to do any kind of mining/reporting of the captured data you eitehr have to query against the window log or move the data from the log and into a table in whatever SQL Db you want.
Maybe its just me but it seems like as if each attempt at data auditing in SQL Server has been done half arsed (in the case of CDC) or by someone other then a DBA as in teh of SQL Server Audit which looks to be more IT Admin friendly. Then again maybe I'm the only DBA who doesn't prefer storing this kind of info in a windows log or wants to capture the WHO along with the when and what.
Kindest Regards,
Just say No to Facebook!June 26, 2013 at 10:41 am
I believe SQL Audit can do a lot of this at the database level. You'd have to experiment a bit with that. However you can do some of this with simple triggers if you are just trying to capture what changed and not the actual SQL. The "robustness" of triggers is there in terms of reliability. However if you're looking for something else in term of being robust (options, flexibility, archiving, etc), take a look at SQL Audit. It's been awhile, and I'm not completly sure if this will work for you, but IIRC, it should.
June 26, 2013 at 12:28 pm
Steve Jones - SSC Editor (6/26/2013)
I believe SQL Audit can do a lot of this at the database level. You'd have to experiment a bit with that. However you can do some of this with simple triggers if you are just trying to capture what changed and not the actual SQL. The "robustness" of triggers is there in terms of reliability. However if you're looking for something else in term of being robust (options, flexibility, archiving, etc), take a look at SQL Audit. It's been awhile, and I'm not completly sure if this will work for you, but IIRC, it should.
SQL Server Audit (as of 2008) can capture everything I need it just puts it in a Window Log and while I can get at it via the fn_get....blahblah UDF it seems kludgy or over kill for Chage Data Capture. Its really weird to because I woudl have thought that by 2008R2 this kind of thing would be a native part of the product like UDFs.
As far as table level triggers, thats what I use now and while I'm happy with using them because they let me capture everything I need and store it how I like, its not feasible for some tables whcih are very volatile and would be impacted by the over head of a trigger. The tables I'm referring to have hundreads of milliosn of rows and are over 50GB in size EACH.
That said, the Table triggers solution (for the tables I can use them on) come with their own caveat and thats how to report in the change data captured. This may not be a big deal for most normal DB's but the one we use which is generated by a thrid party acounting app is full of top heavy tables, tables with way too many columns that are used/populated only in a small percentage of the rows. One of the main tables I Change Capture with triggers has 62 columns and thats one of the smaller tables. The probelm with capturing the data before its changed eacc time is how to then use that info in a report that will show only that which has changed.
Currently I view the raw data in SSMS Query Results and comapre one row to the next one column at a time. Its cumbersome and time consuming.
Do you know if there are any best practices, guides, artcicles (perhaps something by Itzah BenGahn, Paul Randel or Grant Fintchney (or others like them) that outlines the full setup for doing Change Data Capture with table level triggers including how to report on the change data captured so as to show just the changes?
Thanks
Kindest Regards,
Just say No to Facebook!June 26, 2013 at 12:38 pm
YSLGuru (6/26/2013)
SQL Server Audit (as of 2008) can capture everything I need it just puts it in a Window Log and while I can get at it via the fn_get....blahblah UDF it seems kludgy or over kill for Chage Data Capture. Its really weird to because I woudl have thought that by 2008R2 this kind of thing would be a native part of the product like UDFs.
I don't think that's correct. You can write to the Windows log, but AFAIK, you can set a target for data to be a file. It might be cumbersome to load this back into a table, but I bet an SSIS package can do this regularly.
http://msdn.microsoft.com/en-us/library/cc280448.aspx
Do you know if there are any best practices, guides, articles (perhaps something by Itzah Ben Gan, Paul Randal or Grant Fritchey (or others like them) that outlines the full setup for doing Change Data Capture with table level triggers including how to report on the change data captured so as to show just the changes?
Thanks
AFAIK, those guys don't use SQL Audit. Auditing isn't a area many people work in, largely because it's a pain.
If you have very large tables with triggers, what about partitioning them? That should reduce some overhead on the triggers.
June 27, 2013 at 9:45 pm
CDC wasn't really built to be an audit tool per se, although it can be used that way if it happens to satisfy your requirements. For you it clearly does not. It's ok though because its primary reason for existing is to be able to enable one to sync data changes to an external data store, e.g. a data warehouse, reliably and efficiently and without having to write a bunch of triggers to track change manually. It works well for that.
SQL Audit should be your vehicle of choice if sounds. Here are a couple reasons why SQL Audit should not write to a table: 1. Writing to a table is slower than writing to a file and Audit needs to be as unobtrusive to system performance as possible and not drop any events, and 2. The goal of Auditing is to help secure your data. If an audit writes to a table, the malicious person whose activity is being audited might have a much easier time covering their tracks deleting rows from a table than they would trying to do the same to a Windows Log, or a binary file that SQL Server has an open file handle to.
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply