We help IT Professionals succeed at work.

WHICH DATABASE IS THE BEST (for server side or client side) ?

WolfgangKoenig
on
Criterions of choice:

1) Very good performance in a client/server environment
   (Especially db transactions and search requests)

2) The server side db can handle concurrently client requests

3) The client side db user licence should't be very expensive

4) The db should support replication mechanisms

5) The client side db should be also available when the server is offline. Therefore special replication mechanisms are needed

6) The db should be support common db interfaces (ODBC/SQL)

7) The db should be easily to access from a common programming language environment (Java/Delphi/VB/C++ ...)


What are your recommendations for such a database ?

Best regards
WoK
Comment
Watch Question

Commented:
I would say to look into either InterBase or MySQL.
Or Oracle if u have the money :(

Author

Commented:
YodaMage:
How expensive is a InterBase license for a client ?

cybermoonlight:
Yeah, but the price for the license ...
Guy Hengel [angelIII / a3]Billing Engineer
GOLD EXPERT
Most Valuable Expert 2014
Top Expert 2009

Commented:
Regarding 1), more important than the database system is the application and database design.

2) Any larger database system can handle this
3) For large database systems, you can have per-server or per-processor licences rather than CALs...

4) OK for larger db's ...
5) Thus you need locally stored data. This requires indeed some special replication, but i guess that normal merge replication ...

6) OK for almost all the db's. BTW, SQL Server and Oracle (and certainly others too) support the new interface, OLEDB which is (promised) faster, stabler etc...

7) If you access using ODBC (or OLEDB), the application doesn't matter?!

Until now, there is no definite choice using your requirements...

Author

Commented:
angelIII:
3) A per-server licence means (for instance) that i pay  for one oracle db server and all depending oracle clients that use my application are free of charge ?
Guy Hengel [angelIII / a3]Billing Engineer
GOLD EXPERT
Most Valuable Expert 2014
Top Expert 2009

Commented:
Regarding 1), more important than the database system is the application and database design.

2) Any larger database system can handle this
3) For large database systems, you can have per-server or per-processor licences rather than CALs...

4) OK for larger db's ...
5) Thus you need locally stored data. This requires indeed some special replication, but i guess that normal merge replication ...

6) OK for almost all the db's. BTW, SQL Server and Oracle (and certainly others too) support the new interface, OLEDB which is (promised) faster, stabler etc...

7) If you access using ODBC (or OLEDB), the application doesn't matter?!

Until now, there is no definite choice using your requirements...
Guy Hengel [angelIII / a3]Billing Engineer
GOLD EXPERT
Most Valuable Expert 2014
Top Expert 2009

Commented:
Sorry for the double post...

Yes!

Commented:
Price depends on the number of clients you will be using.  An example is the per processor price for SQL Server Standard is $4999, but the regular price is $1489 with 5 CALs and additional CALs are about $152 each.  This means that you would need more than 28 concurrent users before the per processor licensing is cheaper.  Also remember that most large databases are licensed per concurrent user meaning that if you have 50 users, but only 25 are using it at one time, you only need 25 licenses.

As far as databases that would meet your needs: Sql Server, Oracle, DB2, Interbase, etc., etc., etc.

For the clients to have data even if the server is down, you want merge replication or updatable subscription replication.  Either will work, but the appropriate choice would depend on your application structure and requirements.
maybe a mixed solution?

I know of a few applications that use SQL Server on the server and has custom made modules which "checks out" data (or just flags data as shared) to a local Access DB (hate to call Access a DB, but...) which is then used by clients when offline.

This combines the advantages of a large DB system (i.e. SQL Server in this case) and cheap client licences for client DBs (Access is included in all(?) office installations).

The drawback is that you have to custom make the replication application unless you have a simple DB model and simple work flow in your application.

Commented:
In my experience, I have worked with 3 databases in depth.
I will rank them from small applications to large applications.

MS Access, MS SQL Server/Oracle Standard Edition, Oracle Enterprise Edition.

This is my personal opinon and I'm sure others might agree/disagree.

