Voice of the DBA

Tag: DevOps

Friday Flyway Tips–Linking Commits to an Azure DevOps Work Item

Recently I was doing a demo and a customer asked how I had linked my commit in Azure DevOps to the work item that existed. It’s easy, and tldr; it’s with the commit comment. This post shows how that works.

I’ve been working with Flyway Desktop for work more and more as we transition from older SSMS plugins to the standalone tool. This series looks at some tips I’ve gotten along the way.

Creating a Work Item

In Azure DevOps, there is a board menu. Under here, one of the subitems is Work Items. You can see these below:

I tend to work from the “Boards” item, which looks like this:

Let’s assume we have a new item, so I’ll click the “New Item” at the top of the left column. This gives me an edit box, which I’ll type in a name.

When I hit enter, this gives me a new work item, which you see below, as 250 create products entitty.

Since I don’t want to leave a spelling error, I’ll edit the title to look better. Now I have a work item.

Linking A Commit in FWD

In SSMS, I’ll create the products table. Once I do this, I’ll go to Flyway Desktop and refresh the schema model. When I do this, I see my change. I’ll select this and save to the file system.

Once this is done, I’ll go to the Version Control tab, and I again see my file changed. Here my file is selected, and I’ll enter a commit message, but I’ll include the work item number after a #. This creates a link.

I now click commit and push. In Azure DevOps, in the Repos –> Commit item, I see my commit.

Note the details item under my cursor. If I click that, I see commit details and near the bottom, a link to the work item.

If I click through to the work item, or I access the work item from Boards, I will see my commit linked on the far right of the work item.

This is very handy to have your actual code linked to the work that it relates to. If I have PRs or multiple commits, as long as you use the #nnn, where nnn is the work item, you’ll have everything linked together.

This is less a Flyway Desktop feature than an Azure DevOps one, but you can access this from Flyway Desktop by writing good commit messages with work item numbers.

Try Flyway Desktop out today. If you haven’t worked with Flyway Desktop, download it today. There is a free version that organizes migrations and paid versions with many more features.

Video Walkthrough

I made a quick video showing this as well. You can watch it below, or check out all the Flyway videos I’ve added:

December 29, 2023
Migrations are Worth the Effort

I work with a lot of customers that are trying to find better ways to build and deploy database code. Some of them use a state or model method with SQL Compare or another Redgate product, and some want to use Flyway and its hybrid migration-based framework. It’s always interesting to me how customers lean one way or the other, for various reasons.

No matter which choice people make, we know that using migrations is more effort than a state-based approach. However, I think that it’s a better approach, especially because no matter how simple or smooth you think your development process is, at some time there will be a situation that needs a more complex approach. a migrations-based framework allows you to handle the complexity smoothly, without depending on a person to sort out the issues.

It’s not just me, but many developers feel the same way. There’s an interesting article from a developer that gives some examples of why using migrations work well in different situations. It also acknowledges that the effort is high, but by using migrations you can accomplish complex tasks across time. something I try to get more developers to do. Don’t big-bang-deploy-everything, but break things into chunks.

It’s not just a simple change. There are a few reasons that migrations are hard, one of which I loved. The author compares migrations to working with live electrical wires. Imagine hanging a ceiling lamp and wiring it up without turning off the power (note: don’t try that).

One of the best reasons I have for migrations is that lots of work isn’t done at the same time, especially in a team. If each team or each piece of work is broken into separate migrations, choosing which ones to move over from developer is much easier than if all the changes are captured in a state-ful model. You still have to be aware of dependencies between migrations, but having them all separate at the beginning (and hopefully tagged to pieces of work) makes this easier.

The other reason to choose migrations is that I can stage future changes. Imagine I need to make a change to add something in one migration and remove it later. Suppose we add a new tax column and then after Jan 1, we remove the old tax column. With migrations I can pre-write the removal and create a pull request with the “Tax Removal – DO NOT MERGE UNTIL 2024 JAN 1” title, which should be obvious to anyone. I like keeping things simple, which helps when I never know who might need to handle this work in the future.

