Tracking Azure SQL Data Sync Service data changes

First published on MSDN on Oct 08, 2017
Customers often ask for the capability of tracking the Azure SQL Data Sync Service (DSS) data changes. Since we do not have built-in feature in DSS to provide this option, customers need to build their own solutions.

To help, we verified that if your DSS login is unique, the trigger/auditing table method could provide the functionality of tracking the DSS data changes.

Below is a sample script for your reference. The idea is to create the trigger/auditing table for each of the tables in the sync group schema, on both the hub and member databases after the initial provisioning.   When the sync task is executed, the triggers will be fired, and the auditing tables updated. You can check the auditing table to track the data changes by the unique DSS login ID after the task, and compare the auditing tables between the hub and member databases.



-- table

CREATE TABLE [dbo].[TestEMP]

(

[EmpID] [int] NOT NULL PRIMARY KEY,

[LastName] [varchar](255) NULL,

[FirstName] [varchar](255) NULL,

[Address] [varchar](255) NULL,

);

go





-- Auditing table

create table TestEMP_Audit

(

EmpID int,

[LastName] [varchar](255) NULL,

[FirstName] [varchar](255) NULL,

[Address] [varchar](255) NULL,

Activity varchar(20),

DoneBy varchar(50),

Date_Time datetime NOT NULL DEFAULT GETDATE()

);

Go





-- Trigger

create trigger TestEMP_trigger on TestEMP

after UPDATE, INSERT, DELETE as

declare @user varchar(20), @activity varchar(20);

SET @user = SYSTEM_USER;

if exists(SELECT * from inserted) and exists (SELECT * from deleted)

begin

SET @activity = 'UPDATE';

INSERT TestEMP_Audit

(EmpID,LastName,FirstName, Address, Activity, DoneBy)

SELECT EmpID, LastName, FirstName, Address, @activity, @user FROM Deleted

INSERT TestEMP_Audit

(EmpID,LastName,FirstName,Address, Activity, DoneBy)

SELECT EmpID, LastName, FirstName, Address, @activity, @user FROM inserted

end

If exists (Select * from inserted) and not exists(select * from deleted)

begin

SET @activity = 'INSERT';

INSERT TestEMP_Audit

(EmpID,LastName,FirstName,Address, Activity, DoneBy)

SELECT EmpID, LastName, FirstName, Address, @activity, @user FROM inserted

end

If exists(select * from deleted) and not exists(select * from inserted)

begin

SET @activity = 'DELETE';

INSERT TestEMP_Audit

(EmpID,LastName,FirstName,Address, Activity, DoneBy)

SELECT EmpID, LastName, FirstName, Address, @activity, @user FROM Deleted

end