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.

MS SQL Libraries for Delphi

Hello,

We have a Delphi 6.0 application that uses the BDE against a Paradox database but we have outgrown it and the high number of transactions/users seem to cause inestability once every other day unless we install the database in the same computer as the program (Terminal Server environment).

We are thinking into changing the database back-end to MS SQL Server to avoid a library-based access which supposely is quite prone to errors with the network latencies and see basically two options because we need to stick to Delphi for some other reasons:

- Use the "official" SQL Links technology.
- Use some third party library to access MS SQL Server.

Has anybody already gone thru this path? Is there anything that you recommend or disrecommend?
Joe
Wednesday, November 30, 2005
 
 
Having used D7 Enterprise and now D2005 Enterprise against MS SQL Server, I can assure you that the only reason we spent ~$3000 for Enterprise versions is that 1) the application was written for SQL Links and 2) we get SQL Links with the Enterprise level version.

Paying a $2500 per developer workstation premium for a particular level of database driver doesn't seem to make great sense to me. (Oh, and I had to disable Delphi's 'Together' stuff because it was slowing down the IDE dramatically.)

Since you're looking at a partial rewrite anyway, I would recommend *any* alternative access method offered by Delphi first.
Bored Bystander Send private email
Wednesday, November 30, 2005
 
 
If you just want a more robust solution than BDE/Paradox then there's not really any need to go to SQL Server. 

There are a number of "BDE replacement" databases that offer both fileserver and true database server configurations, and which are robust and reliable with large db sizes and many concurrent users.  Porting your application to one of these databases would likely be very easy, much simpler and faster than porting to SQL Server.  Names to check out are DBISAM (http://www.elevatesoft.com ) , Advantage , NexusDB .  All of these have the ability to "embed" the db server in your application for simple installation.

If you want to port to SQL Server (or the free version: SQL Server Express) then there are a number of ways to access it.  These include simply using the ADO components packaged with Delphi since version 5, or using 3rd party components.  Corelabs is one of the 3rd party db access providers, and they have solutions to connecting to SQL Server through ADO.NET, VCL components, or Borland's DB Express interface that provides an added level of abstraction if you ever wanted to port to a different db than SQL Server: ( http://www.crlab.com/products.html )  MicroOLAP is another vendor that supplies specialized access components for SQL Server: http://www.microolap.com/products/connectivity/msadodac/

I think there are at least one or two other vendors that provide SQL Server access components, can't remember at the moment.  Those are two I hear about most often, I think.

Rather than moving to a BDE Replacement db or to SQL Server you might also consider Firebird and PostgreSQL.  Firebird, in particular, would be a good match.  It's a robust and full-featured open source database.  It's closely related to Borland's own (non-open-source) Interbase, and though neither Firebird nor Interbase are written in Delphi (they're c++), both are heavily used by Delphi developers.

If I were you I'd go with one of the BDE replacements though.  DBISAM would be my first choice for you, without knowing more.  You can find out more by making a post in the borland.public.delphi.thirdpartytools.general newsgroup.  Regarding DBISAM you could find out more at the Elevatesoft website.  I've used both DBISAM and NexusDB extensively and would highly recommend either.  Nexus is going through a bit of a teething process with the SQL implementation in their V2 over the last couple months, so DBISAM is a better bet.
Herbert Sitz Send private email
Wednesday, November 30, 2005
 
 
Elevatesoft has web-accessible newsgroup interface here:
http://www.elevatesoft.com/scripts/newsgrp.dll?action=groups

and you can get access to list of same groups for newsreader here:
http://www.elevatesoft.com/scripts/newsgrp.dll?action=nntpgroups
Herbert Sitz Send private email
Wednesday, November 30, 2005
 
 
Thank Herbert, that's pretty insightful!

