DEV Community: David Wengier

Debugging IIS Rewrite Rules

David Wengier — Mon, 21 May 2018 00:00:00 +0000

Where I work we have a number of ASP.NET web applications that run different parts of our site so that we can have some segregation of code and containment of scope without just having an enormous monolithic project that holds everything intermingled together. It’s nothing too exciting technically, but the marketing department also needs to be able to present the entire site as a whole to visitors, and the Google Bot, for that sweet sweet SEO juice (and easier navigation and other less cynical reasons I’m sure). The way we achieve that is with prodigious use of the IIS URL Rewrite engine, which allows us to create a set of rules that take the incoming HTTP requests and either route them through to different web applications, or different virtual paths, or stop some in their tracks entirely.

There is lots of documentation and examples on the web about setting these up, and I certainly don’t claim to be an expert in the full range of their capabilities, but one thing I do know is that whilst they are fantastic when they are working, and just sit there happily doing their job without complaint, when adding new ones it can sometimes appear to be a bit of a mystery as to whether they are working. Additionally, because we use them to consolidate a lot of different applications and URLs into one coherent set of public URLs, getting them running locally quickly ends up with requests to local environments being redirected to live environments, with no real way of knowing if it was because the rules are working perfectly, or simply because they fell through to some catch all at the end.

Fortunately there is a way to debug the rules, or at least get logging out of the engine, albeit a little hidden.

Not all failures are failures

The answer lies in the IIS Failed Request Tracing feature and the fact that it is possible to configure it to trace successful requests just as easily as failed ones. The feature can be access through the IIS manager or the configuration can be specified in the web.config file of your application.

The module itself has quite a nice wizard to guide you through setting up a new rule, however to debug rewrites in the way that I want to, its a little unintuitive so I’ll detail exactly what I did.

The first step is straight forward enough, where you select which filenames you want to trace. In the modern era of MVC and WebApi this feels a little antiquated, since file names are a bit naff, so probably best, and certainly easiest, to just leave this selection on “All content”.

The second screen is where the real magic happens:

The first input option here is to specify which HTTP status codes should be traced, and this is where we flip the “failure” title on its head. By specifying a successful code here (ie, 2xx or 3xx) we get tracing for successful requests and not just failed ones.

Depending on how much logging you want happening, you could narrow this down to just the statuses you want to track, for example just specify 301 to trace permanent redirects, or you could widen it. I think starting as wide as you can and specifying 200-399 for this value is the best, that way even if you’re adding a new permanent redirect rule that you want to trace, you’ll get the logs even if you had something wrong with your rule and the request fell through to a different rewrite rule.

If the requests you’re trying to trace are getting through to your site and resulting in errors or bad URLs you also might want to add in 404 and 500 to the list.

The 3rd screen allows for the selection of which IIS modules you want to trace so in order to keep some of the noise out of the log its best to untick everything except Rewrite and RequestRouting. Leave the verbosity at verbose, mainly because its fun to say “verbose verbosity”.

And thats it, you’re all configured. You can also configure the equivalent of all of this in the web.config file with the follow config:

<tracing>
    <traceFailedRequests>
    <add path="*">
        <traceAreas>
        <add provider="WWW Server" areas="Rewrite,RequestRouting" verbosity="Verbose" />
        </traceAreas>
        <failureDefinitions timeTaken="00:00:00" statusCodes="200-399" />
    </add>
    </traceFailedRequests>
</tracing>

Finally make sure the feature itself is enabled by clicking “Edit Site Tracing…” from the right hand bar and tick the Enabled checkbox. If it’s already enabled then great, but the screen is still useful to grab the path to the log files, which by default is %SystemDrive%\inetpub\logs\FailedReqLogFiles.

Looking at the log files

