The Design of Software (CLOSED)

A public forum for discussing the design of software, from the user interface to the code architecture. Now closed.

The "Design of Software" discussion group has been merged with the main Joel on Software discussion group.

The archives will remain online indefinitely.

Need help picking a database

I need some suggestions on picking an SQL database for a server product. Or, at least, some ideas on how serious some of my concerns actually are in practise.

Here's the list of requirements:

It must run on Windows. Linux support would be great, but Windows-only is perfectly acceptable. (Will be at least Win2000, definately not 9x because that would be stupid, and not breaking if Vista gets installed would be important.) Unless I'm much mistaken, MSDE and earlier products are not likely to be good/available options now.

It must support partition tables. Perhaps. A large amount of data will be accumulated, but usually only recent entries, or entries made within a timespan of a week or two of a point of interest, will be relevant at any one time. Good performance of that situation is definately important.

MySQL and anything with similar licensing issues is right out, unless it's otherwise wildly superior, and not insanely priced.

SQL Server Express 2005 would be just great, but the dependancy on .NET 2.0 combined with the requirement to uninstall other versions of .NET makes me feel uncomfortable about installing it on a customer's server. Forcing an upgrade seems to risk disrupting other services, and I'ld rather not make them choose what to abandon. I'm pretty much assuming that SQL Server support will, eventually, be necessary and thus would be a step in the right direction.

PostgreSQL seems fine, and doesn't have horrible dependancy issues, but I'm willing to bet that Windows server admins will be less familier with it. Causing unnecessary stress for the customer's server admin seems counter-productive.

Unless Oracle has something similar to SQL Server Express 2005 without the .NET issue it's not an option - less experience with Oracle than anything else on the list, and I have yet to hear that it's remarkably superior.