Steve Jones

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

December 18, 2023
Try, Try Again, Until It’s Right

One of the challenges with making changes in a database environment is that undoing those changes can be hard. What’s often preferred is rolling forward with a new change to correct the issue, but that’s often done with limited analysis and thought. Instead, we hope our staff makes a quick patch and a better decision under pressure than they did with more time to examine the problem. That works if it’s a simple mistake that was made in implementation but not if we haven’t designed our solution well at the start.

I ran across an article on DoorDash that I thought was interesting. During the pandemic, their business exploded and they outgrew the Aurora PostgreSQL database. They migrated to Cockroach, a cloud version of PostgreSQL that’s distributed and can (theoretically) scale much higher.

The thing I found interesting is that the engineers at DoorDash were trying to break apart their monolith and get better scalability, primarily from certain tables, by extracting their tables to get single writers in a cluster, which should help them handle a larger workload. They wanted to use their main identity table as a test, which I assume is the table that tracks each user in the system. They tried to migrate this and cutover to a new cluster 4 times before a fifth attempt worked.

I think any large migration is fraught with issues, but I appreciated the design here that allowed them to rollback their change and revert to the previous version of the database. That’s something I don’t see many teams think about or build into their database change process. I think having a clear, known, tested way to undo changes is important, at least for some of your tables.

There are two pieces of advice they give that I often give to customers as well. First, learn to spread out changes across batches. When I work with Flyway customers, I always let them know they need to think of a migration script as a unit of deployment and break those apart as best you can. Those often also become units of rollback, so keep them small. Not necessarily every change in its own script, but don’t bundle too many things together.

Second, keep things simple. Too often I find engineers build clever solutions that make sense to them, but no one else. You never know the quality of your next hire, so don’t overcomplicate things without a really good reason.

Did their process work? They’ve grown to about 1.9PB of data. That’s a lot of food orders. They’ve also had other metrics of success, and seem to be saving time for their tech team, which is often one of the main reasons to build a better process and use it consistently.

Steve Jones

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

December 15, 2023
Who Still Uses SP_ for Naming?

An interesting post recently from Chad Callihan on the reasons why someone shouldn’t use sp_ to prefix their stored procedures. This is advice that I’ve seen for years, but I haven’t run into this lately with many customers. It seems I do see some usp_, but not sp_. I think that’s good, and if you’re not sure why, read Chad’s post.

I think my main reason for not doing this is that I sometimes create procs in master. Not because it’s a good idea (it isn’t) or I want to, but because I’ll make mistakes. I’ll accidentally connect to master and deploy a script, because I’m not paying enough attention. Usually, I’ll catch this right away, but if not, soon after I’ve told a client things are working, they’ll let me know they aren’t. Then I connect to the right database.

In a vacuum, or even as you sit reading this today, it’s easy to criticize someone else for making the mistake of connecting to the wrong database, or even forgetting to delete the object in master after I’ve created it in the right one. However, all of us are human. We’ll make mistakes. We’ll be pressed for time or sick or tired or want to leave or some other situation where we aren’t using all of our faculties. Even if none of those are true, we might depend on muscle memory because we’ve done this 1,000 times (or may 12 times in the last hour) and then don’t notice we’ve connected to the wrong database.

What might be even worse if when we’re updating objects and potentially update in one place but not another. What if v7 of the proc is in the user database, but v5 is in master. Who knows if we (or the client) will notice things aren’t working as expected right away.

Mistakes are one of the reasons I try hard to get everyone to follow a DevOps approach. Put your code in version control (so you know where it is when you make mistakes), use automation and CLI calls to deploy code. Whether you use something like Flyway or your own SQLCMD scripts, make sure things are automated. Then ensure you have logs with commands run, and if needed, output from the scripts.

We’re human, we make mistakes, so figuring out how to automate a process and then ensuring that automation is running results in repeatable, reliable deployments. Something we should all want, especially the control-obsessive DBAs.

Steve Jones

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

December 13, 2023