Friday, April 7, 2017

TLS/SSL Encryption with MS SQL Server and FreeTDS

Introduction

This tutorial walks through enabling TLS/SSL encryption between an application and an Microsoft SQL Server 2014 database, including basic encryption, certificate validation, and common name validation.

Unfortunately MS SQL Server doesn't support mutual authentication via TLS/SSL; that is, the database cannot validate the application's certficate and common name. This is just as well though, as there is also no obvious way to configure FreeTDS to use a private key and certificate on the client-side.


Assumptions

For the purposes of this tutorial, the following assumptions are made:

  • An MS SQL Server 2014 database is running on a Windows system named db.firstworks.com.
  • The database contains an instance named EXAMPLEDB which is accessible using exampleuser/examplepass credentials.
  • An application which accesses the database is installed on a linux server named app.firstworks.com and currently configured to access the database without encryption.
  • The application uses FreeTDS to access the database.
  • A private key and certificate for the database don't currently exist, but will be created, and will be named db.key and db.crt.
  • A certificate chain for the certificate authority which will sign db.crt exists, named ca.pem.

Basic Encryption

First, we will configure basic TLS/SSL encryption of the communications between the database and application server.


Database Server Configuration

By default, MS SQL Server 2015 supports encryption, and enables it if the application so requests. We can configure the database to require encrypted connections, though, as follows.

  • Open the Start menu.
  • Select the Microsoft SQL Server 2014 program group.
  • Select the SQL Server Configuration Manager.
  • Expand SQL Server Network Configuration.
  • Right-Click Protocols for EXAMPLEDB and select Properties.
  • Select the Flags Tab.
  • Change Force Encryption to Yes.
  • Click OK.
  • Click OK.
  • Exit the SQL Server Configuration Manager.

Restart the database server.

  • Press Windows-key-R.
  • Type "services.msc" and hit return.
  • Scroll down to SQL Server (EXAMPLEDB) and select it.
  • Click Restart the service.

That's all on the database server.


Application Server Configuration

MS SQL Server speaks versions 7.0 - 7.4 of the TDS (Tabular Data Stream) protocol. Version 7.0 doesn't support encryption, but versions 7.1 and higher do.

FreeTDS requests encryption when it's configured to use TDS version 7.1 or higher. Just to be sure, though, we can also configure the connection to require encryption, rather than just request it.

Edit the FreeTDS configuration file (usually /etc/freetds.conf), find the server entry that you use to connect to the EXAMPLEDB, verify that the TDS version is set to 7.1 or higher, and add a line to require encryption, as follows:

[EXAMPLEDB]
 host = db.firstworks.com
 port = 1433
 tds version = 7.3
 encryption = require

Note that in this example, "tds version" is set to 7.3. I have had trouble with 7.4 resulting in "Unexpected EOF from the server" errors. Your mileage may vary.

That's all on the application server.


Testing With tsql

Connect to the database from the application server using the FreeTDS-supplied tsql program as follows:

tsql -S EXAMPLEDB -U exampleuser -P examplepass

Unfortunately there is no easy way to verify that the connection is encrypted. If you access the database as the "sa" user, then you can run:

1> select encrypt_option from sys.dm_exec_connections where session_id = @@SPID
2> go
encrypt_option
TRUE
(1 row affected)

But non-admin users don't typically have access to sys.dm_exec_connections.

Another way to verify that the connection is encrypted is to run tsql with TDSDUMP enabled:

TDSDUMP=stdout tsql -S EXAMPLEDB -U exampleuser -P examplepass

If you see lines in the output like:

  REC[0x55a2216316b0]: Decrypted Packet[1] Application Data(23) with length: 421

then the connection is encrypted.

If the connection fails, it will most likely fail with:

  • Error 20017 (severity 9): Unexpected EOF from the server
    • The server didn't like something about the protocol and hung up.
    • Verify that you are using tds version 7.1 - 7.3 in your freetds.conf server entry.

Updating Your Application