The Failed Request Tracing module logs to XML files which I definitely don’t recommend looking at in raw form. Fortunately IIS also generates an XSL file for you which nicely formats the logs into something vaguely readable. I’ve found the easiest way to view the logs is simply to open the XML file in Internet Explorer (yes, I know) as that will automatically find and apply the xsl file, whereas Chrome did not.

The logs themselves are quite verbose, as we requested:

You’ll see the output for each rule you have in your rewrite configuration, you can see the input values and the patterns matched against. You can see whether each on succeeded, though its worth noting that you need to apply the negate value yourself so a negative rule might say “Succeeded: false” and you have to remember that that means the rule as you wrote it did in fact match.

Hopefully after trawling through the file you can work out what is going on, though personally I found it easier to search the file for the rule you think is probably at fault, rather than scanning through them all, but I’m coming from a codebase with a lot of rules.

Promoting Binaries and Hotfixable Deployments

David Wengier — Mon, 07 May 2018 00:00:00 +0000

There are a two different schools of thought when it comes to deploying to production environments. Well okay, we’re developers, so there are probably 100 different schools of thought but bear with me. One option is to promote the same binaries from testing, through staging, and all the way to production, and the other is to maintain a branch in your source repository for the current state of production, and deploy from that. The general thinking is that with the former you get safety in knowing that your production deployments is exactly what has been through your testing cycles, and with the latter you’re always in a position to hotfix and correct a production issue regardless of what state your testing branch might be in.

Fortunately its an argument that can be avoided, and instead you can set up an environment where you get the best of both worlds: Predictable results from promoting binaries to production and an insurance policy in case you need to hotfix. I am using TeamCity and Octopus Deploy to do this but the ideas are the same no matter what technology you use.

Commit hashes are important

One of the best pieces of advice I have for anyone setting up any kind of CI/CD, automation, devops workflow is to get your commit hashes into your binaries and packages as early and as often as possible. Having a known identifier that can track binaries and directly correlate them to source code is invaluable for all sorts of things, but in this case its especially important so that the build server and deployment packages know what each other is talking about.

To get commit hashes into your build output in TeamCity is as straightforward as configuring a setting on the build in question. The “Build number format” setting dictates how TeamCity should format build numbers in its output, and also the format of the %build.number% variable that you can use in, or pass in to, scripts and build steps. The normal approach for a build number would be something like 1.0.%build.counter%, where the major and minor versions are hardcoded to 1.0, and the build number increments automatically with every build. Personally I’m a fan of using something like GitVersion to allow the number of commits to be used instead of the build number, as it allows resiliency across build server reinstalls, but thats for another discussion.

Tagging the commit hash on the end is done by adding a hyphen after the build counter, and then inserting the commit hash. In TeamCity this is the %build.vcs.number% variable, so our full build number format is as follows.

1.0.%build.counter%-%build.vcs.number%

This will give a build number something like 1.0.134-770ac6d169006ce42b5bbc022a6a166135bbe8a7. Success in that we have the commit hash in the build number, but its a bit ugly and unnecessarily long. You only need around 7 or 8 characters to be unique for most repos (the Linux Kernel is starting to need 12 but they have hundreds of thousands of commits) so I like to shorten the hash down a bit. Doing this in TeamCity is a little un-intuitive as there are no operations that can be performed in the simply macro language you use to specify the build format. To change the build number you need to use a build step and use the TeamCity feature called service messages, which is a standard pre-defined structure of output, written to standard output, that TeamCity will pick up and process. I’ve done this with a short PowerShell script as the first step in each build I define.

Write-Host "Old build number was: %build.number%"
$buildNumber = "1.0.%build.counter%"
$shortHash = "%build.vcs.number%"
$shortHash = $shortHash.substring(0, 10)
$buildNumber = "$buildNumber-$shortHash"
Write-Host "New build number is: $buildNumber"
Write-Host "##teamcity[buildNumber '$buildNumber']"

My script is overly long because of the debugging output but I find build logs verbose enough already so keeping a couple of lines out of it isn’t worth worrying about. Strictly speaking the whole script could be a one-liner.

