locked
Updates to a Tables Primary Key field(s) are not synchronised by Data Sync process? RRS feed

  • Question

  • When I update the value of a primary key field the change is not propagated to the other databases in the Data Sync topology.
    On investigating this issue I have found out the follow:
    - On deployment/provisioning of the Sync Topology the DataSync “dss” tables are populated with the all records (and associated Primary Key fields) of the associated user table.
    - When you Update a Primary Key field of the user table the associated DataSync table is not updated because the dss_update_trigger joins the DataSync table with the INSERTED table on the primary key fields and because we have updated one of the primary key fields the INSERTED record does not match a record in the associated DataSync table - so not update occurs.
    I’ve included T-SQL script below to try and illustrate the problem. There’s two parts to the script
    1) PART 1 - CREATE OBJECTS AND POPULATE WITH EXAMPLE DATA
    2) PART 2 - REVIEW DATA AND UPDATE PRIMARY KEY TO ILLUSTRATE PROBLEM

    --#####################################################################################
    -- PART 1 - CREATE OBJECTS AND POPULATE WITH EXAMPLE DATA
    --#####################################################################################
    CREATE SCHEMA [DataSync] AUTHORIZATION [dbo]
    GO
    
    -- CREATE TABLES
    CREATE TABLE [dbo].[ExampleTable](
    	[Project] [varchar](25) NOT NULL,
    	[SiteID] [varchar](25) NOT NULL,
    	[DepthFrom] [numeric](9, 3) NOT NULL,
    	[DepthTo] [numeric](9, 3) NOT NULL,
    	[DrillDate] [date] NOT NULL,
    	[Shift] [varchar](2) NOT NULL,
    	[DrillType] [varchar](8) NOT NULL,
    	[DrillCompany] [varchar](20) NOT NULL,
    	[DrillRig] [varchar](10) NULL,
    	[Driller] [varchar](50) NULL,
    	[BitSize] [numeric](9, 2) NULL,
    	[PsiCfm] [varchar](15) NULL,
    	[CasingType] [varchar](10) NULL,
    	[CasingDepth] [numeric](9, 2) NULL,
    	[CasingIDmm] [numeric](9, 2) NULL,
    	[EquipmentLost] [char](1) NULL,
    	[Comments] [varchar](250) NULL,
    	[InsertedBy] [varchar](100) NOT NULL,
    	[InsertedDate] [smalldatetime] NOT NULL,
    	[AuditGuid] [uniqueidentifier] NOT NULL,
     CONSTRAINT [PK_ExampleTable] PRIMARY KEY CLUSTERED 
    	(
    	[Project] ASC,
    	[SiteID] ASC,
    	[DepthFrom] ASC,
    	[DepthTo] ASC,
    	[DrillDate] ASC,
    	[Shift] ASC
    	)
    )
    GO
    
    CREATE TABLE [DataSync].[ExampleTable_dss_tracking](
    	[Project] [varchar](25) NOT NULL,
    	[SiteID] [varchar](25) NOT NULL,
    	[DepthFrom] [numeric](9, 3) NOT NULL,
    	[DepthTo] [numeric](9, 3) NOT NULL,
    	[DrillDate] [date] NOT NULL,
    	[Shift] [varchar](2) NOT NULL,
    	[update_scope_local_id] [int] NULL,
    	[scope_update_peer_key] [int] NULL,
    	[scope_update_peer_timestamp] [bigint] NULL,
    	[local_update_peer_key] [int] NOT NULL,
    	[local_update_peer_timestamp] [timestamp] NOT NULL,
    	[create_scope_local_id] [int] NULL,
    	[scope_create_peer_key] [int] NULL,
    	[scope_create_peer_timestamp] [bigint] NULL,
    	[local_create_peer_key] [int] NOT NULL,
    	[local_create_peer_timestamp] [bigint] NOT NULL,
    	[sync_row_is_tombstone] [int] NOT NULL,
    	[restore_timestamp] [bigint] NULL,
    	[last_change_datetime] [datetime] NULL,
     CONSTRAINT [PK_DataSync.ExampleTable_dss_tracking] PRIMARY KEY CLUSTERED 
    	(
    	[Project] ASC,
    	[SiteID] ASC,
    	[DepthFrom] ASC,
    	[DepthTo] ASC,
    	[DrillDate] ASC,
    	[Shift] ASC
    	)
    )
    GO
    
    -- CREATE dss triggers on user [ExampleTable]
    CREATE TRIGGER [dbo].[ExampleTable_dss_delete_trigger] ON [dbo].[ExampleTable] FOR DELETE AS
    UPDATE [side] SET [sync_row_is_tombstone] = 1, [local_update_peer_key] = 0, [restore_timestamp] = NULL, [update_scope_local_id] = NULL, [last_change_datetime] = GETDATE() FROM [DataSync].[ExampleTable_dss_tracking] [side] JOIN DELETED AS [d] ON [side].[Project] = [d].[Project] AND [side].[SiteID] = [d].[SiteID] AND [side].[DepthFrom] = [d].[DepthFrom] AND [side].[DepthTo] = [d].[DepthTo] AND [side].[DrillDate] = [d].[DrillDate] AND [side].[Shift] = [d].[Shift]
    GO
    
    CREATE TRIGGER [dbo].[ExampleTable_dss_insert_trigger] ON [dbo].[ExampleTable] FOR INSERT AS
    UPDATE [side] SET [sync_row_is_tombstone] = 0, [local_update_peer_key] = 0, [restore_timestamp] = NULL, [update_scope_local_id] = NULL, [last_change_datetime] = GETDATE() FROM [DataSync].[ExampleTable_dss_tracking] [side] JOIN INSERTED AS [i] ON [side].[Project] = [i].[Project] AND [side].[SiteID] = [i].[SiteID] AND [side].[DepthFrom] = [i].[DepthFrom] AND [side].[DepthTo] = [i].[DepthTo] AND [side].[DrillDate] = [i].[DrillDate] AND [side].[Shift] = [i].[Shift]
    INSERT INTO [DataSync].[ExampleTable_dss_tracking] ([i].[Project], [i].[SiteID], [i].[DepthFrom], [i].[DepthTo], [i].[DrillDate], [i].[Shift], [create_scope_local_id], [local_create_peer_key], [local_create_peer_timestamp], [update_scope_local_id], [local_update_peer_key], [sync_row_is_tombstone], [last_change_datetime], [restore_timestamp]) SELECT [i].[Project], [i].[SiteID], [i].[DepthFrom], [i].[DepthTo], [i].[DrillDate], [i].[Shift], NULL, 0, @@DBTS+1, NULL, 0, 0, GETDATE() , NULL FROM INSERTED AS [i] LEFT JOIN [DataSync].[ExampleTable_dss_tracking] [side] ON [side].[Project] = [i].[Project] AND [side].[SiteID] = [i].[SiteID] AND [side].[DepthFrom] = [i].[DepthFrom] AND [side].[DepthTo] = [i].[DepthTo] AND [side].[DrillDate] = [i].[DrillDate] AND [side].[Shift] = [i].[Shift] WHERE [side].[Project] IS NULL AND [side].[SiteID] IS NULL AND [side].[DepthFrom] IS NULL AND [side].[DepthTo] IS NULL AND [side].[DrillDate] IS NULL AND [side].[Shift] IS NULL
    GO
    
    CREATE TRIGGER [dbo].[ExampleTable_dss_update_trigger] ON [dbo].[ExampleTable] FOR UPDATE AS
    UPDATE [side] SET [local_update_peer_key] = 0, [restore_timestamp] = NULL, [update_scope_local_id] = NULL, [last_change_datetime] = GETDATE() FROM [DataSync].[ExampleTable_dss_tracking] [side] JOIN INSERTED AS [i] ON [side].[Project] = [i].[Project] AND [side].[SiteID] = [i].[SiteID] AND [side].[DepthFrom] = [i].[DepthFrom] AND [side].[DepthTo] = [i].[DepthTo] AND [side].[DrillDate] = [i].[DrillDate] AND [side].[Shift] = [i].[Shift]
    GO
    
    -- Populate user table with data
    -- Add 10 rows to [dbo].[ExampleTable] (dss insert trigger populates associated [DataSync].[ExampleTable_dss_tracking] table)
    INSERT INTO [dbo].[ExampleTable] ([Project], [SiteID], [DepthFrom], [DepthTo], [DrillDate], [Shift], [DrillType], [DrillCompany], [DrillRig], [Driller], [BitSize], [PsiCfm], [CasingType], [CasingDepth], [CasingIDmm], [EquipmentLost], [Comments], [InsertedBy], [InsertedDate], [AuditGuid]) VALUES ('Proj1', 'Site1', 0.000, 100.000, '2011-02-11', 'D', 'HQ', 'LITHO', '9000-2', NULL, 63.50, NULL, NULL, 100.00, 102.00, NULL, NULL, 'Computer\User', '2011-11-25 11:48:00.000', '1073518b-2d8e-471d-8a94-9c0068e7e40e')
    INSERT INTO [dbo].[ExampleTable] ([Project], [SiteID], [DepthFrom], [DepthTo], [DrillDate], [Shift], [DrillType], [DrillCompany], [DrillRig], [Driller], [BitSize], [PsiCfm], [CasingType], [CasingDepth], [CasingIDmm], [EquipmentLost], [Comments], [InsertedBy], [InsertedDate], [AuditGuid]) VALUES ('Proj1', 'Site1', 100.000, 103.000, '2011-03-11', 'D', 'NQ', 'LITHO', '9000-2', NULL, 47.60, NULL, NULL, NULL, NULL, NULL, NULL, 'Computer\User', '2011-11-25 11:48:00.000', 'b1454e2c-f726-40ef-a382-1cda51ab6098')
    INSERT INTO [dbo].[ExampleTable] ([Project], [SiteID], [DepthFrom], [DepthTo], [DrillDate], [Shift], [DrillType], [DrillCompany], [DrillRig], [Driller], [BitSize], [PsiCfm], [CasingType], [CasingDepth], [CasingIDmm], [EquipmentLost], [Comments], [InsertedBy], [InsertedDate], [AuditGuid]) VALUES ('Proj1', 'Site1', 103.000, 106.000, '2011-03-11', 'D', 'NQ', 'LITHO', '9000-2', NULL, 47.60, NULL, NULL, NULL, NULL, NULL, NULL, 'Computer\User', '2011-11-25 11:48:00.000', 'ac7dd54d-395e-4bb1-9dfe-eb2bcdd4f266')
    INSERT INTO [dbo].[ExampleTable] ([Project], [SiteID], [DepthFrom], [DepthTo], [DrillDate], [Shift], [DrillType], [DrillCompany], [DrillRig], [Driller], [BitSize], [PsiCfm], [CasingType], [CasingDepth], [CasingIDmm], [EquipmentLost], [Comments], [InsertedBy], [InsertedDate], [AuditGuid]) VALUES ('Proj1', 'Site1', 106.000, 112.000, '2011-04-11', 'D', 'NQ', 'LITHO', '9000-2', NULL, 47.60, NULL, NULL, NULL, NULL, NULL, NULL, 'Computer\User', '2011-11-25 11:48:00.000', 'd83dc2fc-cb6d-45b3-95bc-fdc8627b553a')
    INSERT INTO [dbo].[ExampleTable] ([Project], [SiteID], [DepthFrom], [DepthTo], [DrillDate], [Shift], [DrillType], [DrillCompany], [DrillRig], [Driller], [BitSize], [PsiCfm], [CasingType], [CasingDepth], [CasingIDmm], [EquipmentLost], [Comments], [InsertedBy], [InsertedDate], [AuditGuid]) VALUES ('Proj1', 'Site1', 112.000, 133.000, '2011-04-11', 'D', 'NQ', 'LITHO', '9000-2', NULL, 47.60, NULL, NULL, NULL, NULL, NULL, NULL, 'Computer\User', '2011-11-25 11:48:00.000', '55b6f6b7-a342-4e13-8024-87ebd8774d55')
    INSERT INTO [dbo].[ExampleTable] ([Project], [SiteID], [DepthFrom], [DepthTo], [DrillDate], [Shift], [DrillType], [DrillCompany], [DrillRig], [Driller], [BitSize], [PsiCfm], [CasingType], [CasingDepth], [CasingIDmm], [EquipmentLost], [Comments], [InsertedBy], [InsertedDate], [AuditGuid]) VALUES ('Proj1', 'Site1', 133.000, 166.000, '2011-05-11', 'D', 'NQ', 'LITHO', '9000-2', NULL, 47.60, NULL, NULL, NULL, NULL, NULL, NULL, 'Computer\User', '2011-11-25 11:48:00.000', '6eadb890-2d68-4302-af73-9a6711c0fb11')
    INSERT INTO [dbo].[ExampleTable] ([Project], [SiteID], [DepthFrom], [DepthTo], [DrillDate], [Shift], [DrillType], [DrillCompany], [DrillRig], [Driller], [BitSize], [PsiCfm], [CasingType], [CasingDepth], [CasingIDmm], [EquipmentLost], [Comments], [InsertedBy], [InsertedDate], [AuditGuid]) VALUES ('Proj1', 'Site1', 166.000, 184.000, '2011-05-11', 'D', 'NQ', 'LITHO', '9000-2', NULL, 47.60, NULL, NULL, NULL, NULL, NULL, NULL, 'Computer\User', '2011-11-25 11:48:00.000', 'd174a710-ae37-4fb9-97e6-89964dc98e61')
    INSERT INTO [dbo].[ExampleTable] ([Project], [SiteID], [DepthFrom], [DepthTo], [DrillDate], [Shift], [DrillType], [DrillCompany], [DrillRig], [Driller], [BitSize], [PsiCfm], [CasingType], [CasingDepth], [CasingIDmm], [EquipmentLost], [Comments], [InsertedBy], [InsertedDate], [AuditGuid]) VALUES ('Proj1', 'Site1', 184.000, 205.000, '2011-06-11', 'D', 'NQ', 'LITHO', '9000-2', NULL, 47.60, NULL, NULL, NULL, NULL, NULL, NULL, 'Computer\User', '2011-11-25 11:48:00.000', 'b43641c6-1ac2-4a30-a5be-4e8d21d567c9')
    INSERT INTO [dbo].[ExampleTable] ([Project], [SiteID], [DepthFrom], [DepthTo], [DrillDate], [Shift], [DrillType], [DrillCompany], [DrillRig], [Driller], [BitSize], [PsiCfm], [CasingType], [CasingDepth], [CasingIDmm], [EquipmentLost], [Comments], [InsertedBy], [InsertedDate], [AuditGuid]) VALUES ('Proj1', 'Site1', 205.000, 223.000, '2011-06-11', 'D', 'NQ', 'LITHO', '9000-2', NULL, 47.60, NULL, NULL, NULL, NULL, NULL, NULL, 'Computer\User', '2011-11-25 11:48:00.000', '2e49d60a-fd8b-4ed5-8f64-a7ddd5e6b619')
    INSERT INTO [dbo].[ExampleTable] ([Project], [SiteID], [DepthFrom], [DepthTo], [DrillDate], [Shift], [DrillType], [DrillCompany], [DrillRig], [Driller], [BitSize], [PsiCfm], [CasingType], [CasingDepth], [CasingIDmm], [EquipmentLost], [Comments], [InsertedBy], [InsertedDate], [AuditGuid]) VALUES ('Proj1', 'Site1', 223.000, 232.000, '2011-07-11', 'D', 'NQ', 'LITHO', '9000-2', NULL, 47.60, NULL, NULL, NULL, NULL, NULL, NULL, 'Computer\User', '2011-11-25 11:48:00.000', 'f2e52b98-32aa-41ca-b8b4-be48699c8f1a')
    
    GO
    
    
    --#####################################################################################
    -- PART 2 - REVIEW DATA AND UPDATE PRIMARY KEY TO ILLUSTRATE PROBLEM
    --#####################################################################################
    
    -- Look at data in the two tables before update (10 records in each)
    SELECT *
    FROM [dbo].[ExampleTable]
    
    SELECT *
    FROM [DataSync].[ExampleTable_dss_tracking]
    
    
    -- Update the [Shift] column (which is part of the primary key) from 'D' to 'N' for the first record
    UPDATE [dbo].[ExampleTable]
    SET [Shift] = 'N'
    WHERE [Project] = 'Proj1'
    	AND [SiteID] = 'Site1'
    	AND [DepthFrom] = 0
    	AND [DepthTo] = 100
    	AND [DrillDate] = '2011-02-11'
    	AND [Shift] = 'D'
    GO
    
    -- The "ExampleTable_dss_update_trigger" (shown below commented out) fires as a result of the update however as the 
    -- [INSERTED].[Shift] is not equal to the [DataSync].[ExampleTable_dss_tracking].[Shift] no update occurs to the [DataSync].[ExampleTable_dss_tracking] table
    /*
    UPDATE [side] 
    SET [local_update_peer_key] = 0, 
    	[restore_timestamp] = NULL, 
    	[update_scope_local_id] = NULL, 
    	[last_change_datetime] = GETDATE() 
    FROM [DataSync].[ExampleTable_dss_tracking] [side] 
    	JOIN INSERTED AS [i] 
    		ON [side].[Project] = [i].[Project] 
    		AND [side].[SiteID] = [i].[SiteID] 
    		AND [side].[DepthFrom] = [i].[DepthFrom] 
    		AND [side].[DepthTo] = [i].[DepthTo] 
    		AND [side].[DrillDate] = [i].[DrillDate] 
    		AND [side].[Shift] = [i].[Shift]
    */
    
    -- Look at the resultant first record from each table
    -- Record in user table has been updated 
    SELECT *
    FROM [dbo].[ExampleTable]
    WHERE [Project] = 'Proj1'
    	AND [SiteID] = 'Site1'
    	AND [DepthFrom] = 0
    	AND [DepthTo] = 100
    	AND [DrillDate] = '2011-02-11'
    
    -- There has been no change to the [DataSync].[ExampleTable_dss_tracking] table so the change is not propagated during the next sync cycle
    SELECT *
    FROM [DataSync].[ExampleTable_dss_tracking]
    WHERE [Project] = 'Proj1'
    	AND [SiteID] = 'Site1'
    	AND [DepthFrom] = 0
    	AND [DepthTo] = 100
    	AND [DrillDate] = '2011-02-11'
    
    

    I'm not sure if I'm missing something but from what I can see Updates to Primary Key fields are not synchronised by the Data Sync process. Definitely when I look at the Sync logs no changes are uploaded and when I compare the database where the change was made to other databases in the Sync Topology the change is not applied elsewhere??

    This is causing the databases in my sync topology to get out of sync very quickly so any assistance would be greatly appreciated.


    Wednesday, February 8, 2012 9:47 AM

