September 12, 2012 at 7:12 am
Hello All,
We all know that in SQL Server standard edition we cannot do any auditing on Server and DB Level. So, alternative for this is to create triggers. Now my questions is, I would like to audit all tables(300) on my database. I am using is standard edition. The alternative solution I thought is of having DML and DDL triggers on whole database. Now for DML triggers I need to create 300 triggers to audit activity on each table. So, by creating these many triggers will there be any performance impact or will be there be any issues. Any suggestions??
September 12, 2012 at 8:49 am
DBA_Learner (9/12/2012)
Hello All,We all know that in SQL Server standard edition we cannot do any auditing on Server and DB Level. So, alternative for this is to create triggers. Now my questions is, I would like to audit all tables(300) on my database. I am using is standard edition. The alternative solution I thought is of having DML and DDL triggers on whole database. Now for DML triggers I need to create 300 triggers to audit activity on each table. So, by creating these many triggers will there be any performance impact or will be there be any issues. Any suggestions??
There will of course be a performance impact from a trigger. There will not be any additional impact because there are so many triggers. The performance hit will be based on each trigger. Being on standard edition that is probably about the only way to get auditing on all 300 tables. Just make absolutely certain that your triggers are set based and can handle multiple row insert/update/deletes.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
September 12, 2012 at 8:56 am
And minimize any logic in the triggers. They have to be fast and accurate. Remember, if the trigger fails so does the transaction that fires it.
September 12, 2012 at 9:54 am
Ya, I am not implementing any spcific logic..here is the way i am doing..
CREATE TABLE LOG_TABLE (Add_dttm datetime DEFAULT (GetDate()), TABLE_NAME sysname, Activity char(6),login_sname sysname DEFAULT SUSER_SNAME())
GO
DECLARE @sql varchar(8000), @TABLE_NAME sysname
SET NOCOUNT ON
SELECT @TABLE_NAME = MIN(TABLE_NAME) FROM INFORMATION_SCHEMA.Tables
WHILE @TABLE_NAME IS NOT NULL
BEGIN
SELECT @sql = 'CREATE TRIGGER [' + @TABLE_NAME + '_Usage_TR] ON [' + @TABLE_NAME +'] '
+ 'FOR INSERT, UPDATE, DELETE AS '
+ 'IF EXISTS (SELECT * FROM inserted) AND NOT EXISTS (SELECT * FROM deleted) '
+ 'INSERT INTO LOG_TABLE (TABLE_NAME,Activity) SELECT ''' + @TABLE_NAME + ''', ''INSERT''' + ' '
+ 'IF EXISTS (SELECT * FROM inserted) AND EXISTS (SELECT * FROM deleted) '
+ 'INSERT INTO LOG_TABLE (TABLE_NAME,Activity) SELECT ''' + @TABLE_NAME + ''', ''UPDATE''' + ' '
+ 'IF NOT EXISTS (SELECT * FROM inserted) AND EXISTS (SELECT * FROM deleted) '
+ 'INSERT INTO LOG_TABLE (TABLE_NAME,Activity) SELECT ''' + @TABLE_NAME + ''', ''DELETE''' + ' GO'
SELECT @sql
EXEC(@sql)
SELECT @TABLE_NAME = MIN(TABLE_NAME) FROM INFORMATION_SCHEMA.Tables WHERE TABLE_NAME > @TABLE_NAME
END
SET NOCOUNT OFF
Also, just want to know how about doing server side tracing using tsql code..will that work?
September 12, 2012 at 12:53 pm
DBA_Learner (9/12/2012)
Ya, I am not implementing any spcific logic..here is the way i am doing..CREATE TABLE LOG_TABLE (Add_dttm datetime DEFAULT (GetDate()), TABLE_NAME sysname, Activity char(6),login_sname sysname DEFAULT SUSER_SNAME())
GO
DECLARE @sql varchar(8000), @TABLE_NAME sysname
SET NOCOUNT ON
SELECT @TABLE_NAME = MIN(TABLE_NAME) FROM INFORMATION_SCHEMA.Tables
WHILE @TABLE_NAME IS NOT NULL
BEGIN
SELECT @sql = 'CREATE TRIGGER [' + @TABLE_NAME + '_Usage_TR] ON [' + @TABLE_NAME +'] '
+ 'FOR INSERT, UPDATE, DELETE AS '
+ 'IF EXISTS (SELECT * FROM inserted) AND NOT EXISTS (SELECT * FROM deleted) '
+ 'INSERT INTO LOG_TABLE (TABLE_NAME,Activity) SELECT ''' + @TABLE_NAME + ''', ''INSERT''' + ' '
+ 'IF EXISTS (SELECT * FROM inserted) AND EXISTS (SELECT * FROM deleted) '
+ 'INSERT INTO LOG_TABLE (TABLE_NAME,Activity) SELECT ''' + @TABLE_NAME + ''', ''UPDATE''' + ' '
+ 'IF NOT EXISTS (SELECT * FROM inserted) AND EXISTS (SELECT * FROM deleted) '
+ 'INSERT INTO LOG_TABLE (TABLE_NAME,Activity) SELECT ''' + @TABLE_NAME + ''', ''DELETE''' + ' GO'
SELECT @sql
EXEC(@sql)
SELECT @TABLE_NAME = MIN(TABLE_NAME) FROM INFORMATION_SCHEMA.Tables WHERE TABLE_NAME > @TABLE_NAME
END
SET NOCOUNT OFF
Also, just want to know how about doing server side tracing using tsql code..will that work?
That isn't exactly what I would call auditing but it will give you a history of when an insert, update or delete is executed for any table. Not sure how useful that really is. Be careful. Your list of tables does not exclude your audit table so your code will generate the trigger on your LOG_TABLE too. That will put you in an infinite loop because it will keep trying to insert data to itself which will cause the trigger to fire again...and again...and again...
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
September 12, 2012 at 1:33 pm
ya that's correct..How about working on Server side scripting...instead of setting sql profile setup on another server?/
September 12, 2012 at 1:40 pm
if you want to keep track of old and new values, I think the Change Data Capture is what you are after;
there is a project on codeplex which adds a CDC-equivilent to 2005 and above Standard Edition SQL Server.
check this out and see if it's going to add the tracking you want:
http://standardeditioncdc.codeplex.com/
Lowell
September 13, 2012 at 8:57 am
CDC captures only insert,update,delete on tables..but I don't think it captures drop,truncate,select ...Also in order to create that we need sql agent which on back ground runs the jobs..It requires separate set of system tables and need to enable cdc on each table. I think server side scripting will be more better interms of creation and performance..
September 13, 2012 at 9:06 am
DBA_Learner (9/13/2012)
CDC captures only insert,update,delete on tables..but I don't think it captures drop,truncate,select ...Also in order to create that we need sql agent which on back ground runs the jobs..It requires separate set of system tables and need to enable cdc on each table. I think server side scripting will be more better interms of creation and performance..
but what server side events, extended events, etc can capture updates/inserts/deletes?
I'm under the impression those changes are available only inside of a the trigger/OUTPUT clauses, and not available anywhere else...
DDL events like DROP and CREATE, yes...but not DML, or am I wrong?
Lowell
September 13, 2012 at 2:37 pm
Simple method is to have server side tracing using TSQL..Let me know how this code is...
--Server Side tracing
declare @rc int
declare @TraceID int
declare @maxfilesize bigint
set @maxfilesize = 2
-- Please replace the text InsertFileNameHere, with an appropriate
-- filename prefixed by a path, e.g., c:\MyFolder\MyTrace. The .trc extension
-- will be appended to the filename automatically. If you are writing from
-- remote server to local drive, please use UNC path and make sure server has
-- write access to your network share
exec @rc = sp_trace_create @TraceID output, 0,
N'D:\TraceFile\Audit', @maxfilesize, NULL
if (@rc != 0) goto error
-- Client side File and Table cannot be scripted
-- Set the events
declare @on bit
set @on = 1
--SQL STMTSTARTING
exec sp_trace_setevent @TraceID, 40, 1, @on
exec sp_trace_setevent @TraceID, 40, 10, @on
exec sp_trace_setevent @TraceID, 40, 11, @on
exec sp_trace_setevent @TraceID, 40, 14, @on
exec sp_trace_setevent @TraceID, 40, 15, @on
exec sp_trace_setevent @TraceID, 40, 18, @on
exec sp_trace_setevent @TraceID, 40, 34, @on
exec sp_trace_setevent @TraceID, 40, 35, @on
--SQLSTMTCOMPLETED
exec sp_trace_setevent @TraceID, 41, 1, @on
exec sp_trace_setevent @TraceID, 41, 10, @on
exec sp_trace_setevent @TraceID, 41, 11, @on
exec sp_trace_setevent @TraceID, 41, 14, @on
exec sp_trace_setevent @TraceID, 41, 15, @on
exec sp_trace_setevent @TraceID, 41, 18, @on
exec sp_trace_setevent @TraceID, 41, 34, @on
exec sp_trace_setevent @TraceID, 41, 35, @on
--SP Completion
exec sp_trace_setevent @TraceID, 45, 1, @on
exec sp_trace_setevent @TraceID, 45, 10, @on
exec sp_trace_setevent @TraceID, 45, 11, @on
exec sp_trace_setevent @TraceID, 45, 12, @on
exec sp_trace_setevent @TraceID, 45, 13, @on
exec sp_trace_setevent @TraceID, 45, 14, @on
exec sp_trace_setevent @TraceID, 45, 15, @on
exec sp_trace_setevent @TraceID, 45, 16, @on
--exec sp_trace_setevent @TraceID, 45, 17, @on
exec sp_trace_setevent @TraceID, 45, 18, @on
--exec sp_trace_setevent @TraceID, 45, 25, @on
exec sp_trace_setevent @TraceID, 45, 26, @on
exec sp_trace_setevent @TraceID, 45, 34, @on
exec sp_trace_setevent @TraceID, 45, 35, @on
-- Set the trace status to start
exec sp_trace_setstatus @TraceID, 1
-- display trace id for future references
select TraceID=@TraceID
goto finish
error:
select ErrorCode=@rc
finish:
go
--Execution
SELECT TextData,DatabaseName,ApplicationName,LoginName,ServerName, StartTime
FROM FN_TRACE_GETTABLE('D:\TraceFile\Audit.trc', DEFAULT)
September 13, 2012 at 2:49 pm
oh yeah, i'm very familiar with the server side trace, but that only can capture the command text that was executed, for example.It cannot catch old versus new values, which is what I thought you were after.
reading your post again, I think I might be the one who injected old vs new values into the conversation, sorry.
Lowell
September 14, 2012 at 7:09 am
Yeah i think there some confusion went..Anyways as you said server tracing gives what are all things happeining on the client...we actually not required old values of data of what being modified.just want to audit the things of what's happening
September 17, 2012 at 9:07 am
You can also use event notifications and Service Broker to audit DDL and DML events in Standard Edition. They run asynchronously, so you don't have the potential direct impact on the end user application like you have with triggers.
September 17, 2012 at 9:18 am
There is no DML Event Notifications;
there used to be a mistake in the documentation that seems to imply it, but if you look at the actual events available, they are all DDL events.
Colleen M. Morrow (9/17/2012)
You can also use event notifications and Service Broker to audit DDL and DML events in Standard Edition. They run asynchronously, so you don't have the potential direct impact on the end user application like you have with triggers.
Lowell
September 17, 2012 at 9:23 am
The AUDIT_SCHEMA_OBJECT_ACCESS_EVENT group can be used to audit DML statements. Including failed events (i.e. permission denied).
Viewing 15 posts - 1 through 15 (of 23 total)
You must be logged in to reply to this topic. Login to reply