Write-Host "##teamcity[buildNumber '1.0.%build.counter%-$("%build.vcs.number%".substring(0, 10))']"

I’m keeping the short has at 10 characters for no good reason, you could easily change that to whatever you desire. Its worth noting that with this as the first step of the build plan the “Build number format” setting has been rendered effectively useless for all but the first few seconds of the build, until it runs this script. With the script in blame the build number will now be 1.0.134-770ac6d169.

Pass hashes through to Octopus

Now that we have our short build number its important to use that in the version number for any package pushed to Octopus, and the release made from those packages. This gives full traceability from git commit, to build, through to deployment. If you also use something like NerdBank.GitVersioning you can tag your DLLs with the same commit hash, which means you can also include it in your application logs or audit tracking.

With the version number in the package being deployed in Octopus it means we can now create a powershell script, and put it in the process for a production deployment. That script fast forwards the master branch to the specific commit that has been deployed, guaranteeing that the master branch will point at exactly where the develop branch was at when that package was built.

Set-Location -Path "<path to source repository>"
$vers = "$($OctopusParameters["Octopus.Release.Number"])".Split("-")[1]
Write-Host "Version is: $vers"
$commitHash = $vers.Substring($vers.IndexOf("-") + 1)
Write-Host "This release is from commit hash: $commitHash" 

Write-Host "Fetching latest origin just to be sure"
git fetch origin --prune
Write-Host "Resetting to current master"
git reset origin/master --hard
Write-Host "Fast forwarding to $hash"
git merge $hash --ff-only
Write-Host "Pushing back to origin"
git push origin
Write-Host "Finished"

The script needs to be run in a git working copy and assumes master is checked out, though that could be added easily enough. I could have reset to the specific commit and just pushed that, but I like the extra protection that --ff-only provides. It ensures that if anything goes wrong with the working copy, or the script gets run at an incorrect time, there at least won’t be any commits lost that will require navigating the reflog for. There might be a better way to achieve this, or perhaps that worry is for nothing, but I don’t profess to be a git expert.

Hotfixes are now just another build

Now that master is at the point of the deployed production build, hotfix branches can be created from, and merged back into, the master branch, which can then be built and deployed with the normal build and deployment process knowing that any changes that have been made to the develop branch will not be included. In an ideal world develop remains deployable and this process isn’t needed, but an insurance policy is a good idea and in this case, cheap to have. In my case I’ve set up a separate build on TeamCity for the master branch that is not automatically triggered, and considering each production deploy will change the master branch thats best.

The hotfix build releases on a hotfix channel in Octopus so that it can deploy direct to staging, avoiding test. This way test still maps to the develop branch so that process isn’t interrupted. Specifying the channel to use is a matter of setting the right parameters in the TeamCity build step that does your Octopus release creation.

The only issue that I ran into with this is that because I’m not using a “smart” build number, but instead just a numerically increasing build counter, the first hotfix build didn’t actually get deployed by Octopus. Looking at the TeamCity and Octopus logs it was clear that while the build and release versions were correct, when it came time to pick which packages went into a release Octopus saw the hotfix build as being older than the last develop build, simply because of the build counter.

To solve this I configured the Octopus release creator to force a package version to use. Since we have commit hashes at every step of the way the actual version numbers all become rather irrelevant so this feels like a perfectly safe thing to do. In theory if two releases point to the same commit hash, it doesn’t matter if one is v2.0.1 and the other is v3.56.231, they have the same code and therefore will function the same way.

You might need to click “Show Advanced Options” in TeamCity to get this item to appear.

Hope for the best, plan for the worst

Now we have a situation where the devlop branch is build an deployed automatically, as often as we like. We know the commit hash at every step of the way, so we can map everything back to the raw source commit and we have our insurance policy in place if things go wrong, via the master branch moving and hotfix build available for manual triggering.

