I have a client that has several geographically dispersed installations of a 3rd party commercial product that logs data to their own instances of an SQL Express database (GE's CIMPLICITY HMI if you are interested). He now wants to collect the logging data to central DB for long term storage (IE the local installations will keep a 3 month rolling log of their own data, but the central location will keep a 5 year rolling log of all data. And total data generation is probably less than 100MB per month). The table structures will be kept the same in both the remote and local DBs so that the same/similar reports can easily be run on al the data.
If these were full SQL installations then I believe that I could have used publication replication to solve the problem. But I am dealing with Express editions on the remote ends, (though I will have a full edition to play with at the central location).
So I am looking for how best to simulate publication replication on an Express DB using 3rd party tools (and not having to bang out custom code of my own). To balance the low data throughput, 3 of the installations are actually connect back home via a VPN running over DSL, so there is an expectation of link drop outs. This isn't seen as a big problem as long as the remote data eventually gets centrally logged.
(One possible solution is to convert all the express editions to full editions, but I believe that would be $$ prohibitive, and would be an administration headache to boot. So I am not even considering that at the moment)
Thanks for your help
I've decided to go with a custom code solution that uses MSMQ to perform queuing on the remote side. I probably could have used SSIS to do the same thing, but for me it was jut as easy to roll my own.