How to: Provision a Server Database for Collaborative Synchronization (Non-SQL Server)

Article
09/29/2009

Note

The topics in this section of the documentation, Synchronizing Other ADO.NET Compatible Databases, are designed to demonstrate how databases other than SQL Server can be synchronized by using Sync Framework. In this release, SQL Server is used in code examples, but the code can be used for other ADO.NET compatible databases, with some modifications to the SQL Server-specific objects (such as SqlConnection) and the SQL queries that are shown. For information about SQL Server synchronization, see How to: Configure and Execute Collaborative Synchronization (SQL Server).

This topic describes how to provision a database that is synchronized by DbSyncProvider, so that incremental data changes can be tracked in that database. Changes are tracked so that they can be applied to other nodes during a synchronization session. To provision a database for Sync Framework, follow these steps:

Enable snapshot isolation for the database
Identify tables to synchronize
Create tracking tables to store per-table metadata; and create indexes on those tables
Create triggers on each base table to populate and update the tracking tables
(Optional) Handle existing data in the database
Create a tracking table to store per-scope metadata; and create an index on this table
Define scopes to synchronize, which specifies which tables are synchronized as a unit
Create stored procedures to select and update data and metadata

These steps are not required for databases that are synchronized by SqlCeSyncProvider; provisioning is handled by Sync Framework when the database is initialized.

After a database is provisioned, it can be synchronized with other nodes. For more information about how to configure and execute synchronization, see How to: Configure and Execute Collaborative Synchronization (Non-SQL Server).

Enable Snapshot Isolation for the Database

During the change enumeration phase of a synchronization session, Sync Framework starts transactions under snapshot isolation. To start transactions under snapshot isolation, you must set the ALLOW_SNAPSHOT_ISOLATION database option to ON, as shown in the following code example:

ALTER DATABASE [database name] SET ALLOW_SNAPSHOT_ISOLATION ON

For more information, see SQL Server Books Online.

Identify Tables to Synchronize

The first step in provisioning the database is to identity the tables that will be synchronized. Each table must have a primary key. Consider the following code example. It shows the Sales.Customer table schema in the SyncSamplesDb_Peer1 database.

CREATE TABLE Sales.Customer(
    CustomerId uniqueidentifier NOT NULL PRIMARY KEY DEFAULT NEWID(), 
    CustomerName nvarchar(100) NOT NULL,
    SalesPerson nvarchar(100) NOT NULL,
    CustomerType nvarchar(100) NOT NULL)

Each table that you synchronize has an associated DbSyncAdapter object, and you specify the primary key in the RowIdColumns collection for that object. For more information, see How to: Configure and Execute Collaborative Synchronization (Non-SQL Server).

Tables can be empty or they can contain existing data. If the table contains existing rows of data that should be synchronized, you must ensure that each row has a metadata entry in the appropriate change-tracking table. For more information, see Handle Existing Data in the Database.

Create Tracking Tables for Per-Table Metadata

Sync Framework requires a way to track which rows have changed since the previous synchronization session between two nodes. Changes are represented by two different types of metadata:

Per-table metadata, which tracks inserts, updates, and deletes for each table that is synchronized.
Per-scope metadata, which tracks which changes each node has received from other nodes.

Per-table metadata is tracked by using one tracking table for each base table. The base table and tracking tables must be present in each database that is synchronized by DbSyncProvider. The primary key of the tracking table is the same as in the base table, and additional columns are required. These columns are described in the following table. The names of the additional columns do not have to be the same as those listed; but they must match in order and type the queries or procedures that access the tracking tables. Some of these procedures are included in Create Stored Procedures to Select and Update Data and Metadata.