Your application shouldn't actually require any changes. As long as it is configured to use the EXAMPLEDB server entry, all communications between it and the database will be encrypted.


Adding Database Certificate Validation

So far, we have encrypted communications between the application and the database, but we can make the application more secure by installing a certificate on the database server, and configuring the application to validate the certificate.


Database Server Configuration

The database server must have has host name "db" and primary DNS suffix "firstworks.com". But, Windows servers are commonly configured without a primary DNS suffix.

To set the primary DNS suffix:

  • Press Windows-key-R.
  • Type "control panel" and hit return.
  • Click System and Security.
  • Click System.
  • Under "Computer name, domain name, and workgroup settings", click Change settings.
  • Next to "To rename this computer or change its domain or workgroup, click Change", click Change.
  • Click More.
  • Under "Primary DNS suffix of this computer" enter "firstworks.com".
  • Click OK.
  • Click OK.
  • Click OK.
  • Reboot the database server.

Generate the certificate for the database server.

The certificate can be generated by a well-known certificate authority, generated by an in-house certificate authority, or can even be self-signed.

In any case, MS SQL Server places some specific requirements on the certificate:

  • The common name MUST match the fully qualified domain name of the database server. In this case: db.firstworks.com.
  • The Enhanced Key Usage property MUST include Server Authentication.
    • In openssl terms, the extendedKeyUsage extension must include serverAuth in the extfile used to generate the certificate signing request.

Combine this certificate, its associated private key, and certificate for the certificate authority that signed it into a PKCS#12 certificate chain file named db.pfk.

There are various ways to do this, but with openssl you can run:

PASSWORD="" openssl pkcs12 -export -passout env:PASSWORD -in db.crt -inkey db.key -certfile ca.crt -out db.pfx

When the pfk is created, it MUST be created with KeySpec set to AT_KEYEXCHANGE. Or, in openssl terms, the -keyext option must be used when creating the pfk file. Actually though, -keyext is the default, so just DON'T create the pfk file using -keysig.


The C:\ProgramData\Microsoft\Crypto\RSA\MachineKeys folder often has the wrong permissions. Verify that both Administrators and Everyone have Full Control. If they don't, then give them both Full Control. You may get an error when you do this, indicating that permissions couldn't be changed on an individual file, but this seems safe to ignore.


Now that the prerequisites are taken care of...


Copy db.pfk to the database server, and install it in the Windows certificate store:

  • Press Windows-key-R.
  • Type "mmc" and hit return.
  • Click File.
  • Click Add/Remove Snap-in.
  • From Available snap-ins, double-click Certificates.
  • Select Computer Account.
  • Click Next.
  • Select Local Computer.
  • Click Finish.
  • Click OK.
  • Expand Certificates (Local Computer).
  • Right-Click Personal.
  • Click All Tasks.
  • Click Import...
  • Click Next.
  • Click Browse.
  • Select All Files *.* from the file-types pulldown.
  • Select db.pfk
  • Click OK.
  • Enter a private key password, if your private key has one.
  • Click Next.
  • Click Next.
  • Click Finish.
  • Exit
  • Don't save settings.

Configure MS SQL Server to use the certificate.

  • Open the Start menu.
  • Select the Microsoft SQL Server 2014 program group.
  • Select the SQL Server Configuration Manager.
  • Expand SQL Server Network Configuration.
  • Right-Click Protocols for EXAMPLEDB and select Properties.
  • Select the Certificates Tab.
  • Select db.firstworks.com from the pulldown list.
    • If db.firstworks.com is missing from the list, then either:
      • The common name in the certificate is something other than "db.firstworks.com".
        • The best solution is to generate a new certificate.
      • The database server's host name is something other than "db".
        • See instructions above for setting the host name.
      • The database server's primary DNS suffix is something other than "firstworks.com".
        • See instructions above for setting the primary DNS suffix.
  • Click OK.
  • Click OK.
  • Exit the SQL Server Configuration Manager.

