Sync Framework is a comprehensive synchronization platform that enables collaboration and offline access for applications, services, and devices. Sync Framework features technologies and tools that enable roaming, data sharing, and taking data offline. By using Sync Framework, developers can build synchronization ecosystems that integrate any application with data from any store, by using any protocol over any network.
This article shows how to synchronize efficiently with a remote server by using a proxy provider on the local computer over secure WCF channel. The proxy provider uses the Remote Change Application pattern and Windows Communication Foundation (WCF) to send serialized metadata and data to the remote replica so synchronization processing can be performed on the remote computer (server) with fewer round trips between the client and server computers. Microsoft Sync Framework synchronizes data between data stores. Typically, these data stores are on different computers or devices that are connected over a network. In our case we will be using synchronization between local SQL Server 2008 and central (remote) SQL server 2008 (express edition). Following are the different (Visual Studio 2010 – .Net 4.0) projects involved in the solution
- Sync.WebServer : Web server (Asp.Net) project to host WCF Sync service, Authentication service and web portal to manage Sync clients.
- Sync.Library : Sync library (Class Library) used by client/server which provides server proxy to client and also provides base class for RelationalSyncService, ISqlSyncContract for WCF Sync service.
- Sync.Client : Windows based client which will perform database sync between SyncLocal and SyncServerCert via WCF service.
For synchronizing two databases, Sync Framework supports two-tier and N-tier architectures that use any server database for which an ADO.NET provider is available. For synchronizing between a client database and other types of data sources, Sync Framework supports a service-based architecture. This architecture requires more application code than two-tier and N-tier architectures; however, it does not require a developer to take a different approach to synchronization.
The following illustrations show the components that are involved in N-tier, and service-based architectures. Each illustration shows a single client, but there are frequently multiple clients that synchronize with a single server. Sync Framework uses a hub-and-spoke model for client and server database synchronization. Synchronization is always initiated by the client. All changes from each client are synchronized with the server before the changes are sent from the server to other clients. (These are clients that do not exchange changes directly with one another.)
N-tier architecture requires a proxy, a service, and a transport mechanism to communicate between the client database and the server database. This architecture is more common than a two-tier architecture, because an N-tier architecture does not require a direct connection between the client and server databases.
N-Tier Architecture
For demo purpose the server side database is very simple, with just two tables in used in synchronization process. Create blank database SyncCenter and execute script SyncCenter_Script.sql (See database script attached with source code)
Class ServerProvisioning.cs (in project Sync.WebServer) is used to create sync filter template and then create filtered scope for each client based on this template.
We need add our tables to function CreateTemplate (filtered template)
//Add tables which will participate in Sync sequence matters
scopeDesc.Tables.Add(GetDescriptionForTable("Clients", ConnectionString));
scopeDesc.Tables.Add(GetDescriptionForTable("Products", ConnectionString));
//With each table we have to add @Id and filter records based on Client id
SqlSyncTableProvisioning Clients = serverTemplate.Provisioning.Tables[GetTableFullName("Clients")];
Clients.AddFilterColumn("Id");
Clients.FilterClause = "[side].[Id] = @Id";
Clients.FilterParameters.Add(new SqlParameter("@Id", SqlDbType.UniqueIdentifier));
SqlSyncTableProvisioning Products = serverTemplate.Provisioning.Tables[GetTableFullName("Products")];
Products.AddFilterColumn("ClientId");
Products.FilterClause = "[side].[ClientId] = @Id ";
Products.FilterParameters.Add(new SqlParameter("@Id", SqlDbType.UniqueIdentifier));
Please note that you for each table which is used in synchronization process we have to add filter clause to filter records based on client id. You may use complex SQL queries with joins etc. to specify which records should be synchronized between multiple clients. The central server (single) holds data for multiple clients (multi – tenancy).See article Sync framework – choose your primary keys type carefully: http://www.codeproject.com/Articles/63275/Sync-framework-choose-your-primary-keys-type-caref
The above mentioned code id called when you click on create template button from Central MS Sync web site (Project: Sync.WebServer).
Before running our web server project (Sync.WebServer), you need to change connection string SyncCenterConnectionString in web.config file.The web server project Sync.WebServer is used to host WCF Sync service and also provides you admin panel from where you can setup and create Sync template and sync scopes for each client. The sync template will create filter based template and specify tables used in sync process and also define filter clause (Sql Queries) where as the sync scope will create scope of each client based on these templates where clientId is fixed. So that whenever you setup new sync client you need to create scope for this client before it can take participate in sync process.
OK now, web server project is almost ready to run however we need to install membership provider and create few certificates which will be used in later stage. The Asp.Net membership provider is used to access this web portal.
Follow below mentioned steps to install membership provider on your SyncCenter database.
Run aspnet_regsql.exe utility from C:windowsMicrosoft.NETFrameworkv2.0.50727 folder on your machine.
Choose your database and click next, next … finish
And click next, next…. Finish.
Now we need two X.509 certificates “SyncServerCert” and “SyncClientCert“
Certificate SyncServerCert will be used by web server where as SyncClientCert will be distributed to its clients.
To create certificate follow tasks mentioned below
Execute Make Cert.bat available under certificates folders (download).This batch file is having following commands.
Makecert.exe -r -pe -n "CN= SyncServerCert " -b 01/01/2000 -e 01/01/2050 -eku 1.3.6.1.5.5.7.3.1 -ss my -sr localMachine -sky exchange -sp "Microsoft RSA SChannel Cryptographic Provider" -sy 12 Winhttpcertcfg.exe -g -c LOCAL_MACHINEMy -s "SyncServerCert" -a ASPNET Winhttpcertcfg.exe -g -c LOCAL_MACHINEMy -s "SyncServerCert" -a "NETWORK SERVICE" Winhttpcertcfg.exe -g -c LOCAL_MACHINEMy -s "SyncServerCert" -a "LOCAL SERVICE" Makecert.exe -r -pe -n "CN= SyncClientCert " -b 01/01/2000 -e 01/01/2050 -eku 1.3.6.1.5.5.7.3.1 -ss my -sr localMachine -sky exchange -sp "Microsoft RSA SChannel Cryptographic Provider" -sy 12
Makecert is available with visual studio installation and you can download from Winhttpcertcfg.exe from http://www.microsoft.com/download/en/details.aspx?displaylang=en&id=19801
Launch mmc and add Certificates (Local computer) to mmc. You will found certificates SyncServerCert & SyncClientCert under Personal Certificates.
Copy (right click copy and paste) these certificates under Trusted People /certificates and under Trusted Root Certificate Authorities/certificates.
Now export SyncClientCert certificate (alone with Private key) as pfx file. This can be deployed to sync clients.
Server project is ready to run now, Launch web server project,create login (register) and navigate to sync Clients tab.
Click on create template and then click on setup sync for each client
Setup sync will execute following code
//For filter parameter name see template below
serverProv.Provisioning.PopulateFromTemplate(SyncConfigurations.ClientScopeName(ClientId), ServerProvisioning.TemplateName);
serverProv.Provisioning.Tables[GetTableFullName("Clients")].FilterParameters["@Id"].Value = ClientId;
serverProv.Provisioning.Tables[GetTableFullName("Products")].FilterParameters["@Id"].Value = ClientId;
which will create sync scope of each client.
At this stage, our server is ready to sync with 3 clients. Please note that Sync service uses wsHTTP binding with certificate authentication SyncServerCert.
Sync.Client
For sync client setup, create blank database “SyncLocal” on local machine or where your client application will run,Edit the connection string app.config and also specify the clientId in config file.Not, in production system you may need to provide service from where user will request authentication and once authenticated from server the server will provide clientId based on logon details. In that case you don’t need to hard code client id however for simplicity I have just used the fixed value (read from app.config).
Run this application and click “Sync With Client”, Change Client Id in text box and again click sync With Client.
Please note that sync framework will only create table (and primary keys) however relationships and constraints were not in scope o Sync. For more information check Microsoft documentation.
Pixytech 
Hey, The article and included code is a great piece of code to start developing such application.
Could you please answer on a feq questions?
1. How would you solve the task to load data for synchronization based on joins with the other table?
E.g.: Table1 has ID (uniqueidentifieR), Table2 (ID, DEP). And for instance I would like to synchronize Table1 only for DEP = 10?
2. In your example (above) each user has to have its own parameters’ configuration in scope_parameters table. Is it required in your scenerio?
Thanks
LikeLike
1.You first create server sync template , where you define the input parameter (filter parameter) and use join tsql (filter clause) to provide relationship between different tables.Based on this template you create scope where you provide value for above configured template.As long as any data (either in different table) is reachable via complex or simple sql join you can synchronize data.
2. In this example , the server is multi-tenant host which holds similar kind of data for different client where client is identified by ClientId.,so that clientid can be used in sync filter clause.
You can specify filtering criteria in the scope-configuration file when you are provisioning the database for the desired scope. See this link for details http://msdn.microsoft.com/en-us/library/ff928701.aspx
I must mention that these are filter criteria are static i.e. once a scope has been provisioned with one set of citeria, you would not be able to change the filter at runtime. If you have need for multiple filters (say one clients need only red items and other needs red and blue items), you could work around by havine multiple scopes for multiple filters.
When ever Sync happens it just pass the scope name configured durnig provisining.
Regards
Rajnish Noonia
LikeLike
1.You first create server sync template , where you define the input parameter (filter parameter) and use join tsql (filter clause) to provide relationship between different tables.Based on this template you create scope where you provide value for above configured template.As long as any data (either in different table) is reachable via complex or simple sql join you can synchronize data.
2. In this example , the server is multi-tenant host which holds similar kind of data for different client where client is identified by ClientId.,so that clientid can be used in sync filter clause.
You can specify filtering criteria in the scope-configuration file when you are provisioning the database for the desired scope. See this link for details http://msdn.microsoft.com/en-us/library/ff928701.aspx
I must mention that these are filter criteria are static i.e. once a scope has been provisioned with one set of citeria, you would not be able to change the filter at runtime. If you have need for multiple filters (say one clients need only red items and other needs red and blue items), you could work around by havine multiple scopes for multiple filters.
When ever Sync happens it just pass the scope name configured durnig provisining.
Regards
Rajnish Noonia
LikeLike
Few example of filterclause with join which can be used while you create template
//”[side]” is an alias name for the tracking table
.FilterClause = “[side].[SxID] in (Select su.SxID from schemaName.User su where su.ClientId= @ID)”
Or
.FilterClause = “[side].[PrescriptionID] in (select p.PrescriptionID from schemaName.ServiceUser u inner join schemaName.ServiceUserPrescription p on u.SUID = p.SUID where u.ClientId= @ID)”;
LikeLike
Few example of filterclause with join which can be used while you create template
//”[side]” is an alias name for the tracking table
.FilterClause = “[side].[SxID] in (Select su.SxID from schemaName.User su where su.ClientId= @ID)”
Or
.FilterClause = “[side].[PrescriptionID] in (select p.PrescriptionID from schemaName.ServiceUser u inner join schemaName.ServiceUserPrescription p on u.SUID = p.SUID where u.ClientId= @ID)”;
LikeLike
Few example of filterclause with join which can be used while you create template
//”[side]” is an alias name for the tracking table
.FilterClause = “[side].[SxID] in (Select su.SxID from schemaName.User su where su.ClientId= @ID)”
Or
.FilterClause = “[side].[PrescriptionID] in (select p.PrescriptionID from schemaName.ServiceUser u inner join schemaName.ServiceUserPrescription p on u.SUID = p.SUID where u.ClientId= @ID)”;
LikeLike
Hi,
I’m able to follow your article and run the synchronization successfully with the same DB server for Client and Server..but when I deploy my Server app. and try to Sync. from my Local(client app). I’m getting this error: http://screencast.com/t/lQYC1asqqz , Please help.. Thanks
Regards,
Alvin J.
LikeLike
Hi Alvin,
I seams that sync proxy is not able to open the communication channel .Make sure that certificates are properly installed on local and your server.Try to open default.aspx page of your sync web server and navigate to web server URI (also mentioned in web.config) to ensure that your web service is properly configured and activated.
Regards
Rajneesh
LikeLike
Hi,
I’m able to follow your article and run the synchronization successfully with the same DB server for Client and Server..but when I deploy my Server app. and try to Sync. from my Local(client app). I’m getting this error: http://screencast.com/t/lQYC1asqqz , Please help.. Thanks
Regards,
Alvin J.
LikeLike
Hi when I run your sample code I receive an error: System.Reflection.TargetInvocationException: Prorerty access ‘Depth’ on object sqldatareader threw the following exception: ‘Invalid attempt to call Depth when reader is closed.
Does anyone have an idea on how I can resolve this.
Thank You
LikeLike
Thanks rajnish this is what I was looking for.
LikeLike
Thanks rajnish this is what I was looking for.
LikeLike
Great article.
Given .bat file didn’t work with windows 7.
We have to replace ASPNET with IIS_IUSRS in the given makecert.bat. Then created certificates with the modified bat but when I run the client or go to the service link from the web browser it gives an error.
I have Visual Studio 2008 pro and Visual Studio 2010 Pro installed. So I did the following work around to get it work.
1. Changed the ASPNET to IIS_IUSRS in the “Make Cert.bat” batch file.
2. For the next step I used Default makecert.exe installed with Visual studio 2010 instead of makecert.exe comes with the source code. Copy batch file(MakeCert.bat)to a different folder (C:Test)
3. Downloaded “Winhttpcertcfg.exe” from the given link and copied it to the same folder (C:Test)
4.Opened “Visual Studio Command Prompt(2010)” as an Administrator.
(This can be opened from Start/All Programs/Microsoft Visual Studio 2010/Visual Studio Tools/Visual Studio Command Prompt(2010))
5.Execute the “MakeCert.bat”
LikeLike
Great article.
Given .bat file didn’t work with windows 7.
We have to replace ASPNET with IIS_IUSRS in the given makecert.bat. Then created certificates with the modified bat but when I run the client or go to the service link from the web browser it gives an error.
I have Visual Studio 2008 pro and Visual Studio 2010 Pro installed. So I did the following work around to get it work.
1. Changed the ASPNET to IIS_IUSRS in the “Make Cert.bat” batch file.
2. For the next step I used Default makecert.exe installed with Visual studio 2010 instead of makecert.exe comes with the source code. Copy batch file(MakeCert.bat)to a different folder (C:Test)
3. Downloaded “Winhttpcertcfg.exe” from the given link and copied it to the same folder (C:Test)
4.Opened “Visual Studio Command Prompt(2010)” as an Administrator.
(This can be opened from Start/All Programs/Microsoft Visual Studio 2010/Visual Studio Tools/Visual Studio Command Prompt(2010))
5.Execute the “MakeCert.bat”
LikeLike
Great article.
Given .bat file didn’t work with windows 7.
We have to replace ASPNET with IIS_IUSRS in the given makecert.bat. Then created certificates with the modified bat but when I run the client or go to the service link from the web browser it gives an error.
I have Visual Studio 2008 pro and Visual Studio 2010 Pro installed. So I did the following work around to get it work.
1. Changed the ASPNET to IIS_IUSRS in the “Make Cert.bat” batch file.
2. For the next step I used Default makecert.exe installed with Visual studio 2010 instead of makecert.exe comes with the source code. Copy batch file(MakeCert.bat)to a different folder (C:Test)
3. Downloaded “Winhttpcertcfg.exe” from the given link and copied it to the same folder (C:Test)
4.Opened “Visual Studio Command Prompt(2010)” as an Administrator.
(This can be opened from Start/All Programs/Microsoft Visual Studio 2010/Visual Studio Tools/Visual Studio Command Prompt(2010))
5.Execute the “MakeCert.bat”
LikeLike
Hello Rajnish,
Is the source code still available for the sample?
The above link seems to have died.
Thanks,
Will.
LikeLike
Thanks for highlighting the problem, this should be back soon..
Regards
Rajnish
LikeLike
Hello Rajnish,
Is the source code still available for the sample?
The above link seems to have died.
Thanks,
Will.
LikeLike
Thanks for highlighting the problem, this should be back soon..
Regards
Rajnish
LikeLike
Hi Rajnish,
Very nice sample.
Could you point me to a correct link with your sample code?
The one you provided previously seems to be broken (404),
Thank you in advance,
Regards,
Nikola
LikeLike
Hi Rajnish,
Very nice sample.
Could you point me to a correct link with your sample code?
The one you provided previously seems to be broken (404),
Thank you in advance,
Regards,
Nikola
LikeLike
Hi Rajnish,
Thanks for sharing great information.
But I can’t access your source code, yet. Can you share the source code?
LikeLike
Hi Rajnish,
Thanks for sharing great information.
But I can’t access your source code, yet. Can you share the source code?
LikeLike
Hi Rajnish,
Thanks for sharing great information.
But I can’t access your source code, yet. Can you share the source code?
LikeLike
The link is sill dead, making this whole tutorial a complete waste of time.
LikeLike
The link is sill dead, making this whole tutorial a complete waste of time.
LikeLike
I agree with Shadi. You said the link would be fixed over 4 months ago. Still dead. A total waste of time for sure!!!
LikeLike
Sorry for inconvenience ..
The links were back for few months and were again removed by ISP.
All source code is now available on Sky drive,Please use below mentioned URL to get the source code till the links on blog are fixed.
Source code http://sdrv.ms/1fEDAfw
Regards
Rajnish
LikeLike
I agree with Shadi. You said the link would be fixed over 4 months ago. Still dead. A total waste of time for sure!!!
LikeLike
I agree with Shadi. You said the link would be fixed over 4 months ago. Still dead. A total waste of time for sure!!!
LikeLike
Links are back now forever
LikeLike
Links are back now forever
LikeLike
Hi Rajnish,
I can synchronize the data by using your sample. Thanks for that.
This is very helpful for understanding the concept of data filtering functionality in sysnc frame work. But I have few concerns. Is it possible to provide filters for multiple tables? Because in my sync scenario, my main dB consists of a main Company and the remote db is it branches.Employee details for all branches are consolidated in the main company. Tables are COMPANYBRANCH,EMPLOYEE,ATTENDANCE. Companybranch $ attendance are relational table(PK-FK) and Employee is a master table and it is related to attendance(PK-FK).I have remote databases for singapore & dubai branches.Both remote Dbs are keeping the same structure olike in Main database.
Kindly shed some light to synchronize the data in above scenario.
Thanks & regards,
Sreekumar
LikeLike
Hi Rajnish,
I can synchronize the data by using your sample. Thanks for that.
This is very helpful for understanding the concept of data filtering functionality in sysnc frame work. But I have few concerns. Is it possible to provide filters for multiple tables? Because in my sync scenario, my main dB consists of a main Company and the remote db is it branches.Employee details for all branches are consolidated in the main company. Tables are COMPANYBRANCH,EMPLOYEE,ATTENDANCE. Companybranch $ attendance are relational table(PK-FK) and Employee is a master table and it is related to attendance(PK-FK).I have remote databases for singapore & dubai branches.Both remote Dbs are keeping the same structure olike in Main database.
Kindly shed some light to synchronize the data in above scenario.
Thanks & regards,
Sreekumar
LikeLike
Hi Rajnish,
I can synchronize the data by using your sample. Thanks for that.
This is very helpful for understanding the concept of data filtering functionality in sysnc frame work. But I have few concerns. Is it possible to provide filters for multiple tables? Because in my sync scenario, my main dB consists of a main Company and the remote db is it branches.Employee details for all branches are consolidated in the main company. Tables are COMPANYBRANCH,EMPLOYEE,ATTENDANCE. Companybranch $ attendance are relational table(PK-FK) and Employee is a master table and it is related to attendance(PK-FK).I have remote databases for singapore & dubai branches.Both remote Dbs are keeping the same structure olike in Main database.
Kindly shed some light to synchronize the data in above scenario.
Thanks & regards,
Sreekumar
LikeLike
Keyset does not exist
LikeLike
locally sync is working fine when move to server this similar way i tried in server certificate and all installed but when i open the Sync service Keyset does not exist how to resolve its showing as cryptographic exception
Keyset does not exist
]
System.Security.Cryptography.Utils.CreateProvHandle(CspParameters parameters, Boolean randomKeyContainer) +5816739
System.Security.Cryptography.Utils.GetKeyPairHelper(CspAlgorithmType keyType, CspParameters parameters, Boolean randomKeyContainer, Int32 dwKeySize, SafeProvHandle& safeProvHandle, SafeKeyHandle& safeKeyHandle) +96
System.Security.Cryptography.RSACryptoServiceProvider.GetKeyPair() +139
System.Security.Cryptography.RSACryptoServiceProvider..ctor(Int32 dwKeySize, CspParameters parameters, Boolean useDefaultKeySize) +208
System.Security.Cryptography.X509Certificates.X509Certificate2.get_PrivateKey() +236
System.ServiceModel.Security.SecurityUtils.GetKeyContainerInfo(X509Certificate2 certificate) +44
System.ServiceModel.Security.SecurityUtils.CanKeyDoKeyExchange(X509Certificate2 certificate) +247
System.ServiceModel.Security.SecurityUtils.EnsureCertificateCanDoKeyExchange(X509Certificate2 certificate) +63
[ArgumentException: It is likely that certificate ‘CN=SyncServerCert’ may not have a private key that is capable of key exchange or the process may not have access rights for the private key. Please see inner exception for detail.]
System.ServiceModel.Security.SecurityUtils.EnsureCertificateCanDoKeyExchange(X509Certificate2 certificate) +337
LikeLike