Flyway with Multiple Folders

Posted on May 31, 2023 by way0utwest

A customer asked about how they could organize their migration scripts in different ways to manage them and worried about it being complex. I decided to test a few things. This post looks at using multiple folders for scripts.

Setup

Flyway is controlled by a number of options. These can be in a configuration file or they can be passed into the command line. I’m lazy, and I like things self-contained, so I tend to drop things into the flyway.conf file and specify this in the command line.

I’m also lazy about command lines, so I created a fw.cmd file with this in it:

flyway migrate -configFiles=”./flyway.conf”

Then I set up a few folders on my system. These were subfolders in a repo called SQL1, SQL2, SQL2. I know, I’m not creative. Here are the folders I set up. SQL1 has 3 scripts, each containing a CREATE TABLE statement. In this case, I am creating Table01, Table03, and Table04.

2023-05-18 12_28_18-sql1

I do something similar in SQL2, with table Table02, Table10, and Table11.

2023-05-18 12_28_22-sql2

SQL3 gets the 5th and 6th tables.

2023-05-18 12_28_25-sql3

I did this to check order of operations, as well as using multiple folders. For reference, each file looks similar to this, with numbers changed:

2023-05-18 13_04_21-V010__tenthtable.sql - ARISTOTLE.foldertest (ARISTOTLE_Steve (60))_ - Microsoft

I then edited my flyway.conf file. You can name this differently, you just need to pass the appropriate file to flyway when you execute it. By default, flyway.conf is looked for. I changed the variable for flyway.locations to be a comma separated list. In my case, I entered this:

flyway.locations=filesystem:sql1, filesystem:sql2, filesystem:sql3

Make sure you uncomment the # from the line.

Once this was done, I created an empty database, called foldertest. I then ran my fw command and saw this:

2023-05-18 12_30_45-cmd

Success, with all my tables created.

Flyway is set up to search the locations path and load the files and execute them. You can organize your scripts into different subfolders if you want to, and flyway will sort out the ordering, if you’ve named them correctly.

Posted in Blog | Tagged , , | Leave a comment

Scaling Up Monitoring

Posted on May 31, 2023 by way0utwest

Monitoring databases is important when it’s the systems that are in production. Operations departments know that catching issues early, being proactive, and having data to troubleshoot issues make their job easier. Not having these things makes their job much more stressful.

Most of us work with data in some way and the availability of that is important. Certainly, security, integrity, and performance matter as well, but availability is key. Many organizations don’t have any monitoring systems set up. Instead, they troubleshoot problems when someone files a ticket or calls. I’m amazed at this, though I know building and managing a monitoring system is hard and purchasing third-party products can be outside of your budget. Still, having something in place makes everyone’s job easier.

If you decide to build a system, then you can do it in many ways. I saw a description of how Amazon built a monitoring system for their Prime Video service. This is a more complex system than many of us deal with for databases, but I did find it interesting that they chose a distributed architecture that used multiple components. It didn’t scale, so they started to move from small functions, almost like microservices, to a bit more monolithic structure.

I am not saying that microservices or functions or serverless are bad choices. They meet certain needs, and they can work very well. Azure SQL Database Serverless can work well in some situations. However, I do think that this was a case of engineers trying to be too clever and making assumptions about production loads from PoC-type experimentation.

I would say that far too many software engineers think that their solution will scale without actually testing it. Too often their view is if it works here, it will work there, but the history of software has shown that working on my machine doesn’t mean working on another. That’s why we use Continuous Integration: for independent validation and verification. This is also a problem when databases are involved, as the level of data used for development and testing doesn’t do a good enough job of predicting how the system works under load. We need better test data management, which is becoming a whole new category of software practices and tools.

We should ensure we include good instrumentation in our software for monitoring purposes, but we should also ensure that we start monitoring and evaluating how our system will perform in test and development environments, as that’s the idea of shift-left. Lastly, I think monitoring in production is important, but I wouldn’t build another system. I admit I’m biased, as I work for a company selling monitoring software. However, I also think the build v buy debate doesn’t make sense here unless your staff has a lot of spare time to spend maintaining a homegrown system. I’d like to think most of them have better things to do.

Steve Jones

 

Listen to the podcast at Libsyn, Stitcher, Spotify, or iTunes.

Posted in Editorial | Tagged , | Leave a comment

Migrating from EverNote to Joplin

Posted on May 30, 2023 by way0utwest

Recently I got a message that my Evernote subscription was going up. It’s been a $3 a month service, but moving to $4 for me. From USD$35 to USD$50 a year. Not a big change, but a little annoying to me. I have a few reasons, outlined below, but this post is mainly describing the process of moving my data.

Setting up Joplin is easy. You download the app, install it, and then (optionally) connect it to Dropbox, where it adds itself as an app in the /apps folder.

Export and Import

I can easily export a notebook in Evernote to their ENEX format. I do this by right clicking a notebook and selecting Export notebook.

