Synchornise with Remote Databases

kinton

Hi,

I am thinking about using SQL Toolkit to synchronise with remote databases. Baiscally we have 500 odd users who all insist on using an offline database, i.e. they are out on the road an always need access to data. We've installed MSDE (am thinking about upgrading to SQL Express) on each of their laptops and need them to synchronise changes with the master databases nightly . They will only have HTTP/FTP connections to our network and hence our database.

The master SQL 2000 database in my network is approx 90 gb and could have thousands of changes each day, each remote database is approx 1 gb. Of the changes at the master database only a small subset in each table are relevent to each user (they are sales reps and it's just the changes to their customers data they need). Of the changes on the reps databases all changes need to come to us.

Is SQL Toolkit the right job for this?

richardjm

You could use sql command line with a project set up for each user that excluded the non-relevant sets of data from the server database to speed up the comparison.

In 6.0 there is a bug where the .sdc project files aren't completely supported with all the settings in the UI although in the upcoming 6.1 release we should have fixed that.

If you want total control over the comparison and the synchronisation though SQL Toolkit and a small custom application is definitely the way to go.

HTH

kinton

Hi,

Thanks for the reply. The concept I am struggling with is that the two databases will not be able to talk to one another. Can I say export a text file from one with changes and compare it to another?

richardjm

Ahh I thought they could communicate with each other, that makes the problem somewhat more difficult . I suppose what you could do is transport a SQL Backup of the remote database (which would be quicker and smaller than a standard SQL Server backup). Then locally or in the network of the main database perform a comparison restricted appropriately prompted via some service or scheduled task. Then the remote machine could pick up the synchronization sql generated and run it locally to perform the update.

Does that make sense in your environment?

kinton

ok - I think so. The biggest problem is the large database to the remote small database. The changes from smaller database are minimal so we couuld do that in another way. I like the thought of the Sync SQL. I will have a play with that. Out of interest, how does the toolkit track changes and updates if it has nothing to comparte against?

richardjm

I think you would then have to think about the SQL Packager API. Although with nothing to compare against all you would be able to generate are insert statements for complete tables, no updates or deletes. The alternative I suppose is to keep a backup of the main database after the last synchronization and then compare the main database with that to give the updates needed to keep the remote databases in synch. Migrating the changes from the remote databases could be done in a similar way with comparing to backups after the last synch so only changes would be generated for inclusion in the main database.

You've got a complex scenario here and I don't envy you one bit

kinton

Indeed I have! Thanks for your help, I will have a think.