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