SQLite is probably ideal in many ways (trivial installation, license isn't a problem) but it lacks one important feature - partition tables supported by SQL Server and PostgreSQL. And the whole storing everything as ASCII text feels a little off, even though I suspect it's not a huge problem. But being able to embed the database in my application and simply store data in a file, with the benefit of SQL queries and transaction support, appeals.

I'm not experienced with ODBC - is it likely to give true database independance (thus allowing the actual issue of installing a database to be put onto someone else) or is that over-hyped? And, in any case, it would still leave the issue of what actually database should be installed, and doesn't seem like making a decision. Having a generic database interface then being left with no database installed wouldn't help.


SQL Server Express 2005 and PostgreSQL are the leading candidates at the moment, with the dependancy on .NET being the main reason that SQL Server Express hasn't already won the argument.

Anyone have any tips or comments?  Is there any other sensible option I haven't listed here?

Wednesday, November 22, 2006
 
 
Oracle does have something similar to SQL Express without the .Net issues:

http://www.oracle.com/technology/products/database/xe/index.html

Next rung up the ladder:

http://www.oracle.com/database/Std_One.html

One benefit of Oracle is that there is a ton of documentation.


Another option is IBM's Express-C:

http://www-306.ibm.com/software/data/db2/express/download.html

Next rung:

http://www-306.ibm.com/software/data/db2/9/edition-express.html



One more - Sybase SQL Anywhere:

http://www.sybase.com:80/products/mobilesolutions/sqlanywhere
*myName
Wednesday, November 22, 2006
 
 
Aside from Sybase SQL Anywhere. Sybase Adaptive Server Enterprise (ASE). http://www.sybase.com/products/databasemanagement/adaptiveserverenterprise
j2e
Thursday, November 23, 2006
 
 
Oracle Express is great from a functionality standpoint. However, it does use a lot of resources, so that might be an issue.
Fritz Send private email
Thursday, November 23, 2006
 
 
"SQL Server Express 2005 would be just great, but the dependancy on .NET 2.0 combined with the requirement to uninstall other versions of .NET"

I don't believe that there is a requirement to uninstall other versions of .NET in this case. Do you have a reference to where it states that other versions of .NET have to be uninstalled if SQL Server Express 2005 is installed?
Mike
Thursday, November 23, 2006
 
 
The Best choice would seem Oracle Express, but be aware that system load will grow high. It has perhaps the largest DBMS footprint on a system.

Let away mySQL and Postgress, there are few admins who are really good at administering them and they generally smell bad to win people.

SQL lite is quite a toy, and I do not know about sybase.

If MS Access is underperformig to you, what about FoxPro? Quite cheap, feew administration task, good performances.
Sevenoaks Send private email
Thursday, November 23, 2006
 
 
Before choosing, take a look at Firebird (an open source version of Borland InterBase). It's a very capable RDBMS, has a small footprint and runs on Windows or Linux.
K Renaud
Thursday, November 23, 2006
 
 
SQL2005 does not make you uninstall previous .Net versions. Unless you have .net 2 beta versions sitting around that is.
Oracle is pig to install on Windows unless the admin understands unix concepts. TNS Settings anyone? Which listener do you want it on -listener what the &*((^
FoxPro is free and does have good performance, but it is a file based database not a client server one. I.e. you run a query, it all goes off in the memory space of the client, not the server.
I would recommend (and I have been developing database software for the last 20 years) SQL2005. You have the express version for free (at least I think it's still free) and you can scale the product up substantially if the need arises. Plus you can install your own instance as part of the install without any admin involvement
Nigel Ainscoe Send private email
Thursday, November 23, 2006
 
 
Oh, the other thing thing I forgot Oracle download 160MB, IBM download 320MB SQL Server Express 58MB, although as a developer you probably want to download all the Samples, Books Online etc. which will increase the size.

 http://msdn.microsoft.com/vstudio/express/sql/download/
Nigel Ainscoe Send private email
Thursday, November 23, 2006
 
 
If you're going to look at Oracle XE then take time to try some of the supplied Application Express (Apex) functionality. Getting involved in that looks like a great business opportunity.
David Aldridge Send private email
Thursday, November 23, 2006
 
 
I can vouch for Firebird. I used Interbase 5 years ago (Firebird now) and found it to be strong, easy to administer and never had a headache once. We had up to 50 simultaneous users running very heavy queries, sprocs with cursors, very acceptable performance. The server was no big stuff either. I think at the time it was a 200 Mhz with 512 RAM. Small footprint, very fast.
I still code in Delphi
Thursday, November 23, 2006
 
 
"FoxPro is free and does have good performance, but it is a file based database not a client server one. I.e. you run a query, it all goes off in the memory space of the client, not the server."

FoxPro is not free.  You can distribute as many copies of the runtime as you wish, but the development version costs.

It is a midlevel DBMS.  I use it.  It does not have everything, but it may have enough.  It can access data in SQL Server and others' databases.

Sincerely,

Gene Wirchenko
Gene Wirchenko Send private email
Thursday, November 23, 2006
 
 
I'm surprised nobody has mentioned VistaDB. They just released v3.0. It has at least a couple modes. One mode is file-based like MS Access, and the other mode is server like the other larger databases. I believe it comes with a 2 license version of the server product when you buy it. It runs on both Windows and Linux (through Mono).

http://www.vistadb.net/

Firebird also has an embedded version of the database

Description
http://www.firebirdsql.org/manual/fbmetasecur-embedded.html
http://www.firebirdsql.org/manual/ufb-cs-embedded.html

Downloads
http://www.ibphoenix.com/main.nfs?a=ibphoenix&page=ibp_download_20
Hector Sosa, Jr Send private email
Thursday, November 23, 2006
 
 
As others have pointed out SQL Server Express does not require you to uninstall previous versions of the .NET framework.  Side-by-side installations are one of the selling points of the .NET framework.

I have 1.1, 2.0, 3.0 and SQL Server Express installed on my main machine without issues.
Posted by me
Thursday, November 23, 2006
 
 
You beat me to this thread Hector :)

VistaDB 3.0 has been completely redesigned and redeveloped from scratch with fully managed and typesafe C#. Unlike our 2.x version (which was developed in Delphi), 3.0 is a pure .NET database with a new full blown SQL query processor built-in. VistaDB 3.0 is still in pre-release mode, but it is quite stable and feature rich today. VistaDB is first and foremost an embedded SQL database engine, but we will release a server edition in mid 2007.

Features:
- small 600kb footprint (1 assembly)
- 100% fully managed and typesafe
- .NET 1.1, .NET 2.0, CF and Mono support (2.0 and CF available now)
- full integration into VS 2005 (Server Explorer)
- SQL Server T-SQL syntax compatibility
- Views, CLR Procs and more.

In the next 3.0 update, we will be making the datatypes 1:1 compatible with SQL Server 2005 so migrating to/from VistaDB and SQL Server 2005 will be utterly seamless. Today, there are subtle differences in the datatypes but they will be gone in the next build. More info on our site:
http://www.vistadb.net/vistadb3.asp

Anthony Carrabino
www.vistadb.net
Anthony Carrabino Send private email
Thursday, November 23, 2006
 
 
http://download.microsoft.com/download/b/d/1/bd1e0745-0e65-43a5-ac6a-f6173f58d80e/RequirementsSQLEXP2005.htm

If you are installing SQL Server 2005 Express Edition, perform the following steps in order:

Uninstall .NET Framework 1.2 and later versions from your computer (versions 1.0 and 1.1 do not have to be uninstalled). Then, install .NET Framework 2.0. from the .NET Framework 2.0 Download Center.


That bit definately indicated to me that older versions don't do the side-by-side thing. Apparantly that may be overly-cautious instructoons though, Which seems kinda odd. I would assume that Microsoft would hype the whole side-by-side installation as a major advantage.


I appreciate the other suggestions, and will be spending some time going over the relevant documentation.

Thanks for the help.

Thursday, November 23, 2006
 
 
1.2 & later probably refers to beta & ctp editions of 2.0, which were floating around when it was released.
*myName
Thursday, November 23, 2006
 
 
Screw the partition thingy and give SQLite a go
my $0.02
trandism
Thursday, November 23, 2006
 
 
MyName I agree with you, 1.2 is referring to beta of 2.0 since there is no official .NET 1.2 release; it makes sense that you'd need to uninstall the beta of 2.0 (though it would be bad practice to have beta installed on a production machine anyway).
anon
Thursday, November 23, 2006
 
 
Sounds like SQL Server Express ought to be suitable, then - if 1.0, 2.0 and 3.0 can all exist side-by-side then either they already have the version required, or can safely install it without messing things up which is the biggest concern.

Weird version number games are always asking for trouble.  ;)

And anyone using beta products in a production environment is going to have problems when the "real" release is made and they have to upgrade in order to get important fixes.

Thursday, November 23, 2006
 
 
"MySQL and anything with similar licensing issues is right out"

???

Explain.
Andrew Brehm Send private email
Friday, November 24, 2006
 
 
Mysql is GNU licensed, and you can not distribute with your application.

A couple of years ago there is a BSD licensed Berkeley DB. Now it is gone.
Burner
Friday, November 24, 2006
 
 
SQL Server and Oracle XE both limit both the amount of data and the hardware that the database can run on.

IBM DB2 Express-C only limits the hardware, no data limits.  It's feature set is comparable to Oracle, and it uses less resources.  The download size is larger than SQL Server and Oracle, but it includes many tools such as admin tools and Add-Ins for Visual Studio .NET 2003 and 2005 that put Microsoft's SQL Server integration to shame.  There is also a Developer Workbench that is a separate download for Java developers, and for working with DB2 stored procedures, etc.  The documentation (comparable to SQL Server Books Online) is also downloadable.  It is the only database that can handle relational data in XML format (meaning it's stored/access/manipulated as XML).

It is the best database out there, IMHO, and I would recommend it to users who use Visual Studio (2003 or 2005) or Java for development and are looking for a database.

Firebird may be good, but I would like a database with better documentation than the Interbase 6.0 docs and more than 1 book (and better tools & IDE integration for developers, also).

PostgreSQL I haven't spent much time with, and MySQL is busy adding features that have been in other databases since the 80s-90s.

I installed Oracle XE on my machine and it became virtually unusable until I uninstalled it.  It was using 300+ MB of RAM after the initial install+reboot.  SQL Server 2005 XE has the smallest memory footprint.

DB2 has the largest download size.  Oracle XE and SSE are about the same when you acount the other parts of SSE that you need to download to work efficiently with the DB (Management Studio Express, Books Online, etc.).

I don't think the database size limits of Oracle XE and SSE are a nice catch, however.  You never know when you will need more (projects scale, etc.) and once you have procedures, and applications that use proprietary features, you may be forced to upgrade the database to a higher version because porting the data to a new database won't be a viable solution.

DB2 Express-C saves you from this problem with their database limits, or lack thereof.

- Nate.
Nate Send private email
Friday, November 24, 2006
 
 
> Mysql is GNU licensed, and you can not distribute with
> your application.

Um. MySQL is available under the GPL if you want to distribute with a GPL application *and* under a commercial license if you want to distribute with a closed-source application.
Lazlo Send private email
Friday, November 24, 2006
 
 
If you're a Java guy, and you like ugly software, then use DB2 UDB. Otherwise use SQL Server 2005.

Vistadb loooked interesting, but the forums don't reflect the performance claims ... looks to be a work very much in progress.
HeWhoMustBeConfused
Friday, November 24, 2006
 
 
>> IBM DB2 Express-C only limits the hardware, no data limits.  It's feature set is comparable to Oracle, and it uses less resources.

There's nothing that touched Oracle for concurrency though -- readers don't block writers, writers don't block readers, and there's no dirty data and no lock escalation.
David Aldridge Send private email
Friday, November 24, 2006
 
 
VistaDB 3.0 performance issues are related to INSERTs and we are working on that. The next build will exhibit better speed and will be closer to a release candidate featuring the new SQL Server 2005 data types. We have already proven that we can achieve great performance with the included 2.x "SpeedTest" demo -- screenshots are here:
http://www.vistadb.net/screenshots_speed.asp

Hey, VistaDB 3.0 is a break-through product so we hope developers are patient as we work hard to make VistaDB the BEST embedded database engine for building .NET, CF and Mono apps.

I always see posts talking about ORACLE, SQL Express, SQLite, Firebird, MySQL and now SQL Compact Edition for building .NET apps. I think that these are wonderful database products. I do. But let me ask fellow .NET developers, if you are building C# or VB.NET applications and are trying to conform to the "managed and typesafe" mantra that Microsoft has defined as the way to build apps, then why would you not use a truly "managed and typesafe" SQL database to build your .NET apps? That's what VistaDB 3.0 provides for you.

ALL of the database products listed are unmanaged Win32 products except for VistaDB 3.0. If these are the products/tools/components that we, as professional .NET developers, ultimately use to build our managed applications, what's the point of using .NET then? I mean, lets just stay in the old Win32 world and forget about managed code.

If you are like us, and agree with Microsoft's vision and firmly believe that .NET/CF/Mono is the future, then we ask you to support us in building VistaDB 3.0 and lets get a PURE .NET database (that supports SQL) on the market for ALL of us to use. We are big supporters of .NET -- i have bet the future of my company on it. SQLite may be great product for Win32 but i'm sorry, it is not managed. An ADO.NET Provider does not make a database .NET compliant. It just makes an unmanaged Win32 database work under .NET. One major benefit of building pure .NET apps is that your apps run natively everywhere .NET runs, and using VistaDB 3.0 lets you do this.

Anthony Carrabino
www.vistadb.net
Anthony Carrabino Send private email
Friday, November 24, 2006
 
 
I've only used VistaDB 2.1 but I'm actually rather fond of it - it is certainly "fast enough" for many applications and its DLLs are tiny and can be xcopy-deployed which is great for a product that will be downloaded.

It is certainly no Oracle in terms of scaleability but most applications don't need that anyway.

Note that I have no connection with VistaDB - just a happy customer!
Arethuza Send private email
Saturday, November 25, 2006
 
 
"ALL of the database products listed are unmanaged Win32 products except for VistaDB 3.0. If these are the products/tools/components that we, as professional .NET developers, ultimately use to build our managed applications, what's the point of using .NET then? I mean, lets just stay in the old Win32 world and forget about managed code."

Isn't that a good thing, though?  You can have your database (Oracle, MySQL, PostgreSQL/EnterpriseDB, IBM DB2) running on a Unix Server and still access it from both .NET and Native applications, or you can run it on Windows (only choice for MSSQL), or Linux or perhaps BSD, or some other Operating system (like MacOS).

Your product is fine, but I think many users (developers especially) respect the amount of flexibility that these other tools give you in respect to where and on what you can run the database.

- Nate.
Nate Send private email
Saturday, November 25, 2006
 
 
firebird rocks...if you are using blobs, firebird rocks. jim starkey was the first creator of blobs back in the day...I have personally pushed the blob side of the database to the limit over the past 3 years and have yet to corrupt a database file.

firebird is very capable. and free.

Sunday, November 26, 2006
 
 
"It must support partition tables."

According to this chart, you will need Sql Server Enterprise for table partitioning.

http://www.microsoft.com/sql/prodinfo/features/compare-features.mspx

Still probably better to build in some sort of horizontal table partitioning and/or archive strategy into the application and use sql server express because of costs...

Postgres is a really a great database, but definitly not as easy to maintain and things like transaction log backups are not easy or clear to implement in my experience.
mike Send private email
Sunday, November 26, 2006
 
 
Anthony,

I took a look at VistaDB a few months ago for a product I'm developing. VistaDB 3.0 seemed like it would be exactly the right component for my product.

But without a downloadable evaluation version, I had to pass. (Many other .NET embedded databases have serious speed & stability issues, and I'd like to verify successful integration with VistaDB before buying a license.)

I'd even be fine with a feature-limited demo version, but with nearly every other database vendor providing a free demo, it was not difficult to cross VistaDB off my list.

I'm sure many other solo developers feel the same way during the development of their products.

Just thought you'd like to know :)
BenjiSmith Send private email
Tuesday, November 28, 2006
 
 
Anthony,

I'm in a similar position. I am intrigued by the product, it looks like a suitable solution for my needs, but I'm concerned that your promotion has outstripped your state of development.
HeWhoMustBeConfused
Tuesday, November 28, 2006
 
 
Hey Benji,

I just took a look at the Vistadb site and they offer a 30 day trial download.  Did you try that?
Oogly Moogly
Wednesday, November 29, 2006
 
 
Their web site is misleading.

The link that says "Download Trial" takes you to a screen for the entry of eMail address.

The eMail you receive is for a download of Vista 2.1.

There is a link which says "Looking for the VistaDB 3.0 trial?". Click that link and you're taken to a page which tells you that there isn't one.

Total waste of time.

Like I said, the pace of their marketing has outstripped the readiness of the product. Bad karma here.
HeWhoMustBeConfused
Wednesday, November 29, 2006
 
 
Yeah, I fell for that too.

I downloaded the trial version, only to discover that it's a trial for the 2.1 version.

No thanks. Not what I'm looking for.

I'm also aware that there's a money-back guarantee for the 3.0 version. But I'm not particularly interested in the hassle of buying a product that I can't even test-drive, even if there is a promise of a refund.
BenjiSmith Send private email
Friday, December 01, 2006
 
 
It is so nice to see ads posted.  It reminds me why I rarely stop by anymore.

Monday, December 04, 2006
 
 
To be fair, the "ad" was technically a valid answer to my question, and even though it's not the best fit for what's needed it might have been - and projects that have a budget can afford to at least consider commercial options. At least the "ad" was posted in response to a relevant query, not just spammed at random.

Thursday, December 07, 2006
 
 

This topic is archived. No further replies will be accepted.

Other recent topics Other recent topics
 
Powered by FogBugz