Column	Description	Updated when…
<Primary key (PK) of the base table> - include a column for each PK column.	Primary key columns of the base table.	A row is inserted into the base table. The insert could originate from the local or remote node.
update_scope_local_id	The ID of the scope that performed the last update or delete. For updates or deletes that originate at the local node, this column is NULL. Refers to the scope_local_id column in the scope information table. For more information, see "Create Tracking Tables for Per-Scope Metadata".	An update or delete from a remote node is applied to the base table.
scope_update_peer_key	The identity of the node that performed the last update or delete.	An update or delete from a remote node is applied to the base table.
scope_update_peer_timestamp	The timestamp value at the remote database when the row was originally updated or deleted.	An update or delete from a remote node is applied to the base table.
local_update_peer_key	The identity of the local node. This column will contain a value of 0 for each row unless the local database was restored from a backup. 1	An update or delete from a local operation or from a remote node is applied to the base table.
local_update_peer_timestamp	The timestamp value at the local database when the row was updated or deleted in the local database. 1	An update or delete from a local operation or from a remote node is applied to the base table.
create_scope_local_id	The identity of the scope that performed the insert. For updates or deletes that originate at the local node, this column is NULL. Refers to the scope_local_id column in the scope information table. For more information, "Create Tracking Tables for Per-Scope Metadata" in How to: Provision a Server Database for Collaborative Synchronization (Non-SQL Server).	An insert from a remote node is applied to the base table.
scope_create_peer_key	The identity of the node that performed the insert.	An insert from a remote node is applied to the base table.
scope_create_peer_timestamp	The timestamp value at the remote database when the row was originally inserted.	An insert from a remote node is applied to the base table.
local_create_peer_key	The identity of the local node. This column will contain a value of 0 for each row unless the local database was restored from a backup. 1	An insert from a local operation or from a remote node is applied to the base table.
local_create_peer_timestamp	The timestamp value at the local database when the row was inserted into the local database. 1	An insert from a local operation or from a remote node is applied to the base table.
sync_row_is_tombstone	A value of 1 indicates that a metadata entry is for a delete in the base table.	A row is deleted from the base table. The delete could originate from the local or remote node.
last_change_datetime	The date and time when the metadata row was last updated.	A row in this tracking table is inserted or updated.
restore_timestamp	Stores the value of local_update_peer_timestamp at the time of a database restore. The value is then used as the local update timestamp value.	Typically NULL, but can be set by the restore process. Set to NULL whenever a row is updated.
<Filter Columns> - add a column for each non-PK column used in the filtering WHERE clause for any scope.	Required only if a table is filtered for one or more scopes. Stores the value of the filtered columns for inserts, updates, and deletes.	A row is inserted, updated, or deleted from the base table. The delete could originate from the local or remote node.

1 Used by Sync Framework when overlapping scopes are synchronized. Consider the following example of updates for a database that synchronizes scope X with client A and scope Y with client B. Both scopes contain row Q.

Row Q is updated at client A and then synchronized with the database.
Client B synchronizes with the database and receives the update to row Q.

Client B is not aware of scope X, so the change from client A must appear as if it originated at the database. This is accomplished by using the values from local_update_peer_key and local_update_peer_timestamp when synchronizing with client B or any other client that does not synchronize the scope stored in update_scope_local_id for row Q.
Row Q is updated at the database and then synchronized with client A.

Client A is aware of scope X, so the values from scope_update_peer_key and scope_update_peer_timestamp are used when synchronizing with client A or any other client that synchronizes scope X.

We recommend that you create a separate database schema for the tracking table and all other objects that are related to synchronization metadata. This helps to isolate the metadata from the data in the base tables. For optimal performance, create indexes on each tracking table:

If data is not filtered: use the same primary key as the base table; and create a non clustered index on (local_update_peer_timestamp)
If data is filtered: use the same primary key as the base table; and create a non clustered index on (local_update_peer_timestamp, <filter columns>, <primary key columns>)

The following code examples create a table in the Sync schema that tracks changes for the Sales.Customer table, and add an index to the table.

CREATE TABLE Sync.Customer_Tracking(
    
    CustomerId uniqueidentifier NOT NULL PRIMARY KEY,          
    
    update_scope_local_id int NULL, 
    scope_update_peer_key int,
    scope_update_peer_timestamp bigint,
    local_update_peer_key int,
    local_update_peer_timestamp timestamp,

    create_scope_local_id int NULL,
    scope_create_peer_key int,
    scope_create_peer_timestamp bigint,
    local_create_peer_key int,
    local_create_peer_timestamp bigint,

    sync_row_is_tombstone int, 
    restore_timestamp bigint, 
    last_change_datetime datetime default NULL)

CREATE NONCLUSTERED INDEX NonClustered_Customer_Tracking
ON Sync.Customer_Tracking ([local_update_peer_timestamp])

Create Triggers to Populate and Update Tracking Tables

After creating tracking tables, add an INSERT, UPDATE, and DELETE trigger to each base table. When a user or Sync Framework inserts, updates, or deletes a row in a base table, a trigger fires and metadata for that row is inserted or updated in the change-tracking table. If Sync Framework applied the change to the base table (because it came from another node), Sync Framework updates the change-tracking table to reflect the origin of the change.

