Roll your own replication

  • 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?



    Andy Scrase








  • 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


    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

  • 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