Suitable method For synchronising online and offline Data

I have two applications with own database.

1.) Desktop application which has vb.net winforms interface, runs in offline enterprise network and stores data in central database [SQL Server]

**All the data entry and other office operations are carried out and stored in central database

2.) Second application has been build on php. it has html pages and runs as we开发者_如何学JAVAbsite in online environment. It stores all data in mysql database.

**This application is accessed by registered members only and they are facilitied with different reports of the data processed by 1st application.

Now I have to synchronize data between online and offline database servers. I am planning for following:

1.) Write a small program to export all the data of SQL Server [offline server] to a file in CVS format.

2.) Login to admin Section of live server.

3.) Upload the exported cvs file to the server.

4.) Import the data from cvs file to mysql database.

Is the method i am planning good or it can be tunned to perform good. I would also appreciate for other nice ways for data synchronisation other than changing applications.. ie. network application to some other using mysql database

What you are asking for does not actually sound like bidirectional sync (or movement of data both ways from SQL Server to MySQL and from MySQL to SQL Server) which is a good thing as it really simplifies things for you. Although I suspect your method of using CSV's (which I would assume you would use something like BCP to do this) would work, one of the issues is that you are moving ALL of the data every time you run the process and you are basically overwriting the whole MySQL db everytime. This is obviously somewhat inefficient. Not to mention during that time the MySQL db would not be in a usable state.

One alternative (assuming you have SQL Server 2008 or higher) would be to look into using this technique along with Integrated Change Tracking or Integrated Change Capture. This is a capability within SQL Server that allows you to determine data that has changed since a certain point of time. What you could do is create a process that just extracts the changes since the last time you checked to a CSV file and then apply those to MySQL. If you do this, don't forget to also apply the deletes as well.

I don't think there's an off the shelf solution for what you want that you can use without customization - but the MS Sync framework (http://msdn.microsoft.com/en-us/sync/default) sounds close.

You will probably need to write a provider for MySQL to make it go - which may well be less work than writing the whole data synchronization logic from scratch. Voclare is right about the challenges you could face with writing your own synchronization mechanism...

Do look into SQL Server Integration Service as a good alternate.