ISV - our struggle with database selection | Tech Off

ferguslogic

We have a VB6 app that has been out for about 6 years now in productoin and is currently ran by a few thousand users across the United States. This app is downloaded from our website, installed by the user, evaluated and purchased.

I have struggled with database selection for years. On intial release of the application we released the application with an MS Access 2000 backend. We selected this because of xcopy deployment, extremely small footprint and small download size for our setup.   As our application has grown in features and users however more and more larger business want to run it and people often request it on 10-20 systems today.

MS Access just doesn't cut it for 10-20 systems..it corrupts.   Sql express is way, way, way too big and requires all kinds of configuration on the client for MSDTC and networking capabilities and even after all of this some other application can still come along and screw up the sql express install.   I am not a huge fan because of all of this work.

VISTADB is a nice database but even according to their own forums it is 50% slower than MS Access and we need performance.

MYSQL is okay but its not free like people think. Its only free for personal use and we got quoted like $50,000 if we needed a license for each of our customers that already have the system.

Because ISV's like us don't seem to have any great commercial options available for a database that is small and can be installed easily (you would think someone would build something like this but nobody seems to have done so)   am beginning to look hard at open source databases including firebird and postGreSQL.

What do you guys think about firebird or PostGreSQL for use with VB6 and C#.net applications win form based applications. Do these databases seem worse off or better off to you guys than sql server express?

Generic questions...just looking for some feedback and info from people who may have experienced these same Database woes.

jh71283
Throw new System.Beverage. OutOfCoffeeException()

Have you thought about SQL Server Compact Edition

ferguslogic

In reply to jh71283

#Oct 10th @ 3:20 AM

As far as I know sql server ce is for 1 system. We need to support 1 -20 systems. Today we run up to 8 systems simultaneously and it works well We don't go higher than 8 systems because then Access becomes unstable and corrupts more frequently.

Today if a customer has 15 computers we lose the sale because Access can't handle it and we have no db to support them.

figuerres
???

In general I would say to go with SQL Express and SQL standard.

many reasons, one standard format that the data can move up to larger servers, you do not have to then modify any code, it "just works".

and tools, you can use SQL tools to test, tune, debug.

and things like backup, backing up a SQL db is a known thing that can be integrated with 3rd party backup software packages.

I am not sure what you mean that it's "way to big" can fit on a single CD.

I have run SQL Express on plain old pc's and it's still running today.

as for installers well if a 3rd party breaks your stuff that could happen with any 3rd party install

you can take express and (as far as I recall) run it's installer with your own setup and set all the options, just configure your install to run as a named instance and supply your own name for that. If you make sure your instance name is not one that other ISV's will be using then other installs of express should not hose your install.

also I think that if you contact MSFT they have stuff for ISV's that want to re-dist SQL to help with packaging the install.

TadejK
Illuminate me

In my opinion, go with PostgreSQL.
I've used all of the mentioned databases, but they all have their shortcomings -
Firebird is okay if you need embedded db, otherwise it lacks in everything;
SQL Express is great, but if you need to run it on linux, macosx or another non-windows system, you're out of luck; also, SQL Express is limited (it's like a demo) and when you want to (or need to, because of the limitations) upgrade to a 'real' SQL Server, then that costs *big time*;
MySql .. erm, don't even get me started - you probably love your data too much to lose it someday, right? Wink

;
MS Access - you already know of it's shortcomings;
PostgreSQL - ever since I've stumbled upon it, I've been using it for all of my needs, and it hasn't failed me yet - support is also great (professional and via mailing lists). Oh, and talking about backuping - you have several options available, so you're very likely to find one which suits your needs exactly. And if you're wondering - yes, it can be silently installed along side with your own app.

Regards,
Tadej

Papillion

I have to second Tadej suggestion of PostgreSQL. Our company develops medical records software and we were all go go go on MS SQL Server until we started seeing the added costs to our customers. It was very prohibitive for even mid-sized organizations to adopt our solution based on SQL Server.

So we went looking.

