Voice of the DBA

Category: Blog

Set a Runtime Variable in an Azure DevOps Pipeline

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.

Classic Pipelines

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.

YAML Pipelines

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:

Summary

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.

July 5, 2024
A New Word: Symptomania

symptomania – n. the fantasy that there’s some elaborate diagnosis out there that neatly captures the kind of person you are, tying together your many flaws and contradictions into a single theme – which wouldn’t necessarily sort out the mess inside your head but would at least let you mark it with a little sign so people know to walk around it.

Another great definition. Maybe we should have signs we can hold up to let people know we’re feeling a little off and they should just walk around us and ignore us

I don’t feel symptomania, mostly because I am accepting of flaws, and I don’t try to simplify the world so much anymore. I don’t see black and white but rather lots of gray. I don’t try to reduce things to a simple measure, but accept a complex way of the world as the way things are.

From the Dictionary of Obscure Sorrows

July 5, 2024
Inside SQL Server Backup and Restore History Pruning with sp_delete_backuphistory
I had a customer that was looking to document a restore that had occurred on one of their systems and didn’t see it. They had concerns about SQL Server accurately tracking history across time and noted they hadn’t cleaned any history.

We dug through some of their instance jobs and found one that ran sp_delete_backuphistory. The person didn’t realize this removes restore history as well. This post talks a bit about how this works.

The important thing to understand here is that this removes backup and restore history. Not just backups. I don’t know I like this, but it is what is documented (emphasis mine).

In this case, the sysadmin didn’t realize this removed restore entries. Once they did, they stopped worrying about things. We could have potentially restored an old backup of msdb and found this data, but they elected not to do this.

How The Procedure Works

We can actually see the code for this proc. I have expanded the msdb programmability section under system stored procedures.

I won’t show it, but this works in the following way:
1. create three table variables with a single ID column
2. insert data into these two tables from backupset where the date is older than the parameter passed in.
  1. backup_set_id from backupset
  2. media_set_id from backupset
3. insert data into the third table that matches the backup_set_id from the table in A
4. start a transaction
  1. delete from backupfile the matching backup_set_id values
  2. delete from backupfilegroup the matching backup_set_id values
  3. delete from restorefile the matching backup_set_id values
  4. delete from restorefilegroup the matching backup_set_id values
  5. delete from restorehistory the matching backup_set_id values
  6. delete from backupset the matching backup_set_id values
  7. delete from backupmediafamily where the media_set_id values match
  8. delete from backupmediaset where the media_set_id values match
5. commit the transaction (or rollback if errors).
This is a pretty simple flow, and it works well. The tricky part is that the is joins data in a way that makes sense, but might not be what you expect. This doesn’t remove restores based on the date, but based on the backup rows being removed.

Know Your Tools

This is a poorly named procedure, but that’s not an excuse for anyone. If you use this, and likely should, you need to ensure that you understand how it works. The phrasing in the documentation makes sense, but it can be a little misleading as many of us might assume the date is applied to backup and restore history tables.

It is not.
July 3, 2024
DBAs, Organizing Your SQL Scripts in Git

I wrote an article that was published on SQL Server Central on how to get your scripts into Git. This post adds a few more thoughts on how you might get started.

This is part of my series on git that is designed to help people get started using version control in their daily work. You can see all my other posts on Git as well.

Organizing My Scripts

I showed a sample folder that looked like this, with a main area of scripts and then a subfolder for reports.

As a DBA team, I want to ensure we all easily can find things. If we don’t have a lot of scripts, I might keep most in a single folder. However, if this becomes 3 or 40 scripts, it’s easy to make mistakes or have too many similar things.

What I might organize things slightly better like this:

Here I’ve moved the Diagnostic queries from Glenn Berry into their own folder. Those I might run more rarely, though I might update them more often. Getting them into their own folder lets me move them out of the way. You could organize those by version, but the names keep them separate, so I’d probably just keep them all there.

I added a “ETL” folder for specific scripts related to that function. I might need those regularly, but this helps me find them. If I had different types of ETL stuff, like on-prem and AWS, or maybe different apps (“Sales DW” vs. “Inventory ETL”), I might put those in subfolders below there.

I also renamed the who_is_active.sql to “common_who_is_active_scenarios” where I have some calls with specific parameters set.

I didn’t do this, but looking at this, I’d probably add a “installation scripts” folder where I moved the sp_WhoIsActive.sql and other install versions of scripts into that location.

What I’m trying to do is just get DBAs to easily and quickly find scripts without accidentally picking the wrong scripts. This helps in pressure situations and also helps onboard new team members.

I didn’t show how to update and version scripts, but I’ll do that in a new SSC article.

July 1, 2024