Portability
-
Write once, suck everywhere. That's kind of been my motto when someone asks me to be sure that I can keep the code portable for movement to other platforms or database systems. To me, if you're really writing something that will sell well enough to run on multiple platforms, hire a development DBA for each platform and have them optimize the code.
So being having built some software and talked with other developers building large applications, it's what most of them have agreed with. Management is another story. So I'm wondering ...
How important is portability?
In where you work or in software you've developed. Is it important to consider porting your code to other platforms?
In my experience it's not. Unless you're writing an operating system or something relatively low level, but not touching the hardware, it doesn't make sense to really code for portability. Instead I think it makes sense to code for your platform and then use the idea or concepts to let someone else rewrite it in an optimized fashion for another platform.
So I'm wondering how many of you actually actively consider portability or even think we should.
-
I work for an elephant that creates, manages, supports other elephant software needs including Microsoft and Sun, no I don't think Oracle is included. Here is what we do, the application main database runs on Oracle, and reports runs on SQL Server or both runs on SQL Server. The customer makes the call, the suits meets with the customer every week or month as needed. My current project both ends run on SQL Server. In Oracle we don't use PL/SQL but in SQL Server we use T-SQL, the teams using Oracle and SQL Server use Vault source control, the SQL Server team uses VSS. Yes you can write portable code but you have to plan and pay for skilled data people and give some money to your friends at Data Direct the people who sells the XQUERY tool, they also sell socket level drivers that can help you write portable code. The questions is will your customer pay for it the answer from most elephants no.
Kind regards,
Gift Peddie -
I have been three times in position of database architect responsible for design, implementation and support of multiple databases simultaneously.
The database independent approach never worked, because:
· Some functionality should be implemented inside database using stored procedures unless you agree to suffer from really bad performance
· If you want to right an efficient code almost immediately you need to use vendor’s proprietary SQL extensions
Unfortunately, writing vendor specific database backend has also some cons. If you use two different database vendors your development efforts even not doubled, but tripled. The pain of supporting synchronization of application development, database development itself and testing are equally unbearable.
Let me introduce an approach that could help:
· As early as possible identify vendors that you going to support
· Try to find at least one DBA that is familiar with all of them
· Research the main differences between vendors functionality
· Identify the subset that you will never use because there is no adequate support from other vendors
· Select your primary database development environment. This choice depends on availability of automatic database migration tools from primary vendor to secondary ones.
Try to avoid:
· Hardcoding SQL statements inside application
· Using string builders and/or concatenations
Create database access layer and concentrate all vendor specific database access classes over there.
Develop or use database unit testing system that should allow you to test vendor specific code simultaneously.
Suggested workflow:
· Main database development is performed for primary database vendor
· Automatic database migration tools are used for schema and business logic migration Note: Support of incremental migration by tool is a big plus.
· Required manual tuning is performed after automatic migration
· Parallel database unit testing is a must
Igor Bekelman
Principal Architect
SQL Server Migration Assistant
Disclaimer: The post reflects my personal opinion
-
Ah, yes, "-ability" . The suffix of management and marketing. "Port-ability". "Scal-ability". "Compat-ibility" (eh, close enough).
As opposed to "-tion". The suffix of the worker. "Frustra-tion". "Despera-tion". "Resigna-tion".
James Stover, McDBA -
The most important thing to get portability, (from a theoretical POV as I've never done it myself), is to identify what changes between platforms and stick them in a substitutable section, e.g. a DLL.
For databases, that's the Business Logic Layer, etc, that was mentioned above.
Of course, James's post highlighted just how bad going down this route can be without such a considerable amount of effort much better than anything I could think of, so I'll just say 99% of the time that it's not worth it.
Finally, how far down the portability route should you take it? Multiple databases; multiple OSs; multiple levels of user competence?
-
Our flagship product is currently SQL Server only on the back-end. Occasionally we still get a potential client who "needs" it to work against an Oracle back-end, or MySQL, or...fill in the blank. We used to be open to those requests until I hit upon what is now my standard response to anyone muttering about our "lack of portability." I'll edit it for generics here:
If a business solution is valuable and efficient it's technology is irrelevant.
Basically, then, portability is irrelevant to a large extent if the solution is valuable enough to matter. Any potential client who doesn't grasp that isn't buying the solution for the right reasons and will end up being a problem client. That's bad for them and us.
Having said that I entirely support the advice of earlier posts, and it's been reflected in our architecture since the beginning; data layer isolation is the key to developing with portability in mind. After all, just because you shouldn't need to be portable, doesn't mean you shouldn't strive to have it be possible -- it tends to make for better design decisions anyhow.
But to have an cost-efficient system...portability is a bane for most solutions.
-
I always strive for portability just to make life easier and then use vendor specific code when it makes sense. For example, 90% of SQL will work between SQL Server/Oracle/MySQL in a typical app, but if it's something data intensive and needs some database specific functions, there's no reason to make something slow just to avoid using db specific functions. After all there's usually a reason to use a specific db for a purpose.
I just always try to keep it clean and then optimize for a db where it makes sense. As other posters have mentioned, db code should be in it's own assembly/library so that it can be loaded for the appropriate db.
We've been using iBatis and found it invaluable for this. All the SQL calls are in xml files (don't be scared until you've seen it) and the data access framework converts between that and your Java/.NET/Ruby objects. It's really nice because a dba can see and tweak all the sql calls by just looking at the xml files. Plus, you can use any sql you want including procs and vendor specific sql. And, swapping between SQL Server, Oracle, MySQL whatever just means you write a new set of xml files for that rdbms. It's pretty slick and used in some pretty high load environments.
As far as just app code goes, we've been able to write our Java code pretty cleanly and it runs just fine on the Windows or UNIX servers here without even a recompile and it's pretty fast on the latest JVMs, so I don't think write once run anywhere always means such everywhere :^) although I agree it can mean that. Of course, we keep it nice and simple and don't use J2EE.
-
It comes down to the classic DBA answer, it depends. I've primarily done work for internal applications, code that runs in-house for a company doing X. Platforms I've done that on include SQL Server, Sybase and Oracle. Never once did I seriously consider making my code portable on any of those projects. Why? Usually when I'm building something for a company, it's not a throw-away application. It's going to be there for 5-10 years. Why would I sacrifice the functionality that a CTE might bestow because the MS implementation of CTE's won't work on a Sybase database that I'm never going to be running this code on anyway? We were interviewing people for a position where I work and we were asking a few detailed questions about some query hints in SQL Server. The guy we were interviewing started chiding us for using unportable SQL code. End of interview. We're building SQL Server applications. We use SQL Server code and constructs. Period. To do otherwise is a disservice to the company I support and is just plain stupid.
On the other hand, I was working for a company that wanted to sell it's data. Because of that, we took as generic an approach as possible to our actual data structures. We went to town on our internal code, why not make our own apps performance scream? So, it was a business decision on the structures, not a technical one, but as usual, the technology had to support the business.
So, if I were working for a vendor that wanted to market to Unix platforms as well as Windows or WII or whatever, yeah, I'd go with the generic approach. Almost everywhere else, I'd focus hard on whatever platform I was working on in order to squeeze as much performance, scalability and functionality out of the apps as I could. I sure thought that was my job instead of blind adherence to some sort of dogma.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore RooseveltAuthor of:
SQL Server Execution Plans
SQL Server Query Performance Tuning -
"Write once, suck everywhere" is an excellent line.
I'm on the other end on the portability issue. We run mostly software purchased from other companies and most of the problems we run into can be traced (in my opinion) to their attempts to achieve portability - ultimately at our expense.
I'm convinced that we are the only shop running one of our larger apps on SQL Server. Not a single where clause in the entire application.
I think if customers wisened up and purchased software that was blatantly optimized for a particular environment, perhaps that would swing the tendency to shoot for portability.
-
I know we focus on db's here, but I think there is more call for browser portability these days than anything, and look at the pain that can result from that!
As far as databases achieving portability, I've seen two choices made. One is to code to the lowest common denominator (which used to be MySQL) and stick to CRUD. The other is to have the data layer mentioned earlier and each db get's it's own implementation optimized for the platform.
I used a system at a previous job that was platform independent, and one thing they planned for was that not all db's might support case insensitivity. So if you needed the ability to search a column, you had to define a second column with two underscores appended (so you'd have ADDRESS, and ADDRESS__) and their data layer would auto populate the latter column with an upper case version of the former. Hows that for improving performance?
I often tell students you have the choice; use your database as a data store and avoid all but the most basic TSQL, or use it as a platform (CLR, SSIS, non ANSI SQL, etc) and use the hell out of it. Just depends on your situation.
-
I'd agree with Mr Buchan, except for one thing. There's another rule in selling anything. The Customer is always right. They may end up being a problem customer, maybe not, any in many cases, it may be that they're buying an app for the wrong reasons if a specific DB backend is required, but maybe not. I think it's a little presumptuous to claim that you'll always know more of what the customer needs than they do. Often you will, but always?
It's certainly possible to write DB agnostic code. And it can be done using where clauses. I'd throw out the idea that any significant SQL based app that is found to not contain any where clauses was poorly architected from the start.
Finally, if you've got a big enough dev shop, by all means, hire DB specific DBA's and optimize the crap out of each supported platform. But if you work in a small shop, with limited bandwidth and funding and hefty requirements, you have to leverage code in as many ways as possible.
In the end, sometimes, you have to +have+ a product first, in order to make enough money to build the product you really want to sell.
-
For our software if you don't support mutiple DBMSs your out of business. We can't make sales in an Oracle or DB2 shop selling products that only run on SQL Server.
In our case where we market business solutions we have to be database neutral. Once we've convinced the business manager to purchase our products we don't need the IT department to kill the sale because they don't want to support the "wrong" DBMS.
This makes perfect sense from their perspective, why buy more license keys, learn another DBMS, etc when you already have huge investments in Oracle, DB2, Sybase, or SQL server.
When your selling off the shelf business solutions DBMS neutrality makes it easy to say "yes" during the sales process.
Doug -
I think... It depends. Are you making the back-end portable or the front-end? Or both? Are you a Windows-only shop or mixed environment? What are the business needs? If you can get away with a single platform solution, then by all means, take advantage full of the tools available on the target platform. If you have clients running Macs, Sun, Linux, Windows, OS/2 (....) then portability makes it possible to service them. Maybe you can make the presentation layer portable (think AJAX and the like) and live with one database (platform).
Making the back-end cross-platform is hard to justify, though. Find the one that does the job BEST and stick with it. As mentioned above, if the technology meets the need (well), they'll go with the platform required. Or they can go somewhere else (and probably not be as well served).
-
I've dealt with vendor apps in the healthcare industry for several years and my experience with apps written to cross DBMS platforms is: (drum roll) they suuuuuuuuuck. Shop-Vac city. Every last one of them. "Portable code" I've seen does stuff like
- Rather than use an IDENTITY constraint, they use a table to keep track of the last ID entered in each table.
- "What do you mean the SA password has to be blank for the app to work...?"
- "We've confirmed that a single report query with no filter criteria can bring down the system. We encourage you train your users not to run that query."
- The list goes on and on and on...
ANSI compliant does not necessarily mean good code.
The bottom line: I'm sure the "native" platform that the app was written for will run great, but unless a veteran team dedicated to optimizing the code on [the particular target platform] does the port, the app is doomed to suck.
(Of course, software developers in the healthcare industry are completely substandard and IMHO can be put to shame by code written by trained chimps.) Harsh? Any DBA in the healthcare industry that deals with 3rd party apps will agree with me. There are still healthcare software vendors out there struggling to port their apps to use SQL 2000.
-
I know we focus on db's here, but I think there is more call for browser portability these days than anything, and look at the pain that can result from that!
Hello Andy,
I agree that browser portability is more useful than database portability.
I've worked with one application that implemented database portability - that is, to support Oracle and SQL Server requiring only a page or two of changes to the settings. But since we never moved to Oracle, all of that Oracle code throughout the application never served any purpose. I think that the vendor built the product that way so that they could sell one version to everyone. But it made a lot of the code harder to read and maintain. It would have been better to write two separate versions and sell the appropriate one based on the database a customer was using.
I see that as fundamentally different from making web applications with cross-browser support (if I correctly understood your comment above). In the database case, any given customer will likely have one large DBMS (such as Oracle or SQL Server) devoted to the application and is unlikely to change that DBMS without a lot of effort and discussion. They will be fairly predictable in that regard.
On the other hand, with web browsers, the code could be serving pages to 5 different browsers on 5 consecutive requests. Because of that unpredictability, cross-browser portability is absolutely important. So whereas a lot of the anticipated portability for database applications will be wasted (assuming the writers try to stuff all of the portability into one version), most of it in web applications is essential.
Those are my two cents on the issue.
Thanks,
webrunner
-------------------
A SQL query walks into a bar and sees two tables. He walks up to them and asks, "Can I join you?"
Ref.: http://tkyte.blogspot.com/2009/02/sql-joke.html
Viewing 15 posts - 1 through 15 (of 21 total)
You must be logged in to reply to this topic. Login to reply