MS Access is great for small desktop applications which don't need lots of networking and doesn't need to store lots of data. I have found that a maximum of 5 concurrent users can use Access with no ill-effects. Also, there is a limit on the amount of data it can hold which is 2 gigs.

MS SQL Server and Oracle Standard edition are more of a mid size RDBMS. They both can do the backups, storing gigs of data and have performance tuning ability.

MS SQL Server has a lot of nice wizards to speed up the administration of the database, however, Oracle allows you to do all the custom changes ( but not as many wizards).

As for the Oracle Enterprise Edition, you can get into partitioning (splitting up) of table's storage over many different hard drives. This way you can turn on/off parts of a table's data if it is not needed. So instead of deleting the data, just take it offline until it is needed. This improves SQL performance a lot.

Hope this helps.
Guy Hengel [angelIII / a3]Billing Engineer
GOLD EXPERT
Most Valuable Expert 2014
Top Expert 2009

Commented:
FYI: If you have SQL Server as your main server, rather that using Access (which isn't a DB), use the free MSDE database, which has the same engine as SQL Server...

CHeers

Author

Commented:
Joebob:
What are a CALs = 1 concurrent users?

egomaster:
The DB model and work flow is unfortunately not very simple

ZANTAR:
How are the licence prices between Oracle and MS SQL Server ?

angelIII:
What stands MSDE database for?

Thanks for all comments!
Guy Hengel [angelIII / a3]Billing Engineer
GOLD EXPERT
Most Valuable Expert 2014
Top Expert 2009

Commented:
CAL = Client Access License

MSDE = Micro$oft Data Engine. In short, it's the same engine that MS SQL Server 7, using the same services, database files and interface (ODBC/OLEDB). The differences:
free, but some technical limitations like filesize is smaller... other thing is that the Enterprise Manager doesnt come with it neither, but you can use the Enterprise Manager to connect to the MSDE installation.

To compare the prices, you should ask your local vendors for Oracle and MSSQL Server licences, as the prices can vary... On the internet you might find some good indications too.

CHeers
Angellll:
Well... You learn something everyday... MSDE is obviously a better alternative than Access.

WolfgangKoenig:
If you have a complex application/workflow my guess is you have to make your own application specific synchronization engine. If you can avoid this - good, but I'm afraid you won't be able to...

Commented:
postGRE has most of the features of the more expensive databases, and is free.

MySQL is fine, but lacks stored procedures.

Don't pay for a database!!!!!!!
Commented:
If you use MSDE on the clients and SQL Server 2000 on the main server, you can setup Merge Replication between the clients and server.  Merge Replication is a system that allows multiple databases to be kept in sync.  It has some problems, such as it doesn't remember transactional boundaries, but with proper planning this is less of an issue.  The nice thing is that you can use a system that is already written rather than write your own synchronization method.

Regarding the "free" databases, I don't personnaly like them.  With free software, you have no structured support network.  You have to either search the net to find solutions to problems or pay a contractor to come in and fix your problems.  I only use "free" software in 2 cases: if it is simple and includes source code, or if I have no other choice due to budget constraints.

CAL does indead mean Client Access License.  With SQL Server you will need one for every concurrent user unless you get the per-processor license.

Oracle pricing versus SQL Server: Oracle is generally twice the price of SQL Server, but it is arguably a more capable/flexible DBMS.  One advantage with Oracle is that the server doesn't have to be a Windows Server, it could be a Unix server farm if you want.

Commented:
Ever heard of INFORMIX? To me this is the best DBMS around-server & Client.Very much tied to UNIX though.And i also think it is a bit cheaper since it not as resource hungry as ORACLE.

Commented:
I recommend Interbase/Firebird..
It's free, it's fast and very reliable and very small footprint (works very well with 4 MB RAM).
And also due to its MGA (Multi Generation Architecture) its very fast on reads, because it does not lock on reads (also much smaller log).
Good support for replication.
One of the most used DB for developement with Delphi.

If you want commercial support Borland has the commercial version (but it costs :(..)

At least it should worth a try..

Author

Commented:

Author

Commented:
Thanx biki and razwp ;)

Commented:
Interbase is also free. It has Triggers, stored procedures, roles, user defined functions, etc...

Commented:
Interbase is not free.

Free interbase is called Firebird.  Look at www.IBPhoenix.com which has the server + client + odbc all free.

To allow offline editing you will need to use MIDAS.  You can edit like a normal table, then save to file and load from file.  Finally when connected to the server you can ApplyUpdates.

I think a MIDAS license is something like $2500 per server.

Pete
====
http://www.HowToDoThings.com (Delphi aritcles)
http://www.Stuckindoors.com/Delphi (Open source graphics, sound, and FastStrings library)

Author

Commented:
Thanks for all comments !)

