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

Tracking changes to a database via triggers and the transaction logs Expand / Collapse
Author
Message
Posted Sunday, March 10, 2013 5:33 PM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Yesterday @ 2:52 PM
Points: 65, Visits: 143
Hello --

We have SQL Server 2008R2 standard running on one of our servers. The server contains three databases. One of the databases contains data that normally does not change, and is of such nature that if a change occurs, the administrators should be notified of the event. The databases on the server all part of a maintenance plan that includes Full, Differential, and Transaction Log backups, the last of which is backed up once every hour. The Database Mail utility has been configured so that notifications of job failures are sent to the administrators.

A colleague of mine suggested that a trigger could be created that would monitor the transaction logs for entries that recorded any changes in the database. This trigger could then send a notification to the administrators. I had several questions concerning this approach.

1. Is this the correct approach?

2. If the answer to the first question is yes, is this something that can be done through the SQL Studio?
Post #1429035
Posted Sunday, March 10, 2013 6:08 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 10:32 AM
Points: 5,567, Visits: 24,757
Have you examined / evaluated the use of Change Data Tracking. If not you may want to start here"

http://msdn.microsoft.com/en-us/library/bb933875(v=sql.105).aspx


If everything seems to be going well, you have obviously overlooked something.

Ron

Please help us, help you -before posting a question please read

Before posting a performance problem please read
Post #1429041
Posted Monday, March 11, 2013 7:11 AM


Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Friday, June 6, 2014 11:07 AM
Points: 11, Visits: 47
Hi!

I wrote a simple script one time that can help you on this question:

/*

TRACKING DML COMMANDS (INSERT,UPDATE,DELETE)
BY ANDRÉ CÉSAR RODRIGUES 13/09/2012 V1

*/

--FIRST YOU NEED TO CREATE IN SOME DATABASE THE TABLE BELOW THAT WILL KEEP THE TRACKING DATA

CREATE TABLE DMLMon(Changed TIMESTAMP,DateChanged DateTime,TableName char(30),UserName varchar(50),AppName varchar(50),host_name varchar(50),Operation char(6))

--DROP TABLE DMLMon

--CHANGE THE NAME TbMon FOR THE NAME OF THE TABLE YOU WANT TO MONITORING.

CREATE TRIGGER AuditDML ON TbMon
AFTER
INSERT, UPDATE, DELETE
AS DECLARE @Operation char(6)
if exists (select * from inserted) and exists (select * from deleted)
select @Operation = 'Udate'
else if exists (select * from inserted)
select @Operation = 'Insert'
else
select @Operation = 'Delete'
INSERT INTO DATABASE.dbo.DMLMon(DateChanged,TableName,UserName,AppName,host_name,Operation)
SELECT GetDate(), 'TbAuditada', suser_sname(),app_name(),host_name(),@Operation
-- Change the DATABASE to the name of the database that you create the table in the beggining of this script.

/*

Good luck!

Regards,


André CR
Post #1429247
Posted Monday, March 11, 2013 7:16 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 1:44 PM
Points: 42,454, Visits: 35,509
kaplan71 (3/10/2013)
A colleague of mine suggested that a trigger could be created that would monitor the transaction logs for entries that recorded any changes in the database. This trigger could then send a notification to the administrators. I had several questions concerning this approach.

1. Is this the correct approach?

2. If the answer to the first question is yes, is this something that can be done through the SQL Studio?


Well, first problem there is that the transaction log is not a table and can't have a trigger put on it. Second problem is that the transaction log is not an audit log, it's for database integrity and durability.

Have a look at Change Data Capture and SQLAudit



Gail Shaw
Microsoft Certified Master: SQL Server 2008, MVP
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Post #1429250
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse