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.
Follow me on Twitter: @way0utwestForum Etiquette: How to post data/code on a forum to get the best helpMy Blog: www.voiceofthedba.com
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.
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.
· 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
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".
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.
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.
"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.
Blog | Twitter | LinkedIn
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.