This is Part 4 in a series on Azure DevOps.
In Part 1 I created a simple web app called WidgetApi. I then put it under source control and pushed it up to an Azure DevOps repo. In Part 2 I configured a build pipeline and made a code change to trigger that build with continuous integration. In Part 3 I set up a release pipeline and deployed our build artifacts to Azure. In this part I’m going to add a database to WidgetApi and use a DACPAC file to bundle database changes for deployment in the release pipeline. Finally, I’ll configure a production environment with an approval process.
Let’s Add a Database
We’re going to add a database project to the WidgetApi solution. Now you might question that decision. Why not just bundle up some SQL scripts and execute them in the pipeline? You could. A tool like DbUp supports SQL scripts as embedded resources right in your code. And for many use cases and microservices this is just fine. But think ahead a little bit. Do you expect a lot of change over time? How do you want to manage that change? Do you want to write alter table scripts? Or do you want your scripts to describe end state and let a tool like ReadyRoll or sqlpackage.exe manage the diffs for you? If you read the philosophy behind DbUp you can see an excellent defense of the former approach. I’m going to show the desired state configuration approach instead.
Add a new SQL Server Database Project called WidgetApi.Database to the WidgetApi solution. Add a table script for Widget and a post-deploy seed script to populate it:
CREATE TABLE [dbo].[Widget]
(
[ID] INT NOT NULL PRIMARY KEY IDENTITY,
[Name] NVARCHAR(50) NULL,
[Shape] NVARCHAR(50) NULL
)
/*
Post-Deployment Script Template
*/
INSERT dbo.Widget ([Name], [Shape]) VALUES ('Cog', 'Square');
INSERT dbo.Widget ([Name], [Shape]) VALUES ('Gear', 'Round');
INSERT dbo.Widget ([Name], [Shape]) VALUES ('Sprocket', 'Octagonal');
INSERT dbo.Widget ([Name], [Shape]) VALUES ('Pinion', 'Triangular');
GO
Let’s Add EF Core
I’m not going to go step-by-step here because EntityFramework is out of scope for what I’m doing. So let me just show snippets of relevant code without comment so we can get on to the good stuff:
// appsettings.json
{
"ConnectionStrings": {
"DefaultConnection": "ConnectionStringGoesHere"
}
}
In Startup.cs ConfigureServices:
services
.AddDbContext<WidgetContext>(o =>
o.UseSqlServer(Configuration.GetConnectionString("DefaultConnection")));
Infrastructure code:
using Microsoft.EntityFrameworkCore;
namespace WidgetApi.Infrastructure
{
public class WidgetContext : DbContext
{
public WidgetContext(DbContextOptions<WidgetContext> options) : base(options) { }
public DbSet<Widget> Widgets { get; set; }
protected override void OnModelCreating(ModelBuilder modelBuilder)
{
base.OnModelCreating(modelBuilder);
modelBuilder.ApplyConfiguration(new WidgetMap());
}
}
}
using Microsoft.EntityFrameworkCore;
using Microsoft.EntityFrameworkCore.Metadata.Builders;
namespace WidgetApi.Infrastructure
{
public class WidgetMap : IEntityTypeConfiguration<Widget>
{
public void Configure(EntityTypeBuilder<Widget> builder)
{
builder.HasKey(t => t.ID);
builder.ToTable("Widget");
builder.Property(t => t.ID);
builder.Property(t => t.Name);
builder.Property(t => t.Shape);
}
}
}
WidgetController.cs:
using System;
using System.Collections.Generic;
using System.Linq;
using System.Net;
using System.Threading.Tasks;
using Microsoft.AspNetCore.Mvc;
using Microsoft.EntityFrameworkCore;
using WidgetApi.Infrastructure;
namespace WidgetApi.Controllers
{
[Route("api/[controller]")]
[ApiController]
public class WidgetController : ControllerBase
{
private readonly WidgetContext _context;
public WidgetController(WidgetContext context)
{
_context = context ?? throw new ArgumentNullException(nameof(context));
_context.ChangeTracker.QueryTrackingBehavior = QueryTrackingBehavior.NoTracking;
}
[HttpGet]
[ProducesResponseType(typeof(IEnumerable<Widget>), (int)HttpStatusCode.OK)]
[ProducesResponseType(404)]
public async Task<IActionResult> Get()
{
var items = await _context.Widgets.OrderBy(w => w.Name).ToListAsync();
if (items == null || items.Count == 0)
{
return NotFound();
}
return Ok(items);
}
}
}
Now push these changes straight to the master branch (or you’d prepare a pull request if you were working on a team).
Release The DACPAC
Modify the build pipeline . Add a new Copy Files build task and move it just below the Copy ARM Template task. Configure it to copy the DACPAC to the target folder:
Save and then modify the release pipeline. Add a new Azure SQL Database Deployment task to the bottom of the task list. This task is a simple wrapper around sqlpackage.exe. The SQL DB Details section is self-explanatory (again just hard-code the database password). The Deployment Package section should point to our DACPAC file at the location where the build pipeline dropped it:
Save it and queue up a new build. Once the build and release has finished with success you can fire up the browser again to test. W00t!
Let’s Use Azure Key Vault
[Update 29 Mar 2019: this section was valid at the time of writing but the Azure DevOps team has since changed how the pipeline can fetch keys from the vault. I now recommend using variable groups instead of the approach I describe here.]
Up until now I’ve hard-coded the database password. But for a production application you really really want to store that password in a key vault and fetch it as a variable in the pipeline using the Azure Key Vault task.
The first step is to create a key vault in your subscription. Then add a secret called widget-api-database-password and enter a strong password. A pro tip I learned the hard way: don’t use a dollar sign ($) special character in the password because it will be interpreted by Powershell as a variable.
Now you need to add an access policy to the key vault so that the Azure DevOps service principal has rights to read the new secret. To find your service principal go into DevOps, click Project Settings > Service Connections and see the service principal name. Mine was called WidgetApi Service Principal. Now in Azure Key Vault add a new access policy. Select the service principal and under Secret permissions check Get and List. Click OK. Now your service principal has rights to get the secret during the release pipeline. Here’s my service principal access policy for my vault:
Let’s Fetch the Database Password in the Release Pipeline
Go into your release pipeline. Add an Azure Key Vault task at the very top of the task list. Configure it to fetch the database password secret using the service principal credentials:
The secrets filter name should match the name of the secret in the vault. This will be stored as an encrypted local variable in the pipeline. Now we can modify the Azure Resource Group Deployment task next in the list. Remember how in previous parts in this series we hard-coded the database password? Now we want to use the key vault variable instead as in this before and after example:
That’s much better. The password is now encrypted in a variable fetched from the key vault and the release engineer (or you!) does not need to know the password that is passed into the ARM template.
Modify ARM Template to Add Connection String as an Application Setting
Earlier we put the connection string in the appsettings.json file. You should have that file excluded from source control by modifying your .gitignore file to exclude it. But people forget all the time and connection strings make their way into public Git repos. How about leaving it out of the appsettings.json file altogether? We can do that if we modify our ARM template to add the connection string to the app service:
"properties": {
"siteConfig": {
"appSettings": [
{
"name": "APPINSIGHTS_INSTRUMENTATIONKEY",
"value": "[reference(resourceId('microsoft.insights/components/', parameters('webAppName')), '2015-05-01').InstrumentationKey]"
},
{
"name": "WEBSITE_NODE_DEFAULT_VERSION",
"value": "6.9.1"
}
],
"phpVersion": "7.1",
"connectionStrings": [
{
"name": "DefaultConnection",
"connectionString": "[concat('Server=tcp:',
reference(parameters('databaseServerName')).fullyQualifiedDomainName,
',1433;Initial Catalog=',
parameters('databaseName'),
';Persist Security Info=False;User ID=',
parameters('databaseUsername'),
';Password=',parameters('databasePassword'),
';MultipleActiveResultSets=False;Encrypt=True;TrustServerCertificate=False;Connection Timeout=30;')]",
"type": "SQLAzure"
}
]
},
"name": "[parameters('webAppName')]",
"serverFarmId": "[concat('/subscriptions/',
subscription().subscriptionId,'/resourcegroups/', resourceGroup().name,
'/providers/Microsoft.Web/serverfarms/', parameters('hostingPlanName'))]",
"hostingEnvironment": ""
},
Notice lines 14 – 24 where a concat function builds the connection string at deploy time using the parameters we pass into the template. Once you commit the change to the template and push it up to the repo the CI build will kick off and create a new release. When it’s finished you should see your connection string on the app service:
Add Production Stage to the Release Pipeline
This is our last step. We need to extend the release pipeline to include a production stage. If you edit the release pipeline you’ll see a clone button below the Development stage:
Click it and rename the new stage Production. At this point it’s just a matter of editing the three tasks in the Production stage to target a new resource group and resource names on Azure. The resource group could be named widgetapi-prod-rg for example. And you certainly want to change the template parameters for the ARM template to create the proper names for the environment. The most important thing to change is the pre-deployment condition between Development and Production:
Because you cloned it from Development it’s set to continuously deploy on through to the production environment. This is probably not what you want! Mouse over and click the pre-deployment condition on the Production stage and notice you have options. Options are good. A basic scenario is to set an After Stage trigger and choose Development as the preceding stage. Then enable Pre-deployment approvals and enter the names of users or a group that has approval authority. When you do that future releases will pause at the start of the stage and prompt an approval authority for approval:
A Final Word on DevOps
In some shops DevOps is still not embraced because functional silos prevent the automation of the pipeline as I’ve described here. There can also be a lot of fear around change. However, if your shop really wants an agile enterprise and is willing to support build automation then this four-part series should get you well down that road. Taking the time to automate your entire pipeline all the way through to production is worth the effort. By removing human intervention you will mitigate the risks that come with throwing things over the wall and hoping that Ops reads your install docs and configures everything correctly.
I didn’t go over testing but Azure DevOps let’s you run your unit tests as a condition of success for any CI build. (Throw away your mocking frameworks; the new EF Core in-memory database is a dream to use in unit testing.) And there are tools for further functional tests that can be automatically run later in the release pipeline. You could put a QA stage in between Dev and Prod to allow members of your team to run manual or Selinium tests on the application. You have options. The point is this is where you want to spend your energy if you’re serious about risk management and quality software.