That is my recommendation - either use merge replication between the databases, or create a sync agent that runs on the client and calls a set of services on the server (which really means you are writing your own merge replication in a sense).
The thing to recognize up front is that you are essentially writing or creating 3 different applications:
Recognizing that you have these three, essentially separate, applications to deal with is (I think) the critical step to success.
I must have given my client some bad advice. I followed SerratedBobCat's advice to look into the dataset diffgram (my client isn't using CSLA and that wasn't an option). My boss came by and started drawing something similar to what you suggest on the white board... MSDE/SQL Server Express on the laptop with mirrored schema... and some form of resync back with the base server dbms. My initial thought was that the dataset surely offers some assistance in this area, although I had not heard of diffgrams. SerratedBobCat pointed me in the diffgram direction, and I built a demo disconnected application (which I will attach). The demo offers two paths... 1) saving the intermittent disconned session saves (diffgrams) to file OR 2) to a blob. In production the laptop user would "check out" the data they would work with offline (not thrilled about manual locking schemes, but if you are careful, it can be done in some circumstances). Then the user can iteratively (retrieve from disk/blob... save back to disk/blob) disconnected until a point they are read to check back in to the mother ship dbms. So the diffgram provides both the disconnected mainteance and the input to update via dataset back to the base dbms. It certainly seems infinitely easier than resyncing two seperate schema UNLESS I'm missing something, which is entirely possible (I only created the proof of concept demo, and haven't put this into production). Also, one might have certain requirements ... like laptop disconnected audits of some sort which would require a dbms, and then you would have to go the resyncing route. I'm here to learn... so if there are pitfalls to know about with the dataset diffgrams, let me know... I'm likely to be the one to develop that disconnected feature in our application at a later date.
I'm attaching the demo for anyone that is interested. I was new to memory and filestreams, so I don't claim the code is efficient. The real point of the demo is a proof of concept on disconnected mainteance using diffgrams. The sln is a VS 2003 app (that's what the client is using on this application). There is a readme in the database project folder that explains how to get up and running... you just need two VERY simple tables. Also note, there is connection string literals at the top of form to set once you have setup your two tables.
I will check in later and give more feedback if anyone is interested in the app. Also, everyone please give any feedback on the sidebar issues... like maybe better coding techniques for going from dbms to winforms and vice versa. Same for files.
Oops... where is the attach option? I will come back and do this... have to run.
Can we attach file here? I'm not seeing it. I was hoping for a reserved non-scrolling place in this new forum for posting sample code, articles, sample/demo applications, CSLA design tips, etc. If that's not a possibility, I was going to suggest we just create a post in this new forum titled something like "Demo apps and Sample code"... and we could all just post that type of sharing in the same thread. We would all get used to searching for that individual thread. I found the old forum frustrating because you knew there was a wealth of knowledge there... but it would be very easy to miss it by relying on search. For example... I saw Andres posted a great tip about Winforms and generics... and that will just disappear in the CSLA vapors.
Anyway... I posted the "Offline Maintenance" app I mentioned above on the old forum here. I ended up using the term "Offline" mainteance rather than disconnected because aren't all n-tier apps disconnected (other than winforms non-remoted traditional client server).
So back to the discussion.
First prc... man I would certainly hope you had the option to provide some form of "locking at checkout" on the base dbms. Writing one of those home grown replication/merge things seems like a potential maintenance nightmare. You sure would want to make sure there is enough $ upside to warrant the "non-locking plus multiple merge mini-dbms engine" object. ![Big Smile [:D]](/emoticons/emotion-2.gif)
Now... still playing devil's advocate... why would folks pick a mirrored laptop schema / merge path rather than the dataset diffgram. The idea would be the entire table structure and it's relationship would be housed in the diffgram... and that's the basis for the updates back to the base dbms. This seems infinitely better than laptop dbms intallation, schema updates mirrored from base dbms, etc. There would obviously be some applications that would require more functionality (user CRUD audits captured on the laptop.. i.e. audits beyond the diffgram table relationships), and that might require one to move beyond the diffgram. Even then... couldn't one include those laptop user offline audit table requirements to the base dbms, and include the audit tables in the check out? Save the diffgrams to file... or to a simple row blob column (yeah... you would then have to install the MSDE or SQL Server Express.. but a simple table definition ... don't have to keep up with dual schema maintenance).
Now if you were a CSLA shop... why wouldn't you use the CSLA BO as the diffgram equivalent... and serialize the BO (and it's associate object graph) to file or to dbms blob. Again, it would seem infinitely easier to apply the offline BO saved state back to the base dbms... with the built in CSLA plumbing rather than try and do a dbms->dbms resync/merge.
Interesting ... this seems to be a fairly common application need out there.
btw... if anyone actually tries to run the app I posted on the old forum and has trouble, let me know. It's pretty simple ... just start with the readme in the dbms project create script folder.
SerratedBobCat... are you out there. Chime in on your diffgram usage.
SeratedBobCat to pelinville. I'm going to have to start a xref. :)
Hey... thanks for the long post. You raised some interesting points... including the size of the datasets/diffgrams. In my company's case, I believe the row counts would not be an issue. What might be an issue is any limit that is reached on tables/relationships in A dataset... and like you said, you could always split them up. I tested a very simple scenario (one table)... and I knew I would need to further test the diffgram updates when they housed relationships (i.e. parent - child). In theory... it's all just table updates, so it should work fine.
Note: my choice was dataset + dataset widgets OR laptop dbms + merge back to base dbms. CSLA was not an option. Would you agree you could do the exact same thing with CSLA BOs that you did with diffgrams? It sounds like you were saving the diffgrams to disk... i.e. not to laptop dbms. Actually, the CSLA undo feature seems like it might serve many offline needs very well... i.e. offline setup, profile creation, etc...
In the n-tier world where most apps are disconnected... the plumbing is pretty much the same whether you update that disconnected data in 5 minutes or 2 days (with some intermittent BO saving to disk).
Rocky's comments tally with my own experiences. We have just released an application where winform clients run against an MSDE database and synchronise via a web service with an Oracle DB. User's had to be able to edit records on either DB and the winform client can also run against the Oracle DB when suitable connections are available.
Because of the difference in DB platforms, we ended up writing our own replication system. Don't underestimate the work involved in doing this!
The biggest problems occurr when there are business rules that span related tables, e.g. a value in table A determines what valid values are in table B. What happens if someone edits the value in table A on one database whilst at the same time, someone else is editing values in table B? If you aren't careful, whilst synchronisation may not break any database constraints, business rules may be violated. Do you implement business rules in your database with triggers, etc.? Or do you restrict what the user can do so that you reduce the risk of rules being broken? I don't think there's an easy answer.
I don't want to put you off - we're just starting on a new application with a similar architecture so it hasn't deterred me ![Wink [;)]](/emoticons/emotion-5.gif)
- but be aware of the pitfalls
cheers
Paul
prc...
Question: why MSDE and not SQL Server Express. My client had MSDE in the specs also... but planned on approaching the client with a change to SQL Server Express. Of course we had the luxury of starting from scratch... you may not.
We started this two years ago so SQL Server Express wasn't an option then. I would go with it now though
If you are not an expert on database replication or your organization doesn't have an expert DBA doing replication, I suggest you create your own replication as what Rocky has suggested.
Creating your own merge replication does have a cost in development but in my own opinion you I prefer that way than going to DB replication and you don't have experience in doing it.
ajj3085:
One more thing for Rocky..
Are those three bullet points only if I go an write my own merge manager, or did you mean they apply regardless of which route I go?
Thanks
Andy
I mean they apply regardless. The only difference is that your sync "app" might be a database replication technology instead of an actual application. But even in that case, you will likely end up writing some sort of code to deal with data collisions that can't be resolved automatically - and that is part of the sync app.
Hi,
Just wondering why no-one is mentioning the soon available "SQL Server Everywhere", which is in fact "SQL Mobile", but also useable on the desktop versions of Windows. It's lightweight and should both support clickonce deployment and merge replication. Have a look at http://blogs.msdn.com/stevelasker/archive/2006/04/10/SqlEverywhereInfo.aspx . To me this looks as the perfect solution for disconnected cases, with clients running on Windows based laptops. Looking forward to your opinions / comments.
Regards,
Jan
It would seem the design needs of this topic "disconnected / offline mode" would be dependent on the scope of the "offline needs". If the entire application needs to be available disconnected/offline, then one would lean toward replicating (and double maintaining) the entire schema on the disconnected clients. However, if you were talking about a narrow scope of the application required in disconnected mode, which could be provided by division of labor/data in a check out fashion (i.e. lock down the data that was checked out) and simple disconnected diffgram management (or disconnected CSLA BO management) on the client PCs, then signing up to maintain schemas and a dbms on all of those client machines seems like a maintenance mistake/overkill... even if tools like SQL Server Everywhere make it easier (haven't read the link yet). Of course, there are tons of grey between those two extremes... and many ways to skin the cat.
Jan,
Hey, thanks for the SQL Server Everywhere (SQL/e) link... I just read it. That's some very good information. Jeeze... you are reminded why Rocky says code defensively against the Microsoft data connection technology and widgets (sounds like SQL/e isn't ADO.NET... at least not yet). Also important to note that SQL/e doesn't support procs or procedures... so you sure would want to know that ahead of time in your design. Sounds like they aren't planning on XCOPY deployment... ?? Anyway, good information. SQL/e and SQL Server Express are two different products... and I guess for two different purposes (don't know about that one, seems like we only needed one client side dbms rather than two). The current project I'm on that will need some disconnected functionality has mainframe zOS DB2 7.1.2 as it's dbms. We refer to it as the MF database... and MF doesn't stand for mainframe if you get my drift. :) I don't think we will be automagically merging SQL/e data to mainframe zOS anytime soon. :)
The following is from the link... can anyone explain what this means.
Q: Can I use SQL/e data files as my application document format?
A: Yes, SQL/e data files are pure data. Unlike the server SKU’s, SQL/e doesn’t have any code. No sprocs, views, triggers, extended sprocs, macros or ability to run XP_CmdShell. Because of this, we do consider SQL/e files a doc safe format.
I just found this, after having posted a new similar thread.
I'm still torn between SQL Repl and my own custom code. I understand the 3 app approach. I wrote a bastardized CSLA hack a while back to do this whole thing in one app, and it got very ugly. I manually configured remoting on the fly.
What I'm wondering is this: How would I best load CSLA BO's from the local DB, then use the remote DataPortal to save them to the Server?
Thanks,
Kevin
Thanks all for a very useful discussion!
Jan, thanks for the SQL Everywhere link. Very interesting stuff! Just one side note. SQL Express supports merge replication ONLY as a subscriber. What it means is that only the changes from the corporate side (assuming that corporate db is hosted by SQL Workgroup/Standard/Enterprise) can be merged into the client's db that is hosted by Express :(
See here for details: http://www.microsoft.com/sql/prodinfo/features/compare-features.mspx
IMHO that was a very disconcerting change from MSDE 2000 that allowed (with certain limitations) Distribution/Publishing as well as Subscription modes. See here:
http://support.microsoft.com/default.aspx?scid=kb;en-us;Q324992
This is actually one of the reasons our company considers using MSDE 2000 on clients instead of Express. We've had for years an optional (custom) sync module for our main app that we called "Laptop sync". This module allows agents on field do their job and bring it back to the main office to sync with the main db. I can tell you from the experience that the module has always been somewhat difficult piece of code to maintain. We are moving towards database replication for better maintainability. SQL Everywhere will be a great thing if - along with other good things - it allows merge replication as a Distributor/Publisher. :)
Just found this on the topic of SQL Everywhere CTP:
Looks very good to me.
Plain: