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=S
    yncTesting; 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

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

Halo dunia!

Akhirnya bisa bergabung di Microsoft User Group Indonesia. Ini adalah tulisan pertama saya di blog komunitas ini yang secara otomatis di-generate oleh sistem sebagai penyemangat untuk mulai dan terus menulis. Saya berharap dapat berkontribusi lebih dan menebarkan manfaat untuk komunitas ini. Salam kenal semua! 🙂