Targeting builds for multiple frameworks and machines

David Wengier — Mon, 30 Apr 2018 00:00:00 +0000

I’ve recently starting working on a new project in my spare time, DbUpgrader, and I’m trying to work on it for at least a few minutes every night. I variously use a MacBook Pro or Windows machine, and sometimes I use Visual Studio 2017 but sometimes I’m just using Visual Studio Code and mucking around on the console. I’d like to also try out Visual Studio for Mac sometime soon. All of these different environments have their advantages and features, but I mostly want to make sure that I can work in all of them, on the same project, without issue.

Enter the new project system in Visual Studio which allows for minimal .csproj files that remain easily editable MSBuild targets without having to compromise and have separate build scripts for each scenario. The challenge I set myself was to see if I could create a single solution with projects that fulfilled the following needs:

Opens in Visual Studio on Windows without error
Builds in Visual Studio without issue
Tests appear in the Test Explorer in Visual Studio and tests run as expected
Works with dotnet build on Mac and Windows
Works with dotnet test on Mac and Windows

This may seem easy but its slightly complicated by the fact that I want to support not only the full .NET Framework v4.6 on Windows, but also .NET Core on Mac and Windows, without the .NET 4.6 support being an issue on Mac. To support .NET 4.6 the shared libraries need to be .NET Standard 1.3 or lower, but I also have some functionality and tests that use Microsoft.Data.Sqlite which is .NET Standard 2.0, and therefore incompatible with .NET 4.6. So on Windows I want a build for .NET 4.6 without Sqlite support, and a build for .NET Core with it, and on Mac a build for .NET Core with support and no errors relating to missing .NET Framework support.

Multi-targeting means multi-builds

The easiest way to think about multi-targeting in the new project system is to remember this simple fact: Each target framework acts like its a duplicate of the whole project. Consider a .csproj file with the following declaration.

<TargetFrameworks>net46;netcoreapp2.0</TargetFrameworks>

When building this project MSBuild will run the build twice, ones for .NET Framework 4.6 (net46) and once for .NET Core (netcoreapp2.0). Knowing this helps explain the logic of how the project file should be laid out in order to change what is built for each target.

In my case I want the Sqlite code to only be built for netcoreapp2.0 because it needs to target .NET Standard 2.0, and net46 is not quite at that level. The full table of versions and what they support is on GitHub but suffice to say that net46 maps to .NET Standard 1.3.

Armed with this information we know that we need to exclude the Sqlite dependencies and files when building for net46 and this is done with a Condition attribute on the relevant spots in the project file.

<ItemGroup Condition="'$(TargetFramework)' == 'netcoreapp2.0'">
  <ProjectReference Include="..\..\src\DbUpgrader.Sqlite\DbUpgrader.Sqlite.csproj" />
</ItemGroup>

Here I am instructing the project system to only reference the Sqlite project if the target framework of the build is netcoreapp2.0. This is where thinking about the targets as separate builds makes sense. When its passing through this file building for net46 MSBuild will see that the condition is not met, and simply skip over this part of the file. No reference will be added. When building for netcoreapp2.0 the reference will be added.

Excluding files

Thats all well and good for the reference but obviously if the reference is there then there must be files that use it. Because the new project system doesn’t need specific file inclusions its unlikely that you would have a node that can have a condition added to it, so we need to be a bit creative.

You can use an exclude attribute on a <Compile> element alongside the normal include but I found the usage of that a bit ugly, and since by default there isn’t any <Compile> elements needed in the Sdk projects it seemed a bit clunky to add one back in. The solution I settled on was to simply update the DefaultItemExcludes property that already exists, and is already used by the default project. The glob support in the new system makes this a breeze too, needing only a single addition to exclude multiple files and folders/subfolders.

<PropertyGroup Condition="'$(TargetFramework)' == 'net46'">
  <DefaultItemExcludes>$(DefaultItemExcludes);Integration\Sqlite\**\*</DefaultItemExcludes>