The following code example creates a trigger that updates change tracking metadata in the Sales.Customer_Tracking table when an update is made to the Sales.Customer table. For examples of insert and delete triggers, see Setup Scripts for Database Provider How-to Topics.

CREATE TRIGGER Customer_UpdateTrigger ON Sales.Customer FOR UPDATE
AS    
    UPDATE t    
    SET 
        update_scope_local_id = NULL, local_update_peer_key = 0, 
        restore_timestamp = NULL, last_change_datetime = GetDate() 
    FROM Sync.Customer_Tracking t JOIN inserted i ON t.[CustomerId] = i.[CustomerId]

Handle Existing Data in the Database

Change-tracking metadata for each table is inserted and updated by triggers on the base table. Therefore, change-tracking tables do not contain information about any rows that were inserted into a base table before the triggers were added. To handle existing data in the database, metadata should be inserted for the existing data. Then, during the first synchronization session, all rows are sent as new inserts to the destination database. The following code example shows the command to execute for each base table after you have added triggers to the base table:

INSERT INTO [tracking table] ([pk columns], create_scope_local_id, local_create_peer_key, local_create_peer_timestamp, update_scope_local_id, local_update_peer_key, restore_timestamp, sync_row_is_tombstone)
SELECT [pk columns], NULL, 0, @@DBTS+1, NULL, 0, NULL, 0 from [base table] baseT left outer join [tracking table] trackingT
On baseT.[pk columns]=trackingT.[pk columns]
where tracking.[pk columns] is null

Create Tracking Tables for Per-Scope Metadata

Per-scope metadata is typically tracked by using two tables in each database:

The scope information table stores synchronization knowledge in a binary format for each scope. A scope is a logical grouping of tables that defines what data should be synchronized as a unit.
The scope mapping table identifies which tables in a database belong to a particular scope. It is possible for a table to belong to more than one scope. The mapping table must contain one entry for each <scope, table> pair.

Sync Framework uses knowledge to determine which changes to send to each database during synchronization. Applications do not have to work with knowledge directly. Consider a bidirectional synchronization topology with three nodes:

Node1 and Node2 synchronize all changes.
Node1 synchronizes with Node3.
A user performs an update at Node2.
Node3 synchronizes with Node2.

When Node3 synchronizes with Node2, Node3 already has most of the changes from Node2, because Node3 synchronized with Node1 first. Knowledge enables Sync Framework to recognize this, and to synchronize only the update that occurred at Node2. For more information about knowledge, see Understanding Synchronization Knowledge.

The columns of the scope information table are described in the following table.

Column	Description	Updated when…
scope_id	An identifier for the scope, typically a GUID.	Never updated
scope_local_id	An integer identifier for the scope. This should be an IDENTITY column.	Never updated
scope_name	The name of the scope.	Never updated
scope_sync_knowledge	A binary representation of the synchronization knowledge for each scope.	All changes have been applied to a destination for a synchronization session.
scope_tombstone_cleanup_knowledge	A binary representation of the forgotten knowledge for each scope. Forgotten knowledge is used for metadata that has been cleaned up.	All changes have been applied to a destination for a synchronization session.
scope_timestamp	The timestamp value when the metadata row was last updated.	A row in this tracking table is updated.
scope_cleanup_timestamp	Timestamp value when the most recent tombstone cleanup was performed for this scope.	Tombstones are cleaned up for another scope that has overlapping tables.

The following code example creates a scope information table.

CREATE TABLE Sync.ScopeInfo(       
    scope_local_id int IDENTITY(1,1),
    scope_id uniqueidentifier default NEWID(),
    scope_name nvarchar(100) NOT NULL PRIMARY KEY,
    scope_sync_knowledge varbinary(max) NULL,
    scope_tombstone_cleanup_knowledge varbinary(max) NULL,
    scope_timestamp timestamp,
    scope_cleanup_timestamp bigint)

The scope information table is almost always queried to retrieve information about a particular scope, based on the scope name. Therefore, the primary key is defined on the scope_name column.

The columns of the scope mapping table are described in the following table.

Column	Description	Updated when…
table_name	The name of the table.	Never updated
scope_name	The name of the scope.	Never updated

The following code examples create a scope mapping table and an index on that table.

CREATE TABLE Sync.ScopeTableMap(    	
    scope_name nvarchar(100) ,
    table_name nvarchar(100)     
    )

CREATE UNIQUE CLUSTERED INDEX Clustered_ScopeTableMap ON Sync.ScopeTableMap(scope_name, table_name)