When we found PostgreSQL, it was a Godsend. No fees of any kind and no licensing restrictions on distribution. Support, especially for an open source product, is some of the best in the industry and you will never be left wanting when using PostgreSQL.

Good luck!

Anthony Papillion
OpenEMR HQ, Inc.
www.openemrhq.com

figuerres
???

In reply to TadejK

#Oct 14th @ 12:56 AM

Not to argue but I would like to know what in SQL Express you see as limiting it to a "demo" ?

I know that the sql db size is limited to as I recall 4 gigs.

in MSDE there was a connection limit / speed limit but that is *NOT* part of SQL Express.

also if you need to move from express you do not have to spend all that much really, there are SQL Workgroup Ed that cost less than SQL Std Ed.

and IMHO if a business needs reliable Data storage and SQL they should be wiling to spend *something* to do it.

granted I have always heard good comments about PostgreSQL, I have not used it but never heard any bad comments about it. so I can't say it's bad or good.

PerfectPhase
"This is not war, this is pest control!" - Dalek to Cyberman

In reply to figuerres

#Oct 14th @ 2:24 PM

Limitations of SQL Server Express Edition

1. Number of CPUs supported
SQL Server Express uses only one CPU at a time. It can be installed on a server with multiple CPUs, but it will use only one CPU at a time.

2. Maximum memory used
SQL Server Express uses a maximum of 1 GB memory for it's data buffer. So, if your server has severaql GB memeory, SQL Server Express cannot take advantage of it.

3. Database size limit
Maximum database size is limited to 4 GB (log file size is not counted)

4. Job Scheduler
Job Scheduling service is not available with SQL Server Express

That said I have have quite a few sites on SQL Express with no issues. Another thing for us, and this depends on the type of customer you deal with, most of our customers that can't use Express push us to use their existing SQL servers anyway, so no extra cost for us.

jh71283
Throw new System.Beverage. OutOfCoffeeException()

In reply to PerfectPhase

#Oct 14th @ 6:05 PM

and just how many of those points are going to be an issue for an app that currently survives using MS access?

TadejK
Illuminate me

In reply to jh71283

#Oct 15th @ 4:17 AM

You're right .. but, why would you even want to limit yourself?
Nowadays, with multicore processors in every computer, it's not bad if your database can use multiple CPUs.

As I see it, the problem isn't really with these limitations in regards to this specific application, but to the fact that once you start using a database, you're most likely to use it for all of your projects (you already have your code adjusted to that db) - and it's very easy to bump into those limits. For example, let's say you now want to deploy a web app using this db - with several users using it at once, those limits start becoming very low Wink

I'm just trying to say - why limit yourself, if you don't have to? Why buy a car with only 3 seats - what if someday, you'll want to drive a forth person?

Oh, and let's not forget - they're barely surviving using ms access Wink

Regards,
Tadej

PerfectPhase
"This is not war, this is pest control!" - Dalek to Cyberman

In reply to TadejK

#Oct 15th @ 6:01 AM

Out of interest what it the Clustering, Mirroring and Replication support like in PostgreSQL how about equivalents for things like SSRS, SSAS, SSIS etc?

TadejK
Illuminate me

In reply to PerfectPhase

#Oct 15th @ 7:55 AM

You have several options for Clustering, Mirroring and Replication, as can be seen from http://wiki.postgresql.org/wiki/Replication,_Clustering,_and_Connection_Pooling.

I've found an old survey, from which you can see what options are available for PostgreSQL as equivalents (at least to some extent - don't know the product well enough) to SSRS - http://www.postgresql.org/community/survey.43.
SSAS is data mining, if I'm not mistaken - PostModel looks interesting in that field (also provides connection with R Project).
Primary use of SSIS is supposed to be data warehousing. I'm not sure what would come close to that, but you should ask the PostgreSQL mailing list about that .. perhaps the below-mentioned Bizgres.
There is also Bizgres - enhanced PostgreSQL for business - for data warehousing and similar business stuff, but I don't know enough about it to compare it to SSRS, SSAS or SSIS Wink