Update Database with Dbup When Deploying With Kudu

Update: @DavidEbbo checked the post and thankfully he notified me that we don’t need to use the RESTapi to add an environment variable to Kudu on Azure; we can use the AppSettings in the portal.

——-

Kudu is a deployment engine that enables websites to deploy directly from git repository, it is the one behind git deployments in Azure Web Sites.
Two things I loved about Kudu: you can use it locally in your environments, and it is open source!

There are a lot of video’s and articles that explain how to use Kudu, the source is the Windows Azure Friday, just search for Kudu (using the search box under “Last Friday”, not the one in the header).

In this post I will explain how to do custom deployment with Kudu INCLUDING updating the database with Dbup (a library that tracks your change scripts and deploy them to your database accordingly), both locally and on Azure.
There are some prerequisites for this post, and I am not going to clutter the internet with repeated information; so you will need to know how to

1. Run Kudu locally
2. and how to do custom web site deployment.

What are you waiting for, go watch the two videos!

Now that you’re back, let’s see how we can update the database within our deployment.

 

Deploying Locally

So let’s add a console project “UpgradeDatabase” to the solution and add the Dbup nuget package to it so we can use that console app to manage our database updates.
We need to tell our console app which database we need to apply our change scripts to, we do this by passing the connection string as a parameter to the exe (just like the guide on Dbup main page):

Don’t worry about the parameter for now, we will explain how to supply it later.

Great, now let’s go to the “deploy.cmd” batch file generated in video #2, this file is the batch file that Kudu will use to deploy our website, note here that if this file should be added to your repo so Kudu can retrieve and use to do the deployment, otherwise Kudu will use its default batch file.

Now that we have the script in our hands, we need to edit it so we build the database project and execute the exe file passing the proper connection string so we can upgrade within the deployment.

First, let’s edit “deploy.cmd” so it builds the project “UpgradeDatabse” we created above; we need to do this explicitly because Kudu builds the web project file only, and of course whatever dependencies the project has, but not all projects in your solution. So we add the following line just under the “Deployment” section in the file:

::::::::::::::::::: snippet 1
echo Upgrading the database
%MSBUILD_PATH% UpgradeDatabase\UpgradeDatabase.csproj
IF !ERRORLEVEL! NEQ 0 goto error
::::::::::::::::::

Very simple! now that we have built the project we have the executable “UpgradeDatabase” generated, which will be run by our “deploy.cmd” to apply the changes to our database, so let’s add the following lines to the “deploy.cmd”:

:::::::::::::::::: snippet 2
call %DEPLOYMENT_SOURCE%\UpgradeDatabase\bin\debug\UpgradeDatabase.exe %DatabaseToUpgrade%
IF !ERRORLEVEL! NEQ 0 goto error
::::::::::::::::::

Couple of paragraphs above we said that we will pass the connection string as a parameter to the console app, and if you inspect snippet 2, you will find that we are actually passing an environment variable as the connection string named “DatabaseToUpgrade”, the question is where do we set this variable? this variable, and in fact all of the environment variables used in “deploy.cmd”, are set by Kudu. Thoughtfully, the team of Kudu have made available for us to edit (except few).
So let’s set this variable.

If you have followed video 1 above, which explains how to run Kudu locally, you have the local Kudu ready in your local machine, through the interface provided we can add the “DatabaseToUpgrade” variable; under the main page of the application you have created on Kudu –> go to the “Configuration” menu item –> Customer Properties –> and add the new variable to the list.

Note: you have to escape the double quotes of your connection string with back lash \

 

 

Now that you have added the property with the proper connection string as a value (likely a database on your local machine), we are ready to push deploy, so now we push to the git repo in my local Kudu, you can get the Url from the Kudu interface.

git push http://localhost:4514/bunianlocally.git

Hopefully if everything is set right you will see your change script code executed and your database upgraded depending on the scripts you have put in your Dbup project.

Deploying on Azure

The good news is that we won’t have to do a lot; all what we have to do is to add the variable “DatabaseToUpgrade” to the environment variables on Kudu on Azure, but this time with the value of a connectionstring that refers to our live Database.

The problem is if you go to Azure interface you will find that there is no interface to add such a variable (at least according to my knowledge)! luckily though, and thanks to the Kudu team, we can add this variable through the RESTapi.

So we need to POST the variable “DatabaseToUpgrade” to the resource “settings”, of course the endpoint you will use to access this resource is the Kudu URL on your windows Azure account; you can find this it under your Azure website –> Configure –> and then scroll to Git URL. 
NOTE:The Kudu URL is the same URL but remove the last portion of the URL (i.e websitename.git).

To add the new key you can use any web client tool (curl, fiddler, …etc), so if I omit my personal  authentication header, and using curl, the request should look like this:

curl –data "{‘DatabaseToUpgrade’:’liveDbConnectionString’}" http://localhost:4514/settings -H "Content-Type: application/json"

Of course you will have to use the proper authentication to access to this resource, which should be your Azure ftp username and password for this website (at least up until the moment of writing this post). If you need to know how to construct an authentication header by Fiddler maybe this question on SoF might help.

And voila! hopefully you have done it!

Things to keep in mind

1. The “deploy.cmd” file is in your repo, this means that anyone has access to your repo can have access to the file and change it.
2. I could have kept the connection string in my app.config file and used a “switch” key rather than putting the connection string itself as a Kudu variable, but this means that I have to keep the real connection string in my code repository, making it visible to all the people who have access to the repo, which is not usually desirable!
3. Depending the Visual Studio installations you have on your machine, and depending on the way you reference the web targets in your project file .csproj…depending on these two factors, the web targets might not be found during. So keep in mind that you can pass build arguments to MSbuild through Kudu. In my case, I had to pass the Visual Studio version to build upon like the following:

 

 

Conclusion

Kudu is awesome

Let me know if you have any questions.