Define Scopes to Synchronize

After you create the scope tables, decide on one or more scopes to synchronize. For example, you could define a scope named Sales and include the tables Customer, CustomerContact, OrderHeader, and OrderDetail in that scope. When the Sales scope is synchronized, the changes made to the four tables are exchanged between the two nodes. Defining a scope is a two-part process:

Add entries to the scope information table and to the scope mapping table, such as in the following code example.

INSERT INTO Sync.ScopeInfo(scope_name) VALUES (''Sales'')
INSERT INTO Sync.ScopeTableMap(scope_name, table_name) VALUES (''Sales'', ''Sales.Customer'')
INSERT INTO Sync.ScopeTableMap(scope_name, table_name) VALUES (''Sales'', ''Sales.CustomerContact'')

Specify the scope name for the ScopeName property of the DbSyncProvider object and add a DbSyncAdapter object for each table that you want to include in the scope. For more information, see "Application Code for ScopeName and Connection" in How to: Configure and Execute Collaborative Synchronization (Non-SQL Server).

Important

After a scope has been synchronized for the first time, the scope should not be changed. Changing tables in the scope or filtering clauses for those tables can lead to non-convergence of data.

Filtered and Overlapping Scopes

A scope is filtered if only a subset of the rows from the base table is included in the scope. For example, you could define a filtered scope named sales-WA that contains only the sales data for the state of Washington. To filter the data, the query or procedure that you specify for the SelectIncrementalChangesCommand property of the DbSyncAdapter object must include a WHERE clause that selects the appropriate data. The query or procedure should select changes based on the filter columns in the tracking table rather than the filter columns in the base table.

The following types of filtering are not supported:

Column filtering: all columns must be included in the queries or procedures that select and apply changes.
Updates to columns that are used for filtering: if a user updates a value in a column that is used for filtering, a row moves from one scope to another. The row is sent to the new scope that the row now belongs to, but the row is not deleted from the old scope.

Two scopes overlap if they share common data between them. For example, the table products could be included in a sales scope and an inventory scope. Scopes can be both overlapping and filtered. The following scenarios demonstrate ways in which filtering and overlap can occur:

Scenario 1:
- Scope 1 is sales-WA. This scope includes: products; orders, with a filter of state=WA; and order_details, with a filter of state=WA.
- Scope 2 is sales-OR. This scope includes: products; orders, with a filter of state=OR; and order_details, with a filter of state=OR.
In this scenario, the entire products table is shared by both scopes. The orders and order_details tables are in both scopes, but the filters do not overlap; therefore the scopes do not share rows from these tables.
Scenario 2:
- Scope 1 is sales-WA. This scope includes: products; orders, with a filter of state=WA; and order_details, with a filter of state=WA.
- Scope 2 is sales-Northwest. This scope includes: products; orders, with a filter of state=WA OR state=ID; and shippers.
In this scenario, the entire products table is again shared by both scopes. The orders table is in both scopes and the filters overlap: both scopes share the rows that satisfy the filter state=WA. The shippers and order_details tables are not shared between the scopes.

There are many different ways in which scopes can be defined, but the following principle must be followed: any data that is synchronized between a pair of databases in the synchronization topology can belong to only one scope. For example in Scenario 2 above, Database A and Database B could synchronize Scope 1; and Database A and Database C could synchronize Scope 2. Database A and Database B cannot also synchronize Scope 2 because of the products and orders rows that belong to both scopes.

Create Stored Procedures to Select and Update Data and Metadata

After you create metadata tables, create SQL queries or stored procedures (recommended) to select and apply changes to the base tables and metadata tables. Stored procedures are recommended for performance and security reasons. These queries or procedures are specified for the following DbSyncAdapter commands. These commands are described in "Synchronization Adapter" in How to: Configure and Execute Collaborative Synchronization (Non-SQL Server).

The following code examples create a set of stored procedures to handle data and metadata changes for the Sales.Customer table. For brevity, the procedures to select data and to handle updates are included, but those for inserts and deletes are not. For examples of insert and delete procedures, see Setup Scripts for Database Provider How-to Topics. For a template that enables you to more easily create all of these stored procedures, see Server Provisioning Template (Non-SQL Server).

In the complete code example at the end of this topic, many of the values that are passed to these procedures come from session variables. These are built-in variables that enable Sync Framework to pass values to commands during a synchronization session. For more information about session variables, see How to: Use Session Variables for Collaborative Synchronization (Non-SQL Server).

