1000's of locations have a desktop application that need to upload diff's of their db to a central store

Question

Hi, There are literally thousands of locations where an application is running (.net desktop app),and a requirement is to have the updates to their database (diff from the last upload) to be sent to a central server that will store all the locations data.

What options do I have in coming up with a solution?

One idea is to generate an XML file with all the rows since the last sych., and upload that to a file server that will then be imported into the main database.

Note: the changed data will be minimal since this process will run every few hours.

Answer 1

I would probably make an effort to avoid writing this code. It sounds like the kind of problem database replication was designed to solve. It would depend on criteria you don't communicate in your question, such as database engine in use, available transports, whether different locations updates would overlap each other, the design of the database as it relates to keys and unique indexes, etc.

Answer 2

Since there are 1000's of locations I suggest that each desktop application sends an xml string to a web server. The web server application could update the database upon receiving the string.

If you are using .NET on both sides you could implement this using the framework's Web Services classes.

Answer 3

this works like a charm... http://www.sqlteam.com/article/centralized-asynchronous-auditing-across-instances-and-servers-with-service-broker