Press "Enter" to skip to content

Database deployment via DbUp and Octopus Deploy

I recently read the book “Exploring Octopus Deploy” by Steve Fenton. It is a good read.

There’s also a good description of how to install DbUp at: read the docs
And how to use DbUp with Octopus Deploy at:
youtube video

In his book, he describes how to deploy databases using the migration-based approach via the DbUp tool and Octopus Deploy.

Here’s how to do it.
1. Create a console application in Visual Studio.
dbup1

2. Create a folder named “Scripts”.
dbup2

3. Create a .sql file. The name matters here. I’ll call mine “Script-00001_Create_Database.sql”. DbUp will be creating a journal table and will be tracking these scripts.
dbup3

Note: Make sure that any scripts that you create has a Build Action Property of “Embedded Resource”
dbup_note1

4. Add the DbUp NuGet package to the solution.
dbup4

5. Alternatively, you could type in “Install-Package DbUp” in the “Package Manager Console”.
dbup5

5. Add the following code to the “Program.cs” file:

6. Specify the database that you want in the connection string of the App.config.

7. Add a reference to the “System.Configuration” assembly.
dbup7

8. Import the reference into Program.cs.
dbup8

9. Add the code to a new “Deploy.ps1” in the root of the console solution.

10. Change the property of the “Deploy.ps1” file to be “Copy always”.
dbup10

11. Add OctoPack to the solution for Octopus Deploy NuGet facilitation.
dbup11

12. Make sure that you now have two NuGet packages included.
dbup12

13. Add some code to the “Script-00001_Create_Database.sql”. As an example, you can use the following code.

14. Create a batch file named “test_nuget.bat” and copy it tothe same directory as DbUpDeployer.sln in your solution.

15. Run the “test_nuget.bat” file and you should see a nupkg created. This is the file that will be pushed to Octopus Deploy.
dbup15

16. Note: If you were using this to deploy the database for an application (like a web application), you could specify a build order so that the web application is built before DbUpDeployer pushes the database. For more information on this, see the Exploring Octopus Deploy book.

17. Publish the package to the Octopus server. To start, you’ll need to get the API key from the Octopus server. To do this, select the “Profile” option from the dropdown menu.
dbup17

18. Select the “API keys” tab, then click on the “New API key” button.
dbup18

19. It will ask you for the purpose of the key, put in something descriptive. Then it will show you the actual key. I’ve obfuscated it here, but you’ll want to copy this value and keep it in a safe place.

20. Modify the “test_nuget.bat” file to add the OctoPackPublishApiKey and OctoPackPublishPackageToHttp values:

21. Test out the “test_nuget.bat” file by running it. You can see below that the package was pushed to the Octopus Deploy Server’s internal NuGet repository.
dbup21

22. To check this on the Octopus Server, click on the “Library” tab and then the “Packages” tab.
dbup22

23. Scroll down and you’ll see the actual package.
dbup23

24. At this point you would add the command from the “test_nuget.bat” file into your build server. This is beyond what we’re trying to do here, but if you have Team City or a TFS build server, it’s pretty straight forward for a developer with good StackOverflow-Fu skills.

25. Click the “Add Project” button on the “Projects” tab.
dbup25

26. Give the project a name and click the “Save” button.
dbup26

27. Click on the “Process” button and then the “Add Step” button.
dbup27

28. Click on the “Deploy a package” link.
dbup28

29. Update the “Step name”, “Deployment targets” and “Package”. Note that the “Deployment targets” is any role that you want to associate.
dbup29

30. If you want to change the app.config values, you could click this setting. For the purpose of what we’re doing here, we don’t need to do this.
dbup30

31. With this step in place, we can now create a release by clicking the “Create Release” button.
dbup31

32. We can add release notes, as you see in step 1 shown below. Then click the “Save” button as shown in step 2.
dbup32

33. Press the “Deploy to ” button.
dbup33

34. Then press the “Deploy Now” button.
dbup34

35. If everything worked correctly, you should now see a success screen like the one shown on the screen below:
dbup35

36. Now check in SQL Server Management Studio that the “Book” table that we created actually was created. In the picture below, you’ll see #1 shows it was created. Note also that there’s a second table called “dbo.SchemaVersions” at #2. This is the table that DbUp uses to track which scripts have been run on each system. To check the values, run a query like the one in #3 and you’ll see a result come back like #4 showing which scripts have been applied.
dbup36

I hope that this has helped you to get DbUp up and running against your Octopus Server.

Be First to Comment

Leave a Reply

Your email address will not be published. Required fields are marked *