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)
