Tag: Flyway

I have been doing some testing with Redgate’s Flyway Desktop as a new way of managing code for databases. However, just like Git, I appreciate clients, but I want to know how the CLI (command line interface) works. I spent time learning git add, git push, git checkout and more. Now I have more comfort understanding how SourceTree or GitKraken work.

I wanted to do the same thing with Flyway, just to be sure that I know what the options, switches, and behavior for Flyway operations would be.

The Scenario

I had an existing database, and I wanted to play around with adding this to a DevOps flow. I was looking for a basic experiment, and decided to create a new repo. I copied the default flyway.conf file into this folder and changed it.

The only thing I did was alter the Flyway conf file in my folder to work with SQL Server. I copied the connection string into the flyway.url parameter and set it as follows:

flyway.url=jdbc:sqlserver://aristotle:1433;instanceName=SQL2017;databaseName=AdventureWorks2017;integratedSecurity=true

When I ran the info command, it failed.

When I ran the same command with a different database, it worked:

I was highly confused. I tried a number of different databases, and some of them worked, not I couldn’t see a pattern.

I checked a number of things, including the database owners, a few of which I changed. I thought it might be some permissions and dropped my sysadmin account and added it back.

I tried connecting with SSMS and with sqlcmd. Both of those tools seemed to work.

I was really stumped.

A Small Conflict

Finally, after a bit of back and forth with a few developers, someone noted that I shouldn’t need the port included in the string. Sure enough, when I removed it, things started working.

Apparently, the JDBC documentation notes the issue. I kept looking at Flyway docs, but they just pass things along to the JDBC driver from the various parameters and environment variables.

There is a note that says provide the port number to stop a round trip to the browser to determine the port number for a named instance. If the port number and name are included, the port takes precedence.

I have two instances, some of which have the same databases on each. The databases that worked were on a different instance (which responds to 1433). The ones that didn’t, weren’t on that instance. I kept examining the \SQL2017 instance, but that wasn’t the one I was logging into with my string.

A silly mistake, but a good one to note. The port is a higher priority than the instance in a Java connection string.

I can’t find a priority in the docs for OLEDB or the native client, but they do all say the address takes precedence over the address parameter.

I’ve been working on a demo for a customer. Part of the demo uses a new Redgate product, but Flyway is a part of that. In testing a couple things, I realized that I didn’t have FlywayDB installed on  this new machine, so I did a quick walkthrough.

Installation

This is actually simple, or fairly simple. First, download the .zip file from Flyway.

Next, put this somewhere. For me, I wanted to be organized, so I put this in C:\Program Files\Red Gate. This did require some UAC approval to unzip the download into this spot.

 

Once I did this, I saw the flyway.cmd file in the subfolder. The instructions note I need to add this to my path.

There are different ways to add things to the path, but the quickest for me on Windows 10, is to get to the properties of “This PC”. There is an advanced system settings here.

This let’s me see properties, including the “Environment Variables” at the bottom.

Clicking this shows me the various system variables, including the PATH.

If I edit this, I get a standard dialog where I can add the Flyway folder.

Once done, I can test this with “flyway info” at a command prompt.

 

One note, if I have a command prompt open, I need to restart it to get the new path.

Licensing Flyway

This is an interesting item. I didn’t directly find an answer in the quick start. Most people probably use the Community edition, so they don’t need a key. I, however, wanted to play with the Enterprise version. I got a key from Redgate, and then set the environment variable. As you can see below, this isn’t enough. I got an error that I didn’t have a license.

I wasn’t sure if flyway.licenseKey is a file or a setting. I looked around a bit, and on the download and install page, I found an item doe the Configuration. This mentions that the first place Flyway looks is the install folder/conf/flyway.conf. Aha!

I looked in the conf folder under my Flyway install. Under here is the flyway.conf file, which is a key-value configuration file. It reminds me of the old  Windows 3.1 .ini files.

When I open this, I see a lot of values. These are mostly commented out with a #. If you need to enable a value, remove the comment at the start of the line.

Scrolling to the bottom shows me that the last entry is for the license key. I removed the comment character (# ) and then pasted in my key.

The next time I ran flyway info I see this:

No license message, though obviously I haven’t set up the connections yet.

That gets me started, and I know things are installed. Now I need to start using it, which is something for another post.