</PropertyGroup>

Since we’re now telling MSBuild to exclude items we don’t want, we flip the condition so its based on net46. These two things combined mean we get the project including everything we want when building for .NET Core, and not including the wrong things when building for .NET Framework.

Targeting the targets

If the conditions so far have been based on the frameworks being targeted, then how do you make the targets conditional? To do that you need something at a higher level and fortunately the operating system fills this role perfectly. We can tell MSBuild to build .NET Core and .NET Framework on Windows, just .NET Core on a Mac, and everything will flow correctly from there based on whichever target is being built at the time. The conditions look very similar too.

<TargetFrameworks>netcoreapp2.0</TargetFrameworks>
<TargetFrameworks Condition="'$(OS)' != 'Unix'">net46;netcoreapp2.0</TargetFrameworks>

Two things to note here: The first thing is that the OS for Mac is “Unix”. This surprised me, but is not a big deal. I guessed originally that it would be “Mac” and when that didn’t work I simply added a build task to my project file and observed what the output was. The task is as follows, and its run by specifying InitialTargets="LogDebugInfo" in the <Project> node, but its a good reminder again that these csproj files are also simply MSBuild scripts and can be treated as such - though double check Visual Studio is still happy afterwards.

<Target Name="LogDebugInfo">
  <Message Text="Building for $(TargetFramework) on $(OS)" Importance="High" />
</Target>

Secondly you’ll notice that there is only a condition on one of the elements. This was not what I tried first, as I assumed that there would be problems having duplicated elements without conditions to differentiate them. Indeed whilst having conditions on both worked fine in the dotnet build world (on Mac and Windows) Visual Studio itself got very confused. I posted about it on Twitter and the very helpful David Kean who works for Microsoft on the new project system pointed me to this GitHub issue explaining that I’d hit a bug. It wasn’t a big deal to remove one condition I just had to make sure the order was right. Having two <TargetFrameworks> elements means the second one overrides the first, so in order for Windows to still get net46 support it had to come last.

It looks like as long as the project file has one element without a condition Visual Studio (at least v15.6.7 that I’m trying this on) is happy, though I suspect the IDE thinks I’m developing for .NET Core only. When building from Visual Studio however, since it just runs MSBuild, there is no issue. In theory this could mean that the IDE could mark something as correct and have the build subsequently fail, or vice versa, but thats a minor price to pay for the flexibility and I presume that would only be a temporary problem until the build is fixed.

I like your new stuff better than your old stuff

In general the new project system is great, and I love being able to edit the project file while its open in Visual Studio and seeing the changes take effect immediately. In general getting people to think about project files and build files is a good thing as it encourages the “devops mindset” which I’m personally a fan of, and think every developer should try to attain.

But thats commentary for another time.

Codify your coding standards with .editorconfig

David Wengier — Mon, 23 Apr 2018 00:00:00 +0000

Every dev team has coding standards. Sometimes they’re established through convention, tradition, example and maybe sometimes there is even a formal document outlining them (hopefully in a living format that can be updated!). No matter how its done though, nobody wants to be the bad guy in code reviews or pull requests and pull people up for what are usually minor infractions, however at the same time nobody wants to see a codebase be neglected and let inconsistency creep in, or readability wane.

Visual Studio has many excellent rules and formatting options to enable it to be fully configured to match your coding standards and conventions, but in a team environment it can be a pain to keep everything in sync. There are “team settings file” options which work most of the time but its not perfect and it still requires everyone to configure Visual Studio to use that shared file any time they join a team, or reinstall their machine.

Fortunately there is a way to enforce some coding standards at tooling level without these concerns, in particular with Visual Studio 2017 it now honours the configuration in a .editorconfig file, which overrides an individual developers settings and tells the IDE how to behave on a per-repository basis. The .editorconfig file is simply committed to the root of the repository and from then on it dictates things like indentation, formatting, style and naming rules. Not all IDEs will support all of the same features but the list on the official site is certainly impressive.