Procedure for SelectIncrementalChangesCommand

create procedure Sync.sp_Customer_SelectChanges (
    @sync_min_timestamp bigint,
    @sync_metadata_only int,
    @sync_scope_local_id int,
    @sync_initialize int
)
as

--if @sync_initialize = 0
--begin
    -- Perform additional logic if required.
--end
    
begin
    select  t.CustomerId, 
            c.CustomerName,
            c.SalesPerson,
            c.CustomerType, 
            t.sync_row_is_tombstone,
            t.local_update_peer_timestamp as sync_row_timestamp, 
            case when (t.update_scope_local_id is null or t.update_scope_local_id <> @sync_scope_local_id) 
                 then case when (t.restore_timestamp is null) then t.local_update_peer_timestamp else t.restore_timestamp end else t.scope_update_peer_timestamp end as sync_update_peer_timestamp,
            case when (t.update_scope_local_id is null or t.update_scope_local_id <> @sync_scope_local_id) 
                 then t.local_update_peer_key else t.scope_update_peer_key end as sync_update_peer_key,
            case when (t.create_scope_local_id is null or t.create_scope_local_id <> @sync_scope_local_id) 
                 then t.local_create_peer_timestamp else t.scope_create_peer_timestamp end as sync_create_peer_timestamp,
            case when (t.create_scope_local_id is null or t.create_scope_local_id <> @sync_scope_local_id) 
                 then t.local_create_peer_key else t.scope_create_peer_key end as sync_create_peer_key
    from Sales.Customer c right join Sync.Customer_Tracking t on c.CustomerId = t.CustomerId
    where t.local_update_peer_timestamp > @sync_min_timestamp
end

Procedure for UpdateCommand

CREATE PROCEDURE Sync.sp_Customer_ApplyUpdate (                                    
        @CustomerId uniqueidentifier,
        @CustomerName nvarchar(100),
        @SalesPerson nvarchar(100),
        @CustomerType nvarchar(100),
        @sync_min_timestamp bigint ,                                
        @sync_row_count int OUT,
        @sync_force_write int)        
AS      
    UPDATE c
    SET c.CustomerName = @CustomerName, c.SalesPerson = @SalesPerson, c.CustomerType = @CustomerType      
    FROM Sales.Customer c JOIN Sync.Customer_Tracking t ON c.CustomerId = t.CustomerId
    WHERE ((t.local_update_peer_timestamp <= @sync_min_timestamp) OR @sync_force_write = 1)
        AND t.CustomerId = @CustomerId  
    SET @sync_row_count = @@rowcount

Procedure for UpdateMetadataCommand

create procedure Sync.sp_Customer_UpdateMetadata (
        @CustomerId uniqueidentifier,
        @sync_scope_local_id int,
        @sync_row_is_tombstone int,
        @sync_create_peer_key int,
        @sync_create_peer_timestamp bigint,                 
        @sync_update_peer_key int,
        @sync_update_peer_timestamp timestamp,                      
        @sync_row_timestamp timestamp,
        @sync_check_concurrency int,        
        @sync_row_count int out)        
as  
    declare @was_tombstone int
    select @was_tombstone = sync_row_is_tombstone from Sync.Customer_Tracking 
    where CustomerId = @CustomerId
    
    if (@was_tombstone is not null and @was_tombstone=1 and @sync_row_is_tombstone=0)
        -- tombstone is getting resurrected, update creation version as well
        update Sync.Customer_Tracking set
            [update_scope_local_id] = @sync_scope_local_id, 
            [scope_update_peer_key] = @sync_update_peer_key,
            [scope_update_peer_timestamp] = @sync_update_peer_timestamp,
            [local_update_peer_key] = 0,
            [restore_timestamp] = NULL,
            [create_scope_local_id] = @sync_scope_local_id, 
            [scope_create_peer_key] = @sync_create_peer_key, 
            [scope_create_peer_timestamp] =  @sync_create_peer_timestamp, 
            [sync_row_is_tombstone] = @sync_row_is_tombstone                        
        where CustomerId = @CustomerId          
        and (@sync_check_concurrency = 0 or local_update_peer_timestamp = @sync_row_timestamp)
    else    
        update Sync.Customer_Tracking set
            [update_scope_local_id] = @sync_scope_local_id, 
            [scope_update_peer_key] = @sync_update_peer_key,
            [scope_update_peer_timestamp] = @sync_update_peer_timestamp,
            [local_update_peer_key] = 0,
            [restore_timestamp] = NULL,
            [sync_row_is_tombstone] = @sync_row_is_tombstone                        
        where CustomerId = @CustomerId          
        and (@sync_check_concurrency = 0 or local_update_peer_timestamp = @sync_row_timestamp)
    set @sync_row_count = @@rowcount

