Sync between 2 SQL Server 2005 databases RRS feed

  • Question

  • I am trying to create an occasionally connected application using Sync Framework 2.0. Server is going to use SQL Server 2005 and clients SQL 2005 Express. I implemented the code used in http://msdn.microsoft.com/en-us/library/dd918848(SQL.105).aspx for Collaborative Synchronization between 2 SQL Server databases. The example works fine, and I see that changes sync both ways. Inserts, updates or deletes on the clients are passed to the server and similar changes on the server are passed to the clients. That makes it a very pleasing scenario for my application. However, there are 2 major problems:

    1. If I make a schema change on the server, for example add a column on a synced table, no schema change is propagated to the client. I understand that these changes need to be done by the application on the client, but even if I add the column on the client database table, sync does not update the new column, if there are changes in it. Seems like such a schema change, needs a new provisioning on the server and client, since provisioning stored procedures remain the same, and as such, its impossible to update the client table, if the update sp does not have such a column. Is there a way I could tell the sync framework to 'refresh' provisioning to reflect the added column?

    2. There is no way to make a dynamic filter in this solution. For example we have a table with 300.000 records, and each client needs to get different subset of this data, for example one might get 10.000, another client 20.000 records, all based on the client user, specified on logon time. Its totally out of the question to send all 300.000 rows to each client for performance, security and space reasons. Is there a way, one can achieve such a dynamic filter functionality, without resorting to tricks like a different scope for each user?

    Thanks in advance

    E. Gimissis


    • Moved by Mahesh DudgikarMicrosoft employee Tuesday, December 22, 2009 4:14 AM (From:SyncFx - Technical Discussion)
    • Moved by Max Wang_1983 Tuesday, April 19, 2011 10:43 PM Forum consolidation (From:SyncFx - Microsoft Sync Framework Database Providers [ReadOnly])
    Wednesday, December 16, 2009 6:23 PM


  • Hi,

    I am assuming that you are using SqlSyncProvider on both the SQL 2005 and Express client which is the right thing. We will also be posting samples for this topology for 2-tier and N-tier pretty soon.

    to answer your questions:
    The schema changes are unfortunately not propagated automatically by Sync framework. This is something that the application needs to take into account. Please see these articles for more info on how to do it: Working with Table Schemas and Deploying and Versioning Applications 

    Also, dynamic filtering is possible, but you would need to have as many scopes as you have the number of users. Currently this is the design limitation and we are working on making this story better and easy for the customers in the next release.

    This posting is provided AS IS with no warranties, and confers no rights
    Thursday, December 24, 2009 6:48 PM