Azure Web App + MySQL in a Docker Container

Continuing from my previous post on how to get a website and a MySQL db running on Azure, this post describes the Web App + MySQL in a Docker Container option. 

I've created a sample Azure Resource Manager template that will create the following in an Azure resource group:

  • A virtual machine running Ubuntu with Docker installed and a MySQL docker container running in it
  • Networking and supporting services for the VM (storage account, public IP address, virtual network, network interface, network security group)
  • A Web App and an App Service Plan for the Web App

 

The files are in the env folder in this GitHub repo:

https://github.com/nzthiago/WebAppMySQLDocker

 

Interesting bits from the ARM Template

There are some parts of the template that are worth calling out. For example, I added an extra security rule to the network security group to allow connections to the MySQL 3306 port:

{
  "name": "mysql",
  "properties": {
    "description": "Allow MySQL",
    "protocol": "Tcp",
    "sourcePortRange": "*",
    "destinationPortRange": "3306",
    "sourceAddressPrefix": "Internet",
    "destinationAddressPrefix": "*",
    "access": "Allow",
    "priority": 100,
    "direction": "Inbound"
  }
}

Docker is installed on the Ubuntu VM by using the DockerExtension extension, and docker compose is used to create and run a MySQL image. Note how I set the MySQL root password using MYSQL_ROOT_PASSWORD and the parameter passed in via the template parameters, and how I used MYSQL_DATABASE to create a new database called expressmysql-development

{
      "type": "Microsoft.Compute/virtualMachines/extensions",
      "name": "[concat(variables('vmName'),'/', variables('extensionName'))]",
      "apiVersion": "2015-05-01-preview",
      "location": "[resourceGroup().location]",
      "dependsOn": [
        "[concat('Microsoft.Compute/virtualMachines/', variables('vmName'))]"
      ],
      "properties": {
        "publisher": "Microsoft.Azure.Extensions",
        "type": "DockerExtension",
        "typeHandlerVersion": "1.0",
        "autoUpgradeMinorVersion": true,
        "settings": {
          "compose": {
            "db": {
              "image": "mysql",
              "ports": [
                "3306:3306"
              ],
              "volumes": [
                "/var/lib/mysql:/var/lib/mysql"
              ],
              "environment": [
                "[concat('MYSQL_ROOT_PASSWORD=', parameters('mysqlPassword'))]",
                "MYSQL_DATABASE=expressmysql-development"
              ]
            }
          }
        }
      }
    }

And the last bit I want to call out is how I'm setting up app settings on the Web App with details of the MySQL database being created as part of the same template. I'm setting a MYSQL_HOST setting by getting a reference to the fqdn from the VM, and also setting MYSQL_ROOT_PASSWORD with the password passed in via the parameters:

"appSettings":  [
  {
    "Name":  "MYSQL_HOST",
    "Value":  "[reference(concat('Microsoft.Network/publicIPAddresses/', variables('publicIPAddressName'))).dnsSettings.fqdn]"
  },
  {
    "Name":  "MYSQL_ROOT_PASSWORD",
    "Value":  "[parameters('mysqlPassword')]"
  }
]

NICE! How can I use it?

Assuming you already have an Azure subscription, you can deploy the ARM template using the CLI, PowerShell or other means.

Here's how to do it using the Azure CLI. First, make sure to install the CLI and log in to your Azure subscription using the CLI.

Next, clone the sample:

git clone https://github.com/nzthiago/WebAppMySQLDocker.git

This should download the sample into a WebAppMySQLDocker folder for you. Then browse to the env folder: 

cd WebAppMySQLDocker/env

Switch to the ARM mode of the CLI:

azure config mode arm

Next use your favorite editor to change the azuredeploy.parameters.json file with the username and password values. The mySqlPassword parameter in that file will be set to your MySQL database root user password. The adminUsername and adminPassword will be the credentials set on the ubuntu VM if you need to ssh into it.

Finally - deploy the sample. You need to pass in values for the resource group name, location, and use the -f parameter to pass in the template file and the -e parameter for the template parameters file. For example:

azure group create "thiagonodexp" "West US" -f azuredeploy.json -e azuredeploy.parameters.json

