Thanks to everyone who attended, though with one stage I had a fairly captive audience. I enjoyed the event and look forward to coming back to the Twin Cities in the future.
A few nice questions, which I’ll address in a few posts:
Slides: DevOpsDaysMN_BestPracticesForDatabaseDeployments
You can also watch the presentation on YouTube:
For my talk at SQL Saturday Baton Rouge 2024, here are the resources.
Slides: Architecting Zero Downtime.pptx
GitHub: https://github.com/way0utwest/ZeroDowntime
If you have questions, reach out.
Note: I DO NOT recommend this. Any changes to a pipeline should be in code and through a PR.
That being said, I know this information is out there and some people need it. The question from a friend was how can they set a variable in an Azure DevOps Pipeline at runtime. This was for testing, and they wanted to change the pipeline behavior to test things when they ran them.
This post will show how to do this in classic and YAML pipelines. As a scenario, I’m just going to get a directory listing of a folder, and change that at runtime.
I know the trend is everything in code. For experimenting and learning, I find this slightly annoying, so I like classic pipelines. I know others do.
In a classic pipeline, I can set variables. I’ll add a new one and call it myLocation. Over on the far right, there is a checkbox for “settable at runtime”. Check that.
Now, I’ll add a task to this pipeline that runs a dir, using this variable.
I can save and run this, and I see the results of c:\Users from a hosted agent.
That’s the default behavior.
Now, let’s alter this at runtime. When I click “run pipeline, I see this on the right side as a blade. Note the “variables” section below.
I can click this and see my variables. System.debug is set at runtime by default, but I see my other one.
If I click this, I can change the location. I’ll set this to c:\.
When I let this run, note I get different results.
I’ve changed behavior at runtime.
In a YAML pipeline, I don’t have tabs or variables. Instead, I just get a script of sections, like this.
I can alter this to add a variable by looking in the upper right, where I see a “variables” button. Click this.
I get a list of variables, which is none in this case. I’ll click “New variable”.
This gives me a dialog where I can enter the information. Note I can set a default as well as let users override this with a checkbox.
When I save this, I see my variable.
Now, I can alter my script. I’ll add this as $(myLocation), where I surround the variable name with a $ and ().
I can validate and save this, which I do.
It’s valid, because I typed well, but this really should go through a PR. Since I’m testing, and I’d approve the PR, I’m doing it in main. I shouldn’t do this in any org.
Now when I run the pipeline, I have the variables item where I can change the variable.
I can also set this variable in YAML, like this:
However, if I set that value, I can’t change this at runtime. Here’s the runtime screen.
I can use a parameter instead. I’ll use this structure:
When I run this, I see a new box:
I can override this “Dir location”. When I set this to c:\, I see these results:
I’ve shown how to configure a variable to be set at runtime, both in classic and YAML pipelines.
Note, this a place an administrator can make a mistake, or run rogue, without review. This is not recommended. Put all pipeline changes through a PR.
I often deal with customers who are looking to improve the way they build and manage database software. These could be small companies or large enterprises, with teams of developers trying to enhance their application software to solve new business problems. Often those enhancements require new data, with the related schema changes in a database. Even if there isn’t any new data, often we need to query data in new ways, combing, filtering, aggregating, and otherwise transforming data into extra information that a business can use.
Solving many of these problems is iterative by nature. We try one thing, then another. Often a developer might experiment with a data model or query, trying to match a requirement they’ve been given. Once they produce a solution, we may find problems in testing, or far too regularly, in production. These could be data-related issues, where the developer hasn’t considered values in their solution (zeros, blank or long strings, extreme dates, etc.). These could be logical errors, where the developer just made a mistake. There could also be a problem with the requirement, where the customer provided an incomplete or incorrect specification to the developer.
In any of these cases, what often happens is rework. We need to re-look at the code we’ve written and change it in some way. This isn’t a complete rewrite, but looking for the logical error, incomplete algorithm, or poorly performing structures. Then we need to (test it and ) move the code back through our process of deploying to QA, UAT, production and anywhere else the problem exists. The further we are in the process (closer to production) the more costly the effort to rework code.
Some rework is unavoidable, but I think quite a bit isn’t. If we wrote better-performing and cleaner code the first time and had a pattern for testing our code, we might avoid a fair amount of rework. If we caught silly mistakes early, we could get more done.
I think that often developers minimize the effort required to perform rework. They think small changes are easy. After all, we already know the problem space, we’ve looked at it, and the fix should be quicker than the initial analysis and development.
Or is it?
One of the things I’ve seen is that everyone is busy. We are pulled in many directions by many different people and work on lots of different problems. Even when we work on the same application every week, it takes some time to remember the context under which we wrote the code. If it’s not our code, then we spend time trying to understand how the original developer approached the problem. Without good specifications or tests, we might not view things the same way during rework. We often fix one problem but create another.
Automated testing should help here, as the tests should codify the understanding of the requirements, but too few database developers use automated testing for their code. Even developers who embrace testing from the application might not have sufficient test data to ensure their code will work in production.
Most of us are expected to do more work this year than last year. We’re constantly battling a lack of resources. We can’t necessarily write more code, but we can learn to be more efficient. We can learn to reduce our rework by writing code better the first time. That takes some effort and knowledge. It takes working as a team, sharing successes and struggles, and adapting our code to use patterns and avoid anti-patterns. It requires some automation to embrace static code analysis and testing to avoid silly mistakes getting deployed and wasting time. More importantly, it takes support from management to spend time learning as a team to produce better code and avoid silly mistakes.
A little investment in learning and practice goes a long way. Unfortunately, too many people, both staff and management, are unwilling to commit. Except in fixing bugs and spending time on rework. Far too many people see that as an regular, accepted part of software development.
Steve Jones
Listen to the podcast at Libsyn, Spotify, or iTunes.
Note, podcasts are only available for a limited time online.
Voice of the DBA 