Restart the database server.

  • Press Windows-key-R.
  • Type "services.msc" and hit return.
  • Scroll down to SQL Server (EXAMPLEDB) and select it.
  • Click Restart the service.
    • If the service fails to start, then either:
      • The Enhanced Key Usage property of the certificate is missing or doesn't include Server Authentication.
        • Generate a new certificate with the parameter set correctly.
        • Replace the bad certificate.
        • Restart SQL Server (EXAMPLEDB)
      • C:\ProgramData\Microsoft\Crypto\RSA\MachineKeys has the wrong permissions.
        • See instructions above for setting the permissions.
        • Restart SQL Server (EXAMPLEDB)
  • Exit the Services manager.

That's all on the database server.


Application Server Configuration

Configuring the application server is much simpler.

Copy ca.pem to the application server and install it in a convenient place, like /etc.

FreeTDS will validate the database's certificate if it is provided with a certificate chain to validate it against.

On the application server, edit the FreeTDS configuration file (usually /etc/freetds.conf), find the server entry that you use to connect to the EXAMPLEDB, and add a ca file line, as follows:

[EXAMPLEDB]
 host = db.firstworks.com
 port = 1433
 tds version = 7.3
 encryption = require
 ca file = /etc/ca.pem
 check certificate hostname = no

Note that "check certificate hostname" is set to no. At this point, we're just validating that the database server is a host that we trust. We're not worried about whether it's the exact host we intend to be talking to.

That's all on the application server.


Testing With tsql

Connect to the database from the application server using the FreeTDS-supplied tsql program as follows:

tsql -S EXAMPLEDB -U exampleuser -P examplepass

If the connection fails, it will most likely fail with:

  • Error 20002 (severity 9): Adaptive Server connection failed

Which isn't very helpful. Running tsql with TDSDUMP enabled...

TDSDUMP=stdout tsql -S EXAMPLEDB -U exampleuser -P examplepass

...may be slightly more revealing.

It may say:

tls.c:484:handshake failed: Error in the certificate.

That usually means that the ca.pem's certificate authority didn't sign the database's certificate. Double-check it.

If it also says something like this:

tls.c:368:Certificate hostname does not match

Then "check certificate hostname" is probably misspelled.


Adding Database Certificate Common Name Validation

So far, we are encrypting communications between the application and the database and validating the database server's certificate, but we can also make the application more secure by validating the common name in the database server's certificate.

This is actually pretty simple. In fact we had to do additional work to disable it earlier.

On the application server, modify /etc/freetds.conf and set "check certificate hostname" to yes.

[EXAMPLEDB]
 host = db.firstworks.com
 port = 1433
 tds version = 7.3
 encryption = require
 ca file = /etc/ca.pem
 check certificate hostname = yes

Or, optionally just remove the parameter altogether, as checking the certificate's common name is the default behavior.

You can test the configuration using tsql:

tsql -S EXAMPLEDB -U exampleuser -P examplepass

Again, it may fail with the not-so-helpful:

  • Error 20002 (severity 9): Adaptive Server connection failed

Running tsql with TDSDUMP enabled...

TDSDUMP=stdout tsql -S EXAMPLEDB -U exampleuser -P examplepass

...might be more revealing.

It may say:

...
tls.c:368:Certificate hostname does not match
...
tls.c:484:handshake failed: Error in the certificate.
...

This means that the common name in the database's certificate didn't match the host parameter in the freetds.conf file.

  • Verify the common name in the freetds.conf file and in the database's certificate.
    • If the host name in the freetds.conf file is wrong then change it and try again.
    • If the common name in the certificate is wrong, then:
      • Ideally the certificate should be regenerated and re-deployed.
      • If that's not an option, then you can add a DNS entry (or /etc/hosts entry) for the name that is in the certificate, and update the freetds.conf file with that name.

Thursday, April 6, 2017

Oracle TLS/SSL Encryption Tutorials

So, you want to secure communications between an application (such as SQL Relay) and an Oracle database with TLS/SSL...

