Building Oracle support into a Microsoft-only product
June 5, 2009 9:37 AM Subscribe
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?
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?
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, 2009
If you use a ton of SPROCs, good luck.
posted by unixrat at 9:52 AM on June 5, 2009
Response by poster: 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, 2009
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, 2009
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, 2009 [1 favorite]
posted by benzenedream at 10:33 AM on June 5, 2009 [1 favorite]
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, 2009
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, 2009
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, 2009
posted by SirStan at 1:29 PM on June 5, 2009
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, 2009
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, 2009
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, 2009
posted by gjc at 6:17 PM on June 5, 2009
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, 2009
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, 2009
This thread is closed to new comments.
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, 2009