Several of the trends at play in programming for the oil-and-gas sector—industrial consolidation; aging, outsourced, and slimmed-down IT staffs; expanded governmental reporting requirements; re-purposed and aging refineries and other facilities; and even the sectoral shift in the United States to natural gas production—work at cross purposes. It's typical for a division to have to report on its operations quickly at the same time as it assimilates an acquisition and loses senior IT developers. SQL Relay eases one small aspect of these conflicts, the one having to do with database access.
A decade ago, the focus of IT was often on massively ambitious enterprise-wide roll-outs. More typical now is development which looks to leverage and integrate legacy operations, often by way of Web-based applications, and to do it on a tight budget. At the same time, though, jobs must be completed with slimmed-down staff and beefed-up requirements for security and "auditability," because of federal or state statutes on data accountability. What looks to the end user like a single application could easily pull together separate databases maintained, for example, for:
- Divisional human resources
- Unit engineering
- Compliance and security
- Site financials
One common outcome of these complex connections is inefficiency in development, testing, deployment, and operation: Each staff programmer must program around peculiarities of each database manager, including security arrangements, proxying through different subnetworks, load-balancing, and so on. Think, for instance, what happens on the IT side when, as has happened so often lately, a domestic facility escapes negative refining margins by conversion to a terminal facility. A development team will likely be pulled together that is experienced in storage or distribution user interfaces, probably exposed through the browser. The members of the team have no background, though, in the existing data-storage infrastructure of the site, even though they're expected to build atop it. This inevitably leads to a combinatoric explosion of database details needed by each developer or tester before he or she can begin to work with meaningful results.
With the Open Source SQL Relay product, though
below, for details), all database access
can be managed in a unified and consistent way. An
individual programmer, for example, need not install
separate command-line clients for Sybase, DB2, Postgres,
Oracle, and so on, as part of the toolchain for his or her
daily work (see
sidebar). Instead, a single lightweight, unencumbered
sqlrsh is enough to connect
to the whole range of back ends that SQL Relay manages.
For sophisticated problems, of course, there's still enormous value in the developer toolboxes specific to each database manager. My team's experience, though, is that there are many situations in which an individual programmer needs consistency, simplicity, and reliability more than a "deep" development environment. SQL Relay allows one "liaison" from development to configure all database accesses in a standard way that is convenient for the team as a whole and collapses the combinatorics, giving the team the chance to focus on the algorithms and interfaces where it truly adds value.
Understand clearly that, for most organizations, both the costs and benefits of SQL Relay will be modest. Developers will eventually get their work done even without centralized access configuration, plenty of other products provide connection pooling, and so on. SQL Relay is dispensable, and not a radical "game-changer" in the way an Enterprise Resource Planning (ERP) or Customer Relations Management (CRM) system is.
On the other hand, it is so easy to install and begin to use SQL Relay, and it is so efficient and reliable, that it invites casual experimentation. Start by setting up one connection to one database source; you can continue to use all of your normal access methods at the same time as you stir SQL Relay into the mix. Incrementally move developers to an SQL Relay connection and away from reliance on personal accounts with the native database. You'll soon have an idea how SQL Relay works in your circumstances.
Example use cases
Match impedance to long-cycle database administration
Databases are serious things: They're vaults of what might be an organization's most precious assets. For this reason, access to them is and should be tightly controlled.
This fact interferes with developers' "agility" and impatience, of course. Database administration might have a week-long process for adding one new account, even if the purpose of that account is to allow a new maintenance developer to test a five-minute fix in an existing application.
SQL Relay can help bring both sides together. Arrange for a single "development" account in the native database; that's an arrangement likely to make sense to the database administrators. With SQL Relay, though, you can expose that access to as many or as few of your team members as you choose—SQL Relay controls access by, among other methods, client account and client IP address. You can open or close access at developer speed.
Missing native clients
Suppose you host your Web application on a "minority" operating system; you might rely on OpenBSD for its security, for example. Your next application involves datasources originally developed with Microsoft® Access, though. How do you effectively ship data from the latter to the former?
As it happens, there are several distinct possibilities. For my team, however, the quickest way to resolve the question and return developers to their daily work is to interpose SQL Relay. SQL Relay connects to all of our datasources and has good client libraries on all operating systems.
Isolate teams from administrative "churn"
Especially when dealing with legacy systems, we've sometimes had surprises in database access: catastrophic loss of hardware whose recovery involved unexpected drama, managerial reorganizations in database systems, and so on. SQL Relay has helped us get through these episodes. Rather than each developer having to track changes in database names, password resets, and so on, one SQL Relay "liaison" takes responsibility for maintenance of a consistent view, from the developers' perspective.
Applicability: platforms, licensing, and related considerations
While almost all of my own use of SQL Relay has been on Debian-based Linux®, SQL Relay apparently works quite well on all major Unix®es. For-profit company firstworks takes the lead on development and maintenance of the product (see Resources). I've found firstworks responsive when presented with fault reports.
Although it is also possible to host SQL Relay under Windows®, this alternative is less energetically supported. It requires Cygwin, moreover. SQL Relay itself is written in C++, but also builds in native client application programming interfaces (APIs) for C, Java™, Perl, Python, the Zope application framework, Ruby, PHP, and Tcl. Drivers are also available for such language-specific abstraction libraries as Perl's DBI.
SQL Relay is distributed under the General Public License (GPL). Roughly, this means there's no charge and no restriction on use within an organization.
Currently supported database sources include DB2, Interbase, Jet, mSQL, MySQL, PostgreSQL, ODBC, Oracle, SQLite, SQL Server, and Sybase.
Several products advertise features that overlap those of SQL Relay. There are, for example, specific libraries that manage connection pooling within a specific development language. These tools are often very lightweight—solving the problems of a far narrower niche than SQL Relay's—and correspondingly limited in their capabilities.
The IBM DB2® database manager takes an opposite approach: With its "federation" mechanisms, it unites access to a range of database back ends so that they're all uniformly viewed as DB2 objects. DB2 gives fine-grained control over performance and security characteristics of database access—and demands correspondingly careful attention. For a demanding production environment, DB2 provides all the advantages of enterprise-level support availability, high-end scalability, complete documentation, and so on, that keep SQL Relay in the latter's "lightweight" and "agile" niche.
SQL Relay has other faults and limitations. It is not perfect software, of course, even within the bounds of its goal. Specific releases have had such difficulties as:
- A parsing error that left configuration files with only a single database connection unusable
- A segfault that resulted when a specific logfile had wrong permissions
- Mishandled money results for certain vendor-specific built-in database types
- A poorly reproducible, sporadic error on complex Oracle queries
- Default settings for Oracle retrievals that used far too much memory for our circumstances
- Limits on nesting of Sybase queries when using the no-charge client Sybase library
A list of errors that long will alarm some readers. Our own experience is that workarounds have been relatively painless for our SQL Relay issues, and the benefits of SQL Relay far exceed the effort needed to identify and implement them. Successive releases do fix errors that matter to us, and, of course, the product as a whole is open source, so it's straightforward to correct at least some of the errors for ourselves.
We almost always run SQL Relay on dedicated virtual machines, to match our security requirements. If mishandled, SQL Relay creates a new single-point-of-failure weak-link in an application architecture, one that consumes extra CPU cycles and introduces delays and errors in database retrieval. For us, though, SQL Relay improves overall performance, simplifies development, testing, and deployment, and strengthens our security model.
There's actually much more to SQL Relay than this introduction can cover, including such topics as:
- Use of SQL Relay with replicated databases
- Security refinements such as record-level locking
- Configuration tuning for performance
The main goal of this article, though, is to illustrate how simple and appropriate it might be for you to begin to use SQL Relay on your own. When it begins to pay off for you, you'll be in a better position to judge how you can use the advanced features of SQL Relay.
SQL Relay has the potential to consolidate and streamline typical architectures involved in application development for oil-and-gas operations. It's a lightweight tool that centralizes management of database connection pooling, accounting, and security.
- SQL Relay is a SourceForge project which provides "... database connection pooling, proxying and load balancing ..." While SQL Relay is open source, much of its maintenance is undertaken by for-profit firstworks.
- "Fundamentals of IBM DB2 Federated Server and Relational Connect" (PDF) is a manual to use DB2 as a federating database which supports such back ends as Oracle, Sybase, and SQL Server.
- Zope is a Python-oriented application server for which SQL Relay offers an adapter.
- The developerWorks Web development zone specializes in articles covering various Web-based solutions.
- The developerWorks Open source zone specializes in extensive how-to information, tools, and project updates to help you develop with open source technologies and use them with IBM products.
Get products and technologies
- Download IBM product evaluation versions or explore the online trials in the IBM SOA Sandbox and get your hands on application development tools and middleware products from DB2®, Lotus®, Rational®, Tivoli®, and WebSphere®.