Most TLS/SSL-capable servers are secured by generating a private key, getting a certificate, and adding a few lines to a config file to tell the server the locations of the cert and key files. Of course, if you generate the certificate yourself, then you also have to tell the server about your fake certificate authority. But, for most apps, that's it. Three parameters in a file somewhere: cert, key, and ca.

With Oracle, the idea is similar, but the details are very different.

Rather than storing certificates and keys in files and providing the paths to them in a config file, you store them in an Oracle "wallet" and provide the path to the wallet.

While Oracle wallets are more secure than flat files, the security features introduce some interesting quirks that are worth reviewing before attempting an implementation.

When you're ready to take the plunge, tutorials follow:

Friday, February 24, 2017

SQL Relay 1.0.1 Release Announcement

Version 1.0.1 of SQL Relay, the powerful connection pool, load balancer, query router, and database proxy, is now available.

This release features lots of small improvements, mainly for issues discovered during attempts to make it compliant with Redhat's packaging guidelines, and for a set of issues reported by users.



Thread Fix

This is fairly important.

There was a long-standing bug in the sqlr-listener process that caused multiple threads to access the same variable, without coordination. The symptom was that the sqlr-listener process might crash, randomly, every few days. The variable was a buffer used to build log/notification messages, so the error could only occur if logging or notifications were configured, but it could happen if there were logger or notifications tags in the config file, even if the modules themselves were disabled. The workaround was to comment out the entire logger and notification tag groups, or to configure the sqlr-listener to be multi-process instead of multi-thread (using sessionhandler="process").

None of that is necessary now though. The bug is fixed. Local variables are used to build the messages in the parts of the sqlr-listener that can be run by multiple threads.

sqlrsh Fixes

You can now hit ctrl-D to abort sqlrsh without generating a segfault. Also, errors are now printed to stderr and if an error occurs (in non-interactive mode) then sqlrsh exits with status 1 instead of 0.

ODBC Bug

1.0.0 featured an ODBC fix which itself created a new bug. Between the two, the first line of the result set could be duplicated or skipped. Both bugs should be fixed now.

PDO on 32-bit Systems

The PHP PDO driver compiles correctly for PHP 7 on 32-bit systems now.

Ruby Updates

Ruby semi-recently consolidated Fixnum and Bignum classes into the Integer class. The Ruby API has been updated to support this change.

Router Sequence Fix

There was a bug that made it necessary to start all of the instances that a router instance would route to prior to starting the router itself. Eg. if you had 2 instances: routermaster and routerslave, and a third instance: router, then routermaster and routerslave had to be started prior to router. This wouldn't always happen if the instances were configured to start at boot though. Now, they can be started in any order.

Normalize Translation

Fixed a bug in the "normalize" query translation that caused it to remove spaces before unary ! and - when they followed a keyword. For example, it will now return "select !a,!b from mytable" instead of "select!a,!b from mytable".

Upates to the Drop-in Replacement Libraries

As recommended by rpmlint, the .so links aren't installed for these libraries any more. Now, the major version must be included in the LD_PRELOAD command. Eg: LD_PRELOAD=/usr/local/firstworks/lib/libmysql51sqlrelay.so.4

The Drop-in Replacement Library for MySQL now pays attention to the SQLR_MYSQL_MAP_DATE_TO_DATETIME environment variable. If it's set to "yes" then DATE types are mapped to DATETIME. This is useful if you're mapping a MySQL app to an Oracle database, have NLS_DATE_FORMAT set to include the time, and your app truncates the time if the type of the column is DATE instead of DATETIME.

configure Script Options

The --disable-wall and --disable-werror options have been added to the configure script to work around quirks on systems who's header files generate warnings or particular errors.

The --with-abs-max-connections option has been added to set the absolute maximum number of connections that can be configured at compile time. It defaults to 4096, up from 512 in previous releases.

The --with-fortify option has been added to enable -O -D_FORTIFY_SOURCE=2 -fstack-protector-strong compiler options.