2023-05-09 09_14_45-Editorials - Evernote

This asks me how to store the output. I get a single file, pick a name/location and this creates a file.

2023-05-09 09_15_09-Editorials - Evernote

Once this is done, in the Joplin desktop app, I have a few choices. I pick ENEX and then the type, select the file, and things import.

2023-05-09 08_49_14-Joplin

I tried HTML, but got a mess.

2023-05-09 08_49_51-Joplin

If I could edit in the right pane, this would be OK, but I have to edit in the source pane, which is annoying. I know HTML, but don’t want to write in it.

Instead, I picked markdown, which gives me a better view.

2023-05-09 08_50_57-Joplin

I have perhaps a few thousand notes, but really only about 12 notebooks. Since I can export 12 times and import 12 times, this is easy.

I then have all my notes in a new app, where I can sync them between desktop, mobile, and the iPad in the kitchen.

Note I’ll spend a couple weeks working in Joplin and see what I think.

Why Leave Evernote?

Evernote has continued to expand their capabilities, which I get. As a software service, they want to keep growing and attracting more customers and providing more features.

However, for me, I want to simply take notes. I almost never include images, web clips, etc. Instead, I like simple text and quick software. I want to make notes and save them, syncing across devices. To me, I need a glorified way of capturing text files and moving them between machines.

While Evernote has worked well, they’ve done some things that make this less ergonomic for me. Specifically:

  • They add a div tag that notes this item was clipboard’d, which is perpetually annoying. I also copy/paste often as this is my writing tool and they add some markup in HTML, which I then have to remove.
  • New notes mean I have to select if this is a note, a task, or something. One extra, very, very annoying click.
  • Their navigation options are clunky and slow on mobile/tablet.
  • Their UX has me constantly clicking from the top to the bottom to the top of the screen.
  • They’ve become more chatty, and single-threaded with more lags. Something I find annoying.

They have also deprecated the Plus subscription, which is simple.

I decided to try Joplin, which I found in this PC Mag article. Joplin is an open source app, and if I keep using it, I’ll donate some money to the developer. I don’t mind paying for software, but I need something simple.

If this doesn’t work, I have other options. I might just create a private GH repo and use folders to organize text files. That’s about what I need.

I can also renew Evernote for a year and kick the can and hope they don’t try to force me into the $12/mo subscription.

I know lots of people love OneNote, but I find it overkill and too annoying.

Posted in Blog | Tagged , , | 5 Comments

Validating Password Expiration

Posted on May 27, 2023 by way0utwest

I would guess that the majority of instances I’ve had to manage in my career were those that I didn’t initially install and configure. I’ve inherited more instances than I would bother to count, and I often need to double-check what’s been done in the past. As noted in the series on new jobs from Tracy and Josephine, there are a lot of settings to check and adjust to meet your standards.

While backups are often my first priority, security is second. I usually want to know who the sysadmins are and ensure systems are patched and configured to reduce the attack surface area. There is one other security check that I think I haven’t always been overly concerned about checking: password expiration.

There was a post from Steve Stedman recently that mentioned the way to alter logins and ensure they have CHECK_EXPIRATION set ot on, which ensures that passwords expire and need to get changed. This is especially important for sysadmins. I try to ensure those accounts in that role are secured with AD, but there have been times when SQL accounts are used. Usually, I disable sa, but I’ve seen other accounts, especially those used by monitoring systems who seem to think sysadmin is required. It’s not.

I don’t know that I’ve run queries to check the value in the is_expiration_checked column is appropriately set. If it’s not, then Steve’s post above will help you change those logins. That’s a handy script to have set up and use to ensure that all logins have this set. In fact, this is one of those areas where new logins could be created by junior administrators and not set the option. Perhaps this is something you want to run on a regular basis, perhaps weekly, to ensure that if any new SQL logins are created, they are done so with the password expiration set.

Ideally, no one would ever create logins without expiration set, but sometimes things happen. I’ve seen monitoring systems set up with sysadmin privileges and passwords that never expire. A surefire way to dramatically increase the risk to your database systems. It would be better to have a known, consistent process for setting up accounts. Some companies have specific scripts, or snippets, that administrators use when tickets are filed. One customer of mine had even linked a script to a Slack command in a sysadmin channel. Only admins could use this channel, but they could use Slack to kick off scripts to create logins, add roles, and force password changes.

No matter how you choose to handle security at a process level, it is important to include monitoring and remediation for issues. Mistakes will get made, settings altered, and exceptions approved. Sometimes we can fix things, sometimes we cannot, but knowing what our environment looks like and where we have potential issues is important not only for getting the work complete but getting the approvals to make changes that ensure better security. My recommendation is that you ensure you have a way to regularly check your systems, automatically fix issues where appropriate, and report on those that need additional approvals.

Steve Jones

Posted in Editorial | Tagged , , | 2 Comments