This is the result you should see, confirming the deployment has been kicked off successfully:

After a few minutes you will have all of the items created in the resource group. You can check on it from the Azure Portal:

Or you can use the azure group deployment show command in the CLI to get the status of the deployment. For example, here is the result showing that my deployment was successful (azuredeploy is the default name of a deployment if you don't pass it in as a parameter when creating the deployment):

Screen Shot 2015-11-24 at 11.55.06 AM.png

The important information in there is that ProvisioningState is Succeeded, meaning that our ARM template deployment finished successfully. 

From the Azure Portal you can see that the App Settings of your Web App. They will be configured with the MySQL container host and root password details that you need to connect to your MySQL database. These get exposed as Environment Variables to your web app code:

You can also get these values from the Azure CLI:

Now it's up to you: you can connect to your new MySQL database using the mysql command line shell, or the MySQL Workbench or other client to create tables and other artifacts. The user is root and the port is 3306 which are the default for MySQL.

Once the database is ready, create a local PHP, Node.js, .NET, Java, or Python web app, and when ready, publish your site to your new Web App via git or other ways.

Notes

This post describes a simple Web App + MySQL in a container deployment. It doesn't follow production best practices (for example, the Web App is in the limited Free tier, the MySQL database's 3306 port is exposed externally, the Ubuntu VM is using username and password instead of SSH certificates, etc). But it is a way to quickly get started if you are developing or want to test something that is based on a website and a MySQL database.

You can expand on this sample and make it production ready when needed - the basics are there!

Get started with a free Website and MySQL db in Azure

There's often the need to run a Web App in Node, PHP, .NET or other languages backed by a MySQL database on Azure. Here are some common options:

  • You can go the full PaaS route, with an Azure Web App and a MySQL database provided through Azure's partnership with ClearDB
  • You can have an Azure Web App and host MySQL in a Docker container in a Virtual Machine (PaaS + Docker)
  • You can have an Azure Web App and host MySQL yourself on a Virtual Machine
  • You can have both the web app (Node, ASP.Net, PHP, etc) and MySQL in the same or separate Docker containers

In this post I describe the first option: full PaaS. I have also blogged about the second Pass + Docker option

This assumes you already have a new active Azure subscription. You can get a free trial if you don't have one.

Full PaaS - Azure Web App and ClearDB MySQL

This one is pretty straight forward because of the Azure Marketplace: there is an Azure Marketplace option that allows you to create the Web App and the ClearDB MySQL database in one go:

Clicking the green Create Web App > button takes you to the Azure portal. After logging in with your credentials, the portal will open a blade with the options to fill out the details for your Web App and MySQL database. It will take you through creating:

  • An App Service Plan where your Web App will be hosted - this represents a set of features and capacity that you can share across multiple Web, Mobile, Logic, and API apps. This post shows you how to create the plan in a Free tier.
  • An Azure Web App that runs in the App Service Plan described above  - where you can host your PHP, Node.js, .NET, Java, or Python websites
  • An Application Insights resource for monitoring of your Web App
  • A MySQL database provided by ClearDB
  • A Resource Group as the container for all of the above

Start by entering a name in the Web App text box. What you type here becomes part of your web app public URL. Also create a new Resource Group by entering a name in the Resource Group text box:

The Web App name needs to be globally unique as it will form part of your web app URL. The resource group will be a new container in your Azure subscription for what you are creating.

Next, create a new App Service plan and location. For example, I chose Create New, West US as the location, and changed the pricing to F1 Free

Can't see the F1 Free option? Make sure you click on View All first on the Choose your pricing tier blade:

Then click the Select and OK buttons at the bottom to save your changes. 

Now that the App Service Plan is configured, let's configure the MySQL Database option. Choose a database name, and the same location as you chose for your App Service Plan, and a pricing tier. For example, I chose West US and the free Mercury tier: 

The next step is to review the ClearDB legal terms and if you agree, click Ok:

And that's all the config options done! Finally, click the Create button:

After a few minutes the Azure Portal will notify you that your new resource group has been created with your Web App, App Service Plan, Application Insights, and MySQL database in it (click Resource Groups in the Azure Portal menu to see it):

