Recent

Author Topic: Alternatives to Advantage Database - Advice Needed  (Read 2550 times)

re_Chained

  • New Member
  • *
  • Posts: 12
Alternatives to Advantage Database - Advice Needed
« on: July 27, 2022, 04:00:19 pm »
Hello all,

I'm sure the question of "which database engine is right for me" gets asked a lot, but I'm really looking for some quality opinions on anyone who knows much about Advantage Database. Apologies beforehand for what may be a long-winded post riddled with misunderstandings.

Not to get into specifics, but I'm about to inherit the source code for the applications the small company I work at produces. There are around 5 of them, each serving a specific purpose, and they are all built around providing bookkeeping services for a niche market. They are all written in Delphi, across various compilers, and are in need of updating. I've been studying and learning about app development using Lazarus for the past few months, having a bit of general programming knowledge already, but the task is seeming a bit daunting, to say the least. One thing I'd like to get right off the bat is what database to move to for updated versions of the apps, as Advantage Database licenses are hard to acquire now and SAP doesn't seem keen in continuing the project based on my what I've seen.

For an idea of what the apps do, the 'main' app is the central ledger. Sometimes other programs may post information into its database, or users will enter in invoices, purchase orders, new vendors, etc. as needed. The market the apps are aimed for typically have small offices with one to three people entering this kind of information as it comes in, so the database wouldn't need to be receiving constant small writes or very large writes. Reports are generated from the database a couple times a day. The majority of operations will be read/writes, the tables are rarely altered and no join operations or anything occur.

We offer a "local" and "client/server" versions of the programs, but they are essentially the same. They both use Advantage Database embedded as the main database engine, with .dbf files located in the program folders. The main difference between the two versions is that the "client/server" version looks for the ADS service running on the server. The "client/server" version works by having the program folder shared over a network drive (all the apps are Windows only) to whatever workstations need it, and the users just launch the program from the mapped drive. The number of concurrent apps/connections to the database files they are allowed is determined by the Advantage license they have. For the vast majority of customers, a max 5 people are going to be accessing a particular program at once.

I'd like to move forward changing as little as possible, in regards to our workflow for software support and the installation/use process for customers. The .dbf tables we're currently using support password encryption, and some of our support team often using the Advantage Database tool ARC to open the tables and troubleshoot issues. I've been toying with SQLite with SQLCipher, using the DB Viewer for SQLite to achieve a similar effect. And SQLite works well for a local version of any app, but I'm worried about concurrency with the write limits, and SQLite's own admission that "file locking is buggy in many network file systems" (https://www.sqlite.org/whentouse.html). As mentioned, the software is bookkeeping related with a smallish number of concurrent users, so write conflicts may be manageable with proper implementation, but I'm not sure. I'd ideally also like the eventual migration for customers to be as painless as well.

Our current sole developer has been maintaining these programs in some form for nearly 25 years, but is very behind-the-times in much of modern tech, so I'd love any and all outside advice. Any references, books, examples, what components to use, best practices, criticisms, anything and everything not just related to the above database questions would be greatly appreciated.

marcov

  • Administrator
  • Hero Member
  • *
  • Posts: 11383
  • FPC developer.
Re: Alternatives to Advantage Database - Advice Needed
« Reply #1 on: July 27, 2022, 04:12:08 pm »
File based databases on shares are old, so basically that is a 25 year old technical debt. 

