Synchronising with remote SQL server

[potsy]

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.

Thanks
Duncan

[darkagn]

Check out the Microsoft Sync Framework. It's a bit of work to set up in your application but once up and running it works a treat.

[potsy]

Thanks, I gave up on that before as I was getting a lot of problems with errors relating to data types which are not working with the compact SQL used with MS Sync Framework but will probably revisit the idea as it does seem to be what I want.

[buddylee17]

Have you looked into merge replication? This is exactly what merge replication is designed for.

[potsy]

Have you looked into merge replication? This is exactly what merge replication is designed for.

I shall be re-visiting this idea I think. Using MS Sync Framework just won't work as SQL Server Compact will not meet the requirements.