DbUp makes it easy to deploy and upgrade SQL Server databases by running change scripts.
They aren’t kidding. Using DbUp turns out to be super simple. At first I expected that for this post I’d simply describe step-by-step how to use DbUp to implement a change script strategy. But there is already an excellent article on the Octopus Deploy blog that goes over all this (and more.) The info about DbUp starts about a quarter of the way down the page, and has lots of screenshots.
A more compact article can be found on the home page of the DbUp documentation itself. Either of these articles might tell you all you need to know on this topic. As a result, I’m not going to repeat all that here. But still, let’s summarize the basic steps:
First, use Visual Studio to create a console app.
Next, import the DbUp Nuget package.
Add a Scripts folder in the VS project.
Create and add change scripts to that folder, marking them as Embedded Resources.
Copy a few lines of code into the Main() method (we’ll be expanding on this in the rest of this post.)
Set a connection string in app.config.
Build and then execute the new console app.
Finally, look in the targeted database and verify that your changes were applied.
None of this is difficult, so far. And the beautiful thing about packaging this as a console app is that it can be executed anywhere – there is no dependency on Octopus Deploy. As a result, not only is this approach highly testable, it also means that you can use DbUp even if you don’t use Octopus at all.
Let’s Do That Again!
In the list above, Item 5 mentions the Main() method. To get up and running quickly, you can copy the Main() method from either of the pages linked in the previous section. Both those articles show just about the simplest thing that will work. If your needs are simple, this may be all you’ll ever need. The DbUp package provides a nice fluent interface, and the code is very straightforward. In the simplest scenarios, using DbUp can be pretty much of a slam dunk.
In my case, I started out by copying the entire Main() method directly from the DbUp website. The first time that I ran the resulting console app, everything worked great! Fantastic! However, the second time I tried it for real, my database ended up in a bad state. Sad! So what happened?
I Read It in Somebody’s Journal
One of the benefits of using DbUp is journaling. While DbUp is running a script, it adds the name of each script to a special SchemaVersions table. This feature ensures that a given script only runs one time against a database instance, no matter how many times you might run the console app.
This is a great feature, and it implies that your change scripts are not required to be idempotent. Another benefit is that, if you ever want to know which scripts have been applied to the database to reach its current state, you can simply query this table.
However, my company’s deployment process works differently from some. In our case, for each environment below Production, we take last night’s backup of the Production database, copy it to the non-Prod server, and then apply our set of change scripts. Some of these scripts, of course, make the desired schema changes.
Keeping It Fresh
Others scripts though, which we call PostRefresh scripts, change the Production application settings, for example, to their non-Prod equivalents. We also have a script called Version.sql, which we use to record a row in a table named DbVersion each time that we deploy. The contents of this table gets reflected on each application’s Version page. We need these PostRefresh scripts to run every time we deploy.
Using DbUp, our PostRefresh scripts ran just fine – that is, right until they had been deployed to Production for the first time. After that, because of the journaling feature and the fact that these scripts had been previously “seen” by the database, DbUp prevented the scripts from running again. While this is perfect for our ordinary scripts, for the PostRefresh scripts this is NOT what we needed. So what to do?
Let’s Just Pretend That Never Happened
Fortunately, DbUp also has the concept of the NullJournal. While the NullJournal is in use, the names of the scripts are not written to the SchemaVersions table. Instead, the names of these scripts go straight to the bit bucket. Now these scripts will run every time, over and over. Perfect!
Before we could do this though, we needed to let DbUp know on which scripts to skip the journaling. Since we already referred to these as the PostRefresh scripts, we decided to take a convention over configuration approach. Under this new convention, the name of each such script now starts with the phrase “PostRefresh_” – and not forgetting about Version.sql – we ended up with the IsAlwaysAppliedScript() method shown below in Script 1.