Tech Off Thread

12 posts

Forum Read Only

This forum has been made read only by the site admins. No new threads or comments can be added.

ISV - our struggle with database selection

Back to Forum: Tech Off
  • User profile image
    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.



  • User profile image
    jh71283

    Have you thought about SQL Server Compact Edition

  • User profile image
    ferguslogic

    jh71283 said:
    Have you thought about SQL Server Compact Edition
    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.

  • User profile image
    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.

     

  • User profile image
    TadejK

    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

  • User profile image
    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

  • User profile image
    figuerres

    TadejK said:
    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

    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.

  • User profile image
    PerfectPhase

    figuerres said:
    TadejK said:
    *snip*

    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.

    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.

  • User profile image
    jh71283

    PerfectPhase said:
    figuerres said:
    *snip*
    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.
    and just how many of those points are going to be an issue for an app that currently survives using MS access?

  • User profile image
    TadejK

    jh71283 said:
    PerfectPhase said:
    *snip*
    and just how many of those points are going to be an issue for an app that currently survives using MS access?
    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

  • User profile image
    PerfectPhase

    TadejK said:
    jh71283 said:
    *snip*
    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

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

  • User profile image
    TadejK

    PerfectPhase said:
    TadejK said:
    *snip*

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

    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

    Regards,
    Tadej

Conversation locked

This conversation has been locked by the site admins. No new comments can be made.