15-25 years ago, the "don't want to change much" was acceptable, but it might be time to pull the plug and rewrite to a normal DBMS (I'd advise Postgresql).  Specially since you have to change DB anyhow.

Lazarus doesn't have much like this from itself, specially when shared over multiple machines. Candidates are sqllite, and maybe access over a freetds link.  In theory there is also tdbf, but for share use I wouldn't even trust it as much as sqlite. I sometimes also hear rumours about NexusDB, but don't know exactly what that is.
« Last Edit: July 27, 2022, 04:37:44 pm by marcov »

re_Chained

  • New Member
  • *
  • Posts: 12
Re: Alternatives to Advantage Database - Advice Needed
« Reply #2 on: July 27, 2022, 05:31:51 pm »
File based databases on shares are old, so basically that is a 25 year old technical debt. 

15-25 years ago, the "don't want to change much" was acceptable, but it might be time to pull the plug and rewrite to a normal DBMS (I'd advise Postgresql).  Specially since you have to change DB anyhow.

Lazarus doesn't have much like this from itself, specially when shared over multiple machines. Candidates are sqllite, and maybe access over a freetds link.  In theory there is also tdbf, but for share use I wouldn't even trust it as much as sqlite. I sometimes also hear rumours about NexusDB, but don't know exactly what that is.

Yes, when I first joined the company I was a bit astonished at the level of technical debt we were under. And I'm afraid you may be right, a full rewrite to something more modern and scalable/maintainable is in order. PostgreSQL was on my short list of candidates, I think I'll move forward using that and see how it fares.

NexusDB seems like the almost perfect solution, but we would be confined to using Delphi and paying a license for both, and I would like to move away from that. I want to use something that a larger number of people have experience with, and PostgreSQL seems like a suitable choice. Perhaps if we manage to find another developer that insists on using Delphi we can follow the NexusDB route. Thank you very much for the insight marcov.

PierceNg

  • Sr. Member
  • ****
  • Posts: 369
    • SamadhiWeb
Re: Alternatives to Advantage Database - Advice Needed
« Reply #3 on: July 27, 2022, 05:59:04 pm »
By your description the 'client-server' mode has no server component, as users launch your applications from network shared folders in said mode. But PostgreSQL does have a server component, and you'll have to consider whether every one of your customers is prepared to set up and operate a PostgreSQL server in their environment for their 3-5 users.

Thaddy

  • Hero Member
  • *****
  • Posts: 14205
  • Probably until I exterminate Putin.
Re: Alternatives to Advantage Database - Advice Needed
« Reply #4 on: July 27, 2022, 06:20:23 pm »
File based databases on shares are old, so basically that is a 25 year old technical debt. 

15-25 years ago, the "don't want to change much" was acceptable, but it might be time to pull the plug and rewrite to a normal DBMS (I'd advise Postgresql).  Specially since you have to change DB anyhow.

Lazarus doesn't have much like this from itself, specially when shared over multiple machines. Candidates are sqllite, and maybe access over a freetds link.  In theory there is also tdbf, but for share use I wouldn't even trust it as much as sqlite. I sometimes also hear rumours about NexusDB, but don't know exactly what that is.
Well, both Advantage - which I frankly loved back in the days - and Nexus were really good in its days, but indeed 25 years ago and nobody uses it.
The concurrency issues with the more modern Sqlite are a bit of a hoax.(See how mORMot handles this ), but in general a more mainstream database format is recommended. Sqlite, Postgress, MySql/MariaDb and corporate: expensive but good Oracle itself, although even Oracle has an almost free version .
In a book keeping scenario with multiple users, I would personally use Postgress, but any big gun is a good choice.
« Last Edit: July 27, 2022, 06:33:14 pm by Thaddy »
Specialize a type, not a var.

esdonis

  • Jr. Member
  • **
  • Posts: 61
Re: Alternatives to Advantage Database - Advice Needed
« Reply #5 on: July 27, 2022, 06:49:40 pm »
For me, with Firebird it would be more than enough.

Thaddy

  • Hero Member
  • *****
  • Posts: 14205
  • Probably until I exterminate Putin.
Re: Alternatives to Advantage Database - Advice Needed
« Reply #6 on: July 27, 2022, 07:10:58 pm »
For me, with Firebird it would be more than enough.
For me it would pose a risk to my customers. ( as is well known)
Specialize a type, not a var.

re_Chained

  • New Member
  • *
  • Posts: 12
Re: Alternatives to Advantage Database - Advice Needed
« Reply #7 on: July 27, 2022, 07:19:51 pm »
By your description the 'client-server' mode has no server component, as users launch your applications from network shared folders in said mode. But PostgreSQL does have a server component, and you'll have to consider whether every one of your customers is prepared to set up and operate a PostgreSQL server in their environment for their 3-5 users.

Indeed, the only "server" component is the Advantage Database Service that (essentially) monitors active connections and enforces the license limit. And our market base mainly consists of older people who are heavily resistant to change. We have around ~60 customer installations, and a tech team with a grand total of 1: me (along with an IT partner who manages many sites for us). Our prospective market share looks much the same: roughly 2 to 5 users using a single program at once, hence my want to keep everything simple. In the far future we may look at expanding to customers with a larger userbase, in which case we would want a solution that is a bit more scalable.

For me, with Firebird it would be more than enough.

Firebird was my initial choice, and the one our current developer initially suggested, but it doesn't seem to support encryption of the tables. Security is a major concern going forward, and another item on our long list of technical deficits, and I would like to implement a proper user login system as the system we have is rather poor in my opinion. If I'm going to be storing user passwords in a table, even if they are hashed, I would like the table to have an additional level of security.


Thank you all for the advice. I have much to think about and discuss with business management about how best to move forward. I believe it will come down to whether or not we think our existing customer base will be open to having some SQL server installed or not, and how large we think any userbase in the future may be.

dsiders

  • Hero Member
  • *****
  • Posts: 1052
Re: Alternatives to Advantage Database - Advice Needed
« Reply #8 on: July 27, 2022, 08:47:21 pm »
Firebird was my initial choice, and the one our current developer initially suggested, but it doesn't seem to support encryption of the tables.

Database encryption is available since version 3:
https://firebirdsql.org/file/documentation/release_notes/html/en/3_0/rnfb30-security-encryption.html

Over-the-wire encryption is available in the same release:
https://firebirdsql.org/file/documentation/release_notes/html/en/3_0/rnfb30-security-wirecrypt.html

Additional encryption functions added in version 4:
https://www.firebirdsql.org/file/documentation/release_notes/html/en/4_0/rlsnotes40.html
Preview Lazarus 3.99 documentation at: https://dsiders.gitlab.io/lazdocsnext

alpine

  • Hero Member
  • *****
  • Posts: 1038
Re: Alternatives to Advantage Database - Advice Needed
« Reply #9 on: July 27, 2022, 08:58:33 pm »
*snip*
The concurrency issues with the more modern Sqlite are a bit of a hoax.(See how mORMot handles this ),
*snip*
How mORMot handles this? What exactly do you mean with that?
"I'm sorry Dave, I'm afraid I can't do that."
—HAL 9000

re_Chained

  • New Member
  • *
  • Posts: 12
Re: Alternatives to Advantage Database - Advice Needed
« Reply #10 on: July 27, 2022, 09:32:59 pm »
Quote
Database encryption is available since version 3:
https://firebirdsql.org/file/documentation/release_notes/html/en/3_0/rnfb30-security-encryption.html

Over-the-wire encryption is available in the same release:
https://firebirdsql.org/file/documentation/release_notes/html/en/3_0/rnfb30-security-wirecrypt.html

Additional encryption functions added in version 4:
https://www.firebirdsql.org/file/documentation/release_notes/html/en/4_0/rlsnotes40.html

I see, that's my mistake for not doing my research well enough. I will look into Firebird more and see if that's another possible route.

Thaddy

  • Hero Member
  • *****
  • Posts: 14205
  • Probably until I exterminate Putin.
Re: Alternatives to Advantage Database - Advice Needed
« Reply #11 on: July 27, 2022, 09:33:45 pm »
Well, mORMot is free, examine its sources and docs....
Specialize a type, not a var.

Thaddy

  • Hero Member
  • *****
  • Posts: 14205
  • Probably until I exterminate Putin.
Re: Alternatives to Advantage Database - Advice Needed
« Reply #12 on: July 27, 2022, 09:36:08 pm »
Well, mORMot is free, examine its sources and docs....
And don't use Firebird.
Specialize a type, not a var.

re_Chained

  • New Member
  • *
  • Posts: 12
Re: Alternatives to Advantage Database - Advice Needed
« Reply #13 on: July 27, 2022, 10:07:05 pm »
Well, mORMot is free, examine its sources and docs....
And don't use Firebird.

Why do you suggest against Firebird, Thaddy? It could potentially fit our specific situation nicely, so if there's a certain major reason you're advising against it I'd like to know.

alpine

  • Hero Member
  • *****
  • Posts: 1038
Re: Alternatives to Advantage Database - Advice Needed
« Reply #14 on: July 27, 2022, 11:14:44 pm »
Well, mORMot is free, examine its sources and docs....
I know it is free, I have examined the source and I have read the docs. In fact, I've been doing this a lot in recent years.

Again - How mORMot handles this? What exactly do you mean with that?
And why the mORMot could be a positive example at the expense of the Firebird?
"I'm sorry Dave, I'm afraid I can't do that."
—HAL 9000

 

TinyPortal © 2005-2018