You can find connection and user details about your new database by clicking on the database icon in your resource group and going into All Settings and Properties:

Those are the details you'd need to enter into MySQL Workbench or other MySQL editor and from your Web Apps:

Here's an example of creating a new table in your MySQL database using MySQL Workbench:

Also, because you have created the Web App and MySQL database at the same time, the Web App will already come with an app setting preconfigured with the MySQL database connection string, called defaultConnection:

This means that from your Web App code you can get to the connection string for your MySQL db by looking for an environment variable called MYSQLCONNSTR_defaultConnection. 

You can now create a local PHP, Node.js, .NET, Java, or Python web app and point it at your new Database, and then when ready, publish your site to your new Web App via git or other ways.

Note on the free tiers

The instructions above will get you started with a basic free set up for a Website and a MySQL database on Azure. The instructions created them using free tiers, which have a lot of limitations and throttles. If you plan to use your site and database not in a development scenario review the other tiers for Web Apps and ClearDB MySQL.

Infrastructure as Code and Continuous Deployment of a Node.js + Azure DocumentDB Solution

This post covers the following DevOps practices:

  • Infrastructure as Code
  • Continuous Deployment

I created an Azure Resource Manager template, and modified the sample Node.js solution from the Azure team to include the instructions from the Visual Studio Team Services team for creating builds with Node.js for Azure.

The changes are in my sample on GitHub

Infrastructure as Code - Web App and Document DB in an Azure Resource Manager Template

You can use Azure Resource Manager (ARM) templates to declaratively describe the infrastructure behind your solution. Azure DocumentDB has recently been added to the list of resources supported by ARM, so now you can use ARM to define your DocumentDB account as well.

My sample DocumentDB + Web App ARM template is on GitHub. Here's how I declared the DocumentDB account:

{
      "apiVersion": "2015-04-08",
      "type": "Microsoft.DocumentDb/databaseAccounts",
      "name": "[variables('databaseAccountName')]", 
      "location": "[resourceGroup().location]", 
      "properties": {
          "name": "[variables('databaseAccountName')]",
          "databaseAccountOfferType":  "Standard"
      }
}

I also reference the DocumentDB account endpoint and key info from the Web App resource, linking back to the DocumentDB account endpoint and key: 

"appSettings":  [
              {
                "Name":  "DOCUMENTDB_ENDPOINT",
                "Value":  "[reference(concat('Microsoft.DocumentDb/databaseAccounts/', variables('databaseAccountName'))).documentEndpoint]"
              },
              {
                  "Name":  "DOCUMENTDB_PRIMARY_KEY",
                  "Value":  "[listKeys(resourceId('Microsoft.DocumentDb/databaseAccounts', variables('databaseAccountName')), '2015-04-08').primaryMasterKey]"
              }
          ]

With these details my ARM template defines:

  • A DocumentDB account
  • An App Hosting Plan
  • A Web App in the Hosting Plan, with app settings pointing to the DocumentDB account
  • Three parameters (only projectName is required)
  • Three variables (they use projectName to create names for the site, hosting plan, and DocumentDB account)

Here's the outline of the json file:

You can deploy this template using the Azure CLI or Powershell, but I wanted to include it in my continuous deployment workflow, so read on!

Continuous Deployment - Visual Studio Team services Build Definition

My Build Definition in Visual Studio Team Services first has an Azure Resource Group Deployment task. Note that Template and Template Parameters are set to the ARM template and parameters files described above:

The next task is npm install. This looks for package.json files in your code and installs all the npm modules defined there:

Here's the package.json from my sample, note gulp and gulp-zip as part of devDependencies: 

  "dependencies": {
    "express": "^4.13.3",
    "async": "^0.9.0",
    "body-parser": "~1.10.2",
    "cookie-parser": "~1.3.3",
    "documentdb": "^0.9.3",
    "jade": "~1.9.1"
  },
  "devDependencies": {
    "gulp": "^3.9.0",
    "gulp-zip": "^3.0.2"
  }

Next I have a Gulp step and I point it at the gulpfile.js file that's at the root of my code:

