When I quit my job at Redgate to be a consultant I thought I’d have more time to blog. Boy was that naive! It turns out running your own company is a bit of a time sink.

Anyway, I thought it was about time I wrote a proper technical blog post. But I’ll keep it quick. I’ve got to get back to my accounting. I hate accounting.

The Redgate Templates for Octopus Deploy are great, except…

If you use Redgate DLM Automation and Octopus Deploy to deploy your SQL Server updates you might use the Octopus Deploy “Redgate Create Database Release” step template.

I really like this step template as it allows you to separate the creation of a release from it’s deployment. This means you can review the changes and re-use the same release (including the same upgrade script) against multiple databases. By re-using the same upgrade script your deployments should become more reliable.

For an example of how to set this up, check out this tutorial.

One of the things this step template does is upload a diff report (“changes.html”) and an upgrade script (“update.sql”) as Octopus Deploy artifacts. (It also uploads a warnings.xml file but this is less useful because the same information is more easily read from the diff report).

However, when you have multiple databases being deployed by the same project each Create Release step adds more artifacts with identical names. This was annoying for two of my clients recently, both of whom had tightly coupled databases that they wanted to deploy together. (One had about 10 databases in their Octopus project and the other was close to 20.) As you can imagine, sifting through 20 files with identical names looking for the one that referred to the database you were interested in was pretty annoying.

Interestingly, Martin had posted a suggestion for a fix in the comments section on the Octopus Deploy Library page for the Create Release step template. The Step Template uploads the Artifacts using the Octopus PowerShell cmdlet New-OctopusArtifact. Martin suggested adding the -Name parameter along with the database name to give each artifact a different name. Simple, but effective.

Upon further investigation it seems that tekguy had already implemented Martin’s suggestion. Unfortunately, I think tekguy’s change was rolled back by MarkGould, I suspect by accident.

Working with Octopus Deploy Step Templates can be painful

Contributors to the Octopus Step Template Library need to remember to manually increment the version number of each template else users don’t get notified that updates are available. I suspect most contributors forget so other contributors don’t realise they aren’t using the latest version when making their changes. If they start with an older version of the plugin they will overwrite any newer work that other people have produced. Octopus Deploy don’t make it easy to spot conflicts because they reduce your entire PS script down to a single line of JSON so if you make a single change then the entire PowerShell script shows up as one big, complicated change.

Anyway, I’ve done my best to revive tekguy’s work while maintaining MarkGould’s contribution. Hopefully it’ll be merged soon by the good folk at Octopus Deploy and you can all update.

To summarise my little change:

Before

After (with a database called Trainers_SoC_Test)

And if you had multiple databases you would now have uniquely named artifacts for each database.

The moral of the story

When you work on Octopus Deploy Step Templates:

• Make sure you increment the version number
• Be really careful about conflicts
• Nudge the folk at Octopus to improve the dev experience

Request for feedback

I wrestled for a while with the best way to name the artifacts.

For example, should I use the target database name or the NuGet package ID? One of my clients uses the format “SQLinstance.DBname” for their NuGet packages and they have some databases on different instances that share the same name within the same Octopus project so for them the NuGet package ID would have been more appropriate but I suspect that for most people the target DB name would be more intuitive.

I also thought about including package version. Was it useful to be able to look at any artifact and know which version of the DB was being deployed? I decided not because you normally look at these artifacts from the Octopus Deploy release page so that information is probably already available to you.

Finally, if you go to any release in the Octopus UI, you see all the artifacts that have been created for all environments. This list can get very long so I considered including the #{Octopus.Environment.Name} too – but then the filenames got really long so I decided against it, coming to the conclusion that Martin’s original suggestion was probably the best.

But what do you think? Feel free to leave a comment or get your hands dirty and make your own contributions to the community library.

The convenient thing is that if you have a unique case, like my client who probably wants the package ID, it is relatively easy to copy and edit the step templates yourself on your own Octopus instance.

Anyway – that’s enough blogging. Back to my accounting.

YAWN.