May 5, 2004 at 10:40 pm
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?
Regards
Andy Scrase
May 7, 2004 at 11:45 am
I prefer option 1 with the following suggestions
give each machine an ID
in your master database tables include the following
machineid column
identity column
update_date
that allows you to know immediately if you have imported a record already and if you need to you can compare them and see when it was last changed and if it needs to be re-merged.
yes it is roll your own, but sometimes the off the shelf stuff just doesn't work in the real world
Michael R. Schmidt
Developer
May 7, 2004 at 12:48 pm
this is a textbook case for merge replication. see bol, "Filtering Published Data" and "Dynamic Filters". the real issue is not getting the upload . . . it's preventing the whole db from being downloaded to the subscriber. as i always caution, replication (merge in particular) takes discipline and attention to detail; and close cooperation with the db and app developers. but, replication does work great!
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply