Part 3 – Synchronizing tables where Client and Server Primary Keys are different

I have previously blogged about synching tables with different Primary keys (see: Part 1 – Upload Synchronization where the Client and Server Primary Keys are different  and Part 2 – Bidirectional Synchronization where the Client and Server Primary Keys are different ) and in both approach, we relied on intercepting the change dataset to alter the structure of the table being synched by adding/removing columns to match the Primary key of the destination table.

In the last couple of days, I have received questions around the same scenario. The last one is as follows:

In my case I have a database where the majority of the PK’s are identity autoincrementing ints.

we are now in a position were we need to make this data available in a distributed environment (i.e mobile devices, external software etc) and of course kept in sync.

As I cant change from using the current PK sync I was thinking that i could add a GUID to the columns which would be used as sync.

on the client I have complete control over how that database structure is created so I was thinking there i could have the GUID as the identifying key
on first sync the server would send across the required data (and GUID).

if the client wants to do inserts they can do and then sync (sending across GUID) – although server would auto increment the PK (but we can still match on the GUID for future syncs).

Does this sound like the right solution and if so can the Microsoft Sync framework handle this task?

I promised I’d write a blog post to see if it could be done or at least come up with something similar.

Let’s assume we have the following table structures:

As you would notice, the server table has its PK on the Id column which is setup as an Identity column as well. The client table on the other hand has its Guid column as its PK. In this scenario the Guid is used to uniquely identify the client rows whereas in my previous posts, we used a Guid to uniquely identify clients.

What if we can “trick” Sync Framework into thinking that the PK on the server table is the Guid column instead of the identity column Id? Which means that as far as the physical table is concerned, Id remains the PK, but inside our scope definition, the PK is the Guid column.

So I wrote a quick test to check if this is possible. See the following code:

Code Snippet

1. using System;
2. using System.Data.SqlClient;
3. using Microsoft.Synchronization;
4. using Microsoft.Synchronization.Data;
5. using Microsoft.Synchronization.Data.SqlServer;
6. namespace SyncFxForums
7. {
8.     class Program
9.     {
10.         static void Main()
11.         {
12.             //setup the connections
13.             var serverConn = new SqlConnection(“Data Source=localhost; Initial Catalog=SyncFxLabs; Integrated Security=True”);
14.             var clientConn = new SqlConnection(“Data Source=localhost; Initial Catalog=SyncFxClient; Integrated Security=True”);
15.             const string scopeName = “DifferentPKScope”;
16.             //provision server
17.             var serverProvision = new SqlSyncScopeProvisioning(serverConn);
18.             if (!serverProvision.ScopeExists(scopeName))
19.             {
20.                 var serverScopeDesc = new DbSyncScopeDescription(scopeName);
21.                 var serverTableDesc = SqlSyncDescriptionBuilder.GetDescriptionForTable(“ServerTable”, serverConn);
22.                 // our server and client tables has different names, so let’s setup a common name
23.                 serverTableDesc.GlobalName = “TestTable”;
24.                 serverScopeDesc.Tables.Add(serverTableDesc);
25.                 // remove the identity column PK since we dont need to sync it
26.                 serverScopeDesc.Tables[“TestTable”].Columns.Remove(serverScopeDesc.Tables[“TestTable”].Columns[“id”]);
27.                 // “trick” Sync Fx that Guid is the PK
28.                 serverScopeDesc.Tables[“TestTable”].Columns[“Guid”].IsPrimaryKey = true;
29.                 serverProvision.PopulateFromScopeDescription(serverScopeDesc);
30.                 serverProvision.Apply();
31.             }
32.             //provision client
33.             var clientProvision = new SqlSyncScopeProvisioning(clientConn);
34.             if (!clientProvision.ScopeExists(scopeName))
35.             {
36.                 var clientScopeDesc = new DbSyncScopeDescription(scopeName);
37.                 var clientTableDesc = SqlSyncDescriptionBuilder.GetDescriptionForTable(“ClientTable”, clientConn);
38.                 clientTableDesc.GlobalName = “TestTable”;
39.                 clientScopeDesc.Tables.Add(clientTableDesc);
40.                 clientProvision.PopulateFromScopeDescription(clientScopeDesc);
41.                 clientProvision.Apply();
42.             }
43.             // create the sync orchestrator
44.             var syncOrchestrator = new SyncOrchestrator();
45.             //setup providers
46.             var localProvider = new SqlSyncProvider(scopeName, clientConn);
47.             var remoteProvider = new SqlSyncProvider(scopeName, serverConn);
48.             syncOrchestrator.LocalProvider = localProvider;
49.             syncOrchestrator.RemoteProvider = remoteProvider;
50.             // set the direction of sync session to Upload and Download
51.             syncOrchestrator.Direction = SyncDirectionOrder.UploadAndDownload;
52.             // execute the synchronization process
53.             var syncStats = syncOrchestrator.Synchronize();
54.             // print statistics
55.             Console.WriteLine(“Start Time: “ + syncStats.SyncStartTime);
56.             Console.WriteLine(“Total Changes Uploaded: “ + syncStats.UploadChangesTotal);
57.             Console.WriteLine(“Total Changes Downloaded: “ + syncStats.DownloadChangesTotal);
58.             Console.WriteLine(“Complete Time: “ + syncStats.SyncEndTime);
59.             Console.WriteLine(String.Empty);
60.             Console.ReadKey();
61.         }
62.     }
63. }

If you look at the code, its pretty much standard except for the server provisioning part. Notice also, that the table names differs in the client (ClientTable) and server (ServerTable)  tables so we have to set the GlobalName to  “TestTable” to settle for a common name.

The line :

 var serverTableDesc = SqlSyncDescriptionBuilder.GetDescriptionForTable("ServerTable", serverConn);

simply retrieves the ServerTable structure as it is with columns id, Guid and TestColumn. In addition, GetDescriptionForTable would pick up that column Id is the PK.

Since we don’t need to sync the Id column to the client side, we simply remove it from the column collection using this line:

serverScopeDesc.Tables["TestTable"].Columns.Remove(serverScopeDesc.Tables["TestTable"].Columns["id"]);

We then tag the Guid column as the PK via this line:

serverScopeDesc.Tables["TestTable"].Columns["Guid"].IsPrimaryKey = true;

Looking at the scope_config entry, you’ll find that the Guid column is now tagged as the PK:

The tracking table also reflects that Guid is the PK:

So there you go, we retained the existing PK on the server table as it is. Inside the scope definition though, Sync Framework is configured to use the Guid column as the PK.

Give it a go and let me know how it goes.

Cheers,

JuneT

Advertisement