The --enable-oracle-at-runtime (and similar options for sap, db2, and informix) have been added to enable runtime loading of the database libraries rather than linking them in at compile time.

Error Messages

Various error messages have been improved to suggest solutions.

Erlang Reference

An API reference exists for the Erlang API now.

Cache Manager

The mysterious sqlr-cachemanager process isn't enabled by default any more. This process manages the client-side result set cache, only needs to be run on SQL Relay clients, and only if an app caches result sets. As such, it is installed when building from source, but isn't enabled by default. Also, in the RPM distribution it has been split out into its own RPM. The docs have also been updated to explain when to run the process.

"run", "log", and "cache" directories and file names

This is more of an internal change, but...

In the past there were various directories under .../var/sqlrelay/tmp that contained files used at runtime by the various instances of SQL Relay. These have been consolidated into .../var/run/sqlrelay. The "log" directory has been changed from .../var/sqlrelay/log to .../var/log/sqlrelay. The "cache" directory has been changed from .../var/sqlrelay/cache to .../var/cache/sqlrelay. These changes were motivated by Redhat packaging guidelines, but I think they have roots in the Linux Standard Base.

Sockets created by the sqlr-listener process to communicate with the sqlr-connection processes now have .sock extensions, and 700 permissions rather than erroneous 711 permissions). Files used by ftok() to get IPC ids now have .ipc extensions. "Up-down" files used to indicate whether the database is up now have .up extensions. The sockseq file is no longer used. Instead, the file names of sockets used to resume suspended sessions are based on the process id of the sqlr-connection process.

