0

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.

1 Answer 1

0
+50

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.

4
  • I'd say that this would be one of the better custom approaches if I had to roll my own. I ended up looking at linked servers and I think I can write T-SQL that does retries for data.
    – Peter M
    Aug 3, 2011 at 21:02
  • That is what we ended up doing. The good part is that you can select data from remote tables and do a select into into the local tables. Which is nice because then you are only moving over the missing data and not the whole table.. And of course, once you work out a stored proc that works, just dump it in agent and let it have at it..
    – MikeAWood
    Aug 3, 2011 at 21:21
  • I'll give it to you for making mer think again about a custom solution.
    – Peter M
    Aug 8, 2011 at 16:58
  • The good part is that its flexible enough and if the data structure is simple it should be a piece of cake. Now if its complicated, it might be worth going to standard as your time might be more expensive than just buying the MSSQL licenses and be done with it.. Thanks for the answer points and good luck!
    – MikeAWood
    Aug 8, 2011 at 21:01

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .