Sorry, but this is madness. Rewriting a working application in a different language because someone doesn’t like the style of data access plumbing?! It’s needless expense, effort, disruption, and risk. Where’s the ROI? What is the time-to-value? If the question describes the full reasoning, standard go/no-go business metrics cannot possibly support rip-and-replace.

This isn’t because I disagree. Direct-to-the-database connections have some real downsides, such as limited scalability, and likely violation of the least-privilege security approach. They lock data interpretation into client apps, making multi-platform apps, long version tails, and combination tasks such as data analytics more challenging. And Delphi 7 is getting a little long in the tooth. Etc. etc.

If you were a commercial software company, needed to greatly increase the number of concurrent users, needed to operate with less trusty users or on less secure networks, or had a specific feature need that direct-to-database connections impeded or precluded, I’d be the first to concur with “update your architecture” or “modernize your tooling.” But from your question at least, those don’t seem to be the case. You are a medium-sized company; the software appears to be doing its job; and there is no clear motivation for a clean slate rebuild or major roto-tilling. That makes this a major update to conform to someone’s architectural preferences. That violates primum non nocere (“first, do no harm”) for questionable benefit.

This is exacerbated by the likelihood that you don’t have good test suites to confirm that a new clean-piece-of-paper build works correctly. (Don’t feel too bad about that–most shops don’t for their internal apps.) And if it’s accounting software, getting data wrong can have extremely negative business consequences. All of this shouts “this will end badly!”–or at least, “you will eventually be unhappy that you decided to rebuild something that already worked for shaky reasons, rather than work on something that could genuinely help the company.” I’ve seen too many clean-slate projects end that way.

Major roto-tilling or start-fresh can work–but you have to have substantial backing, management and users that accept it will take a while and introduce disruption along the way, and most important, a solid reason for going that way.

Instead, I’d suggest you examine what incremental update paths you might have from Delphi 7, and develop a more incremental plan and a more benefits-based motivation for making substantial changes.

Not clear whether you are saying your CIO’s issue with your design is the persistent DB connection, or the fact that you use application users mapped to DB users. Neither is necessarily BAD architecture, depending on the requirements. There are many applications that do the same thing, even high end stuff that runs on Oracle Enterprise.

And, even if you eliminate the persistent, direct connection, a competent admin (much less hacker) running your app locally, with or without access to the DB server will be able to connect directly to the database if they work hard enough. Why is that a problem? Sure you don’t want it to be public facing, but internal apps can be managed with firewall rules, or co-resident app + db on the same server. Traditionally, if we embed a database engine in an app we don’t change the port. The security can’t be based on obscurity. Either make sure the app is co-resident so there is no DB network connection except localhost, or ensure it is using an encrypted session, that removes the issue of network snooping, but leaves local hacking possibilities. Outside of that, what is the problem?

Are you not able to dedicate a database instance to the app? Perhaps he wants your app to co-exists on a SQL Server instance with other apps. If so, I can understand the requirement, and it is a good thing. Your choice to map users to real DB users was not a great choice, but it isn’t end of the world, that is more of an old school design that works, but limits your options (requires dedicated database). Now that it works, you have to weigh the costs. Maybe your CIO already has.

Moving on, a local server (local LAN) for locally installed desktop application is the most common model. Otherwise, you either move to a full SaaS web model, or use disconnected synchronization. It isn’t an OLTP web server so I don’t understand the issue with persistent sessions. If he is stuck on this point, there is still some good in rearchitecting it…here are some options, with benefits.

You can port a connected SQL design to a disconnected SOA design by creating a service “agent” (WCF services serving up data calls). You need to map all of your queries over to service calls, implement them in a service layer, then deploy that service layer as the front-end to your database. You can still use fat client data grids all you like, but the data flows via WCF or Data Services from app to broker, broker to DB, and if you need to get fancy, you can implement IQueryable and your own custom data source to allow arbitrary query criteria. I’m not saying it is painless and cheap to transition, but it works. The advantage is you can move your data server anywhere on the Internet (cloud) and your fat app can still work over HTTP/HTTPS. C# / .NET is perfect for this. It’s service interoperability is very rich.

If your DB is not local on a LAN/WAN, and performance/latency is an issue, there are other options that I’ve also had success with, such as implementing a local SQLAnywhere database, replicate the schema of the master DB, and use Mobilink to synchronize against the consolidated database. There are a lot of commercial products with embedded SQLAnywhere or Ultalite DBs, or SQLLite DBs that use this architecture. (Hate to give away tricks of my trade but…)