Building Oracle support into a Microsoft-only product

June 5, 2009 9:37 AM

Currently Microsoft-only, but wanting to change our software soon to support Oracle as well. What are we getting ourselves into?

My company develops a web-based human resources management system and software suite written in .NET which uses a MS SQL backend. We have been a "Microsoft shop" for at least ten years, with all of our products being written in a Microsoft language using Microsoft tools on a Microsoft database.

We recently got a fairly sizable RFP from a company who is extremely interested in our software. The RFP describes our software almost perfectly and they really want to go with us — however, their one stipulation is that our software needs to support Oracle as a backend.

The dollar amount of the contract has the upper level management willing to do whatever it takes to secure this contract, but I'm worried that we're biting off a lot more than we can chew. No one in the whole company has any Oracle experience except for one developer who has said that it's a bad idea. We also have a very tight development schedule for the next year. Also, this is the first RFP we've gotten in many years which requires Oracle as a backend, so I don't think this change will help us earn more business in the future.

Knowing what you do about the differences between MS SQL and Oracle, how reasonable would it be for a small software company to build Oracle support into their flagship product, given limited development resources and almost no Oracle knowledge?

posted by relucent to computers & internet (10 comments total)

The answer to your question of course is it depends. How reliant are you on SQL Server specific features? If there are tons of SPROCs, you might have quite a bit of work to do. .NET actually abstracts away a lot of the nitty gritty DB connectivity stuff from the old days, but if there's some SS specific features you're using, there's going to be a bit of work.

I work for a company whose core product supports both SQL Server and Oracle. A lot of this is handled by using SPROCs only where necessary, and using randomly generated primary keys rather than relying on sequence numbers/auto indexing. And making sure that no keywords for either database are used for any view/table/sproc names. There are a ton of other little gotchas, but if most of your interaction with the database is ANSI-compliant SQL, it's not too terrible.
posted by Calloused_Foot at 9:45 AM on June 5

What C_F said. Straight ANSI SQL via ODBC/OLE shouldn't be that hard to port. Oracle administration would be the biggest headache after that.

If you use a ton of SPROCs, good luck.
posted by unixrat at 9:52 AM on June 5

We use GUIDs for all primary keys.

We used stored prodecures all over the place, probably about 30 or 40 of them, and they are all used as often as possible to keep the codebase light.

We do a heavy amount of client administration. One of the biggest selling points of our software is the support contract, which involves developer-grade support technicians having remote access to the client's servers in order to fix issues and bugs as they come up. All of our technicians are well-versed in MS SQL but have absolutely zero Oracle knowledge. So we'd not only need to retrain the developers and refactor all of the SQL in our software, we'd also need to retrain our entire support team.
posted by relucent at 10:02 AM on June 5

I think you answered your own question. You may be able to deliver the product to Big Money Client, but you won't be able to support it and make it reliable without hiring a seasoned Oracle person and dedicate them to support of company X. So ask management to factor that into the profit margin, unless they just want to get the contract and burn a bridge with buggy software that you can't support.
posted by benzenedream at 10:33 AM on June 5 [1 favorite has favorites]

If your support and development structure are SQL-heavy, and you're doing things that are going to vary quite a bit from platform to platform like stored procedures, then it's a pretty big jump to make to add Oracle support.

What you could do, codebase-wise, is work switching to a DB-agnostic ORM framework like NHibernate into your multi-year plan. It won't alleviate any of the stored procedure pain, but it'd make the simpler database work portable in the future.

In the meantime, listen to your one developer.
posted by mikeh at 12:22 PM on June 5

Can you use Oracle as a "data warehouse" and not use it for the live system? It would be an order of magnitude easier and less costly to implement, and might meet the customers needs?
posted by SirStan at 1:29 PM on June 5

The dollar amount of the contract has the upper level management willing to do whatever it takes to secure this contract

Excellent. You'll need:
1 Crackerjack Oracle DBA
5 Contractors to convert existing SPs from T-SQL to PL/SQL
Free training for your existing staff to get up to speed.

Whatever it takes, right?
posted by Civil_Disobedient at 5:35 PM on June 5

Sir Stan's idea seems like a good one- write a "translator" that sits between your app and the oracle backend that translates what needs to be translated.
posted by gjc at 6:17 PM on June 5

I don't see this conversion as a major problem, if it's just what you've outlined above. Oracle is different from MS-SQL Server, but not that different.

As I understand it, you have all the requirements finalized (as you have the current code), and you can test against the existing code base. Given that, translating from T-SQL to PL/SQL is not particularly difficult.

Anything that's ANSI SQL probably works as is.

30-40 sprocs? Written purely in T-SQL? If you employed functional decomposition (and thus these aren't 1000 line monsters), I'd expect I could translate any one in at most three days (and probably much more quickly unless the code is doing something dubious). Call it a month to get up to speed in what you're actually doing, and another month to crank out the code, for one guy.

As far as your CRUD activities, the only real issue is that Oracle doesn't allow multi-table updates /deletes (but does allow subqueries, so it's mostly straight-forward to convert.)

Basically I agree with Civil Disobedient: you'll need an Oracle DBA, and a SQL coder or two, plus some training. To the extent that your existing code is ANSI SQL, you reduce the conversion cost, and the staff training costs.

This is why I always emphasize the ideal of writing code to the ANSI Standard as far as practicable.
posted by orthogonality at 6:22 PM on June 5

To the extent that your existing code is ANSI SQL, you reduce the conversion cost, and the staff training costs.

Get an Oracle consultant soon. One of the interesting thing about Oracle is that the optimizer hates ANSI SQL.

In addition to the development work you are already thinking about, consider how much testing you need to do. Do you have unit tests or integration tests for your DB calls? How long will it take you to write them and verify them?

This is why I always emphasize the ideal of writing code to the ANSI Standard as far as practicable.

Unfortunately, no one really conforms to the ANSI standard. If you really want to be portable, avoid stored procedures as much as possible and keep your SQL as simple as possible. Build an abstracted database layer, and do as much work in your application language as you can. However, if you need to tune the system all that goes out the door. Oracle can get a lot faster if you are willing to work with it on its own terms, as will MSSQL or MySQL.
posted by b1tr0t at 7:06 PM on June 5 [1 favorite has favorites]