All replies

  • you're not missing anything :)

    afaik, this is a known behaviour (even in Sync Framework)

    even if you modify the trigger to update the PK in the tracking table, the update would be recorded but it will not be applied to the other replicas because the update on the destination will not find a matching record because the PK has changed already.

    Assuming you modify the trigger to make the update to the PK appear as an insert, a new row will be applied in the replica as an insert but the previous record will still be there (you now have two records).

    the easiest workaround is to do a DELETE and RE-INSERT. you delete the old row with the old PK and reinsert the row with the new PK.

    the change will be applied at the destination deleting the previous row with the old PK and reinserting a new row with the new PK.

     

    Wednesday, February 8, 2012 10:08 AM
    Answerer
  • Wow!

    Is this not causing major issues for many people?

    I know for the sync topologies I support this makes using the SQL Azure Data Sync not an option as editing the PK of a record is a common occurrence.

    Wednesday, February 8, 2012 4:00 PM
  • Well following the post from JuneT I thought about the DELETE & RE-INSERT workaround and thought that the dss delete & insert triggers do exactly that, so I changed the dss UPDATE trigger to use the code from the dss delete & insert trigger (code shown below). This way I can update the PK of a record like normal but the sync process treats it as a delete and insert.

    Could probably improve trigger so it only does this when updating a PK field otherwise do normal dss update trigger. Anyway this logic seams to make sense and most of all it propogates the changes around all databases in the sync topology.

    ALTER TRIGGER [dbo].[ExampleTable_dss_update_trigger] 
    ON [dbo].[ExampleTable] 
    FOR UPDATE 
    
    AS
    
    -- This is what the delete trigger does
    UPDATE [side] 
    SET [sync_row_is_tombstone] = 1, 
    	[local_update_peer_key] = 0, 
    	[restore_timestamp] = NULL, 
    	[update_scope_local_id] = NULL, 
    	[last_change_datetime] = GETDATE() 
    FROM [DataSync].[ExampleTable_dss_tracking] [side] 
    	JOIN DELETED AS [d] 
    		ON [side].[Project] = [d].[Project] 
    		AND [side].[SiteID] = [d].[SiteID] 
    		AND [side].[DepthFrom] = [d].[DepthFrom] 
    		AND [side].[DepthTo] = [d].[DepthTo] 
    		AND [side].[DrillDate] = [d].[DrillDate] 
    		AND [side].[Shift] = [d].[Shift]
    		
    -- This is what the Insert trigger does
    UPDATE [side] 
    SET [sync_row_is_tombstone] = 0, 
    	[local_update_peer_key] = 0, 
    	[restore_timestamp] = NULL, 
    	[update_scope_local_id] = NULL, 
    	[last_change_datetime] = GETDATE() 
    FROM [DataSync].[ExampleTable_dss_tracking] [side] 
    	JOIN INSERTED AS [i] 
    		ON [side].[Project] = [i].[Project] 
    			AND [side].[SiteID] = [i].[SiteID] 
    			AND [side].[DepthFrom] = [i].[DepthFrom] 
    			AND [side].[DepthTo] = [i].[DepthTo] 
    			AND [side].[DrillDate] = [i].[DrillDate] 
    			AND [side].[Shift] = [i].[Shift]
    
    INSERT INTO [DataSync].[ExampleTable_dss_tracking] 
    	([i].[Project], [i].[SiteID], [i].[DepthFrom], [i].[DepthTo], [i].[DrillDate], [i].[Shift], [create_scope_local_id], [local_create_peer_key], [local_create_peer_timestamp], [update_scope_local_id], [local_update_peer_key], [sync_row_is_tombstone], [last_change_datetime], [restore_timestamp]) 
    SELECT [i].[Project], [i].[SiteID], [i].[DepthFrom], [i].[DepthTo], [i].[DrillDate], [i].[Shift], NULL, 0, @@DBTS+1, NULL, 0, 0, GETDATE() , NULL 
    FROM INSERTED AS [i] 
    	LEFT JOIN [DataSync].[ExampleTable_dss_tracking] [side] 
    		ON [side].[Project] = [i].[Project] 
    			AND [side].[SiteID] = [i].[SiteID] 
    			AND [side].[DepthFrom] = [i].[DepthFrom] 
    			AND [side].[DepthTo] = [i].[DepthTo] 
    			AND [side].[DrillDate] = [i].[DrillDate] 
    			AND [side].[Shift] = [i].[Shift] 
    WHERE [side].[Project] IS NULL 
    	AND [side].[SiteID] IS NULL 
    	AND [side].[DepthFrom] IS NULL 
    	AND [side].[DepthTo] IS NULL 
    	AND [side].[DrillDate] IS NULL 
    	AND [side].[Shift] IS NULL
    GO
    
    
    

    I would be very interested in any feedback on this solution (good or bad), and if anyone can see any issues with replacing the dss update triggers this way?

    Wednesday, February 8, 2012 4:39 PM
  • your assumption is right, you should only be doing the delete/insert if the updated column is the PK, otherwise you should let the normal DSS update trigger as it is.
    Thursday, February 9, 2012 10:10 AM
    Answerer
  • Craig,

    You bring up some very good points. The sync team is looking at what kind of implications this would have on our system. So far the biggest difference is what would happen in the case of a collision.

    Austin

    Thursday, February 9, 2012 7:26 PM
  • one other thing to note is that this DSS trigger update has to be applied on all members. A newly added member will have to be modified as well.

    Friday, February 10, 2012 1:05 AM
    Answerer
  • Hi JuneT & Austin,

    Thankyou very much for your feedback - much appreciated!

    Austin,

    Is there anyway of being notified by the Sync Team when a permanent solution for this is avaliable? At the moment I'll have to go with the work around, but as JuneT pointed out anytime the sync topology is changed I'm going to have to remember to include deploying this workaround as an additional step.

    Cheers

    Craig

    Friday, February 10, 2012 2:24 PM