i have multiple databases(sql server 2008) each with multiple tables,each database at different servers to be synced at real time changes

by sahil dombe   Last Updated February 11, 2019 10:06 AM

They should be synced at all servers,update on any table at any server should be synced.In short,all tables should have same data at all times.

DbSyncScopeDescription scopeDesc = new DbSyncScopeDescription("Scope");

        // get the description of the table from SyncDB dtabase
        DbSyncTableDescription tableDesc = SqlSyncDescriptionBuilder.GetDescriptionForTable("table1", serverConn);
        DbSyncTableDescription tableDesc1 = SqlSyncDescriptionBuilder.GetDescriptionForTable("table2", serverConn);

        // add the table description to the sync scope definition
        scopeDesc.Tables.Add(tableDesc);
        scopeDesc.Tables.Add(tableDesc1);

        // create a server scope provisioning object based on the Scope
        SqlSyncScopeProvisioning serverProvision = new SqlSyncScopeProvisioning(serverConn, scopeDesc);

        // skipping the creation of table since table already exists 
        serverProvision.SetCreateTableDefault(DbSyncCreationOption.Skip);

        //Create new selectchanges procedure for our scope
        serverProvision.SetCreateProceduresForAdditionalScopeDefault(DbSyncCreationOption.Create);

        // Start the provision-USE ONLY ONCE
        //serverProvision.Apply();

        //Get definition for Scope 
        DbSyncScopeDescription scopeDescription = SqlSyncDescriptionBuilder.GetDescriptionForScope("Scope", serverConn);

        //Provise NODE using this definition
        SqlSyncScopeProvisioning productionProvisioning = new SqlSyncScopeProvisioning(serverConn2, scopeDescription);
        SqlSyncScopeProvisioning productionProvisioning2 = new SqlSyncScopeProvisioning(serverConn3, scopeDescription);
        // Start the provision-USE ONLY ONCE
        //productionProvisioning.Apply();
        //productionProvisioning2.Apply();

        SyncOrchestrator syncOrchestrator = new SyncOrchestrator();

        //Specify source database
        syncOrchestrator.RemoteProvider = new SqlSyncProvider("Scope", serverConn, null, null);

        //Specify production database
        syncOrchestrator.LocalProvider = new SqlSyncProvider("Scope", serverConn2, null, null);
        SyncFn(syncOrchestrator);

        syncOrchestrator.LocalProvider = new SqlSyncProvider("Scope", serverConn3, null, null);
        SyncFn(syncOrchestrator);

    }
    public void SyncFn(SyncOrchestrator syncOrchestrator)
    {
        // execute the synchronization process
        SyncOperationStatistics syncStats = syncOrchestrator.Synchronize();
        Initiatedtxtbx.Text = syncStats.SyncStartTime.ToString();
        ChngesUptxtbx.Text = syncStats.UploadChangesTotal.ToString();
        ChngesDwntxtbx.Text = syncStats.DownloadChangesTotal.ToString();
        Completntxtbx.Text = syncStats.SyncEndTime.ToString();
    }

It works with 1 same table in 2 databases.

All have identity increment as primary key. data in table1 is around 20000 and table 2 300000.The first instance of SyncFn(syncOrchestrator) itself keeps running indefinitely without errors, cant understand whether its processing or stuck somewhere;i changed only 1 row to check sync.

what options do i have ?what am i missing?



Related Questions


Distributing MS Access Database

Updated May 16, 2016 08:02 AM



Error 1064 (4200) MySQL on CentOS 7 64 bit

Updated December 02, 2018 05:06 AM