I have a problem I need some help with.
We have an application that is a desktop MSDE solution (.NET) rolled out to several (potentially hundreds) of rural customers.
We have a requirement to upload this data in some form to a central database server, for reporting and other purposes (the nature of these requirements is a bit vague at this stage). However, the data transfer is strictly one-way - i.e upload to central server only
The client does not want to implement SQL Server replication on the local MSDE database installations as this is perceived to be too hard to maintain.
Each MSDE database is fairly small (<10Mb backup file at a guess)
So my options at the moment seem to be:
(1) Write a web service that uploads the data to the server, and write custom code to insert/update the rows in the master database. I see the main problem here being maintaining integrity - each MSDE database has its own identity keys with associated foreign keys. Handrolling this code could be hard and difficult to maintain, I feel.
(2) Do a normal database backup of each MSDE database, and upload the backup files. restore on the master server and maintain separate Dbs. Downsides here would seem to be less easy to report off, but upside is no special code to write.
(3) Do as in (2) but use SQL merge replication to combine the databases into one.
Has anyone had similar experiences and/or any light to shed on this before I launch down one of these tracks?