Part 1 – Upload Synchronization where the Client and Server Primary Keys are different

Intrigued by this post Strategies for retrofitting existing databases with sync in the Sync Forums, I decided to write a short test if this could be done.

This is not an uncommon scenario when retrofitting existing databases for synchronization where a lot of times we don’t have the flexibility to re-design the table schemas to make them work with Sync Framework. And this is further complicated by the ease of use of using Identity columns as Primary Keys (mostly with the same seed value). However, when the eventual aggregation/consolidation of multiple client databases into a central store is not taken into consideration, this results to primary key collisions in the central store.

To summarize the scenario in the forum post, there are multiple client databases whose primary key is based on an identity column. Naturally, when this multiple client databases are aggregated/consolidated to a central store, the PK values will collide. As a workaround, a composite PK is implemented on the central table not only to guarantee uniqueness but to identify which client a particular row has come from.

So let me share how to go about doing an Upload only scenario where the Client and Server have different PKs and address the scenario being asked in the forum post above.

Here’s the SQL script to represent the client table:

Code Snippet

1. SET ANSI_NULLS ON
2. GO
3.  
4. SET QUOTED_IDENTIFIER ON
5. GO
6.  
7. SET ANSI_PADDING ON
8. GO
9.  
10. CREATE TABLE [dbo].[testtable](
11.     [id] [int] IDENTITY(1,1) NOT NULL,
12.     [testcolumn] [varchar](50) NULL
13. CONSTRAINT [PK_testtable] PRIMARY KEY CLUSTERED
14. (
15.     [id] ASC
16.   )WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS= ON) ON [PRIMARY]
17. ) ON [PRIMARY]
18.  
19. GO
20.  
21. SET ANSI_PADDING OFF
22. GO

Note the Identity column used as the Primary Key

Here’s the SQL script to represent the table where the different client databases will be uploaded:

Code Snippet

1. SET ANSI_NULLS ON
2. GO
3.  
4. SET QUOTED_IDENTIFIER ON
5. GO
6.  
7. SET ANSI_PADDING ON
8. GO
9.  
10. CREATE TABLE [dbo].[TestTable](
11.     [ClientId] [uniqueidentifier] NOT NULL,
12.     [Id] [int] NOT NULL,
13.     [testcolumn] [varchar](50) NULL
14. CONSTRAINT [PK_TestTable] PRIMARY KEY CLUSTERED
15. (
16.     [ClientId] ASC,
17.     [Id] ASC
18.   )WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS= ON) ON [PRIMARY]
19. ) ON [PRIMARY]
20.  
21. GO
22.  
23. SET ANSI_PADDING OFF
24. GO

Note the composite Primary Key which is based on the client’s original Id column and a ClientId GUID to uniquely identify each client.

Let’s provision the  client:

Code Snippet

1. //setup the client connection
2. var clientConn = new SqlConnection("Data Source=localhost; Initial Catalog=Client1; Integrated Security=True");
3.  
4. // define a new scope named TestScope
5. var clientScope = new DbSyncScopeDescription("TestScope");
6.  
7. // get the description of the TestTable table from the client database
8. var clientTable = SqlSyncDescriptionBuilder.GetDescriptionForTable("TestTable", clientConn);
9.  
10. // add the table description to the sync scope definition
11. clientScope.Tables.Add(clientTable);
12.  
13. // create provisioning scope based on the scope definition
14. var clientProvision = new SqlSyncScopeProvisioning(clientConn, clientScope);
15.  
16. if (!clientProvision.ScopeExists("TestScope"))
17. {
18.     // skipping the creation of table since table already exists on client
19.     clientProvision.SetCreateTableDefault(DbSyncCreationOption.Skip);
20.                 
21.     //apply the scope definition
22.     clientProvision.Apply();
23. }

Let’s provision the server:

Code Snippet

1. //setup the server connection
2. var serverConn = new SqlConnection("Data Source=localhost; Initial Catalog=Aggregator; Integrated Security=True");
3.             
4. // define a new scope named TestScope
5. var scopeDesc = new DbSyncScopeDescription("TestScope");
6.  
7. // get the description of the TestTable table from the server database
8. var tableDesc = SqlSyncDescriptionBuilder.GetDescriptionForTable("TestTable", serverConn);
9.             
10. // add the table description to the sync scope definition
11. scopeDesc.Tables.Add(tableDesc);
12.             
13. // create provisioning scope based on the scope definition
14. var serverProvision = new SqlSyncScopeProvisioning(serverConn, scopeDesc);
15.  
16. if (!serverProvision.ScopeExists("TestScope"))
17. {
18.     // skipping the creation of table since table already exists on server
19.     serverProvision.SetCreateTableDefault(DbSyncCreationOption.Skip);
20.  
21.     //apply the scope definition
22.     serverProvision.Apply();
23. }

