Red Gate forums :: View topic - Synchornise with Remote Databases
Return to www.red-gate.com RSS Feed Available

Search  | Usergroups |  Profile |  Messages |  Log in  Register 
Go to product documentation
SQL Toolkit Previous Versions
SQL Toolkit Previous Versions forum

Synchornise with Remote Databases

Search in SQL Toolkit Previous Versions forum
Post new topic   Reply to topic
Jump to:  
Author Message
kinton



Joined: 04 Jan 2008
Posts: 4

PostPosted: Fri Jan 04, 2008 7:52 pm    Post subject: Synchornise with Remote Databases Reply with quote

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?
_________________
thanks

Darren
Back to top
View user's profile Send private message
richardjm



Joined: 20 May 2005
Posts: 382
Location: Red Gate Software Ltd

PostPosted: Mon Jan 07, 2008 10:48 am    Post subject: Reply with quote

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
_________________
Richard Mitchell
Project Manager
Red Gate Software Ltd
Back to top
View user's profile Send private message Send e-mail
kinton



Joined: 04 Jan 2008
Posts: 4

PostPosted: Mon Jan 07, 2008 11:04 am    Post subject: Reply with quote

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?
_________________
thanks

Darren
Back to top
View user's profile Send private message
richardjm



Joined: 20 May 2005
Posts: 382
Location: Red Gate Software Ltd

PostPosted: Mon Jan 07, 2008 11:20 am    Post subject: Reply with quote

Ahh I thought they could communicate with each other, that makes the problem somewhat more difficult Smile. 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?
_________________
Richard Mitchell
Project Manager
Red Gate Software Ltd
Back to top
View user's profile Send private message Send e-mail
kinton



Joined: 04 Jan 2008
Posts: 4

PostPosted: Mon Jan 07, 2008 11:34 am    Post subject: Reply with quote

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?
_________________
thanks

Darren
Back to top
View user's profile Send private message
richardjm



Joined: 20 May 2005
Posts: 382
Location: Red Gate Software Ltd

PostPosted: Mon Jan 07, 2008 12:03 pm    Post subject: Reply with quote

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 Smile
_________________
Richard Mitchell
Project Manager
Red Gate Software Ltd
Back to top
View user's profile Send private message Send e-mail
kinton



Joined: 04 Jan 2008
Posts: 4

PostPosted: Mon Jan 07, 2008 12:05 pm    Post subject: Reply with quote

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

Darren
Back to top
View user's profile Send private message
Display posts from previous:   
Reply to topic All times are GMT + 1 Hour
Page 1 of 1

 
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot vote in polls in this forum


Powered by phpBB © 2001, 2005 phpBB Group