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
Peter
Anyone????!?!?
Crickets
Edit
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.
If you can enable the TCP Protocol on the SQL Express Editions and add a user account to the remote stations, you could use Job Agent on the main SQL box to pull down all of the data. Maybe backup and restore (which seems like a bad idea given limited bandwidth). You could use triggers on the remotes and push the updates to the main site, but I am not sure how that would work over a "lossy" VPN connection. Just getting the SQL boxes to see each other will probably be the hardest part. Once you do that, it opens up lots of different options.
Info to enable TCP on SQL Express 2005: http://blogs.msdn.com/b/sqlexpress/archive/2005/05/05/415084.aspx
We do something similar with the HMI on some industrial equipment we have, but the connections are all over the local network. Our DBA wrote a few scripts to grab what we needed using "insert into" and leaves a copy on the main SQL box.