SQL Server Integrated Authentication

From time to time we get a customer reporting an error like this:

The key to this error is the text “Faled to load the sqljdbc_auth.dll”

This file is required by the Microsoft SQL Server JDBC driver, in order to handle integrated authentication.

The file must be placed in the Java bin folder. Upgrades to Java can change the location of the Java in folder, by changing the version number higher up the path. This means that the file can no longer be found.

Here are some instructions to resolve this error if it occurs on your server.

Instructions

Download the SQL Server JDBC driver from https://www.microsoft.com/en-au/download/details.aspx?id=11774. Make sure you get the 64-bit version.

Install the driver to any location. It doesn’t matter where, it basically just unzips a file structure and you’ll only be taking one file from its directory.

Go to the folder to which you installed the driver, and find the following path:

\sqljdbc_6.0\enu\auth\x64

Copy the file sqljdbc_auth.dll from this location

Find the location of your Java Runtime Environment using the WHERE command:

where java

It should be something like this:

C:\Program Files\Java\jdk1.8.0_121\bin\java.exe

Copy the sqljdbc_auth.dll into the java folder. Given the result I’ve just shown, on my machine I would copy it to:

C:\Program Files\Java\jdk1.8.0_121\bin\sqljdbc_auth.dll

Now, when you restart the Ajilius application, integrated authentication should work on your machine.

Custom Code Injection

Ajilius has always supported a rich set of transformations and custom calculations to support complex ELT. Occasionally, however, we’ve had requests for custom code injection before or after ELT transformations. That request is now satisfied.

All Load and Transform jobs now support blocks of user-defined code at the beginning and end of the job.

Code injected into Load jobs is in the Groovy scripting language, while code injected into Transform jobs is pure SQL.

To add custom code to a job, select the Change menu item for the table being processed:

The table form now contains two new fields, named Custom Code – Pre-SQL and Custom Code – Post-SQL. These fields contain any legitimate SQL statements.

When you run the transform script for this table, the custom code will be injected into the appropriate place in the script:

Please note that custom code may not be supported across target data warehouse platforms, and may need to be reviewed and edited after migration.

Ajilius. Round-trip customisation.

 

Continuous Deployment with Ajilius

Many data warehouse automation products are good at single database development, but struggle when asked to cope with multiple environments such as Dev, Test, SIT, UAT and Production. When you’re striving to be Agile, you need the ability to handle CI / CD in any environment, in any state.

Release 3.2 of Ajilius brings full CI / CD to data warehouse automation.

Within the browser UI, we now support immediate generation of alignment scripts that preserve data across multiple generations of deployment. The following picture shows the new UI menu options for deployments, as well as an alignment script in the left-hand pane.

We’ve also extended the Ajilius command line to support scripted deployments across multiple environments. The following screen shot shows the initial deployment of a data warehouse to a new server.

We’ve then modified the structure of one table, and added a new table, through the browser UI. The second command line invocation alters the data warehouse to align with the new table structure.

But what happens when you have multiple environments, in multiple stages of deployment? Ajilius persists the current deployment state for each environment, and has IMPORT and EXPORT functions to reload that state before alignment. This means, for example, that your DEV, UAT and PROD environments can be progressively deployed in different states.

Because these features are implemented on the command line, as well as in the UI, Ajilius supports full automation of the CI / CD process. For example, our own internal CI / CD test server uses CRON to implement an hourly deployment, based on any changes to the metadata in the last hour. Of course, we fully integrate with products like GoCD, Control-M, etc. for enterprise-level automation.

The metadata changes that can trigger an alignment include:

  • Add table or column
  • Rename table or column
  • Change column data type
  • Change column role (ie, SCD2 to SCD0)
  • Change table distribution method
  • Change column sequence

And remember, every change preserves your existing data. We even fully re-key fact tables if an SCD2 dimension changes to any other type.

Ajilius. Enterprise maturity in data warehouse CI / CD.

DWA: Data Warehouse Abandonware?

Frequency of updates is a good indication of the on-going viability of a data warehouse automation platform.

From what we see and hear, too many competitors are good at getting their products released, but not so good at error correction and enhancement.

This came to mind while I was packaging our latest update – huge changes in incremental deployment – and looked at the following:

That’s the releases we’ve made during just the past four months.

  • Every update is cumulative, you don’t have to apply them all
  • Every update contains one or more new features, usually driven directly by customer requests
  • Most updates contain performance enhancements
  • Many updates contain code enhancements and corrections to errors reported by users

Here’s what a couple of weeks looks like in Mercurial:

All our release planning is done through a Trello board. At present it has a backlog of around 18 months for the current development team. New target platforms. New data sources. New transformations. New BI interfaces. New methodology support. New features we’re not yet ready to discuss. We’re going to continue, or even accelerate the rate of new feature deployment over the next  year.

The one thing that won’t change is our licence policy. We’ll continue to be a site-licensed product, with no lock-ins, including the guarantee that your data warehouse will continue to function even if you don’t renew your Ajilius licence, and we’ll never increase your annual renewal for as long as you use Ajilius.

Ajilius. USD 15k / site. The best value in data warehouse automation.