Procedure for SelectRowCommand

create procedure Sync.sp_Customer_SelectRow
        @CustomerId uniqueidentifier,
        @sync_scope_local_id int
as
    select  t.CustomerId, 
            c.CustomerName,
            c.SalesPerson,
            c.CustomerType, 
            t.sync_row_is_tombstone,
            t.local_update_peer_timestamp as sync_row_timestamp, 
            case when (t.update_scope_local_id is null or t.update_scope_local_id <> @sync_scope_local_id) 
                 then case when (t.restore_timestamp is null) then t.local_update_peer_timestamp else t.restore_timestamp end else t.scope_update_peer_timestamp end as sync_update_peer_timestamp,
            case when (t.update_scope_local_id is null or t.update_scope_local_id <> @sync_scope_local_id) 
                 then t.local_update_peer_key else t.scope_update_peer_key end as sync_update_peer_key,
            case when (t.create_scope_local_id is null or t.create_scope_local_id <> @sync_scope_local_id) 
                 then t.local_create_peer_timestamp else t.scope_create_peer_timestamp end as sync_create_peer_timestamp,
            case when (t.create_scope_local_id is null or t.create_scope_local_id <> @sync_scope_local_id) 
                 then t.local_create_peer_key else t.scope_create_peer_key end as sync_create_peer_key
    from Sales.Customer c right join Sync.Customer_Tracking t on c.CustomerId = t.CustomerId    
    where c.CustomerId = @CustomerId

Procedure for SelectMetadataForCleanupCommand

CREATE PROCEDURE Sync.sp_Customer_SelectMetadata     
    @metadata_aging_in_days int,
    @sync_scope_local_id int
AS
    IF @metadata_aging_in_days = -1
        BEGIN
            SELECT  CustomerId,
                    local_update_peer_timestamp as sync_row_timestamp,  
                    case when (update_scope_local_id is null or update_scope_local_id <> @sync_scope_local_id) 
                        then case when (restore_timestamp is null) then local_update_peer_timestamp else restore_timestamp end else scope_update_peer_timestamp end as sync_update_peer_timestamp,
                    case when (update_scope_local_id is null or update_scope_local_id <> @sync_scope_local_id) 
                        then local_update_peer_key else scope_update_peer_key end as sync_update_peer_key,
                    case when (create_scope_local_id is null or create_scope_local_id <> @sync_scope_local_id) 
                        then local_create_peer_timestamp else scope_create_peer_timestamp end as sync_create_peer_timestamp,
                    case when (create_scope_local_id is null or create_scope_local_id <> @sync_scope_local_id) 
                        then local_create_peer_key else scope_create_peer_key end as sync_create_peer_key
            FROM Sync.Customer_Tracking
            WHERE sync_row_is_tombstone = 1
        END
    
    ELSE
        BEGIN
            SELECT  CustomerId,
                    local_update_peer_timestamp as sync_row_timestamp,  
                    case when (update_scope_local_id is null or update_scope_local_id <> @sync_scope_local_id) 
                        then case when (restore_timestamp is null) then local_update_peer_timestamp else restore_timestamp end else scope_update_peer_timestamp end as sync_update_peer_timestamp,
                    case when (update_scope_local_id is null or update_scope_local_id <> @sync_scope_local_id) 
                        then local_update_peer_key else scope_update_peer_key end as sync_update_peer_key,
                    case when (create_scope_local_id is null or create_scope_local_id <> @sync_scope_local_id) 
                        then local_create_peer_timestamp else scope_create_peer_timestamp end as sync_create_peer_timestamp,
                    case when (create_scope_local_id is null or create_scope_local_id <> @sync_scope_local_id) 
                        then local_create_peer_key else scope_create_peer_key end as sync_create_peer_key
            FROM Sync.Customer_Tracking
            WHERE sync_row_is_tombstone = 1 AND
            DATEDIFF(day, last_change_datetime, GETDATE()) > @metadata_aging_in_days
        END

Conclusion

This topic walked you through provisioning a database for change tracking. After a database is provisioned, it can be synchronized with other nodes. For more information about how to configure and execute synchronization, see the following topics: How to: Configure and Execute Collaborative Synchronization (Non-SQL Server).

Share via