I always thought that the non-client/server approach was enough to cause inestabilities but maybe I was influenced by the experiences with the BDE (and also because that's what I thought was source of issues with subversion when using the Berkely DB instead of the file system as a store).

We also have some performance issues with DBE, especially when using filters in large DBs.

Obviously we are using intensively in our code the DB visual components; if we used DBISAM, are the DB components left alone and DBISAM plugs in there nicely basically by just replacing the tables or should we use other approaches for the data-aware components? We are concerned about the refactoring in our code that the DB change might impose so we want to be caution about any pros and cons.

When you choose DBISAM instead of BDE without going client/server, which are you main goals, and why do you like especially DBISAM?

Also, with DBISAM, I understand from their website that programing in client/server or in non-client/server is just a difference of a few properties in the components.

Do you also have had any experiences with their client/server database server?
Joe
Wednesday, November 30, 2005
 
 
Joe

We are moving our Delphi/Paradox program to DBISAM in December. 

There is a utility available ( from Elevate Software )to convert from Paradox to DBISAM.  The trial version has all features of the full program with a 'nag' screen.  When you are finished with the conversion, you can buy the DBISAM program and deploy the new version.

You should be able to leave the UI alone, only making changes to the back-end with DBISAM.
Donald Adams
Wednesday, November 30, 2005
 
 
"I always thought that the non-client/server approach was enough to cause instabilities . . . "

A fileserver setup like BDE does not in itself cause stability problems.  Only a poor implementation of it, and the BDE isn't the greatest.  Other than that a fileserver system like BDE will inevitably have problems as you try to scale up to more users.  A well-written app that minimizes network traffic can extend a fileserver db pretty far, though.

Main thing to remember, though, is that the BDE replacements are not fileservers.  They are, or at least they can be, deployed as true client/server systems.  So by going to a "drop-in" BDE replacement you are not ruling out a c/s setup.  DBISAM has both fileserver and c/s versions, Advantage has free fileserver and a c/s setup that is licensed on a per deployment basis, NexusDB is always c/s, even if both client and server are embedded in same desktop .exe.

"We also have some performance issues with DBE, especially when using filters in large DBs."

This will cause lots of network traffic and be a bottleneck with any fileserver setup.  With c/s setup of the one of the BDE replacements you can have the filtering done on the server, which will improve performance.

"Obviously we are using intensively in our code the DB visual components; if we used DBISAM, are the DB components left alone and DBISAM plugs in there nicely basically by just replacing the tables or should we use other approaches for the data-aware components? We are concerned about the refactoring in our code that the DB change might impose so we want to be caution about any pros and cons."

All the BDE replacements are made to be easy replacements for BDE in an existing app.  This is also mostly true even if you switch to SQL Server, since the components you use to access data in SQL Server would be TDataset descendants.  The BDE replacement db's go a step further and try to comply with BDE SQL syntax and table behavior, so necessary changes are minimized.

"When you choose DBISAM instead of BDE without going client/server, which are you main goals, and why do you like especially DBISAM?"

I would ordinarily use DBISAM in its client/server setup.  Improvement over BDE is in overall speed, ability to _reliably_ handle larger db's and more concurrent users.  As always, though, you have to make sure your app is written to minimize network traffic and that queries are optimized so they can make use of available indexes to speed things up.

To clarify the DBISAM fileserver - c/s setup: You can change an app to client/server configuration just by changing a property or two.  This means that you will have a DBISAM server running separately from your client apps on a network machine.  Processing of queries and other table-api operations (e.g. ranges) is done entirely on server machine, only results are sent back to clients.  Not sure about whether the table-api filters are processed entirely on client.  If they aren't, then solution is just to use a DBISAMQuery component rather than a DBISAMTable component.

The DBISAM newsgroups are a great source of information, very helpful to newbies.  Also, you can search archives of them at http://www.fulltextsearch.com .  Use a search string of '^elevatesoft bde' and this will retrieve posts in elevatesoft newsgroups that refer to bde.  A large percentage of these will be discussing use of DBISAM as a bde replacement.  DBISAM also has excellent manuals that you can access from their website.
Herbert Sitz Send private email
Wednesday, November 30, 2005
 
 
Thanks Herbert, DBISAM in c/s mode makes probably a lot of sense, although we have some pressure from our IT department that MS SQL becomes the only c/s back-end database eventually, but on the other side, we are concerned about having to touch excessively the current code because it has worked well for ages.
Joe
Thursday, December 01, 2005
 
 
I also vote for DBISAM, if the choice of database engine is negotiable. A product I developed was ported in an extremely short time frame (I mean less than 1 week) from Paradox tables under BDE, to DBISAM.

The reason for SQL Server could be marketing, though. Some IT departments may have the MS blinders on.

>> A fileserver setup like BDE does not in itself cause stability problems.  Only a poor implementation of it, and the BDE isn't the greatest. 

I personally believe that the multiuser logic employed by "fileserver" (peer to peer databases) like Access, dBase or Paradox has always been inherently risky and is always worth distancing your application from. When you think about it, it's scary. Two or more workstations basically lock and unlock a data file remotely, using the operating system file IO calls, and read and write the file by turns.
Bored Bystander Send private email
Thursday, December 01, 2005
 
 
Bored -- Why is that so much more scary than having the db server lock and unlock things?  Using os file locking doesn't scale as well, but if it's done right excessive client app locking attempts won't cause db corruption, program crashing, or errors, just a drastic slowdown.  And if you write the apps right you can minimize the need for locking and make any necessary locks short; performance can still be good with reasonable numbers of users.  Not that I'm saying fileserver db's are as good as a true c/s setup, but they aren't necessarily terrible for small-scale multiuser environments.
Herbert Sitz Send private email
Thursday, December 01, 2005
 
 
My personal experience in porting old BDE app to DBISAM was mostly positive. It WAS better, but not perfect
Onla after i moved to ASTA + DBISAM, than things got really stable and close to ideal

MS SQL server is a good choice also, if you can make sure
SQL server is properly maintained

for 50-100 users with no DBA , ASTA + DBISAM (Standard edition) is killer combination
Seemore
Friday, December 02, 2005
 
 
>>  Bored -- Why is that so much more scary than having the db server lock and unlock things?

File based multiuser database sharing: multiple workstations, each with their own notion of locking and unlocking, cooperate to write and modify the database tables.

Server based database sharing: just one processor on one box is performing all writes, and serializes all requests.

I just see the peer to peer stuff as fragile and the server based approach as inherently less so. Yes, peer to peer can be made robust. But I think doing so is harder than building a reliable server database, and it relies on having a homogeneous and well maintained set of user workstations.

All of us who have dealt with peer to peer databases have experiences with stupid stuff such as file locking that different workstations perform inconsistently, leading to garbaged databases. Paradox, in particular, was a PITA to get working reliably; each BDE instance on each workstation had to point to the (textually) same lock file name.

This was more true in the days where workstations may be mixed between DOS, Windows 3.1, and Windows 9x and is probably easier to contend with now in Windows XP based environments.
Bored Bystander Send private email
Friday, December 02, 2005
 
 
Bored -- Well, in filesharing setup isn't there a serialization of requests, too.  It's just that the OS handles the serialization rather than the intermediary db server. 

Of all the corruption problems I and others I know have had with filesharing db's, I would rank problems related to file-locking pretty low.  Most common is corruption when a user shuts off client machine without os shutdown so it still has remote file open, or index corruption.  Maybe I'm ignorant, but I couldn't trace any problems I've had to problems with multi-user locking.

Seemore -- I'm curious whether you were using fileserver DBISAM, the 5-connection standard c/s version, or the unlimited connection c/s version for your 50-100 user app.  If one of the former then I'm sure it would have problems.  If you tried the unlimited connection version then I'd expect it to work okay, but probably getting close to DBISAM limits of good performance.

I myself use DBISAM in tandem with kbmMW, which is similar to Asta.  I agree that this is combination that will give best performance, but porting the app to an n-tier setup like that could involve quite a bit more work than just porting BDE/Paradox app to DBISAM c/s version.  If OP's multi-user scenario is less than 20 or 30 concurrent users then I'd expect DBISAM c/s version to work fine.  Still requires app to be well-written, but Tim Young and others in DBISAM newsgroups are full of help on how to do things like that.
Herbert Sitz Send private email
Friday, December 02, 2005
 
 

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

Other recent topics Other recent topics
 
Powered by FogBugz