Click here to monitor SSC
SQLServerCentral is supported by Redgate
Log in  ::  Register  ::  Not logged in
Home       Members    Calendar    Who's On

Add to briefcase 123»»»

Portability Expand / Collapse
Posted Thursday, May 31, 2007 5:19 PM



Group: Administrators
Last Login: Yesterday @ 8:38 AM
Points: 34,366, Visits: 18,586
Portable Computer

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.

Post #370471
Posted Thursday, May 31, 2007 7:46 PM
Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Monday, January 5, 2015 2:56 PM
Points: 3,428, Visits: 14,456

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
Post #370488
Posted Thursday, May 31, 2007 11:42 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Tuesday, August 3, 2010 9:29 AM
Points: 30, Visits: 13

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




Post #370538
Posted Thursday, May 31, 2007 11:58 PM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Sunday, September 8, 2013 5:39 PM
Points: 263, Visits: 862

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
Post #370543
Posted Friday, June 1, 2007 3:17 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Wednesday, November 18, 2015 10:35 AM
Points: 207, Visits: 971

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?


Post #370578
Posted Friday, June 1, 2007 4:37 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Thursday, April 15, 2010 9:02 AM
Points: 85, Visits: 33

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.

Post #370600
Posted Friday, June 1, 2007 5:03 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Tuesday, May 12, 2009 10:03 AM
Points: 27, Visits: 108
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.
Post #370607
Posted Friday, June 1, 2007 5:32 AM



Group: General Forum Members
Last Login: Yesterday @ 12:17 PM
Points: 17,173, Visits: 32,139

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 Roosevelt
The Scary DBA
Author of:
SQL Server Query Performance Tuning
SQL Server Execution Plans

Product Evangelist for Red Gate Software
Post #370621
Posted Friday, June 1, 2007 6:19 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Wednesday, December 31, 2014 3:08 PM
Points: 368, Visits: 534

"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

Post #370635
Posted Friday, June 1, 2007 6:38 AM


Group: Moderators
Last Login: Yesterday @ 1:07 PM
Points: 7,062, Visits: 2,667

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.


SQLAndy - My Blog!
Connect with me on LinkedIn
Follow me on Twitter
Post #370639
« Prev Topic | Next Topic »

Add to briefcase 123»»»

Permissions Expand / Collapse