In this post I’ll be talking about how to codify some specific .NET related rules for Visual Studio. For more detailed information the official documentation is great, though I might be biased since its where I submitted my first ever PR to the docs project.

Naming Rules

Naming rules allow you to codify the standards around naming and casing of fields, properties, constants etc. in your codebase. Each naming rule needs a name, which is specified in lower case with underscores, a severity, and a style to apply. For example:

dotnet_naming_rule.public_members_must_be_pascal.severity = error
dotnet_naming_rule.public_members_must_be_pascal.symbols = public_symbols
dotnet_naming_rule.public_members_must_be_pascal.style = pascal_style

In this example dotnet_name_rule denotes that we’re defining part of a rule, public_members_must_be_pascal is the name of our rule, and we’re going to apply it to symbols that match the public_symbols naming symbols which we’ll define later. We want this rule to be enforced at all times so the severity is error, which means Visual Studio will treat violations the same as it treats compiler errors. Lastly we’ve said that things that match this rule should use the style defined in pascal_style which is the name we will give to our style.

Naming Styles

Naming styles define how a developer should format symbols that match any applied rules. Like naming rules they have a name, and they can then specify prefixes, suffixes, word separators and capitalization rules. In this case we simple need to define the capitalization rule of pascal_case like so:

dotnet_naming_style.pascal_style.capitalization = pascal_case

Again dotnet_naming_style means we’re defining a style and pascal_style is the name of the style which we used in the rule.

Naming Symbols

The final piece of the puzzle tells Visual Studio which symbols the rule should apply to. For our public_symbols we need to specify the accessibility to be public, and that we want the rule to apply to properties, methods, fields, events and delegates. We could probably also add in classes, structs and enums to this.

dotnet_naming_symbols.public_symbols.applicable_kinds = property,method,field,event,delegate
dotnet_naming_symbols.public_symbols.applicable_accessibilities = public

Naming symbols also allow you to specify required_modifiers so that you can target static, readonly, async or const symbols differently.

Putting it all together

Those three elements combined are what makes a rule fully codified and means Visual Studio can be the bad guy when it comes to enforcing coding standards. No more need to have arguments about whether constants are SHOUTING_AT_YOU or are ABitMoreSubtle, you can end the age old battle between _fields and m_fields etc.

Additionally naming symbols and styles can be used by multiple naming rules so you only need to define something like pascal_style once to apply a pascal case capitalization convention to a few different things.

Be warned however, if you’re introducing this to a legacy code base you need to tread carefully and probably just take the hit and fix all of the issues it raises in the same commit. Even if you set the severity to warning or suggestion at the very least you’ll be potentially filling up the error window with issues and it’s never a good idea to give anyone a reason to ignore things in the error window.

The .editorconfig file can also be used to specify indentation styles, brace usage and style, var usage and even whether this. is required to be used, or where System using statements should go. If you can spend the time to fill out all of the possibilities it makes like much easier in a team, as your codebase is immune to the quirks of individual dev machine configurations, or in open source projects ensuring contributors always match the style of the project they’re contributing to.

A full example of the .editorconfig file I’m currently using for my personal projects can be found in the DbUpgrader project here.

Gotchas

Some gotchas with setting up editor config files that I’ve found so far:

If you specify constants should be pascal case then VS won’t error when a constant is all caps since thats still valid pascal case.
Ordering of rules in files seems to be inconsistent so rules around private fields and constants sometimes overlap for private constants, and VS will think you’re doing the wrong this.

I will update the post if I find others.

Reviewable Stored Procedures and Views with DbUp

David Wengier — Mon, 16 Apr 2018 00:00:00 +0000

We use DbUp at work to manage database changes and migrations and for the most part it works fine as long as you have a known schema that you’re coming from. The downside of the current implementation is that changes to stored procedure definitions are not easily reviewable in source control. Fortunately enabling this workflow with DbUp is relatively straightforward.