Full Changelog follows:

  • updated sqlr-bench to create "db"-bench.png
  • added edoc-generated API docs for the Erlang API
  • updated some module-load error verbage
  • added initial support for loading Oracle, SAP/Sybase, DB2, and Informix libraries on-demand at runtime
  • overhauled spec file
  • fixed PDO driver for PHP 7 on 32-bit platforms
  • added permissions to all open() with O_CREAT calls
  • added --with-fortify configure option
  • fixed a segfault on ctrl-D in sqlrsh fixed uncoordinated access to shared debugstr among listener threads by using local instances instead
  • added ping-loop to router connection module's logIn() method to manage cases where routed-to instances are started after the router
  • moved .../var/sqlrelay/tmp/*/* to .../var/run/sqlrelay
  • added .sock extension to sockets, .ipc extension to ipc key files, .pid extension to pid files, and .up extension to files which indicate that the db is up
  • changed mode of listener-connection sockets to 700 from 711
  • moved .../var/sqlrelay/log to .../var/log/sqlrelay moved .../var/sqlrelay/cache to .../var/cache/sqlrelay
  • libmysql*sqlrelay.so and libpqsqlrelay.so are no longer installed
  • replaced sockseq-generated unix socket with pid-based unix socket
  • updated ruby api to support unification of Fixnum and Bignum classes into Integer class
  • added --with-abs-max-connections configure script parameter
  • increased default absolute max connections to 4096 (from 512)
  • updated "can't open ... file" errors to mention user/group
  • added SQLR_MYSQL_MAP_DATE_TO_DATETIME option to mysql drop-in replacement library
  • fixed normalize translation not to remove spaces before unary - and !
  • updated sqlrsh to set error code correctly and write errors to stderr
  • sqlr-cachemanager is no longer enabled at boot by default
  • fixed another subtle bug in the ODBC driver that caused the first row to be duplicated if columns were not bound
  • added --disable-wall and --disable-werror configure options

Rudiments 1.0.4 Release Announcement

Version 1.0.4 of Rudiments, the C++ class library for developing systems and applications, is now available.


This release primarily replaces the dependency on readline with a dependency on libedit. I'd used readline directly in SQL Relay for years and one day decided it would be better if it were pushed up into Rudiments as a prompt class. However, I didn't notice that readline was GPL rather than LGPL, so using rudiments with readline wasn't necessarily GPL compliant. Actually, I guess it would only not be compliant if the application it was ultimately linked into wasn't a GPL application... But, to eschew any such doubts, Rudiments now uses libedit (which has a BSD license) instead of readline.

Some other, extremely obscure, features were added too.

Full Changelog follows.

  • added support for EOT to codetree
  • added loop-avoidance to xmldomevents class
  • added alias support to codetree class
  • replaced readline with libedit
  • included implementation of history_truncate_file for versions of libedit that don't provide it
  • added --disable-wall and --disable-werror configure options

Sunday, January 22, 2017

Rudiments 1.0.3 Release Announcement

Version 1.0.3 of Rudiments, the C++ class library for developing systems and applications, is now available.


This release mainly fixes some packaging issues, and a really subtle issues in the prompt class. The file::open() method also fails with EINVAL if it is used to create a file but no permissions are given, which would otherwise cause the method to crash on systems where FORTIFY_SOURCE is used. No pressing need to update unless these issues sound familiar.

Sunday, December 25, 2016

Rudiments 1.0.2 Release Announcement

Version 1.0.2 of Rudiments, the C++ class library for developing systems and applications, is now available.


There was actually an unannounced 1.0.1 as well. 1.0.1 mainly contained fixes for OpenSSL 1.1.x. 1.0.2 just adds another SSL tweak that's required by Fedora for packaging. No pressing need to upgrade unless you're having trouble building on a platform that uses OpenSSL 1.1.x.

Wednesday, December 21, 2016

SQL Relay 1.0.0 Benchmarks

Introduction

There are a lot of reasons to use SQL Relay, but one of the most popular is just to improve throughput, usually measured in queries-per-second.

Logging in to most databases takes a noticable amount of time...

So much time, in fact, that transient apps (such as web-based applications) which start up, connect, query, disconnect, and shut down to deliver each bit of content, often take longer to log in than to run the queries. As a result, overall throughput (queries-per-second) is generally low unless many queries are run per connection.

SQL Relay maintains a pool of already-logged-in database connections, and logging in to a connection pool is substantially faster than logging into the database directly.

As a result, overall throughput (queries-per-second) should be maximized, and shouldn't depend nearly as much on the number of queries that are run per connection.

In theory.

Anecdotally this is the case and informal testing has always confirmed it. But, I wanted hard data and pretty graphs to prove it. Also, it had been a long time since I'd done any formal performance testing. For all I knew, I'd done something recently to make everything run horribly.


sqlr-bench

Of course there are popular tools for benchmarking apps and databases. JBench, for example. But they all use intermediate layers - JDBC, ODBC, web pages... I wanted raw comparisons: the database's native API vs. SQL Relay's native API. I also wanted it to be easy to run from the command-line, and automatable.

Months ago, I'd begun writing a program called "sqlr-bench" which aimed to run a battery of tests against the database directly, run the same tests through SQL Relay, collect stats, and produce a graph. It had to support every database that SQL Relay supported. It needed a dozen or more tunable paramters... It was a fairly ambitious project. I started and stopped work on it several times but I eventually got it working well enough.

It creates a table, populates it, and runs a series of selects. First it runs 1 select per connection, then 2 per connection, then 3, etc. It times all of this and calculates queries-per-second as a function of queries-per-connection. The whole process is done first against SQL Relay, and then directly against the database. The data is then correlated and gnuplot is used to produce a graph.

Various parameters are configurable, but the defaults are:

  • rows: 256
  • colums: 16
  • column size: 32
  • rows to fetch at once: 10
  • samples: 10
  • queries: 30

Initial Results

I ran the tests against each database, and against instances of SQL Relay running on the local machine (over a unix socket).

Theoretically I figured I should see...

  • ...against the database directly: The number of queries-per-second should increase steadily as the number of queries-per-connection increases. It should start off to be pretty bad, then get better and better, and eventually level off so that running more queries-per-connection doesn't make any more difference.
  • ...against SQL Relay: The number of queries-per-second should be independent of the number of queries-per-connection, and should be roughly the same as the maximum number of queries-per-second that can be run against the database directly.

Performance against the database directly was exactly as I expected, and it generally seemed to level off at about 20 queries-per-connection.

SQL Relay actually does have a small ramp-up period (2 or 3 queries per-connection is slower than 4 or more), which I didn't expect, but the number of queries-per-second were otherwise uncorrelated with the number of queries-per-connection, which I did.

Overall though, SQL Relay did not immediately perform as well as I expected. In fact, for most databases, there was a "break-even" between 5 and 10 queries per-connection.

While apps that use SQL Relay would still likely perform better than if run directly against the database (how many web pages run more than 5 queries that return 256 rows each), I really expected it to perform better.


Tweaks

Thus began days of profiling and analyzation. It appeared that, indeed, over the years new features had steadily introduced inefficiencies.

In most cases, things just needed to be reused instead of reallocated. Everything from database cursors to byte arrays. I replaced a few regular expressions with simple string compares, and made a few case-insensitive string compares case-sensitive. There were also some alignment and padding issues that prevented optimized strcpy/memcpy from being used. My socket buffer sizes were sub-optimal...

Lots of little changes.


Results

In the end, I was pleased with the results. Most of them, anyway.

Informix:

Oh yeah, that looks great.

MySQL:

Still looking good.

Oracle:

Not bad, but why the falloff?

DB2:

It's good where it counts, but why does DB2 eventually outperform SQL Relay?

SAP/Sybase:

Again, it's good where it counts, but throughput is generally low. And again, why the falloff?

PostgreSQL:

My goodness, PostgreSQL is fast!

I checked the code over and over to make sure my test program was legitimately running queries and not just erroring out or something. It was. Why is it so much faster than everything else? Strace showed that the socket is in non-blocking mode and that they do a poll() then read(), basically blocking manually rather than doing a blocking read. My preliminary tests with a similar strategy didn't show much improvement with that over a blocking read though. Non-blocking poll()/read() is a good way to multiplex reads through a single thread, but if you're only reading from one socket at a time, I wouldn't expect it to help at all.

Very strange.

SQLite:

Well, I guess I shouldn't really be disappointed that SQL Relay is slower than SQLite :)

FreeTDS:

82 queries-per-second??? Does SQL Server limit throughput?


What now?

The falloffs are strange. It's odd that it's so inconsistent. There doesn't appear to be a memory leak. Heap fragmentation?

Performance against DB2 and SAP is odd. It shouldn't break even so quickly.

Performance against PostgreSQL is the most strange. Whatever they're doing, SQL Relay needs to be doing it too.

And what's the deal with MS SQL Server?

These things deserve investigation.

I ran tests with SQL Relay on a server between the client and database too. The results were similar, but there was more of a ramp-up. I should publish those results too.


Test Environment

To perform these tests, I configured 2 Fedora Linux VMware VM's. One for the databases and one to run SQL Relay. Both VM's had 1 CPU. The database VM had 2Gb of RAM. The SQL Relay VM had 512MB.

The DB VM ran modern versions of all databases, in their default configurations, without any performance tweaks.

I built Rudiments and SQL Relay without debug and with -O3 optimizations like: CPPFLAGS="-O3" ./configure

The SQL Relay configurations were all pretty standard too. For example, the Oracle configuration was something like:

 <instance id="oracletest" port="9000" socket="/tmp/test.socket" dbase="oracle">
  <users>
   <user user="test" password="test"/>
  </users>
  <connections>
   <connection string="user=...;password=...;oracle_sid=..."/>
  </connections>
 </instance>

For the PostgreSQL tests I disabled typemangling.

The sqlr-bench program can be found in the test/bench directory of the SQL Relay distribution. It must be run from that directory. I ran it with default parameters. There's a benchall.sh script there too, which starts and stops the appropriate SQL Relay instances and runs sqlr-bench. I actually used that script rather than running the tests individually.