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.
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 220.127.116.11.18.104.22.168.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 22.214.171.124.126.96.36.199.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.
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.