Voice of the DBA

Tag: DevOps

A Test Client for Zero Downtime Deployments
I’ll be at VS Live in Las Vegas this March to discuss zero downtime deployments. If you want to come and join me for this session, or any of the other great ones, register today and save $500 with the promo code “Jones”. You can use this link to register.

To simulate the effects of deployments on a workload, I built a small client. It’s nothing great, and likely some of you will laugh at my C# skills, but it works well enough. It’s a simple Windows form application that writes to a text box. However, it’s valuable to determine if there are any issues when you’ve made a deployment.

This post looks at the rough design of the client. Code is available in this repo: https://github.com/way0utwest/ZeroDowntime

Using WPF

I wrote a small app a few years ago to test and present on Always Encrypted. This was a basic WPF app that added the proper values to the connection string for Always Encrypted and let you query encrypted data (or not).

Like all mediocre developers, I copied and pasted that project into a new folder and set about modifying it. In this case, I set up a loop that continues to run and execute some database code, essentially using this loop:
```
while (iRunQuery > 0)

{
```
I set this value to 0 initially, and when a button is clicked, it’s 1. This then runs a bunch of lines to decide which DB code to run. I’ve mostly made this stored procedures to make it easier to adjust demos without touching C# code.

It’s not pretty.

At the bottom, I have this (outside the loop)
```
Application.DoEvents();

System.Threading.Thread.Sleep(100);
```
This is designed to catch me clicking a “stop” button that will set the variable back to 0. I added the delay because otherwise this runs a bit fast.

I have a few option buttons that adjust what code I’m calling, so I can simulate toggling feature flags on and off. I also log results to a window so you can see them, and I catch errors and log those. Errors are also counted, so we can see the impact of “non zero-downtime” changes.

It’s not a great example of software, but it does work.
February 22, 2023
Building Recommended Software Practices
Many of us work inside an organization that has a process for building and deploying software. We may find our org doing this well, or we may feel our process is poor with lots of room for improvement.

A lot of the discussion around how to be better at building software in the last ten years has been around the philosophy of DevOps. This concept doesn’t really prescribe how to build software, but give you goals to aim for. That means you still need to take the ideas of flow, feedback, and learning and decide how you implement them with your staff. What practices do you follow to ensure you can deploy quickly anytime your code is done? These can include ensuring you’ve tested code, getting feedback from customers, and more.

I ran across a post on recommended software engineering practices for an organization. The list includes seven things you should do:
1. Keep documentation in the code repo
2. Have a mechanism for test data creation
3. Use rock solid database migrations
4. Create templates for new projects
5. Automate code formatting
6. Automate a process for new dev environments
7. Automate preview environments
This is a set of things I often preach to customers as well, especially 2, 3, 6, and 7. I often focus on the database and having curated test data, migrations you can count on and easy setup is important. And, of course, with SQL Prompt, you don’t need formatting ;). Just kidding, that’s important, too.

These are solid practices, and none of them are that hard to set up, but they do require some discipline and willingness to work as a team and maintain your process across time. Each of these items needs some care and feeding across time to remain relevant and helpful to your staff.

Do you have good software engineering practices? Are you proud of them and would you bring them to a new position? Or perhaps you wish your team would adopt better habits and a different mentality towards building software.

Steve Jones

Listen to the podcast at Libsyn, Stitcher, Spotify, or iTunes.
February 17, 2023
The Baseline for Flyway
In my previous post, I set up the Flyway Desktop projects for SQL Server and PostgreSQL. I also added a table to each platform for development. In this post, I’ll look at how I let Flyway know what already exists in my system with a baseline.

This is part of a series of working through Flyway and Flyway desktop to demo database changes. Disclosure: I work for Redgate Software

Baseline v Flyway Baseline

I find these terms to be slightly confusing, especially when I look at Flyway vs. Flyway Desktop. In Flyway, there is a “baseline” verb, which you can run at the CLI. This will mark the state of your database at a level and adds the flyway_schema_history table to the schema in which you are working. This baseline causes Flyway to ignore all migrations up to the baseline level.

