Syncronizing Large Databases

Syncronizing Large Databases

Postby muhammadd » Wed 15 Nov 2017 16:54

Hi,

Is it feasible to implement the synchronization of large databases (~20M Rows). I Implemented synchronization of 1-100 rows it works all well and good.As soon as I increase the database size to 1M rows and ran the code, my CPU ram increases to 500MB and synchronization didn't occur without throwing any exceptions(provisioning did occur for both server and client but initially there weren't any rows in server database). If I decide to use the batching to synchronize the data, would I be able to solve this problem.

Code: Select all
using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Windows.Forms;
using Devart.Data.MySql;
using Devart.Data.Synchronization;
using Microsoft.Synchronization.Data;
using Devart.Data.MySql.Synchronization;
using Microsoft.Synchronization;

namespace DataConsolidationApplication
{
    public partial class Form1 : Form
    {
        public Form1()
        {
            InitializeComponent();
        }

        private void Form1_Load(object sender, EventArgs e)
        {
            MySqlConnection serverConnection = new MySqlConnection(@"Database=ptls;host=localhost;user id=root;password=;Connect Timeout=1500");
            MySqlConnection cloudConnection = new MySqlConnection(@"Database=ptls;host=master.cegtagfijlyq.us-west-2.rds.amazonaws.com;user id=root;password=;Connect Timeout=1500");
            try {
                provisionServer(serverConnection);
                provisionCloud(serverConnection, cloudConnection);
                syncronize(serverConnection, cloudConnection);
            }
            catch (System.InvalidCastException ev) {
                MessageBox.Show(ev.Message);
            }
            this.Close();
        }

        public void provisionServer(MySqlConnection server_connection) {
            MySqlSyncScopeProvisioning config = new MySqlSyncScopeProvisioning(server_connection);
           
            if(!config.ScopeExists("DialsScope"))
            {
                // Define the scope, named ProductsScope
                DbSyncScopeDescription scopeDesc = new DbSyncScopeDescription("DialsScope");
                // Retrive the description for the Products table from the database
                DbSyncTableDescription tableDesc  = MySqlSyncDescriptionBuilder.GetDescriptionForTable("dials", server_connection);
                scopeDesc.Tables.Add(tableDesc);
                config.PopulateFromScopeDescription(scopeDesc);
                config.SetCreateTableDefault(DbSyncCreationOption.CreateOrUseExisting);
                config.Apply();
            }
        }

        public void provisionCloud(MySqlConnection server_connection, MySqlConnection cloud_connection) {
            MySqlSyncScopeProvisioning config = new MySqlSyncScopeProvisioning(cloud_connection);

            if (!config.ScopeExists("DialsScope"))
            {
                // Define the scope, named ProductsScope
                DbSyncScopeDescription scopeDesc = MySqlSyncDescriptionBuilder.GetDescriptionForScope("DialsScope",server_connection);
                config.PopulateFromScopeDescription(scopeDesc);
                config.Apply();
            }
        }

        public void syncronize(MySqlConnection server_connection, MySqlConnection cloud_connection)
        {
            SyncOrchestrator syncOrchestrator = new SyncOrchestrator();

            //Specify source database
           // remote.MemoryDataCacheSize = 1000;
            syncOrchestrator.RemoteProvider = new MySqlSyncProvider("DialsScope", server_connection, null, null);
            //local.MemoryDataCacheSize = 1000;
            syncOrchestrator.LocalProvider = new MySqlSyncProvider("DialsScope", server_connection, null, null);

            syncOrchestrator.Direction = SyncDirectionOrder.Download;
            SyncOperationStatistics syncStats = syncOrchestrator.Synchronize();

            MessageBox.Show("Start Time : "+ syncStats.SyncStartTime + "Total Changes Uploaded : "+ syncStats.UploadChangesTotal + "Total Changes Downloaded : "+syncStats.DownloadChangesTotal+ "Complete Time : "+syncStats.SyncEndTime);
        }

        public void deprovision(MySqlConnection connection)
        {
            // Remove the "DialsScope" scope from the server database.       
            MySqlSyncScopeDeprovisioning scopeDeprovision = new MySqlSyncScopeDeprovisioning(connection);
            // Remove the scope.
            scopeDeprovision.DeprovisionScope("DialsScope");
        }
    }
}
muhammadd
 
Posts: 1
Joined: Wed 15 Nov 2017 16:32

Re: Syncronizing Large Databases

Postby Pinturiccio » Fri 17 Nov 2017 16:57

Thank you for the provided information. We are working on the reproducing the issue. We will post here when we get any results.
Pinturiccio
Devart Team
 
Posts: 2046
Joined: Wed 02 Nov 2011 09:44


Return to dotConnect for MySQL