The diagrams below summarizes change tracking approaches, inline and decoupled:

To build this demo, I started with demo II source code and made modifications to the TSQL code in the demo.sql and server_procs.sql files. The commands for selecting inserts, updates, and deletes were part of the code I needed to change them as well. The rest of the code remain intact. So, let's start by looking at the change tracking table and triggers code in the demo.sql. I will focus on 'orders' table only but the same logic goes for 'order_details'.

Change Tracking Table and Triggers

The goal of decupled change tracking model is not to make any changes to the base table schema and thus we will maintain all tracking metadata in a separate table that I named 'orders_tracking'; TSQL code below shows the schema of the table. Notice that, the 'operation' column takes one of the following values

• 'I' - for insert

• 'U' - for update

• 'D' - for delete

Another thing to note here is that the tracking table has 'order_id' as primary key. That means deleting a row in base table and inserting a new row with same PK will result in an error. You can fix that error by altering the insert trigger to update existing old row. I will leave that as an exercise to the reader.

--
-- Create tracking tables
--   the tracking table will store the last operation (I, U, D) to a given row. Rows are identified
--   by PK
--
CREATE TABLE pub_decoupled..orders_tracking(
    order_id int NOT NULL primary key,       
    operation char NOT NULL,
    update_originator_id int default 0,
    update_timestamp timestamp,   
    create_timestamp bigint default @@DBTS + 1)

Recall that in coupled change tracking we only needed update and delete triggers. However, with decoupled tracking we will need another one for inserts too. The main role the tracking triggers play here, other than inserting the row in the tracking table, is to reset the originator_id to the one of the server (remember that server_id is 0). If the change was made outside the sync application, then the originator id of 0 is the right value. If the sync did the change, then it is responsible of updating the originator id with the synchronizing client_id as you will see later when we discuss the insert, update and delete commands in the next section.

Notice that the triggers are responsible for updating the 'operation' column.

-- insert triggers
CREATE TRIGGER orders_insert_trigger on orders for insert
as
    set nocount on
    declare @key int   
    select @key = order_id from inserted     
    insert into orders_tracking(order_id, operation, update_originator_id, create_timestamp)
    values (@key, 'I', 0, @@DBTS+1)
    set nocount off
go

-- update triggers
CREATE TRIGGER orders_update_trigger on orders for update
as
    set nocount on
    declare @key int
    select @key = order_id from inserted     
    update orders_tracking set operation = 'U', update_originator_id = 0 where order_id = @key
    set nocount off
go

-- delete triggers
CREATE TRIGGER orders_delete_trigger on orders for delete
as
    set nocount on
    declare @key int
    select @key = order_id from deleted
    update orders_tracking set operation = 'D', update_originator_id = 0 where order_id = @key
    set nocount off
go   

Synchronization Commands

With the data in one table and the metadata in another, our sync adapter commands will need to change. Again, I will stick with writing the insert, update, and delete commands as stored procedures, while the select incremental commands as a direct TSQL statements.

If you compare the code from the demo II for coupled change tracking with the one below, you see that the code is largely the same except that here I need to use join operation to get hold of the tracking metadata for the row in question. Another difference here is that all commands needs to update the tracking table with the originator id of the client as I mentioned before. In demo two, this was only needed with the delete command.

create procedure dbo.sp_orders_applyinsert (
    @sync_last_received_anchor binary(8) ,
    @sync_client_id_hash int ,
    @sync_rowcount int out,
    @order_id int = NULL ,
    @order_date datetime = NULL)       
as
    insert into [orders] ([order_id], [order_date])
    values (@order_id, @order_date)
    set @sync_rowcount = @@rowcount

    -- update tracking table
    if @sync_rowcount > 0
        update orders_tracking
        set update_originator_id = @sync_client_id_hash           
        where order_id = @order_id     
go

create procedure dbo.sp_orders_applyupdate (   
    @sync_last_received_anchor binary(8) ,
    @sync_client_id_hash int ,
    @sync_rowcount int out,
    @order_id int,
    @order_date datetime = NULL )       
