Step by step using Microsoft Synchronize SDK for multi client

Prepare SQL Server Local Cache

• Start command prompt
• Check your localdb version, if you want deploy to 32 bit client, you should use version 12.xx (SQL server 2014 LocalDB)

Type sqllocaldb v

• To check SqlLocalDB Instannce, use sqllocaldb i

If you have 2 version, now check your MSSQLLocalDB server version

If the version is not version 12.x, you have to create new instance and set version to 12

• Now check again your synctestDB instance using correct version (12.0.2000.8)

• Now you’re ready to go

Open your SQL Management Studio, and connect to localdb with server name : (localdb)\<instance name> (in this example (localdb)\synctestDB) to make sure that SQLLocalDB is running

Create new database and point to new location, in this case I’ll put into “C:\Data\”

• Now go to your Server side SQLServer (you can use Azure / SQLEXpress) and create new database with name SyncTesting too (same with SQLLocalDB database)
• Create new Table

  CREATE
  TABLE [dbo].[TestTable](

  [id] [uniqueidentifier] NOT
  NULL,

  [stationname] [nvarchar](50)
  NULL,

  [checkintime] [datetime] NULL,

  [randomdata] [int] NULL,

  CONSTRAINT [PK_TestTable] PRIMARY
  KEY
  CLUSTERED

  (

  [id] ASC

  )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE =
  OFF, IGNORE_DUP_KEY
  =
  OFF, ALLOW_ROW_LOCKS
  =
  ON, ALLOW_PAGE_LOCKS
  =
  ON) ON [PRIMARY]

  )
  ON [PRIMARY]

  GO

  ALTER
  TABLE [dbo].[TestTable] ADD
  CONSTRAINT [DF_TestTable_id] DEFAULT (newid())
  FOR [id]
  

  GO

Preparing Code

• Download Microsoft Synchronize SDK here
• Install the SDK
• Open your Visual Studio
• Create new Windows Project

  

• Put Textbox
  • Name it with txtLog
  • Set MultiLine => True
  • Set Dock => Bottom
• Put Timer
  • Set Interval = 15000
  • Set Enable = true
• Put Button
  • Set Text = Add random data
• Import DLL SDK

  

• Put into your code

  using System.Data.SqlClient;

  using Microsoft.Synchronization.Data.SqlServer;

  using Microsoft.Synchronization.Data;

  using Microsoft.Synchronization;