Screen Shot 2015-11-12 at 4.48.05 PM.png

This gulpfile is important as it creates a zip file with the contents our node site. Note in the code how I'm creating a file called Package.zip and use process.env.BUILD_STAGINGDIRECTORY to make sure the zip file is put on the Build agent's staging directory:

var gulp = require('gulp');
var path = require('path');
var zip = require('gulp-zip');
var fs = require('fs');
var packagename = "Package.zip";

gulp.task('default', function () {

    var packagePaths = ['**', 
                    '!**/_package/**', 
                    '!**/typings/**',
                    '!typings', 
                    '!_package', 
                    '!gulpfile.js']
    
    //add exclusion patterns for all dev dependencies
    var packageJSON = JSON.parse(fs.readFileSync(path.join(__dirname, 'package.json'), 'utf8'));
    var devDeps = packageJSON.devDependencies;

    for(var propName in devDeps)
    {
        var excludePattern1 = "!**/node_modules/" + propName + "/**";
        var excludePattern2 = "!**/node_modules/" + propName;
        packagePaths.push(excludePattern1);
        packagePaths.push(excludePattern2);
    }

    var stagingpath = process.env.BUILD_STAGINGDIRECTORY;
    
    console.log("Creating file " + stagingpath + "\\" + packagename);
    
        return gulp.src(packagePaths)
            .pipe(zip(packagename))
            .pipe(gulp.dest(stagingpath));
});

And finally I have the Azure Web App Deployment step. Note how I configured it to look for $(Build.StagingDirectory)/package.zip as that's the zip file created by the previous task:

After this I can kick off builds manually or via continuous integration that will make sure my infrastructure is as I defined, restores npm packages, creates the zip file, and deploys the changes to the Azure Web App created by the first step:

The first time your build runs it will take longer as it is running the full ARM deployment and creating the DocumentDB account, Hosting Plan and Web App. Currently around 10 minute for me on the hosted controller provided by the Build system. Unless you change the ARM template or make changes to your resource group on Azure the build should go much faster, around 3 minutes for me currently.

This is what the resource group looks like after it is all deployed:

Screen Shot 2015-11-12 at 5.11.46 PM.png

And finally, browsing to the web app will show the ToDo List sample:

The first time the site is run it will also create the DocumentDB database, and subsequently it will use that database. 

Closing Notes

This post described a simple example of infrastructure as code, continuous integration, and continuous deployment of a Node.js and DocumentDB sample using Visual Studio Team Services. Hopefully helps you and your team automate your solution as well.

The full source code is on GitHub. 

Visual Studio Team Services: Having a chat with the API

TL;DR;

Use the Visual Studio Team Services REST API to get data about your VSO environment. The code: https://github.com/nzthiago/ExtractVSOProjectInfo

Context

We recently hosted a large team of technology evangelists from around the world to an internal two day hackathon. Each team worked on their own Visual Studio Team Services (VSTS) project in a shared VSTS account.

I was tasked with extracting some information once it was over, which you might also be interested in for your own projects:

  • How many git commits were there from each team?
  • Who were the most prolific team members (i.e., most number of check ins)?
  • How many VSO builds did each team kick off?

For a lot of it I used the Visual Studio Team Services provider for Power BI which I will cover on a future post. But there were two specific items I did not manage to extract through Power BI:

  • Number of commits from team member including the ones that did not do any commits
  • Number of builds from each team

Approach and Setting up

I decided to write a command line app and go directly against the Visual Studio API, which is well documented on the Visual Studio Team Services REST API Referencepages. I had already used the API to create the 40 projects needed for the teams to use during the hackathon so this was a good option.

The Visual Studio Team Services REST API now supports OAuth and Basic authentication. When I started on this only Basic authentication was supported using alternate credentials, so that is the path I took with the code. This is the page with the documentation on both methods:

In app.settings I reference a separate settings file (so that credentials don’t get stored in source control or GitHub):

<appSettings file="..\..\..\AppSettingsSecrets.config">
</appSettings>

And the AppSettingsSecrets.configfile has the three pieces of information we need to connect to the REST API. You need to create this file with your specific credentials a folder above the project folder if running from Visual Studio:

