Step by step N-tier configuration of Sync services for ADO.NET 2.0
Recently I have worked on couple of cases where customers were trying to use N-tier configuration of Sync services for ADO.NET on IIS. In this blog we will use IIS and setup N-tier configuration of sync service for ADO.NET version 2.0 which comes as part of Microsoft Sync Framework 1.0.
Preparing Environment:
We need a development machine that we will use to develop the application, a middle tier server where IIS is installed and configured for WCF services and database server. If you like, you can use the same development box for IIS and database server too.
a) Client tier
We will develop the application (client and WCF service) and run the client part of the application in this machine. We will use Visual Studio 2008, SP1, so it should be installed on this machine.
Install sync services on this client machine. Sync Services for ADO.NET version 2.0 comes with Microsoft Sync Framework 1.0 which gets installed if you install SQL Server 2008 or Visual studio 2008, SP1. You can also download it from https://www.microsoft.com/downloads/details.aspx?FamilyId=C88BA2D1-CEF3-4149-B301-9B056E7FB1E6&displaylang=en
Install SQL Server Compact 3.5 Service Pack 1 on this client machine if it is already not there. SQL Server Compact is available in three ways: - Integrated with SQL Server 2008 or later versions, Integrated with Microsoft Visual Studio 2008 or later versions and as a download from the Web site at: https://www.microsoft.com/sqlserver/2005/en/us/compact-downloads.aspx
b) Middle tier
If you want to use a separated middle tier server for IIS to run WCF service then make sure you have IIS installed and configured on this box to run WCF. Install sync services on this machine too. I have used the same development machine for middle tier so I did not have to install it again.
c) Database Server
Install a version of SQL Server other than SQL Server Compact to act as the server database. If you like to you can use SQL Server Express that installs with Visual Studio. I have used developer edition of SQL 2008, SP1.
We are going to use a sample database that should be prepared by following the article on “Setup Scripts for Sync Services How-to Topics” at: https://msdn.microsoft.com/en-us/library/bb726041.aspx. Copy the T-SQL statements of “Custom Change Tracking for Offline Scenarios” that creates a custom change tracking infrastructure. Once you run this script successfully from query analyzer of your SQL Server it will create the new database by the name SyncSamplesDb. I have the database created as shown below:
Developing and deploying WCF service:
Create a WCF Services Application in Visual Studio 2008: (You may need to run Visual Studio as Administrator to create virtual directories in IIS)
Type “WcfForSync” as the name of the project and click on “OK” button. It will create the WCF project with IService1.cs, Service1.svc, Service1.svc.cs and Web.config files with other files and folders. I will keep these default files for simplicity.
Open the IService1.cs file by double clicking it and replace the code with the code below: (Note that the code samples in the following sections have been taken from the MSDN articles mentioned in the reference section at the bottom with little modification)
using System.Collections.ObjectModel;
using System.ServiceModel;
using System.Data;
using Microsoft.Synchronization;
using Microsoft.Synchronization.Data;
using Microsoft.Synchronization.Data.Server;
namespace WcfForSync
{
[ServiceContract]
public interface IService1
{
[OperationContract()]
SyncContext ApplyChanges(SyncGroupMetadata groupMetadata, DataSet dataSet, SyncSession syncSession);
[OperationContract()]
SyncContext GetChanges(SyncGroupMetadata groupMetadata, SyncSession syncSession);
[OperationContract()]
SyncSchema GetSchema(Collection<string> tableNames, SyncSession syncSession);
[OperationContract()]
SyncServerInfo GetServerInfo(SyncSession syncSession);
}
}
Next add a class file by the name SampleServerSyncProvider.cs as below:
Replace the code in this file with the code below:
using System.Data;
using System.Data.SqlClient;
using Microsoft.Synchronization;
using Microsoft.Synchronization.Data;
using Microsoft.Synchronization.Data.Server;
namespace WcfForSync
{
//Create a class that is derived from
//Microsoft.Synchronization.Server.DbServerSyncProvider.
public class SampleServerSyncProvider : DbServerSyncProvider
{
public SampleServerSyncProvider()
{
//Create a connection to the sample server database.
Utility util = new Utility();
SqlConnection serverConn = new SqlConnection(util.ServerConnString);
this.Connection = serverConn;
//Create a command to retrieve a new anchor value from
//the server. In this case, we use a timestamp value
//that is retrieved and stored in the client database.
//During each synchronization, the new anchor value and
//the last anchor value from the previous synchronization
//are used: the set of changes between these upper and
//lower bounds is synchronized.
//
//SyncSession.SyncNewReceivedAnchor is a string constant;
//you could also use @sync_new_received_anchor directly in
//your queries.
SqlCommand selectNewAnchorCommand = new SqlCommand();
string newAnchorVariable = "@" + SyncSession.SyncNewReceivedAnchor;
selectNewAnchorCommand.CommandText = "SELECT " + newAnchorVariable + " = min_active_rowversion() - 1";
selectNewAnchorCommand.Parameters.Add(newAnchorVariable, SqlDbType.Timestamp);
selectNewAnchorCommand.Parameters[newAnchorVariable].Direction = ParameterDirection.Output;
selectNewAnchorCommand.Connection = serverConn;
this.SelectNewAnchorCommand = selectNewAnchorCommand;
//Create a SyncAdapter for the Customer table by using
//the SqlSyncAdapterBuilder:
// * Specify the base table and tombstone table names.
// * Specify the columns that are used to track when
// changes are made.
// * Specify download-only synchronization.
// * Call ToSyncAdapter to create the SyncAdapter.
// * Specify a name for the SyncAdapter that matches the
// the name specified for the corresponding SyncTable.
// Do not include the schema names (Sales in this case).
SqlSyncAdapterBuilder customerBuilder = new SqlSyncAdapterBuilder(serverConn);
customerBuilder.TableName = "Sales.Customer";
customerBuilder.TombstoneTableName = customerBuilder.TableName + "_Tombstone";
customerBuilder.SyncDirection = SyncDirection.DownloadOnly;
customerBuilder.CreationTrackingColumn = "InsertTimestamp";
customerBuilder.UpdateTrackingColumn = "UpdateTimestamp";
customerBuilder.DeletionTrackingColumn = "DeleteTimestamp";
SyncAdapter customerSyncAdapter = customerBuilder.ToSyncAdapter();
customerSyncAdapter.TableName = "Customer";
this.SyncAdapters.Add(customerSyncAdapter);
}
}
public class Utility
{
//Return the server connection string.
public string ServerConnString
{
get { return @"Data Source= SQLServer\instance; Initial Catalog=SyncSamplesDb; User Id=xxxxxx; Password=xxxxxx"; }
}
}
}
Note: You need to update the connection string in the above Utility class to connect to your SQL Server.
Open Service1.svc.cs file in the project by double clicking on it:
and replace the existing code with the code below:
using System.Collections.ObjectModel;
using System.ServiceModel;
using System.Data;
using Microsoft.Synchronization;
using Microsoft.Synchronization.Data;
using Microsoft.Synchronization.Data.Server;
namespace WcfForSync
{
// NOTE: If you change the class name "Service1" here, you must also update the reference to "Service1" in App.config.
public class Service1 : IService1
{
private SampleServerSyncProvider _serverSyncProvider;
public Service1()
{
this._serverSyncProvider = new SampleServerSyncProvider();
}
[System.Diagnostics.DebuggerNonUserCodeAttribute()]
public virtual SyncContext ApplyChanges(SyncGroupMetadata groupMetadata, DataSet dataSet, SyncSession syncSession)
{
return this._serverSyncProvider.ApplyChanges(groupMetadata, dataSet, syncSession);
}
[System.Diagnostics.DebuggerNonUserCodeAttribute()]
public virtual SyncContext GetChanges(SyncGroupMetadata groupMetadata, SyncSession syncSession)
{
return this._serverSyncProvider.GetChanges(groupMetadata, syncSession);
}
[System.Diagnostics.DebuggerNonUserCodeAttribute()]
public virtual SyncSchema GetSchema(Collection<string> tableNames, SyncSession syncSession)
{
return this._serverSyncProvider.GetSchema(tableNames, syncSession);
}
[System.Diagnostics.DebuggerNonUserCodeAttribute()]
public virtual SyncServerInfo GetServerInfo(SyncSession syncSession)
{
return this._serverSyncProvider.GetServerInfo(syncSession);
}
}
}
The application requires references to Microsoft.Synchronization.dll, Microsoft.Synchronization.Data.dll and Microsoft.Synchronization.Data.Server.dll. Right click on “References” from the project and click on Add Reference…
Select Microsoft.Synchronization.dll (Location on my machine: C:\Program Files (x86)\Microsoft Sync Framework\v1.0\Runtime\x86)
And next add the references to the other two dlls (Location on my machine: C:\Program Files (x86)\Microsoft Sync Framework\v1.0\Runtime\ADO.NET\V2.0\x86)
Now you should be able to build the project successfully. Once building is successful publish the WCF to IIS. Go to the properties of the project and under Web* tab type IIS server information (middletierserver) , click on the “Create Virtual Directory” as below:
From IIS you can see a Virtual directory has been created:
If you browse the WCF (https://middletierserver/WcfForSync/Service1.svc) you should get the following page. I have used the same development machine for IIS, so it is showing “localhost” in the URL.
Note:
When tried to browse WCF I have noticed following error with some IIS machines:
HTTP Error 404.3 – Not Found
The page you are requesting cannot be served because of the extension configuration. If the page is a script, add a handler. If the file should be downloaded, add a MIME map. Detailed Error InformationModule StaticFileModule.
If you encounter this error please take necessary action as per article at: https://blogs.msdn.com/rjohri/archive/2009/06/29/the-page-you-are-requesting-cannot-be-served-because-of-the-extension-configuration.aspx
Developing client application and consuming WCF service:
Create a Console application in Visual Studio:
Once the project is created, reference the dlls Microsoft.Synchronization.dll, Microsoft.Synchronization.Data.dll, and Microsoft.Synchronization.Data.SqlServerCe.dll as before. You also need to reference System.Data.SqlServerCe.dll (you should have this dll on your machine once you installed SQL Server Compact 3.5 Service Pack 1, location of this dll on my machine: C:\Program Files (x86)\Microsoft SQL Server Compact Edition\v3.5\Desktop as shown below)
Now we need to do service reference to our WCF that we developed and deployed before. Right click on the Reference in this client project and select Add Service Reference…
In the Add Service Reference screen you type the URL of our WCF and click on Go button as shown below:
Click on “OK” button, it will create service reference as below:
Replace the code in the file Program.cs with the following code:
using System;
using System.IO;
using System.Data;
using System.Data.SqlClient;
using System.Data.SqlServerCe;
using Microsoft.Synchronization;
using Microsoft.Synchronization.Data;
using Microsoft.Synchronization.Data.SqlServerCe;
namespace ClientForSync
{
class Program
{
static void Main(string[] args)
{
//The Utility class handles all functionality that is not
//directly related to synchronization, such as holding connection
//string information and making changes to the server database.
Utility util = new Utility();
//The SampleStats class handles information from the SyncStatistics
//object that the Synchronize method returns.
SampleStats sampleStats = new SampleStats();
//Delete and re-create the database. The client synchronization
//provider also enables you to create the client database
//if it does not exist.
////util.SetClientPassword();
util.RecreateClientDatabase();
//Initial synchronization. Instantiate the SyncAgent
//and call Synchronize.
SampleSyncAgent sampleSyncAgent = new SampleSyncAgent();
SyncStatistics syncStatistics = sampleSyncAgent.Synchronize();
sampleStats.DisplayStats(syncStatistics, "initial");
//Make changes on the server.
util.MakeDataChangesOnServer();
//Subsequent synchronization.
syncStatistics = sampleSyncAgent.Synchronize();
sampleStats.DisplayStats(syncStatistics, "subsequent");
//Return server data back to its original state.
util.CleanUpServer();
//Exit.
Console.Write("\nPress Enter to close the window.");
Console.ReadLine();
}
}
//Create a class that is derived from
//Microsoft.Synchronization.SyncAgent.
public class SampleSyncAgent : SyncAgent
{
public SampleSyncAgent()
{
//Instantiate a client synchronization provider and specify it
//as the local provider for this synchronization agent.
this.LocalProvider = new SampleClientSyncProvider();
//The remote provider now references a proxy instead of directly referencing the server provider. The proxy is created by passing a reference to a WCF service
ServiceReference1.Service1Client serviceProxy = new ServiceReference1.Service1Client();
this.RemoteProvider = new ServerSyncProviderProxy(serviceProxy);
//Add the Customer table: specify a synchronization direction of
//DownloadOnly.
SyncTable customerSyncTable = new SyncTable("Customer");
customerSyncTable.CreationOption = TableCreationOption.DropExistingOrCreateNewTable;
customerSyncTable.SyncDirection = SyncDirection.DownloadOnly;
this.Configuration.SyncTables.Add(customerSyncTable);
}
}
//Create a class that is derived from
//Microsoft.Synchronization.Data.SqlServerCe.SqlCeClientSyncProvider.
//You can just instantiate the provider directly and associate it
//with the SyncAgent, but you could use this class to handle client
//provider events and other client-side processing.
public class SampleClientSyncProvider : SqlCeClientSyncProvider
{
public SampleClientSyncProvider()
{
//Specify a connection string for the sample client database.
Utility util = new Utility();
this.ConnectionString = util.ClientConnString;
}
}
//Handle the statistics that are returned by the SyncAgent.
public class SampleStats
{
public void DisplayStats(SyncStatistics syncStatistics, string syncType)
{
Console.WriteLine(String.Empty);
if (syncType == "initial")
{
Console.WriteLine("****** Initial Synchronization ******");
}
else if (syncType == "subsequent")
{
Console.WriteLine("***** Subsequent Synchronization ****");
}
Console.WriteLine("Start Time: " + syncStatistics.SyncStartTime);
Console.WriteLine("Total Changes Downloaded: " + syncStatistics.TotalChangesDownloaded);
Console.WriteLine("Complete Time: " + syncStatistics.SyncCompleteTime);
Console.WriteLine(String.Empty);
}
}
public class Utility
{
//Return the client connection string with the password. Don’t forget to create folder
public string ClientConnString
{
get { return @"Data Source='D:\\SyncServices\\SyncSampleClient.sdf'; Password=xxxxxxx"; }
}
//Return the server connection string.
public string ServerConnString
{
get { return @"Data Source= SQLServer\instance; Initial Catalog=SyncSamplesDb; User Id=xxxxxx; Password=xxxxxx"; }
}
//Make server changes that are synchronized on the second
//synchronization.
public void MakeDataChangesOnServer()
{
int rowCount = 0;
using (SqlConnection serverConn = new SqlConnection(this.ServerConnString))
{
SqlCommand sqlCommand = serverConn.CreateCommand();
sqlCommand.CommandText =
"INSERT INTO Sales.Customer (CustomerName, SalesPerson, CustomerType) " +
"VALUES ('Cycle Mart', 'James Bailey', 'Retail') " +
"UPDATE Sales.Customer " +
"SET SalesPerson = 'James Bailey' " +
"WHERE CustomerName = 'Tandem Bicycle Store' " +
"DELETE FROM Sales.Customer WHERE CustomerName = 'Sharp Bikes'";
serverConn.Open();
rowCount = sqlCommand.ExecuteNonQuery();
serverConn.Close();
}
Console.WriteLine("Rows inserted, updated, or deleted at the server: " + rowCount);
}
//Revert changes that were made during synchronization.
public void CleanUpServer()
{
using (SqlConnection serverConn = new SqlConnection(this.ServerConnString))
{
SqlCommand sqlCommand = serverConn.CreateCommand();
sqlCommand.CommandType = CommandType.StoredProcedure;
sqlCommand.CommandText = "usp_InsertSampleData";
serverConn.Open();
sqlCommand.ExecuteNonQuery();
serverConn.Close();
}
}
//Delete the client database.
public void RecreateClientDatabase()
{
using (SqlCeConnection clientConn = new SqlCeConnection(this.ClientConnString))
{
if (File.Exists(clientConn.Database))
{
File.Delete(clientConn.Database);
}
}
SqlCeEngine sqlCeEngine = new SqlCeEngine(this.ClientConnString);
sqlCeEngine.CreateDatabase();
}
}
}
Now we have to update the connection string information in the code. In the Utility class in the below methods you should update the connection string for SQL server and client database (SQL CE database)
//Return the client connection string with the password. Don’t forget to create folder
public string ClientConnString
{
get { return @"Data Source='D:\\SyncServices\\SyncSampleClient.sdf'; Password=xxxxxxx"; }
}
//Return the server connection string.
public string ServerConnString
{
get { return @"Data Source= SQLServer\instance; Initial Catalog=SyncSamplesDb; User Id=xxxxxxx; Password=xxxxxxx"; }
}
Build this client project, at this point the project should compile successfully.
Running/testing synchronization:
Run the Console application and you should see the result of the synchronization of this sample as below:
You can run query on the Customer table in SyncSamplesDb to check the changes by setting the break point in the client app and also by opening a SQL profiler you can see the activities going on in SQL Server.
Happy Synchronization!
References:
Overview (Synchronization Services) : https://msdn.microsoft.com/en-us/library/bb726031(SQL.90).aspx
Architecture and Classes (Synchronization Services) : https://msdn.microsoft.com/en-us/library/bb726025(SQL.90).aspx
Getting Started: A Synchronization Services Application: https://msdn.microsoft.com/en-us/library/bb726015.aspx
How to: Configure N-Tier Synchronization at: https://msdn.microsoft.com/en-us/library/bb902831.aspx
A large scale implementation of Sync Service example at: https://blogs.msdn.com/sync/archive/2009/10/19/sharepoint-2010-now-integrates-microsoft-sync-framework.aspx
Microsoft Sync Framework 2.0 redistributable Package - https://www.microsoft.com/downloads/details.aspx?FamilyId=109DB36E-CDD0-4514-9FB5-B77D9CEA37F6&displaylang=en
Author : Faiz(MSFT), SQL Developer Engineer
Reviewed by : Enamul(MSFT), SQL Developer Technical Lead; Azim(MSFT), SQL Developer Technical Lead;Srini(MSFT), SQL Developer Engineer
Comments
Anonymous
December 19, 2012
I am using VS2010, SQL Server CE, SQL Server 2008 R2, WCF & Microsoft Synchronization Framework 2.1 I have created a sample to sync 125 tables from local *.sdf file to server database using WCF. It is working fine if WCF is hosted on my local machine, but when I host it on remote server then its raising the following error: Table name 'tblStudent' is not valid. This might be caused by one or more of the following issues: unable to find a SyncAdapter for the specified SyncTable; the table name is null or empty; or the table name contains the keywords SET, FMTONLY, and OFF. can u help me ?Anonymous
December 20, 2012
Hi Nitin, sounds like the scope on the server is missing the tblStudent - I would start there. Cheers PeterAnonymous
September 02, 2013
Hi nitin, can u plz upload your code?Anonymous
April 27, 2017
But for large projects it is recommended to use three tier implementation. Step by step 3 tier architecture example. configuration . So as you see from above code that we have successfully added connection string "Mythreetier" to web config file.