• Declare the connection and scopename (for more information about scope, click here

  public
  string sqlserverConnectionString = “Data Source=.; Initial Catalog=SyncTesting; Integrated Security = true;”;
  

  public
  string sqllocalConnectionString = @”Server = (localdb)\synctestDB; Integrated Security = true; Initial Catalog=SyncTesting;AttachDbFileName =c:\data\SyncTesting.mdf”;
  

  public
  string scopeName = System.Environment.MachineName;
  

• Prepare database for synchronize

public
void Setup()

{

try

{

SqlConnection sqlLocalDBConn = new
SqlConnection(sqllocalConnectionString);

SqlConnection sqlServerConn = new
SqlConnection(sqlserverConnectionString);

DbSyncScopeDescription myScope = new
DbSyncScopeDescription(scopeName);

DbSyncTableDescription TestTable = SqlSyncDescriptionBuilder.GetDescriptionForTable(“TestTable”, sqlServerConn);

myScope.Tables.Add(TestTable);

SqlSyncScopeProvisioning sqlServerProv = new
SqlSyncScopeProvisioning(sqlServerConn, myScope);

if (!sqlServerProv.ScopeExists(scopeName))

{

txtLog.AppendText(“Provisioning SQL Server for sync “ + DateTime.Now + Environment.NewLine);

sqlServerProv.Apply();

txtLog.AppendText(“Done Provisioning SQL Server for sync “ + DateTime.Now + Environment.NewLine);

}

else

txtLog.AppendText(“SQL Server Database server already provisioned for sync “ + DateTime.Now + Environment.NewLine);

SqlSyncScopeProvisioning sqlAzureProv = new
SqlSyncScopeProvisioning(sqlLocalDBConn, myScope);

if (!sqlAzureProv.ScopeExists(scopeName))

{

txtLog.AppendText(“Provisioning SQL Azure for sync “ + DateTime.Now + Environment.NewLine);

sqlAzureProv.Apply();

txtLog.AppendText(“Done Provisioning SQL Azure for sync “ + DateTime.Now + Environment.NewLine);

}

else

txtLog.AppendText(“SQL Azure Database server already provisioned for sync “ + DateTime.Now + Environment.NewLine);

sqlLocalDBConn.Close();

sqlServerConn.Close();

}

catch (Exception ex)

{

txtLog.AppendText(ex.Message + Environment.NewLine);

}

}

• Procedure for synchronize

  public
  void Sync()
  

  {
  

  try
  

  {
  

  SqlConnection sqlLocalDBConn = new
  SqlConnection(sqllocalConnectionString);
  

  SqlConnection sqlServerConn = new
  SqlConnection(sqlserverConnectionString);
  

  SyncOrchestrator orch = new
  SyncOrchestrator
  

  {
  

  LocalProvider = new
  SqlSyncProvider(scopeName, sqlLocalDBConn),
  

  RemoteProvider = new
  SqlSyncProvider(scopeName, sqlServerConn),
  

  Direction = SyncDirectionOrder.UploadAndDownload
  

  };
  

  txtLog.AppendText(String.Format(“ScopeName ={0} “, scopeName.ToUpper()) + Environment.NewLine);
  

  txtLog.AppendText(“Starting Sync “ + DateTime.Now + Environment.NewLine);
  

  ShowStatistics(orch.Synchronize());
  

  sqlLocalDBConn.Close();
  

  sqlServerConn.Close();
  

  }
  

  catch (Exception ex)
  

  {
  

  txtLog.AppendText(ex.Message + Environment.NewLine);
  

  }
  

  }

• Procedure for synchronize statistic

public
void ShowStatistics(SyncOperationStatistics syncStats)

{

string message;

message = “Sync Start Time:” + syncStats.SyncStartTime.ToString();

txtLog.AppendText(message + Environment.NewLine);

message = “Sync End Time:” + syncStats.SyncEndTime.ToString();

txtLog.AppendText(message + Environment.NewLine);

message = “Upload Changes Applied:” + syncStats.UploadChangesApplied.ToString();

txtLog.AppendText(message + Environment.NewLine);

message = “Upload Changes Failed:” + syncStats.UploadChangesFailed.ToString();

txtLog.AppendText(message + Environment.NewLine);

message = “Upload Changes Total:” + syncStats.UploadChangesTotal.ToString();

txtLog.AppendText(message + Environment.NewLine);

message = “Download Changes Applied:” + syncStats.DownloadChangesApplied.ToString();

txtLog.AppendText(message + Environment.NewLine);

message = “Download Changes Failed:” + syncStats.DownloadChangesFailed.ToString();

txtLog.AppendText(message + Environment.NewLine);

message = “Download Changes Total:” + syncStats.DownloadChangesTotal.ToString();

txtLog.AppendText(message + Environment.NewLine);

txtLog.AppendText(Environment.NewLine + Environment.NewLine);

}

• Double click on Form, and run Setup procedure

  private
  void Form1_Load(object sender, EventArgs e)

  {

  Setup();

  }

• Double click on Timer, and run Sync

  private
  void timer1_Tick(object sender, EventArgs e)

  {

  Sync();

  }

• Double click on Button, to add random data, assume I’ll add data on my LocalSQLData

  private
  void button1_Click(object sender, EventArgs e)

  {

  SqlConnection sqlLocalDBConn = new
  SqlConnection(sqllocalConnectionString);

  SqlCommand insert_data = new
  SqlCommand(String.Format(“insert into TestTable(stationname,checkintime,randomdata) values(‘{0}’,'{1}’,'{2}’)”,System.Environment.MachineName, DateTime.Now.ToString(“yyyy-MM-dd HH:mm:ss.fff”, CultureInfo.InvariantCulture),new
  Random().Next().ToString()), sqlLocalDBConn);

  insert_data.Connection.Open();

  insert_data.ExecuteNonQuery();

  insert_data.Connection.Close();

  }

• Done.. and try to run

Please note, for deploy to client, make sure:

1. Your client have .NET Framework 4.5.2
2. SQLLocaldb Engine is already installed and setup
3. Your client have copy MDF file (in this case SyncTesting.mdf that located at C:\Data) and your client has full access

Download my source code Here

 

Update January 8th, 2017

I’ve update my code, so it will create automatically instance and database file

What you need is:

• install nuget package

Install-Package System.Data.SqlLocalDb

• import sqllocaldb and system.io

using System.Data.SqlLocalDb;
using System.IO;

• declare new connection string

public string sqlDBCheckConnectionString = @”Server = (localdb)\synctestDB; Integrated Security = true;”;

• add this procedure

public void DBGenerator()
{
//check instance
//and create if
try
{
ISqlLocalDbProvider provider = new SqlLocalDbProvider();
ISqlLocalDbInstance instance = provider.GetInstance(instanceNames);
if (instance == null)
{
//if null, then create and start
System.Diagnostics.Process.Start(“sqllocaldb”, ” c ” + instanceNames + ” 12.0.2000.8 -s”);

}

//check database file
}
catch
{
//no instance, then create it
System.Diagnostics.Process.Start(“sqllocaldb”, ” c ” + instanceNames + ” 12.0.2000.8 -s”);

if (!File.Exists(“c:\\data\\SyncTesting.mdf”))
{
//if no database file, then create it
SqlConnection sqlLocalDBConn = new SqlConnection(sqlDBCheckConnectionString);

string str = “CREATE DATABASE SyncTesting ON PRIMARY ” +
“(NAME = SyncTesting, ” +
“FILENAME = ‘C:\\Data\\SyncTesting.mdf’, ” +
“SIZE = 5MB, MAXSIZE = 10MB, FILEGROWTH = 10%) ” +
“LOG ON (NAME = SyncTesting_Log, ” +
“FILENAME = ‘C:\\Data\\SyncTesting_Log.ldf’, ” +
“SIZE = 5MB, ” +
“MAXSIZE = 5MB, ” +
“FILEGROWTH = 10%)”;

SqlCommand createdb = new SqlCommand(str, sqlLocalDBConn);
try
{
sqlLocalDBConn.Open();
createdb.ExecuteNonQuery();
txtLog.AppendText(“Database create Successfully at ” + System.DateTime.Now.ToLongDateString() + ” ” + Environment.NewLine);
createdb.Connection.Close();
}
catch (Exception ex)
{
txtLog.AppendText(“Database create failed at ” + System.DateTime.Now.ToLongDateString() + ” ” + Environment.NewLine);
txtLog.AppendText(ex.Message+Environment.NewLine);
txtLog.AppendText(“Please check manually or create manually from SQL Management Studio” + Environment.NewLine);

}
finally
{
if (sqlLocalDBConn.State == ConnectionState.Open)
{
sqlLocalDBConn.Close();
}
}
}
}
}

• you’re almost there…,call DBGenerator insite setup procedure

public void Setup()
{
try
{
DBGenerator();

…..

• F5 and take a coffee

get full source code Here