Sync Framework

Today I'm working for a customer who wants to implement the Sync Framework. As you probably know the Sync Framework is still beta but RTM is expected somewhere this fall.

The client has a typical problem. They have 7000 field engineers roaming the globe. They need to synchronize a lot of date before and after an assignment. Till now the client had implemented merge replication in SQL 2005 for this. They ran into problems because for merge replication you need to be in the dbo group. Since service engineers are not part of that group they had to edit system stored procedures in order to get this solution to work.

They asked us for a solution. I first tested merge replication in SQL 2008 without success. I heard vaguely about the Sync Framework and decided to give it a try.

   

The Sync Framework is more .Net then SQL Server. Since I'm not a .Net expert I was a bit worried in the beginning. Luckily implementing the Sync Framework is very easy and doesn't require lots of coding. Here's the graphical explanation in VB.Net(I still don't like C# so I always program in VB.Net):

   

1. Create a Windows Forms application in Visual Studio 2008

2. Add a Local Database Cache to your project.

   

      

3. Fill in the wizard screen.

      

   

      

   I connected to a SQL 2008 database so I can use the new change tracking features. This saves your data model because if you don't use change tracking you have to use triggers and tombstone-tables and they will seriously clutter your data model.

   The client connection is a SQL Server Compact 3.5. SQL Server Compact is very small(2MB) and uses very little memory; even with pretty large datasets like 10.000 rows. Of course we can't expect miracles of it so loading up a 2GB database is useless. I did try it but performance was not optimal :).

      

   When you press Ok the wizard will generate scripts and alter your development database. The scripts come in handy when you want to deploy your solution to the production database.

   

      

4. Create a dataset of your generated local data cache.

   The wizard proceeds with the dataset screen:

      

   

      

5. When you are done creating the dataset, bring up the data sources view in visual studio. Now drag a table from your dataset and drop it on the empty form.

      

   

      

   When you drag a datatable on your form Visual Studio adds all necessary components and code. So without programming you end up with a fully functional form.

   I docked the datagridview in the form to make it look better.

      

6. Now its time to add syncing. First add a button on the toolbar for executing syncing. In the code of the form you add the click event of the button. To obtain the sync code you go back to the local data cache designer

      

   

      

   When you press the Show Code Example link you'll get the code you need. Just paste this code in the click event of the button.

      

   Here's the event:

      

   Private Sub cmdSync_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles cmdSync.Click

   ' Call SyncAgent.Synchronize() to initiate the synchronization process.

   ' Synchronization only updates the local database, not your project's data source.

   Dim syncAgent As SampleDataCacheSyncAgent = New SampleDataCacheSyncAgent()

   Dim syncStats As Microsoft.Synchronization.Data.SyncStatistics = syncAgent.Synchronize()

      

   ' TODO: Reload your project data source from the local database (for example, call the TableAdapter.Fill method).

   Me.SyncDataSet.Parameters.Merge(Me.ParametersTableAdapter.GetData())

   End Sub

      

   The merge line I added myself. The form is ready now.

      

7. Now we move on with the sync class itself to choose what kind of syncing we want to have.

      

   

      

   Now you can choose the syncdirection that fits your needs.

      

8. The last step is to add conflict management during syncing:

   Partial Public Class SampleDataCacheServerSyncProvider

      

   Private Sub SampleDataCacheServerSyncProvider_ApplyChangeFailed(ByVal sender As Object, ByVal e As Microsoft.Synchronization.Data.ApplyChangeFailedEventArgs) Handles Me.ApplyChangeFailed

   Dim clientChange As DataTable = e.Conflict.ClientChange

   Dim serverChange As DataTable = e.Conflict.ServerChange

      

   e.Action = Microsoft.Synchronization.Data.ApplyAction.RetryWithForceWrite

   End Sub

      

   End Class

      

   I choose a simple RetryWithForceWrite here but you can code here what you want.

      

      

   Conclusion

   I build a fully functional sync sample in a few minutes. That's not too bad. Of course this is a simple scenario where complete tables are synchronized. If you want to synchronize on queries you have to write a lot of code yourself. But it is possible. I can think of lots scenarios where this framework could be very useful. Especially with all the smartphones and all that.

   Deploying this solution was something else. I ran into lots of problems. There is no installer for Sync Services yet and I didn't succeed in deploying it manually. For now you have to install the complete .Net framework 3.5 SDK and the beta service packs for VS 2008 and .Net 3.5. The installer and the Sync Framework itself are expected to go RTM this fall. So we have to wait a little bit for proper installers.

Comments

• Anonymous
  July 10, 2008
  The fact that merge replication does not work properly if the user is not a member of the dbowner database role is a main drawback and imho, a design killer. For the subscribers, you can make the user a dbo and secure your database some other way. However, we also ran into problems for inserting data into the published database. Unless the user that runs the insertions is a dbo, the automatic identitity arragement doesn't happen. This is a major design flaw as the users inserting data to be later replicated to subscribers are not necessarily dbowner and should not be...We used a workaround but had to add extra logging to keep track of the original requester. Other than that we prefer replication over the sync framework so far. The main disadvantages we found on the Sync Framework were:

• Initially worked only with SQLCE. SQLCE proved to be a lot slower on Tablet PCs and the application performance decreased.
• Schema changes cannot be replicated and cause downtime.
• If you need to filter the replicated data, there is a lot of work to be done, while filtering is pretty straight forward in replication.
• Grabbing the initial data can be more time consuming if using the Sync Framework. Merge replication overall works faster for us, it is raw data over tcp/ip.