Tools for Building an OCA (Occasionally Connected Application)

Question

I will be building an in-house, Occasionally Connected App (OCA). What technologies would you suggest I employ.

Here are my parameters:

1. .NET Shop(3.5sp1)
2. C# for code behind (winform,wpf,silverlight)
3. SQL Server Backend (2005 or possibly 2008 pending approval)
4. Solo Developer
5. Solo SQL Administrator
6. Low Tech end users
7. Low bandwidth to 5 Branch offices
8. This is a LOB app but not a POS.
9. Majority of users have laptops that they take to Member's Home
10. The Data for this App is stored in 5 separate Databases, though in one SQL instance.

I am looking for specific recommendations on which path to choose. Merge Replication or Sync Framework database synchronization providers? SQL Express or SQL CE at the Subscriber? Can I use LINQ to SQL for the DAL?

Is a Silverlight 'Offline/Out of Browser App' Example Here, feasible?

This is my first LARGE business application so any experienced comments are welcome.

---

As requested here is some additional info on the type of Data. My users are Nurses and Social Workers who go to Member's homes and create "Plans" or "Health Assessment Reviews" for them. These are things like a Medication List or a List of there current "Providers". Steps to achieve members' goals or a list of there current/past Diagnosis's. Things like that.

Also the typical Members Name, Address, Phone Number, etc. Mostly this is a Data Storage and Retrieval app that facilitates reporting. Very little "processing" takes place and Nurses and Social Workers work in teams that are assigned members so I usually have very little crossover or potential data conflicts. Nurses and SW's also are responsible for different area's of the MCP(Member Centered Plan)

---

Additional question; Is Sync Framework really only a viable option if I can use SQL 2008? Seems that way due to the Change Tracking etc....thoughts?

Overblown

Please share some info regarding what kind of data you'll be working with. Specifically, what kind of volume and churn are you expecting in each direction (between a user and the server)? – Trivalent

opps, missed the volume part. We have 300 employees and 5000 members. They tend to work on multiple "plans" a day. – Overblown

@RefractedPaladin Did you manage to do this in the end? I'm looking at making a similar program. Any pointers? – Cleodell

@Cleodell Yes though it doesn't much resemble my post....Winforms frontend, SQL 2008R2 backend, L2S, and Merge Replication for my Sync Tech. Advice would be to tread carefully for Replication. Merge Repl is simple to use/setup but it ties you down. If I could do it over again I would finish developing the app, to a reasonable point, before jumping in with Replication. God Speed.... – Overblown

Answer 1

Once you solve the problem of change detection and data movement, everything else is trivial. In other words technologies like WPF, Silverlight, Forms and even WCF are orthogonal to your main problem and your choice should be based on your personal preferences and experience. The real hard nut to crack is working disconnected and synchronizing changes. Which leaves two out-of-the-box avenues: Synch Framework or Replication.

I would say, for your scenario, definetely Synch Framework. Merge replication, like all forms of replication, is designed for systems that are connected continously with intermitent disconnects. And most critically replication can work only over static names. Laptops connecting from various hot-spots and ISPs have a nasty habit of changing FQ names with each connection. Replication can overcome this only if a VPN of sort is used and VPN is usually a major support issue. Replication is just not designed for the high mobility of OCA systems.

Synch Framework will pretty much force you to SQL 2008 back end because of the need to Change Data Capture or Change Tracking, both being SQL 2008 only features.

You will still have plenty of hard problems to solve ahead (authentication, versioning and upgrade, data conflict resolution policies, securing data on the client for accidental media loss etc etc)

Prothrombin

Possibly a stupid question but with Sync Framework I will still be able to use Linq To Sql as my DAL? – Overblown

The typical Sync Framework client is SQL Server CE and LINQ2SQL is only for the 'true' SQL Server (ie. not CE). You can use linq to EF instead. You can also opt to deploy SQL Express on the clients and that will work with both Synch Framework and linq to sql. In your shoes, I would choose linq 2 Entity Framework and SQL Server CE on the clients. Of course, I don't know all your requirements. – Prothrombin

I don't mind the Linq to EF part especially when weighed against the extra 'bloat' of Express vs CE. On a different point; Does it make a difference that they only need to be able to connect when at the Office. Not in the field. There are already VPN's existing to each branch from central as well. Basically they just take the Data with them to the member. Make there changes and then re-sync when they get back to the office. Does this change any of your reccomendation? – Overblown

With Office only you get stability of the names involved and Replication becomes more viable. But keep in mind that replication relies on SQL Agent jobs to run the distribution/subcription agents, and this require at least a SQL Server Standard Edition, which measn either you have all agents runnig at the distributor (push to each laptop, with hundreds of jobs continously trying to connect to the a laptop each) or deploy SQL Standarad on each laptop. Also there are restriction imposed around SQL Express (can be only a replication subscriber, never a publisher). – Prothrombin

Answer 2

Personally, I would say:

• .NET 3.5
• WCF Data Services (for communication between the client app and your data)
• SQL Server 2k5/2k8 (whichever you can use)
• Silverlight w/ Out of Browser Functionality
• VistaDB (to store data locally on the client until you can push to the server)

Undistinguished

I could recommend just SQL Micro Edition with SyncFramework instead of VistaDB as OCA client-server interaction – Limbate

@Limbate Is 'Micro Edition' the same as Compact Edition? – Overblown

@Refracted Paladin: Yea, sorry, my fall. Indeed CE, not ME – Limbate

Answer 3

use unique-identifier for key if you are creating stuff while offline and not connected and when you do connect, updating the database.

this is going to be way easier than using auto-increment key

Invercargill

yup thanks. That is one I already had covered but an important one I am gathering. – Overblown

Answer 4

Having worked on an occasionally connected application, I'd encourage you to look in to SQL Server CE for the client machines, with Sync Services to handle the connections. Here is a good tutorial.

Flagstad

Answer 5

You could create this stuff from the ground up, it seems.

However, this seems an awful lot like a CRM application, and it wouldn't surprise me if you could find an enterprise software package to do this without starting from scratch and instead modify one of the configurations to meet your business rules.

In a previous life, I was a configuration developer for this thing called Siebel that might be close to what your'e looking for. They even have a built-in synchronization tool called Siebel Remote.

It might be a cheaper route to go than rolling your own from scratch.

Colp

Answer 6

I wrote an order taking program for wine sales reps. Here is the video. The client software is installed using click-once. That also installs SQL Server Express and loads the database. I used the Microsoft Sync Framework to sync the local database with the one on the server (see the last section of the video.)

• With powerful clients now I don't see any reason to not use SQL Server Express, it is free with a limit of 4GB.

• SQL CE had too many limitations - no stored procs being a major one.

• You will need to use GUIDs everywhere as the primary key - see the new NewSequentialID().

• I love click-once, it is a big time saver.

I'm looking forward to Silverlight, but just haven't had time to look into it. Not sure if I would have done it with Silverlight if doing it now or not.

Having said all this, this is not a project for anyone inexperienced. So I would also get some very experienced help.

Shemikashemite