<?xml version="1.0" encoding="utf-8" ?>
<appSettings>
    <add key="user" value="<your VSO alternate login>" />
    <add key="password" value="<your VSO alternate password>" />
    <add key="url" value="<your VSO account>.visualstudio.com" />
</appSettings>

Working with the Rest API

I used the Google Chrome App Postman to test out the API as I went along. For example, here is Postman getting a list of builds for the Cirrus project:

For the two sets of data I was after I ended up calling the following API endpoints:

DescriptionURL
Base for all queries https://.visualstudio.com/DefaultCollection/
Get all projects in the VSO account_apis/projects?api-version=2.0
Get all the teams in a project_apis/projects/{Project Name}/teams?api-version=2.0
Get all the team members in a team_apis/projects/{Project Name}/teams/{Team Name}/members?api-version=2.0
Get all repositories_apis/git/repositories?api-version=2.0
Get all the repository commits_apis/git/repositories/{Repository ID}/commits?api-version=2.0
Get commit details_apis/git/repositories/{Repository ID}/commits/{Commit ID}?api-version=2.0
Get all the project builds{Project Name}/_apis/build/builds?api-version=2.0

Using the above it makes calls in parallel and works through it:

But more importantly, using the LINQtoCSV nuget package it creates two CSV files with the information I was after, which you can then open in Excel. For example, here’s MemberCommits.csv including team members with no commits after I changed the content to a table and added a pivot chart (random names and emails used to protect the innocent):

results.png

The Code

The code for this sample is on my GitHub account. There’s a list of improvements to be made. Pull requests and suggestions welcome!

Deploying MiniBlog from GitHub to an Azure Web App automatically using Visual Studio Team Services Build

This post covers the following DevOps practices:

  • Continuous Integration
  • Continuous Deployment

Most articles online cover how to deploy modern ASP.NET applications to Azure from code hosted in Visual Studio Team Services (VSTS) previously known as Visual Studio Online (VSO) itself. For example:

What if you want to automate the deployment of a classic ASP.NET Web Pages project, and from code hosted on GitHub? This post uses the great MiniBlog project as an example and the steps/changes I made to automate it all. At the end VSO will build and deploy it all the way from GitHub to an Azure Web App.

This assumes you already have a Visual Studio Team Services account and permissions to create and run build definitions; that you have an Azure subscription; that you have a GitHub account with access to private repos; and have a good understanding of git.

Get MiniBlog into a private repo in your GitHub account

A good practice is to have MiniBlog on your own private repo so you can make all the changes you need. To do that:

This will give you a copy of the repo on your GitHub account.

Configure the Project with a Publishing Profile

To make it easier for the build system to know I want to package the website into a zip file I configured the project with a publishing profile. Here are the steps:

  • Clone your private repo to your development environment and open the solution in Visual Studio.
  • Right click the Website project and select Publish or Publish Web App.
  • On the wizard choose Custom as the profile type and give it a name, like VSOBuildPackage. Make sure you write down this name as we will need it later. Click Next.
  • On the Connection tab choose Web Deploy Package as the type, and write website.zip as the package location. Click Next.
  • On the Settings page leave the default (Release) and click Next.
  • On the Preview page click Close and when asked to save the new profile click Yes.

These steps will have added two important files to the project that will tell the build system we want the site packaged into a zip file when publishing: website.publishproj and App_Data\PublishProfiles\VSOBuildPackage.pubxml. Finally, we need to get these changes back into your private repo:

  • Edit the .gitignore file and remove the publishproj and pubxml exclusions.
  • Make sure you include all three file changes (the change to .gitignire and the two new files) and push the changes back into your repo.

You can of course make any other changes you like to the project and customize it to your liking before pushing the changes.

Connecting Visual studio team services to GitHub