Project format

Our DbUp project looks fairly standard:

DbUp takes care of running the scripts and making sure none are run more than once via its in build journaling system, a record of which is also stored in the database. The problem is that those “Alter Procedure” scripts all simply have a full copy of the stored procedure in them, even if those half-dozen files are all changing the same stored procedure.

The first step in enabling reviewable stored procs and views is to create a new folder for scripts that will be unjournaled, so they are always run whenever DbUp is run. I’m going to just call this StoredProcs for now as thats the first thing I ‘ll be moving across.

The basic idea is that you use that folder for SQL scripts that contain a simple DROP and CREATE script for each stored procedure. DbUp runs these scripts every time, essentially making sure the database definition is always correct, and allowing developers to make changes to the existing scripts in the source repository, rather than having to create new ones all the time, as with normal migration scripts.

The DbUp script runner

The existing DbUp script runner looks fairly basic, like this:

var upgrader = DeployChanges.To
               .SqlDatabase(connectionString)
               .WithScriptsEmbeddedInAssembly(Assembly.GetExecutingAssembly())
               .LogToConsole()
               .JournalToSqlTable("dbo", "SchemaVersions")
               .WithTransaction()
               .Build();

We need to add a new upgrader to this script and instead of storing the journal in a table we will use the NullJournal that is build into DbUp

var storedProcUpgrader = DeployChanges.To
               .SqlDatabase(connectionString)
               .WithScriptsEmbeddedInAssembly(Assembly.GetExecutingAssembly())
               .LogToConsole()
               .JournalTo(new NullJournal())
               .WithTransaction()
               .Build();

The last piece of the puzzle is to put a filter onto each upgrader so each one only loads the scripts we want. The final code looks like this:

public static int Main()
{
    var connectionString = ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString;

    var upgrader = DeployChanges.To
        .SqlDatabase(connectionString)
        .WithScriptsEmbeddedInAssembly(Assembly.GetExecutingAssembly(), s => !IsStoredProc(s))
        .LogToConsole()
        .JournalToSqlTable("dbo", "SchemaVersions")
        .WithTransaction()
        .Build();

    var storedProcUpgrader = DeployChanges.To
        .SqlDatabase(connectionString)
        .WithScriptsEmbeddedInAssembly(Assembly.GetExecutingAssembly(), s => IsStoredProc(s))
        .LogToConsole()
        .JournalTo(new NullJournal())
        .WithTransaction()
        .Build();

    // migrate the database data, and table schema changes first
    if (!UpgradeAndLog(upgrader))
    {
        return 1;
    }
    // now we can change stored procs that rely on the adjusted schema
    if (!UpgradeAndLog(storedProcUpgrader))
    {
        return 1;
    }

    Console.ForegroundColor = ConsoleColor.Green;
    Console.WriteLine("Success!");
    Console.ResetColor();
    return 0;
}

private static bool UpgradeAndLog(DbUp.Engine.UpgradeEngine upgrader)
{
    var result = upgrader.PerformUpgrade();
    if (!result.Successful)
    {
        Console.ForegroundColor = ConsoleColor.Red;
        Console.WriteLine(result.Error);
        Console.ResetColor();
        return false;
    }
    return true;
}

private bool IsStoredProc(string scriptName)
{
    return (scriptName.StartsWith("My.NameSpace.StoredProcs.", StringComparison.OrdinalIgnoreCase));
}

Get reviewing

Every change to the stored proc of view definition script will be just that - a change - so whatever source repository diff process you use will show only what has been done. Additionally you always have the current up-to-date definitions of your scripts in your source repository so you’re one step closer to not having to worry about having a known good starting point for your database, at least from the schema point of view.

So far we’re rolling this out on a change-by-change basis, but there is no reason all of the relevant parts of the database couldn’t be scripted to seed this effort giving you a known baseline.

