Entire local database sync with sql azure

Question

I am developing a windows application which is using local database. I want to add a function to sync all local data to the sql azure.

Currently I used following code. It enabled me to sync one particular table successfully., here is "Author_Master"

 string sqlazureConnectionString = "XXXX";
 string sqllocalConnectionString = "Server=localhost;Database=Enh_Branchwise_Master_Bookshop;Trusted_Connection=True";

        using (SqlConnection serverCon = new SqlConnection(sqlazureConnectionString))
        using (SqlConnection clientCon = new SqlConnection(sqllocalConnectionString))
        {
            var provider1 = new SqlSyncProvider("scope1", serverCon);
            var provider2 = new SqlSyncProvider("scope1", clientCon);

            prepareServer(provider1);
            prepareClinet(provider2, serverCon);
            SyncOrchestrator sync = new SyncOrchestrator();
            sync.LocalProvider = provider1;
            sync.RemoteProvider = provider2;

            sync.Synchronize();

        }

And following methods also.

 private static void prepareServer(SqlSyncProvider provider)
    {
        SqlConnection connection = (SqlConnection)provider.Connection;
        SqlSyncScopeProvisioning config = new SqlSyncScopeProvisioning(connection);

        if (!config.ScopeExists(provider.ScopeName))
        {
            DbSyncScopeDescription scopeDesc = new DbSyncScopeDescription(provider.ScopeName);
            scopeDesc.Tables.Add(SqlSyncDescriptionBuilder.GetDescriptionForTable("Author_Master", connection));
            config.PopulateFromScopeDescription(scopeDesc);
            config.SetCreateTableDefault(DbSyncCreationOption.CreateOrUseExisting);
            config.Apply();
        }
    }

    private static void prepareClinet(SqlSyncProvider provider, SqlConnection sourceConnection)
    {
        SqlConnection connection = (SqlConnection)provider.Connection;
        SqlSyncScopeProvisioning config = new SqlSyncScopeProvisioning(connection);

        if (!config.ScopeExists(provider.ScopeName))
        {
            DbSyncScopeDescription scopeDesc = new DbSyncScopeDescription(provider.ScopeName);
            scopeDesc.Tables.Add(SqlSyncDescriptionBuilder.GetDescriptionForTable("Author_Master", sourceConnection));
            config.PopulateFromScopeDescription(scopeDesc);
            config.Apply();
        }
    }

My question : Is there any way to sync all the tables in the database at once, without adding one by one table .

My both Databases have the same schema. Please give some suggestions,

I have edited your title. Please see, "[Should questions include “tags” in their titles?](http://meta.stackexchange.com/questions/19190/)", where the consensus is "no, they should not". — John Saunders, Dec 25 '12 at 21:32
Dear i am searching for the same how u have done this for one table can u please help me this thing for me — Neeraj Mehta, Jan 06 '14 at 06:57
@las in m doing the same thing in mvc4 i have used your code but showing error in SqlSyncProvider and all — Neeraj Mehta, Jan 07 '14 at 13:04
@las now i have remove all the code but now the problem is that it is not executing — Neeraj Mehta, Jan 07 '14 at 19:08

Sampath · Answer 1 · 2012-12-26T15:20:04.750

2

You can use SQL Data Sync Tool.

SQL Data Sync is a feature of Windows Azure SQL Database.

With SQL Data Sync you can synchronize selected data through a Windows Azure SQL Database instance.

SQL Data Sync supports synchronizations within or across Windows Azure data centers.

SQL Data Sync also supports hybrid configurations of SQL Database instances and on-premises SQL Server databases.

The SQL Data Sync service is free.

For more details check ScottGu's Blog Post under "SQL Data Sync" sub topic.

I hope this will help to you.

edited Dec 26 '12 at 15:20

answered Dec 26 '12 at 14:28

Sampath

63,341
64
307
441

@JuneT thanks for valuable information.I will update my post accordingly. – Sampath Dec 26 '12 at 15:01
@las check this tool.May be help to you. – Sampath Dec 26 '12 at 15:23

score 1 · Answer 2 · answered Dec 26 '12 at 11:41

1

if you're using Sync Framework, you have to tell it explicitly which tables (or even columns) to actually sync.

answered Dec 26 '12 at 11:41

JuneT

7,840
2
15
14

Currently I am using it, is it ok to iterate all the tables from the database query and execute above code – las Dec 26 '12 at 13:18
you only need to provision once. yes, you can iterate thru a list of tables in add them to a scope. – JuneT Dec 26 '12 at 14:05
ok, thank you for suggestion, that is the thing I'm going to use now – las Dec 27 '12 at 16:23

score 0 · Accepted Answer · answered Jan 06 '14 at 17:03

  Dim clientProvision As SqlSyncScopeProvisioning = New SqlSyncScopeProvisioning(clientCon, syncScope)


                            If Not (clientProvision.ScopeExists("Scope_" + tableName)) Then
                                clientProvision.Apply()
                            End If

                            Dim serverProvision As SqlSyncScopeProvisioning = New SqlSyncScopeProvisioning(serverCon, syncScope)

                            If Not (serverProvision.ScopeExists("Scope_" + tableName)) Then
                                serverProvision.Apply()
                            End If

                            Dim syncOrchestrator As New SyncOrchestrator()

                            ' Create provider for SQL Server
                            Dim clientProvider As New SqlSyncProvider("Scope_" + tableName, clientCon)

                            ' Set the command timeout and maximum transaction size for the SQL Azure provider.
                            Dim serverProvider As New SqlSyncProvider("Scope_" + tableName, serverCon)

                            ' Set Local provider of SyncOrchestrator to the onPremise provider
                            syncOrchestrator.LocalProvider = clientProvider

                            ' Set Remote provider of SyncOrchestrator to the azureProvider provider 
                            syncOrchestrator.RemoteProvider = serverProvider

                            ' Set the direction of SyncOrchestrator session to Upload and Download
                            syncOrchestrator.Direction = SyncDirectionOrder.UploadAndDownload


                            'Dim thread As New Threading.Thread(Sub() ShowStatistics(syncOrchestrator.Synchronize(), tableName))
                            'thread.Start()
                            ShowStatistics(syncOrchestrator.Synchronize(), tableName)

Sampath · Answer 4 · 2012-12-25T18:39:43.093

-1

You can use SQL Database Migration Wizard (SQLAzureMW) for that.

It is an open source application.

You can use it to migrate your SQL database to and from Windows Azure SQL Database.

SQLAzureMW has a user interactive wizard that walks a person through the analysis / migration process.

For get more details about SQL Database Migration Wizard

I hope this will help to you.

edited Dec 25 '12 at 18:39

answered Dec 25 '12 at 18:25

Sampath

63,341
64
307
441

@las why's that?Is there any special requirement or something like that ? – Sampath Dec 26 '12 at 13:54
the migration wizard is not a synchronization tool. its a migration tool. you can't do incremental synchronization with a migration tool. – JuneT Dec 26 '12 at 14:06

Entire local database sync with sql azure

4 Answers4

Linked