Visual Studio Team Services allows you to connect to Services and use from the new Build service. We will deploy from GitHub to Azure, so we need to connect the two to our VSO account.

  • In GitHub, create an access token and save it for later. Use the repo,useradmin:repo_hook scopes so that Visual Studio Team Services can discover private and public repositories that you have access to, create a web hook in GitHub (which is what queues the build in Visual Studio Team Services), and fetch the contents of the repository during the build.
  • Sign in to your Visual Studio Team Services account (http://{youraccount}.visualstudio.com) and go to your project (or create a new one if needed).
  • Once you are in the VSO project page, click on the small gear in the upper right corner of the page to go to the settings of the project.
  • Click the Services tab.
  • Use the New Service Endpoint + button and choose GitHub.
  • Select Personal access token and configure it with the token generated above and give it a name then click OK.

Connecting Visual Studio Team Services to Azure

Now let’s connect to your Azure subscription where the website will be deployed.

  • Like with the GitHub connection, sign in to your Visual Studio Team Services account and go to the Services tab of the settings page of your project.
  • Click the New Service Endpoint + button choose Azure.
  • Fill out the details of your subscription and choose one of the ways to connect to your Azure subscription. I went with the certificate option.

Now you should have both your GitHub and your Azure subscription connected to VSO as below.

Create a Build Definition

Now we can created the build definition that will get the solution from GitHub, build it, and deploy to an Azure Web App.

  • From your Visual Studio Team Services project click on the Build tab.
  • Click the plus sign to create a new build definition.
  • Choose the Visual Studio template and click OK.
  • On the new build definition, click on the Repository tab and change from the default git (this is the git repo of your Visual Studio Team Services project) to GitHub. Then on Repository choose your private MiniBlog repo as per below and leave the default for the rest.
  • Click the Variables tab and change BuildConfiguration to release if not already.
  • Change back to the Build tab.
  • For the purpose of this post we will not be running any tests and will publish to Azure, so delete the Visual Studio TestIndex Sources & Publish Symbols and Publish Build Artifacts steps by hovering over each step and clicking the X button that shows up as per below.
  • Click the Add Build Steps button.
  • From the Deploy list click Add next to Azure Web App Deployment and click Close. We now have our build definition with the two steps we need to configure.
  • On the Visual Studio Build step configuration , click the … button next to Solution. This lets you browse through your GitHub code repo to choose which project you want to build. Choose the website.publishproj file in the Website folder.
  • On the MSBuild Arguments setting enter this to ensure it will package the project and follow our choices from the publish. file: 

/t:Package /p:PublishProfile="App_Data\PublishProfiles\VSOBuildPackage.pubxml" /p:DesktopBuildPackageLocation="$(build.stagingDirectory)" /p:SkipInvalidConfigurations=true

  • Note that above I have VSOBuildPackage.pubxml as the publish file. Change that to the name of the publish package you created earlier if you used a different name.
  • Untick Restore Nuget Packages and leave the rest of the settings as default. Your Visual Studio Build step should look like the below.
  • Now change to the Azure Web App Deployment step.
  • It should have pre-selected your Azure subscription (or choose the one you want to use if you have more than one connected).
  • On the Web App Name field enter a name for your new Web App (if you had already previously created and empty Web App on Azure for this then enter that same name here).
  • On Web App Location choose where you want your website deployed (if you had already previously created and empty Web App on Azure then enter the same location here).
  • On the Web Deploy Package enter $(build.stagingDirectory)\**\*.zip for it to use the zip file that the Visual Studio Build step will create.
  • Finally, click the Save button on the build definition and give it a name to save your Build Definition.

Run a build & check out your new blog site!

Now that we have finished configuring the build definition it is ready to be run. Click the Queue Build button and click OK on the pop up to manually trigger a build.

You should then see the build steps executing:

And once done the status of the build should be Build Succeeded. This means it got the code from your private GitHub repo, ran it through the Visual Studio Build step that generated a zip file with the website content, and then deployed it to Azure to a new or existing Azure Web App.

You can now browse to your new website URL and see it running:

Enable Continuous Integration

Finally, let’s enable continuous integration.

  • Go back and edit your Build definition in Visual Studio Team Services.
  • Select Edit.
  • Change to the Triggers tab.
  • Click the enable Continuous Integration check box and click Save.

And that is it, this will queue a new build every time you check in code into your private GitHub repo.

For extra points, you can add a 'build badge' image to your MiniBlog site to show the latest build status, I describe this on this other blog post.