Ok, so I've been looking all over the web to try and solve my problem... I have an SQL 2005 database on a web server which displays information to customers via an asp.net web site which also offers interaction with the data. I also have a Windows .Net application which to date has been querying and updating to the web server. There are also other modifications from another country and that is of course even slower.
I have been trawling pages on the web to try and find a solution but keep running in to walls. What I would like is for the .Net application to work from an SQL server on the local network and then to regularly merge with the master database on the web server, using the most recent of all modifications as the priority change. I would like multiple networks too, each having it's own local version of the database which regularly updates without putting strain on the individual machines in that network.
The solution I thought would have been the best is upgrading to SQL Server 2008 and using the MS Sync Framework but after managing to add change tracking and set up the local cache in the application, I discovered that MS SQL Compact database would not work, a shame as I really thought that might have been the answer.
What I would be keen to know is if there is a way to use change tracking on the local and web server and sync the different databases without having to modify the application. When using a local database the application runs exactly how I would like it, very fast but that's no good due to the data coming from different locations. I tried using subscriptions but the web SQL server has to use an IP address so that's no good.
I just feel that with the introduction of change tracking on SQL Server 2008 that this should be a simple thing to achieve but I can't find anything.
I've also tried a few compare and synchronisation products but they don't really merge the data the way Zi would like. The sync would need to be on a column basis too.
Any help or advice on tis would be appreciated.