In my testing, when I run this on my database, it defaults to V1 for the first migration script. That’s usually fine, but as I wrote, this can cause issues.

There is also the concept of a baseline migration, which is a Bxx script, and this contains the definitions of all the objects that already exist in your target databases. This ensures that as FW and FWD track and deploy changes, they don’t try to redeploy those migrations that are at a level lower than the baseline numbering (the xx).

Creating a Baseline

My development database for SQL Server looks like this:

There is an object in here, but it’s not in any other environment. Both Integration and QA (and the others) have no objects.

In this case, I don’t need a baseline script, because I want this table to deploy to the downstream databases.

I do, however, need a baseline. I need the baseline marker in my databases to note that we have a base version. This will give me a starting point, but also ensure that FWD creates migrations that are numbered higher than my baseline.

I’ll add this in two ways. One with Flyway Desktop and one with the Flyway CLI.

The Flyway Baseline

For SQL Server, I don’t need to worry about any objects in downstream databases, so I’m just going to run the Flyway CLI. From a command line, I’ll run this code:
```
flyway baseline -url="jdbc:sqlserver://localhost;instanceName=SQL2022;databaseName=FWPoC_1_Dev;encrypt=true;integratedSecurity=true;trustServerCertificate=true"
```
This is run from my project location, though I’m passing in the connection string from Flyway Desktop as I don’t have a flyway.conf file configured for this project. Things work from the FWD gui, but not the CLI.

This works, and I see these results. Note the flyway schema history table is created at the bottom, and the version of the database is set to 1.

Now when I run Flyway info with that URL, I get this. There is an entry in the version tracking for this table:

I can also see this table in my Object Explorer:

Flyway Desktop and PostgreSQL

I’m going to use FWD for my PostgreSQL project. This will do some of the work for me and give me the option for a baseline script.

Note: I set up a shadow database first.

I click “Create baseline” and this asks me for a target. After all, I’m trying to ensure I don’t deploy anything to prod that’s already there.

When I click Add target database, I get a connection dialog. I fill this in with the credentials for prod. This returns me to this screen below, where I see my prod database, which is at this port with this name.

I click Baseline and it goes to work. There’s nothing there, so this returns back to the blank, Generate Migrations tab.

However, there is no baseline or schema tracking table. I didn’t have a poc schema, so perhaps that’s an issue, but that’s OK. We can fix this.

In the Migrations tab, I see this:

That configures this tab to look at (and work with) this database.

In general, I know we won’t be able to see production, but this is a PoC. However, this is something that I, in general, don’t want to do. I want to work with dev/test environments, so let’s do that.

I’ll configure my QA environment. I click “configure target database” and I get this screen. These are all the databases for my project. Here I’m going to click “delete” for production and then I’m going to click the Add and configure my QA database. Once I do that, I’ll see this:

Baseline added for PostgreSQL.

Success.
February 13, 2023
Creating a Flyway Desktop Shadow Database in PostgreSQL

In order to generate migrations, we need to configure Flyway to use a shadow database. This post looks at that process.

This is part of a series of working through Flyway and Flyway desktop to demo database changes. Disclosure: I work for Redgate Software.

Configuring the Shadow

This is an empty database where we run the migration scripts to verify them. Since a user might edit or create their scripts, we want to ensure there are no problems with the syntax or execution with other scripts. This is also a place where we keep the “previous state” of your development database and use this to detect the changes you’ve made.

This database gets cleaned, meaning objects get dropped, regularly, so you configure a space for this. It can be a separate database, or just a schema (more Oracle focused).

For me. I’m going to create a new database in postgreSQL to support this. As you can see below, I use the simple CREATE DATABASE syntax.

Once I do this, I go back to FWD and click the “Generate Migrations” tab. The first time I do this (and only the first time), it asks me to configure a Shadow database.

I click this and get a connection dialog, similar to what I have for my development database. In here I enter the credentials for my shadow database, which are similar to my development ones. I test the connection and verify I can connect.

That’s it. Now I’m configured for a Shadow.

The process is similar for other platforms, just with different credentials. If you need to learn more, you can read about this in the documentation.

February 6, 2023