http://www.sqlservercentral.com/blogs/sqlrepl/2012/10/10/auditing-changes-in-merge-replication/

Printed 2014/12/18 11:38PM

Auditing changes in Merge Replication

By Brandon J Williams, 2012/10/10

One trick I have learned from the folks on the Replication Support Team is how to proactively audit data changes in Merge Replication.  This is useful when troubleshooting issues such as identifying where data changes are coming from, who is causing them, and what the data is before and after the change.  I have found this valuable information to have on a few occasions and thought I would share.

Keep in mind this should only be used for troubleshooting purposes and should always be tested in pre-production prior to deploying.  Make sure it works first!

Auditing data changes for a Merge article can be done by creating insert, update, and delete triggers to capture data changes and record them into an audit table.  The audit table rows consist of GETDATE(), APP_NAME(), HOST_NAME(), SUSER_NAME(), column data, action type, command, and spid for each insert, update, and delete that occurs on the article to audit.  You will have to modify the script to adjust the name of the table being audited and the relevant columns that you think should be included in the audit data.  Usually just the primary key columns are enough, but other columns can be included as well.

Audit script

/************************************************************************
This is a script to implement audit triggers of insert, update, delete on 
a base table. It is based on a generic scenario for a table with four 
columns col1, col2, col3, col4.
 
It will insert into a table called source_audit.
 
Run this script on the database you would like to audit.
 
Test first to be sure it is working as expected!
 
After the problem occurs, export the contents of the source_audit table.
************************************************************************/
 
USE DB_to_audit
GO
 
-- Drop audit table if exists
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[source_audit]') AND type in (N'U'))
DROP TABLE [dbo].[source_audit]
 
-- Create audit table
CREATE TABLE [dbo].[source_audit]
    ([tstamp] datetime NULL, 
     [ProgramName] nvarchar(128) NULL, 
     [hostname] nvarchar(128) NULL, 
     [suser] nvarchar(128) NULL,
     [col1] int ,
     [col2] nchar(10) NULL,
     [col3] datetime NULL,
     [col4] binary NULL,
     [actiontype] char(2) NULL,
     [inputbuffer] nvarchar(255) NULL,
     [spid] int NULL)
GO
 
-------------------------------------------
-- INSERT trigger
-------------------------------------------
 
-- Delete trigger if exists
IF  EXISTS (SELECT * FROM sys.triggers WHERE object_id = OBJECT_ID(N'[dbo].[audit_source_INS]'))
DROP TRIGGER [dbo].[audit_source_INS]
GO
 
-- Create INSERT trigger
CREATE TRIGGER audit_source_INS
on Table_1
FOR INSERT
AS
DECLARE @command NVARCHAR(255)
 
if 0 = (select count(*) from inserted) return
 
CREATE TABLE #InputBuffer ([eventtype] NVARCHAR(30), [parameters] INT, [eventinfo] NVARCHAR(255))
INSERT INTO #InputBuffer exec('DBCC INPUTBUFFER(@@spid) WITH NO_INFOMSGS')
SELECT @command=eventinfo from #InputBuffer
 
INSERT INTO source_audit
SELECT getdate(), app_name(), host_name(), suser_name(), col1, col2, col3, col4, 'I', @command, @@SPID
FROM inserted
GO
 
-------------------------------------------
-- UPDATE trigger
-------------------------------------------
 
-- Delete trigger if exists
IF  EXISTS (SELECT * FROM sys.triggers WHERE object_id = OBJECT_ID(N'[dbo].[audit_source_UPD]'))
DROP TRIGGER [dbo].[audit_source_UPD]
GO
 
-- Create UPDATE trigger
CREATE TRIGGER audit_source_UPD
on Table_1
FOR UPDATE
AS
DECLARE @command NVARCHAR(255)
 
if 0 = (select count(*) from inserted) return
 
CREATE TABLE #InputBuffer ([eventtype] NVARCHAR(30), [parameters] INT, [eventinfo] NVARCHAR(255))
INSERT INTO #InputBuffer exec('DBCC INPUTBUFFER(@@spid) WITH NO_INFOMSGS')
SELECT @command=eventinfo from #InputBuffer
 
INSERT INTO source_audit
SELECT getdate(), app_name(), host_name(), suser_name(), col1, col2, col3, col4, 'UD', @command, @@SPID
FROM deleted
INSERT INTO source_audit
SELECT getdate(), app_name(), host_name(), suser_name(), col1, col2, col3, col4, 'UI', @command, @@SPID
FROM inserted
GO
 
-------------------------------------------
-- DELETE trigger
-------------------------------------------
 
-- Delete trigger if exists
IF  EXISTS (SELECT * FROM sys.triggers WHERE object_id = OBJECT_ID(N'[dbo].[audit_source_DEL]'))
DROP TRIGGER [dbo].[audit_source_DEL]
GO
 
-- Create DELETE trigger
CREATE TRIGGER audit_source_DEL
on Table_1
FOR INSERT
AS
DECLARE @command NVARCHAR(255)
 
if 0 = (select count(*) from inserted) return
 
CREATE TABLE #InputBuffer ([eventtype] NVARCHAR(30), [parameters] INT, [eventinfo] NVARCHAR(255))
INSERT INTO #InputBuffer exec('DBCC INPUTBUFFER(@@spid) WITH NO_INFOMSGS')
SELECT @command=eventinfo from #InputBuffer
 
INSERT INTO source_audit
SELECT getdate(), app_name(), host_name(), suser_name(), col1, col2, col3, col4, 'D', @command, @@SPID
FROM deleted
GO

Examining the results

Once the audit table and triggers are in place we can begin collecting audit data.  The audit data can be exported from the audit table after reproducing the problem to be queried at a later place and time, or it can be queried directly.  Here is a sample audit of a Merge publisher and the audit data after an update and sync from subscriber WS2008R2_1.

SELECT tstamp, ProgramName, hostname, suser,
col1, actiontype, inputbuffer, spid
FROM dbo.source_audit


 
Using this approach, we can identify where data changes are coming from, who is causing them, and what the data is before and after the change.  This can be very useful information to have, especially when troubleshooting conflicts and determining where the conflicting changes are originating — but I will save that for a future post.  If you would like help implementing an auditing scheme in your Merge topology, feel free to drop me a line or leave a comment below.

-Brandon Williams (blog | linkedin | twitter)


Copyright © 2002-2014 Simple Talk Publishing. All Rights Reserved. Privacy Policy. Terms of Use. Report Abuse.