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.

2. Create a folder named “Scripts”.

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.

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

4. Add the DbUp NuGet package to the solution.

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

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.

8. Import the reference into Program.cs.

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”.

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

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

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.

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.

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

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.

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

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

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.

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

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

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

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

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.

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

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

33. Press the “Deploy to ” button.

34. Then press the “Deploy Now” button.

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

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.

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 *