Someone here who have experiences with Sybase?
What is the greater/better dbms mysql or firebird (interbase)?

This thread is a good guide for all users that have to set up a dbms!

Cheers
WoK

Commented:
MySQL versus  Interbase (or PostgreSQL) is a  never-ending
debate.
They are different tools with different purposes.
MySQL is simple, fast, but no enterprise functionality (transactions, stored procedures, triggers, subqueries, declarative referential integrity, ...)

Interbase (and PostgreSQL) are a bit slower because they offer that functionality.

The choice depends on what we want the most (data integrity versus slower speed). Also if your DB contains more than 10 tables than referential integrity becomes a serious problem and nobody recommends to manage it only in your application (without some kind of DB checks), even if it is slower.
Remember, the data you save in the DB are your more precious value, because if something is wrong it will cost you the most to repair
I agree with biki that INFORMIX is the best choice for you.

Sybase is not the best choice.

I don't know which is greater/better dbms mysql or firebird (interbase)

Anyways whatever your choice at last tell us and tell us why :)

Ramy...

Commented:
I have to laugh at one point made in the accepted answer here, that open source software has less support.

Microsoft products cost a great deal of $$$, and M$ support flat out sucks. Many people pay for Crystal Reports, take a poll on what people think of Seagate Support. I believe the newsgroups like borland.opensource or tamarack associates have more info readily available than most commercial products.

(BTW,opensource means that it comes with source code)

Commented:
I've worked with Interbase and MySQL for quite some time.. off the top of my head:

MySQL:
-Lots of data types
-Lots of functions
-Autoincrement columns
-Runs on many platforms (typical of open source)

-Simple, fast, but limited

Interbase:
-Essential data types.. BLOB type can be just about anything
-Limited core functions, but extensible (UDFLib, etc)
-Generators, but with triggers you could have them act as autoincrement (before insert, if id is null, increment generator and set id to that)
-Runs on many platforms

-Stored procedures
-Triggers (I'd say this should be basic in most DBs)
-Views

Indeed there is a free version of Interbase.  That is version 6.0 when Borland made the code available to the public.. They soon changed their mind and came out with 6.5 and 7.0 with closed source.  Firebird is building on the 6.0 code.  They attempt to make it compatible with Interbase and are porting the code to C++.

http://firebird.sourceforge.net/index.php?op=guide&id=ib6_techspec has a lot more info.  I think Firebird will more than suit your needs.  It is free, open source, has much of the "basic advanced" functionality and more, cross-platform, fast, fairly simple but powerful.

Commented:
Q about Microsoft - SQL Server
has to do with cost - long-term
We use accounting/database package called Navision (Financials) Microsoft bought the company.
Yearly maintenance/update fees are exhorbitant as it is.
Now MS has reconfiged pricing poilices, one of the kickers is:
If you do not pay yearly fess then down the road you cannot buy additional CAL nor additional mdoules.
(Also not eligible to upgrade to newer versions etc.)
MS has done this with Solomon & couple of other programs/companies they bought.
In other words, you don't buy it, you rent it.

Question is: say someone goes with SQL Server, MS "business plan" is to do what it did with Navision to all products - so a year or 2 SQL Server users are faced with paying yearly fees to MS to have the right to pay for additonal licenses, updates, upgrades, etc.

Thoughts?

Explore More ContentExplore courses, solutions, and other research materials related to this topic.