The above provisioning script actually looks exactly the same except that they’re ran against different connections and the client and server GetDescriptionForTable calls will retrieve different table structures and PKs.

Since the client PK is only based on the Id column and the server has a composite key based on ClientId and Id, we need to inject the ClientId before the row get’s transmitted to the server.

The server provider needs both ClientId and Id to determine if it already has those rows.

So let’s try to inject a ClientId based on some arbitrary value (you can retrieve the ClientId whichever way you want, but for purposes of this test, we just hardcode a specific GUID).

Let’s subscribe to the Client’s (LocalProvider)  ChangesSelected event:

Code Snippet

1. static void ClientChangesSelected(object sender, DbChangesSelectedEventArgs e)
2. {
3.     //let's check if we're synching the table we're interested
4.     if (e.Context.DataSet.Tables.Contains("testtable"))
5.     {
6.         var dataTable = e.Context.DataSet.Tables["testtable"];
7.                 
8.         //let's add the new column
9.         dataTable.Columns.Add(new DataColumn("ClientId"));
10.  
11.         //let's assign the client guid
12.         var clientId = "89198182-24C9-4CD7-8427-F6790270E62C";
13.  
14.         foreach (DataRow row in dataTable.Rows)
15.         {
16.             // if it's an update or an insert, just assign the clientId
17.             if (row.RowState == DataRowState.Modified || row.RowState == DataRowState.Added)
18.             {
19.                 row["ClientId"] = clientId;
20.             }
21.             // if it's a delete, undo the delete status,
22.             // assign the guid, accept the changes for the quid,
23.             // then revert back the row as deleted
24.             else if (row.RowState == DataRowState.Deleted)
25.             {
26.                 row.RejectChanges();
27.                 row["ClientId"] = clientId;
28.                 row.AcceptChanges();
29.                 row.Delete();
30.             }
31.         }
32.                 
33.     }
34. }

To expound on the code above, what it essentially does is add the ClientId column to the DataTable, then loop thru each row in the DataTable to assign the ClientId value.

However, for Deletes, we cannot simply assign the ClientId value since you cannot access the row and assign a value to any of its columns when it’s flagged as deleted. So we need to undo the Deleted status by calling RejectChanges. We then assign the value and call AcceptChanges to set the ClientId value. Then we revert back the row as Deleted.

That’s it, the client should be able to synchronize/upload its changes to the server.

Code Snippet

1. // create the sync orchestrator
2. var syncOrchestrator = new SyncOrchestrator();
3.  
4. // set local provider of orchestrator
5. syncOrchestrator.LocalProvider = new SqlSyncProvider("TestScope", clientConn);
6.  
7. // set the remote provider of orchestrator
8. syncOrchestrator.RemoteProvider = new SqlSyncProvider("TestScope", serverConn);
9.  
10. // set the direction of sync session to Upload and Download
11. syncOrchestrator.Direction = SyncDirectionOrder.UploadAndDownload;
12.  
13. // subscribe for errors that occur when applying changes to the client
14. ((SqlSyncProvider)syncOrchestrator.LocalProvider).ChangesSelected += ClientChangesSelected;
15. ((SqlSyncProvider)syncOrchestrator.RemoteProvider).ChangesSelected += ServerChangesSelected;
16.            
17. // execute the synchronization process
18. var syncStats = syncOrchestrator.Synchronize();
19.  
20. // print statistics
21. Console.WriteLine("Start Time: " + syncStats.SyncStartTime);
22. Console.WriteLine("Total Changes Uploaded: " + syncStats.UploadChangesTotal);
23. Console.WriteLine("Total Changes Downloaded: " + syncStats.DownloadChangesTotal);
24. Console.WriteLine("Complete Time: " + syncStats.SyncEndTime);
25. Console.WriteLine(String.Empty);
26. Console.ReadKey();

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