[Note: Source for this sample can be downloaded from http://www.maheshwar.net/projects/Sync/DbSyncProviderSample.zip. Just ensure that you modify the connectionstrings in properties\settings file to point to the correct database. This sample is based off the RC0 build recently released.]
This post is to be a starting guide for people wanting to use Microsoft Sync Framework to synchronize databases. We are shipping a customized database provider, DbSyncProvider, that lets users synchronize tables in a peer to peer fashion. Note that we also ship a DbServerSyncProvider/DbClientSyncProvider combination in Sync Services For ADO.NET API that synchronizes data between Server and Client in a hub-and-spoke topology. DbSyncProvider will allow hub-and-spoke synchronization (if that is what is required) but the real power lies in the peer to peer synchronization.
This post will walk through the steps required to sync an existing table using DbSyncProvider. We will use a simple WinForms application to view/edit and synchronize a Customer table across two DB’s.
This post assumes that the table Customer cannot be modified and hence we will use a separate table (DeCoupled Tracking), CustomerMetadata, to track item metadata. To keep our queries simple we will use the default DB metadata column names. I will show at the very end how simple it is to modify the above solution for a table that maintains the metadata in the same table (Coupled Tracking). For sake of simplicity lets approach the database synchronization configuration in these following steps.
Create Scope Information table
Add metadata for Customer table
Configure Source DbSyncProvider
Specify Synchronization Scope commands
Configure Destination DbSyncProvider
Conclusion: Putting it all together
Step 1. DbSyncProvider Specifics
A DbSyncProvider represents a set of tables within a database defined by a “scope”that are to be synchronized. It contains information such as the connection string and the synchronization scope name, as well as Knowledge information that is used to determine what information is known by the local peer. Each table that needs to be synchronized from the database needs to be represented by a DbSyncAdapter and should be added to the SyncAdapters collection on the provider. Each DbSyncAdapter will contain SqlCommands for the following tasks.
Selecting incremental changes that are not in the destination’s knowledge.
Performing Inserts/Updates/Deletes from remote peers and updating corresponding metadata.
Cleanup tombstone metadata.
Select a particular row with a given id.
At the provider level, users need to specify SqlCommands for the following tasks.
Select the current timestamp for the local provider.
Select/update Scope info for given scope_name.
With our sample we will see the very basic properties that needs to be configured at the provider/syncadapter level to get started. To keep this demo simple, I am going to refrain from using stored procedures and rather depend on plain vanilla select/insert/delete SQL commands. For this demo we will create two databases SampleDB1 and SampleDB2 and will create two DbSyncProviders to represent them. Create the above mentioned databases.
Note: For RC release, DbSyncProviders expects to have snapshot isolation turned on at each database. This also means that this can be used against Sql Server 2005 and higher. So after creating them run the following command in both databases.
Step 2. Create Scope Information Table
As I mentioned earlier the provider needs the synchronization scope name and other scope related commands (update/select). Scope represents a set of tables that are involved in a sync session and each scope will need to maintain knowledge (data and tombstone) information for all peers that this scope has synchronized with in the past. We will create a Scope_Info table to maintain this information. The design for the scope_info table in SampleDB1 database is as follows.
It contains the scope id, sync knowledge, tombstone knowledge and the last updated timestamp to hold all scope related information. Scope Id is a unique identifier Lets create a new scope ‘DbSyncDemo’ and use this to synchronize our user tables. Initially it will contain null for DbSyncSession.SyncScopeKnowledge and DbSyncSession.SyncScopeCleanupKnowledge. This will tell the core Sync runtime that this database has not synchronized with any peer.
We need the scope table in all peers that will be involved in the synchronization session so create the same table in the SampleDb2 database as well.
Note: All column names of this table matches the constant values for scope metadata columns in DbSyncSession. The provider will look for these column names from the result set when selecting or updating scope.
Step 3. Add metadata for Customer table
Lets take a look at the Customer table. It contains fields for name, age and country of each customer. Design of Customer table is as follows.
We use id as the Primary Key for this table. Next we need to create a CustomerMetadata table that will hold row metadata for each row in Customer table. Each row in the table represents an Item in the Sync world and hence our metadata table needs to maintain the modified timestamp, creation id, creation timestamp, update key, update timestamp and tombstone metadata for each corresponding row in Customer table. We will use the PK column ‘id’ from the base table to correlated entries in the metadata table. Once again we will use the constant metadata names from DbSyncSession class to name the columns in this table. Design of CustomerMetadata table is as follows.
Next we need to update the metadata table each time a row is inserted/updated/deleted. For that we will add triggers on Customer table. We will create 3 triggers, one each for Insert, Update and Delete. Lets take a look at the insert trigger.
CREATE TRIGGER [dbo].[customer_insert_trigger]
— Insert statements for trigger here
insert into CustomerMetadata (id, sync_row_is_tombstone,sync_create_peer_key, sync_create_peer_timestamp, sync_update_peer_key, sync_update_peer_timestamp)
select id, 0,0,@@DBTS+1,0,@@DBTS+1 from inserted
Since in Sync, ‘0’ is the id for the current peer we enter 0 as the create/update key. Similarly we enter 0 for the tombstone column denoting that it is not a tombstone row.
The update trigger is much simpler. In case of updated we only need to update the update key (will always be 0 denoting current peer) and update timestamp.
CREATE TRIGGER [dbo].[customer_update_trigger]
— Insert statements for trigger here
set sync_update_peer_key = 0,
sync_update_peer_timestamp = @@DBTS +1
from CustomerMetadata meta join inserted i on i.id = meta.id
Finally the delete trigger will set the tombstone column to 1 (in addition to modifying the update key/timestamp pair).
CREATE TRIGGER [dbo].[customer_delete_trigger]
— Insert statements for trigger here
set sync_row_is_tombstone = 1,
sync_update_peer_key = 0,
sync_update_peer_timestamp = @@DBTS+1
from CustomerMetadata meta join deleted i on i.id = meta.id
Create the above tables, triggers in SampleDb1 and SampleDb2 databases. Next we are ready to configure DbSyncProvider’s for each of these endpoints. For starters purpose we will just sync in one direction, i.e SampleDb1 -> SampleDb2. I use this example as opposed to discussing a two way sync as I want to list the specific properties that needs to be set for a “source” vs “destination” provider.
Step 4. Configure Source DbSyncProvider
Lets start by creating a DbSyncProvider object and pass the connection string for SampleDb1. Since we are using Decoupled metadata table we will set the ChangeTracking property on the provider. to ChangeTrackingModel.Decoupled.
Next, create a DbSyncAdapter for Customer table.
Each DbSyncAdapter needs to know the list of Id columns whose value it will use to create a unique SyncId for each row metadata. In our case we will add column “Id” to the RowIdColumns collection. Next the only command that this DbSyncAdapter needs to provide is the SelectIncrementalChanges command. This will return the rows that have been modified/added/deleted since the destination provider last sync’d.
As you can see we specify the SyncMinTimestamp metadata column as an input parameter. This input parameter would be dynamically filled by the provider. The value is computed by merging the local knowledge with the remote knowledge information returned by the destination provider for GetSyncBatchParamters() method call. The above select command will return all new/updated and deleted rows as well. Next we tell the DbSyncAdapter the names of the tracking columns which represent the TombStone flag and the last change timestamp for the row. DbSyncProvider will populate the IsTombstone property on SyncRowMetadata with the value returned from the IsTombstoneColumn and remove the tracking columns from the DataSet returned to the destination provider.
Finally add the DbSyncAdapter to the providers collection.
Next we need to specify Scope commands on the provider.
Step 5. Specify Synchronization Scope commands
Each DbSyncProvider needs certain commands to figure out the current timestamp of the database and to read/update scope information.
We need to set the following 3 properties on DbSyncProvider to enable reading timestamp and scope information.
DbSyncProvider.SelectNewTimestampProvider: This command will enable the provider to determine the current timestamp of the provider (in our sample it will be the current transaction count). Runtime will look for the timestamp in the DbSyncSession.SyncNewTimestamp metadata column so we will specify an output parameter in our query.
This command will be used by the provider to select the data knowledge and cleanup knowledge that this provider knows about for this scope. We will retrieve the required information from the query as output parameters. The required output parameters are DbSyncSession.SyncScopeId, SyncScopeKnoeledge, SyncScopeCleanupKnowledge and SyncScopeTimestamp.
This command will be used by the destination provider to update knowledge and cleanup information each time the provider completes a successful sync from a remote provider. This command is also used when users calls DbSyncProvider.CleanupMetadata() function.
The runtime will make sure that it passes the right values for all the input parameters. We need to pass the rowcount back to the system in SyncRowCount metadata parameter so that the runtime can detect when update of the scope fails.
Finally configure the source DbSyncProvider with the above three commands.
Thats it. SampleDb1 is now configured to be a DownloadOnly source provider.
Step 6. Configure Destination DbSyncProvider
Next we need to create DbSyncProvider representing SampleDb2 database. For this we will create a DbSyncProvider and add the scope specific commands we added in step 4 and 5. Since the database schemas are same we will reuse the same SqlCommands.
Then we create a DbSyncAdapter for table Customer.
We will then add the following destination specific properties on the sync adapter.
In this case we are specifying column names we want to insert in Customer table. As you can see the individual column names are specified as input parameters and these parameters will be set with the corresponding column values for each row added on the source provider. We need to explicitly populate the DbSyncSession.SyncRowCount metadata parameter in our insert command as this is how the provider will determine if the insert succeeded or not. Count will always be 1 for a successful insert but will be 0 for either a constraint violation or when a row with the same PK already exists.
In case of a PK violation and other conflict scenarios, the provider would try to run the DbSyncAdapter.SelectRowCommand to see what is currently in the table. So we would provide the SelectRowCommand as follows.
I am going to reserve conflict detection to a separate blog post as the workflow and commands involved are slightly different (based on type of PK conflict such as Local delete, remote update or local update, remote update). Suffice to say if Insert failed and SelectRowCommand returns 0 results then it means that its a constraint violation and it will raise DbSyncProvider.ApplyChangeFailed event if one is registered.
On a successful insert, the provider will then try to insert/update metadata for the currently inserted row. We need to provide the following insert command to insert metadata in CustomerMetadata table.
We need to return the rowcount back to DbSyncProvider so it can raise a ApplyMetadataFailed event in case its 0.
The update command for our sample is as follows.
On a successful insert of a row(rowcount for InsertCommand > 0), DbSyncProvider assumes the Couple metadata tracking model and hence will first try to execute the UpdateMetadataCommand. In our case the UpdateMetadataCommand will return 0 rowcount and the system will check to see that the model is decoupled and then execute the InsertMetadataCommand. If that fails the ApplyMetadataFailed event will be raised.
This command will update existing record in Customer table. Similar to the Insert command we need to return the rowcount back as an output parameter. If update count is 0 then we try to detect whether the row exists by running SelectRowMetadata and resolve the error. If SelectRowMetadata returns 0 then runtime will retry the update as an Insert.
On successful update, the provider will then execute UpdateMetadataCommand. If that fails (rowcount < 1) then the same fallback procedure mentioned for InsertCommand will be followed.
Finally in the delete command we will remove the row from the Customer table.
Once again runtime expects us to return the rowcount as an output parameter. On failure to delete the record it will try execute SelectRowCommand to try to detect conflict as mentioned above. If that fails then it means that the remote peer created and deleted a row before this peer could synchronize. So the system would try to keep tombstone record for that entry by adding a metadata record for that deleted row to guarantee consistent metadata across peers.
This command is never used directly. Its only used during a conflict during InsertCommand (and the system tries it as an update) or a conflict in UpdateCommand (in that case the system tries it as an insert). In that case DbSyncProvider will first run the DeleteMetadataCommand and if that is successful will then retry the insert/update.
In our sample we will use the following DeleteMetadataCommand.
With the above mentioned 7 commands the destination specific DbSyncAdapter configuration is complete. We will add this table to the destination DbSyncProvider.
Step 7. Conclusion: Putting it all together
With the source and destination providers set we will start Synchronization by creating a SyncOrchestrator object, set the local and remote providers and set the Direction property to SyncDirectionOrder.Download.
Finally we will call Synchronize when the user presses the “Synchronize” button on the winform.
Running the sample:
Lets start off by running the sample. We will add two rows for John and Jane Doe. Add the rows on the left hand side Customer table and press SaveChanges button. Then refresh the tables to see the metadata created by the triggers.
Next press synchronize and see the left side tables getting the values. Also ensure that the create and update key on the left side metadata column points to 1 and not 0.
I will leave the update/delete exercise up to the user. To modify the sample to be a two way synchronization, just add the source level DbSyncAdapter commands to SampleDb2 provider and similarly add the destination specific DbSyncAdapter commands to SampleDb1 provider. Then finally change the Direction property on the SyncOrchestrator to DownloadAndUpload.
To summarize, we did the following to enable syncing Customer table across peers.
- Created a metadata table for DeCoupled tracking in all peers.
- Enabled Snapshot isolation on all peers.
- Configured a DbSyncProvider/DbSyncAdapter for SampleDb1 peer and added source peer specific commands.
- Configured a DbSyncProvider/DbSyncAdapter for SampleDb2 peer and added destination peer specific commands.