This same theory applies to Views or Functions, or anything else where a migration script would need to contain the entire definition, and dropping the object is not a destructive operation.

I don’t want to remote into production

David Wengier — Mon, 09 Apr 2018 00:00:00 +0000

A friend of mine tweeted this article, and excellent summary today, about a recent production outage at Travis CI:

A read-only prod is a slightly safer prod, if you don't have access to truncate all tables, then it is less likely to happen :Phttps://t.co/7urWybtZA8

— NullOps (@NullOpsio) April 9, 2018

I personally feel even stronger about this: I don’t want access to production, read only or otherwise. I don’t even want to install the database management software, be it SQL Server Management Studio or MySQL client, if I can help it.

I started a new job today and during the onboarding it was mentioned that production server access was locked down by IP so if needed, it would have to be done via a VPN if I wasn’t in the office. Not to speak ill of my new employer, let me be clear this was only mentioned as a “just in case” option, as part of an answer to someone elses direct question, and not part of regular onboarding information that people need to know. In fact everyone there knows that its not a good idea, and something that should be worked to remove in future, and helping to do that is a large part of my role, but I digress.

I said flat out: I don’t want access.

I don’t trust myself, and you should too

Eventually everyone has that moment where they do the wrong thing. Perhaps they run an UPDATE statement without a WHERE clause, or they’re connected to the wrong environment when they tweak some configuration value. Most people, you hope, only make these sorts of mistakes once, and I’ve made mine (quite a few years ago, don’t worry!) and I don’t want to do it again. The easiest way I can think of to guarantee that is to simply make it impossible.

Yes, I can double check everything I do. Yes, I can get people to check over my shoulder, or review. Yes, I can work through checklists with well documented steps.

Or I can just make incorrect actions impossible. If I can’t remote to a server, I can’t be remoted to the wrong server. If I can’t connect to a database, I can’t forget part of a script or statement.

If you want something fixed, make it a problem

The ideal situation for production environments (or indeed most other environments) is that their setup and configuration is completely automated and needs no manual work. By making manual work impossible you force people and teams to do the necessary work to create tooling to enable that. There can be no shortcuts taken and temptation is removed by virtue of a firm wall between developers and where their code is deployed. If I need to make a change to a database schema I want the only option to be to create a change script, or similar. I would apply that script to my dev environment and in time to testing and staging environments.

If the only possible path is automated tooling then by the time a deployment to production is done not only are you guaranteed that the tooling is in place, you’ve also ideally tested its execution a few times in various environments.

Its always tempting for developers to leave this sort of effort to the end, but that sort of thinking is what leads to manual processes lasting years as workarounds because other work is higher priority. On the other hand if a developer has no option to solve their annoyance than to do the work right the first time, rest assured they will do that.

Data is cheating too

If you want to take this to the logical extreme, which I do, I also don’t want to modify any data in the database directly. If I’m building out a new feature that requires data in a specific shape, either to run or simply for me to manually test with, then I would rather build the seeding scripts, or ideally the data management front end, first. The seeding scripts can help with functional/integration tests, or the data management work presumably solves some future need in the product (and if thats not the case then don’t do it! But also consider why that data is needed).

Codified knowledge is shared knowledge

The other advantage of creating tooling, scripts or otherwise automating things is that anything that is codified and committed to a source repository is something that other people can reason about, read and hopefully understand. There is nothing that reduces Bus Factor like having a series of scripts and tools that anyone can pick up.

Essentially, avoid making manual work a necessity as much as is humanly possible. Of course be pragmatic about things, in particular there is nothing wrong with doing manual work once to get a feel for it, before automating, but I loathe to do something twice or three times. Additionally some manual work can be fun to opt in to, and I specifically avoid using tools like Ninite or Chocolately for this reason, as I simply enjoy the process of building a new machine.

But I don’t want to touch the production environment.