OT Advice and suggestions needed

[NickFriend]

Hi all,
This is completely off topic, but I’d be interested in some feedback on something I’m investigating at the moment. I’m involved in a large project completely rewriting an old VO program for .Net. I’m afraid it’s all in C# because we started before X-Sharp was available and decided Vulcan would never work for us, but the issues are not language dependent.

The product is aimed at specialist vehicle manufacturers and has two parts. One is for the manufacturers to use to create a database of custom parts information with all the technical info, cost analysis, end user pricing etc. This will run on the corporate LAN or web server and uses Microsoft SQL Server as a back end. This is more or less complete and working very well.

I’m now moving to part 2. This is a smaller application for dealers and sales reps to use to produce quotes for customers. Basically the manufacturer publishes a restricted set of data (stripping out confidential internal info, cost analysis etc.) which can then be loaded into the quoting version as a read-only database of info to use to create the quote.

The quoting program could be used by a whole variety of clients, from a single independent sales rep with his laptop, up to a large dealership with web server and dozens of sellers. And IT ability will range from zero to a full IT support department.

There is an additional complication, which is that in larger organizations the sales rep may have a laptop connected to the company web server, but then disconnects and needs to take data with him on the road without Internet access to work directly with clients. So in this case we have to copy data to the local machine and the local machine will need to have the full database access component of the program on it together with the DBMS.

So I don’t want to use SQL Server as it can be a pig to install and maintain sometimes, and is too large and cumbersome for the smaller clients (who are the majority).

I’d love to know:
- if anyone else has dealt with this type of scenario, in which case I’d be very interested to hear experiences.
- recommendations for a robust, easy to install and maintain Sql DBMS that would be suitable for this range of clients.

Some of the manufacturers data sets can get quite large (1-2Gb) with lots of images, technical docs in PDF format, etc. (which are stored as binary data in the db).

Any thoughts or observations gratefully received.

Nick

[SHirsch]

Hi Nick,

I suggest AdvantageDatabaseServer. You can use local server on single user environments and full server on multi user environments.
We have customers with separated sites that have no connection to their main servers.The server pushes new or changed data to external web space (or ftp). The client pulls the data when internet connection is available. The same happens with data that is created by the client.

Regards,
Stefan

[FFF]

I'd go Postgres. Zero cost, runs on a 5 year old laptop, install a breeze.
Karl

[lumberjack]

Hi Nick,
As Karl stated I tend to agree with PostgreSQL, however in an offline scenario for "Static" data it might be an overkill. SqlLite has all the features of SqlServer even with compatibility and is basically already available on most of today's Win machines which from a support perspective might be a good bet. It does have a plug-in/embedding option so you need not worry about server installations etc...

[wriedmann]

Hi Nick,
as Johan suggested: for a local copy of a database I would use SQLite, but it is a strict single machine database.
I'm using it mostly for configuration data, but also to store data document like. In my Door Configurator, every order is stored in a single SQLite database and can be easily replicated to other systems through any synchronization service like DropBox or others.
I'm using a HTTP connection to a central server where all orders are stored as single .db documents.
Wolfgang

[NickFriend]

HI Stefan,

I used a very similar technique on another program I did, and in fact started initially using Advantage replication to do it. That proved to be a disaster (replication stops dead for any number of problems). So we designed our own push-pull system through web servers, very much as you describe. However it's still the sort of system that needs constant, if intermittent tech support, which we want to avoid.

I'm reluctant to use Advantage again, for two reasons - cost (of the full server version), but mainly because who knows where it's going and how long it will last now. It was great when there were people like Joachim there, but now...?

Nick

[NickFriend]

Karl, Johan,

PostgreSQL is definitely on the watch list.... Has anyone used it with Entity Framework? Up to now we've use EF for all our database access, and it would be nice to continue as we have a lot of generic db access code. I know there's an EF provider, but has anyone got any experience with it?

Nick

[MathiasHakansson]

Hi Nick,

how certain are you that they really want to work offline?

My company sells an estimating software for the construction, plumbing, electrical, CE and electrical power industry in Sweden. In the beginning (2006-2010) they wanted the possibility to work offline so we made a solution when you can check out and lock an estimation from the central sql-server and store it on you local sql-server.
In the last 5 years or so nobody is using this feature. They all connect to remote desktops through vpn instead.

The advantages are obvious when it comes to backup and data security (estimations for big contracts are secret), and if the estimator is ill one day his collegue can easily finish the estimation.

For us, the customers wishes/demands has gone in the opposite direction. They wanted to be able to work with several users in the same estimation where each user lock a smaller part of the estimation (a group, like a folder), and now they can do that.

/MathiasH

[NickFriend]

Hi Wolfgang,

This is very interesting because the old system I'm replacing does something very similar with little groups of DBF files to hold the data for each quote, which are then zipped up into single files. So quotes can be moved around by copying or emailing these files. We had already discussed the possibility of doing something similar with SQLLite as you describe, but haven't actually investigated in any detail yet.

In the discussions we've had, I've actually argued against this! My first thought is that having data outside the control of the main database in this way is a recipe for synchronisation problems, multiple copies of files, people deleting files in Windows, etc. So I've argued for simply including all the quote info inside the main db (in PostgreSQL maybe?) and then having a 'checkout and export' option to pass quotes to the stand-alone programs (perhaps using SQLLite) when necessary.

Trouble then is we end up working with SQL Server for one program, PostgreSQL for another and SQLLite for another, which is potentially a pain... But again if we can do all through Entity Framework we can abstract out the db access.

I'm guessing you've been through all this sort of analysis. I'd love to hear why you settled on the individual file solution in the end,

Nick

[NickFriend]

Hi Mathias,

I have argued exactly this, and it would remove most of the problems in one go. Unfortunately the folks who actually deal with the customers are very insistent on this - users may need to go to pretty remote locations and sit down with a client to fine tune a quote, or they may be on an airplane and want to take data to work on.

I think everyone should relax a bit and have some down time, but that idea doesn't seem to be convincing anyone yet!

Nick