Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Portability


Portability

Author
Message
Steve Jones
Steve Jones
SSC-Dedicated
SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)

Group: Administrators
Points: 36113 Visits: 18742
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.

Gift Peddie
Gift Peddie
Hall of Fame
Hall of Fame (3.5K reputation)Hall of Fame (3.5K reputation)Hall of Fame (3.5K reputation)Hall of Fame (3.5K reputation)Hall of Fame (3.5K reputation)Hall of Fame (3.5K reputation)Hall of Fame (3.5K reputation)Hall of Fame (3.5K reputation)

Group: General Forum Members
Points: 3452 Visits: 14456

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
igor bekelman
igor bekelman
SSC Rookie
SSC Rookie (30 reputation)SSC Rookie (30 reputation)SSC Rookie (30 reputation)SSC Rookie (30 reputation)SSC Rookie (30 reputation)SSC Rookie (30 reputation)SSC Rookie (30 reputation)SSC Rookie (30 reputation)

Group: General Forum Members
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


James Stover
James Stover
SSC Veteran
SSC Veteran (279 reputation)SSC Veteran (279 reputation)SSC Veteran (279 reputation)SSC Veteran (279 reputation)SSC Veteran (279 reputation)SSC Veteran (279 reputation)SSC Veteran (279 reputation)SSC Veteran (279 reputation)

Group: General Forum Members
Points: 279 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

Paul.
Paul.
SSC Veteran
SSC Veteran (207 reputation)SSC Veteran (207 reputation)SSC Veteran (207 reputation)SSC Veteran (207 reputation)SSC Veteran (207 reputation)SSC Veteran (207 reputation)SSC Veteran (207 reputation)SSC Veteran (207 reputation)

Group: General Forum Members
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?



Paul

Frank Buchan
Frank Buchan
SSC Journeyman
SSC Journeyman (89 reputation)SSC Journeyman (89 reputation)SSC Journeyman (89 reputation)SSC Journeyman (89 reputation)SSC Journeyman (89 reputation)SSC Journeyman (89 reputation)SSC Journeyman (89 reputation)SSC Journeyman (89 reputation)

Group: General Forum Members
Points: 89 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.


Samuel Clough
Samuel Clough
SSC Rookie
SSC Rookie (29 reputation)SSC Rookie (29 reputation)SSC Rookie (29 reputation)SSC Rookie (29 reputation)SSC Rookie (29 reputation)SSC Rookie (29 reputation)SSC Rookie (29 reputation)SSC Rookie (29 reputation)

Group: General Forum Members
Points: 29 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.
Grant Fritchey
Grant Fritchey
SSCoach
SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)

Group: General Forum Members
Points: 17586 Visits: 32265

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 and SQL Server Execution Plans
Product Evangelist for Red Gate Software
NULLgarity
NULLgarity
Old Hand
Old Hand (374 reputation)Old Hand (374 reputation)Old Hand (374 reputation)Old Hand (374 reputation)Old Hand (374 reputation)Old Hand (374 reputation)Old Hand (374 reputation)Old Hand (374 reputation)

Group: General Forum Members
Points: 374 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


Andy Warren
Andy Warren
SSCertifiable
SSCertifiable (7.2K reputation)SSCertifiable (7.2K reputation)SSCertifiable (7.2K reputation)SSCertifiable (7.2K reputation)SSCertifiable (7.2K reputation)SSCertifiable (7.2K reputation)SSCertifiable (7.2K reputation)SSCertifiable (7.2K reputation)

Group: Moderators
Points: 7227 Visits: 2679

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.



Andy
SQLAndy - My Blog!
Connect with me on LinkedIn
Follow me on Twitter
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search