Which SQL Server Audit Tools do not create triggers on tables? So far, I only know Entegra does not. Thanks.
What do you mean by audit tools??? Lumigent Log Explorer doesn't. Neither does Quest Central. Not sure what you mean though. MeanOldDBA derrickleggett@hotmail.com When life gives you a lemon, fire the DBA.
http://www.sql-server-performance.com/lockwoodtech_log_navigator_spotlight.asp Satya SKJ Moderator http://www.SQL-Server-Performance.Com/forum This posting is provided “AS IS†with no rights for the sake of knowledge sharing.
Derrick, I should be more specific what audit application I'm talking about. The audit application I'm referring to is the ones that will audit data changes. I find the following applications: http://www.lumigent.com/products/entegra.html http://www.krell-software.com/omniaudit/index.asp http://www.apexsql.com/index_aa.htm http://www.logpi.com/ http://www.upscene.com/index.htm?./products/audit/mssqllm_main.htm I wonder which one has less overhead. My guess is the one that does not just insert triggers to tables. Thanks.
The tools that read the SQL log are not the same as a real long term Audit tool. As an example I back up most of my transaction logs hourly so if someone says "Hey some users just changed some information in Table A", I could use a product like APEX SQL Log,Lumigent Log Explorer or LOGPI to find out who touched that table. But what if the request was "Who was the last person to change this row in a table" where that change happened 6 months ago? I have to search through 6 months of logs (at 24 logs per day) This is unreasonable. First off I'm just not going to keep that many logs. You need to know why the user wants to log data changes to help decide what product will work best. Trigger based products aren't necessarily bad for performance. A well written trigger should add negligible time to the execution of code. Having said that some products are true Auditing systems and don't need triggers. In my mind if there doesn't exist a method of storage for the audit data outside of the SQL Server Log it isn't a real Audit system. This is why Apex SQL has 2 separate products Apex SQL Audit and Apex SQL Log. The first one is trigger based the second just reads the log. But these product exist for different reasons. Don't confuse products like Apex SQL Audit, Omni Audit, Upscene's Log Manager and Entegra with products like Apex SQL Log or LogPI. These products are meant to address different issues. Other Audit tools are SQL AuditPro -http://www.macroenterprises.com/products/SQLAudit.htm and Idera's SQL compliance manager -http://www.idera.com/Products/SQLcm/Default.aspx (No triggers)
Another thought is under some circumstances a trigger based solution may be able to better audit if the appication were written using a single sign on to the database and then some internal user table tracking who users are. If everyone attaches to the databases with the same id, then any method that just reads the log will always indicate the same user made all changes. A trigger based system could supply the right answer. ... Lots to think about.
quote:Originally posted by fhanlon This is why Apex SQL has 2 separate products Apex SQL Audit and Apex SQL Log. The first one is trigger based the second just reads the log. But these product exist for different reasons. Don't confuse products like Apex SQL Audit, Omni Audit, Upscene's Log Manager and Entegra with products like Apex SQL Log or LogPI. These products are meant to address different issues. Other Audit tools are SQL AuditPro -http://www.macroenterprises.com/products/SQLAudit.htm and Idera's SQL compliance manager -http://www.idera.com/Products/SQLcm/Default.aspx (No triggers) thx for the plug! Brian Lockwood ApexSQL Software http://www.apexsql.com http://www.apexsql.com/blog/
I am thankful to for raising this question. i am also looking for audit tool which does not create triggers. http://www.softtreetech.com/download/dbaudit.exe also functioning from creating triggers.
http://www.sql-server-performance.com/lockwoodtech_log_navigator_spotlight.asp Satya SKJ Moderator http://www.SQL-Server-Performance.Com/forum This posting is provided “AS IS†with no rights for the sake of knowledge sharing.
Satya - thx also for props! the product is now called ApexSQL Log - and with 1.85 out in a week or so will be able to recover deleted data from truncated/dropped tables here is an interview with author who describes some upcoming features http://www.apexsql.com/blog/2005/06/developer-interview-with-ivan-erceg.html Brian Lockwood Apex SQL Tools http://www.apexsql.com http://www.apexsql.com/blog/
Rob As you can see a quick response from the vendor as well about the product, so I feel it is a sure shot for you. Satya SKJ Moderator http://www.SQL-Server-Performance.Com/forum This posting is provided “AS IS†with no rights for the sake of knowledge sharing.