as         
    update o
    set o.order_date = @order_date       
    from [orders] o join [orders_tracking] t on o.order_id = t.order_id
    where (t.update_timestamp <= @sync_last_received_anchor or t.update_originator_id =
           @sync_client_id_hash) and t.order_id = @order_id                 
    set @sync_rowcount = @@rowcount

    -- the update trigger will reset the originator id to 0, update it to reflect the synchronizing
       client
    if @sync_rowcount > 0
        update orders_tracking
        set update_originator_id = @sync_client_id_hash           
        where order_id = @order_id     
go 

create procedure dbo.sp_orders_applydelete
      @sync_client_id_hash int,
      @sync_last_received_anchor timestamp,
      @sync_rowcount int out,
      @order_id int,
      @order_date datetime = NULL
as
    delete o
    from [orders] o join [orders_tracking] t on o.order_id = t.order_id
    where (t.update_timestamp <= @sync_last_received_anchor or t.update_originator_id =
           @sync_client_id_hash) and t.order_id = @order_id
    set @sync_rowcount = @@rowcount

    -- the delete trigger will reset the originator id to 0, update it to reflect the synchronizing
       client
    if @sync_rowcount > 0
         update orders_tracking
         set update_originator_id = @sync_client_id_hash           
         where order_id = @order_id                
go

Now let's swing to the code to see how the select incremental commands are set on the adapter. No surprises here too, just use the magic of join to access the metadata to apply the sync logic:

// select incremental inserts command
SqlCommand incInsOrdersCmd = new SqlCommand();
incInsOrdersCmd.CommandType = CommandType.Text;
incInsOrdersCmd.CommandText =
    "select o.order_id, o.order_date " +
    "from [orders] o join [orders_tracking] t on o.order_id = t.order_id " +
    "where t.create_timestamp > @sync_last_received_anchor " +
    "and t.create_timestamp <= @sync_new_received_anchor " +
    "and t.update_originator_id <> @sync_client_id_hash " +
    "order by t.create_timestamp desc "; 
incInsOrdersCmd.Parameters.Add("@" + SyncSession.SyncClientIdHash, SqlDbType.Int);
incInsOrdersCmd.Parameters.Add("@" + SyncSession.SyncLastReceivedAnchor, SqlDbType.Binary, 8);
incInsOrdersCmd.Parameters.Add("@" + SyncSession.SyncNewReceivedAnchor, SqlDbType.Binary, 8);

adaptorOrders.SelectIncrementalInsertsCommand = incInsOrdersCmd;

// select incremental updates command
SqlCommand incUpdOrdersCmd = incInsOrdersCmd.Clone();
incUpdOrdersCmd.CommandText =
    "select o.order_id, o.order_date " +
    "from [orders] o join [orders_tracking] t on o.order_id = t.order_id " +
    "where t.create_timestamp <= @sync_last_received_anchor " +
    "and t.update_timestamp > @sync_last_received_anchor " +
    "and t.update_timestamp <= @sync_new_received_anchor " +
    "and t.update_originator_id <> @sync_client_id_hash " +
    "order by t.update_timestamp desc ";
adaptorOrders.SelectIncrementalUpdatesCommand = incUpdOrdersCmd;

// select incremental deletes command
SqlCommand incDelOrdersCmd = incInsOrdersCmd.Clone();
incDelOrdersCmd.CommandText =
    "select order_id " +
    "from [orders_tracking] " +
    "where update_timestamp > @sync_last_received_anchor " +
    "and update_timestamp <= @sync_new_received_anchor " +
    "and update_originator_id <> @sync_client_id_hash " +
    "and operation = 'D' " +
    "order by update_timestamp desc ";
adaptorOrders.SelectIncrementalDeletesCommand = incDelOrdersCmd;
 

That's it! Now you can take data offline without touching the schema of the table. At any point, you can take all the sync overhead away from the system without affecting any sync-unaware applications.

There is no one-size-fits-all. As shown in this demo, the sync framework is designed to be flexible and extensible. Find that tracking logic that fits your scenario best and your are ready to tap into the sync framework to give your customers the offline experience that keeps them productive anywhere anytime!
 

Install OfflineAppDemo Application Steps:

• Fire SQL server management studio and load demo.sql file
• Execute the script until the "test sample" marker
• Load server_procs.sql file and execute it to create sync stored procedures on the server
• Load VS solution (OfflineAppDemo-DecoupledTracking Project)
• Build the project
• You are ready to go

Version 1.2 [C#]: Download Now