February 22, 2012 at 1:58 pm
OK, I'm coming at this very green. I've never used SQL Replication; I've never seen it used; I've never been in a course, seminar, etc, where it was talked about or any training done on it. Whenever I've read about it (and I have read some about it in the past) I got the feeling that it was only for duplicating all data from one SQL server to another. Sort of like what I would do if I were to do a backup on one database and then restore to another database. Frankly, because that's how I understood it, I never seriously considered it.
I've got a need to replace a really old VB6 application with something newer. I've written a WCF service which runs on a server, and was going to replace what I did with an old COM+ object I wrote way back in the day. In testing I've found that I've made some mistakes in how I planned this out; it isn't getting all of the data from the client back to the server. So, I'm looking at re-writing a significant portion of this WCF service. While I'm thinking of this, I'm considering alternatives, and SQL Replication was suggested to me.
So, let me give you the 30-second elevator description of the problem I'm trying to solve. Our primary data store is in 2 SQL Server 2005 databases on our production server. The old VB6 app (and by extension the new WPF app we'll be writing) is used by the user to work with some of the data (not all of it, just what's relevant to the work items the user will be working on). If the user is in-house, they'll get a copy of the work items data they need and work with it there. Those work items are marked as being "locked", and no one else can modify them while "locked". However, this same application can be used in a disconnected way, in which a user downloads their work items and then goes off-site to do their work. They may be gone for a long time; weeks and in some cases months, before returning to upload the data. The old VB6 app used MS Access as the local data store, but with this new version I've chosen to use SQL Server 2008 R2 Express on the laptops. There's only 1 SQL Express database on the laptops, which has the 100 or so tables that comprise what's in the 2 databases on the back-end SQL Server 2005 databases.
Now, I can leave in place the work I've done in my WCF service to lock the data, perform searchs for work items, etc. It is the synchronizing of work item data on the local machine (desktop or laptop) and the back-end database that I'd like to know, can SQL Replication, solve this problem? Will it work between SQL Server 2005 Standard Edition on the back-end server, and the users' local SQL 2008 R2 Express database?
Kindest Regards, Rod Connect with me on LinkedIn.
March 22, 2012 at 12:52 pm
We use merge replication in a similar scenario. What we have seen is that the publisher must be at an equal or higher version of SQL to work correctly. As a result, we continue to install SQL 2005 Express on the subscriber machines. If anyone knows of a workaround to allow 2008 Express for subscribers while maintaining 2005 for the publisher, I'd be interested to see how it is done as well.
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply