asked
SQL Express Table Audit
I am relatively new to MSSQL environment (Only been working with it for little over a year). I’ve been charged with managing and developing a 2008 r2 Express that was upgraded from an Access Database last year. Management wants more accountability for what’s being changed and updated in their database. I need to be able to Audit updates, inserts, and etc for all of the tables. I’ve looked at adding an Audit trigger to each of the tables (side effects maybe performance). I’ve also looked at letting 3rd party software manage the Audits (Some are a bit pricey). Before I get to in-depth with my challenges, I need good advice on what direction to go with this. I’ve also thought of having the employer upgrade to the enterprise addition if the SSMS Auditing feature was worth it (Express doesn’t support this feature). Desperate is an understatement for my situation. (FYI – this is my very first post on any Q&A site, ever. I’m 43 years old and I generally can find answers on the internet or some version and make my own modification. This project has me stumped! Thanks!)
This site runs off of votes. Please mark all helpful answers by clicking on the thumbs up next to those answers. If any one answer lead to a solution, please indicate that by clicking on the check mark next to that answer.
Welcome!! Hopefully we can provide some sense of direction!! You'll find there are a lot of wizards on this forum. I would probably start with asking what problem the audit's are trying to solve? If you have rampant changes, then you might need to investigate the security as it might need to be adjusted. Also, how are the changes being implemented? Do you have a change control process in place? How about code reviews? Some of these things are a culture change but can help save a lot of money in the long run. Does management just want to know when things change? Data changes too? For data changes, and without knowing how the changes happen, I'd suggest implementing stored procedures and again granting the appropriate folks the ability to execute the procedures. If the only thing that they can do is to execute procedures then they can only do whatever you code the procedure to do. I don't know if that's feasible, but wanted to at least throw it out there. I would also do a cost based analysis of moving to Enterprise. Depending on your environment, moving to that can be very pricey and might not be worth it to management. There can be a performance hit for table triggers and I usually try to stay away from them if possible. It also depends on how they are written and what they are doing as well as what your environment looks like. Hope this helps some!
Ok. I’ve done a little more research. And thank your for your response. At the current time, I am mainly looking at Database-Level Audit Actions on DML’s and then branch out to Database-Level Audit Action Groups at a later time. I personally don’t want to stay with SQL Express if the Enterprise version is a more robust system to work in. I feel as if I wasted a whole week trying to develop a method for tracking data manipulation and haven’t gotten any closer than when I first started. I focused a lot on a generic trigger (
http://www.nigelrivett.net/SQLTriggers/AuditTrailTrigger.html) and have little to no success. Wouldn’t it be worth it to take advantage of the Audit and Database Audit Specification feature already built into Enterprise? I keep saying Enterprise because I don’t believe this feature is supported by the Standard version either, if I’m wrong, please let me know.
This guy has a script that he says will create audit tables (one per target table) and triggers. no muss, no fuss -
http://weblogs.sqlteam.com/brettk/archive/2006/08/10/11126.aspx This guy has a single audit table approach where the audit data is wrapped up in a single xml column (roll your own triggers, he gives a sample) -
http://codeinet.blogspot.com/2006/09/audit-table-data-changes-in-sql-server.html This gal has reporting and historical data purging built into her example -
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=170215
Enterprise is going to definitely be more robust than Express. A lot of features & abilities will be opened if you do indeed move to that version. More money of course, but it can be worthy it. If I recall correctly, Audits at the database level is only supported by Enterprise, server level audits you can do with Standard. I'd have to research that to confirm though. Triggers (if you decide to use them) should be as small and light weight as possible in my opinion. If you don't need to track the differences of the data rather just that something changed & possibly who changed it, this would be more light weight than what you provided. Here is some more guidance that might help:
http://social.msdn.microsoft.com/Forums/sqlserver/en-US/bc960b55-a74f-4457-ba4a-707ae590c9ac/need-to-track-all-user-dml-actions-before-sql-server?forum=sqlgetstarted You can also look at CDC (Change Data Capture) and/or change tracking turned on at the table level. I don't have much experience with the latter, but I'm sure that someone on here does..
Refer to this post regarding Triggers vs sql auditing:
http://social.msdn.microsoft.com/Forums/sqlserver/en-US/078d556d-8a5e-456e-98fe-76f731d7a9f2/trigger-vs-sql-server-audit?forum=sqldatabaseengine and as Enterprise edition is expensive, try to achieve your auditing needs with Triggers. Refer to this post on different auditing best practices
http://ask.sqlservercentral.com/questions/109633/auditing-best-practices.html. If you go for enterprise edition, you have also got Change Data capture to implement auditing.
16 People are following this question.
