Real World DevOps–Breaking it all down (part 1–Provisioning and Restoring DB’s from backup)

Table of Content

Part 1 – Provisioning and Restoring DB’s from Backup

Part 2 – Setting DNS

Part 3 – Setting HTTPS

Part 4 – Setting Up Dynamic Backend Service Url’s in Mobile app (1 build for all stages) (coming soon)

Introduction

I’ve recently started giving a talk titled Real World DevOps where I show the power and flexibility of build/release pipelines. I create a full end to end pipeline for a complex app which consists of

  • Good ‘ol fashioned asp.net web front end written in c#
  • Azure SQL
  • API layer written in node.js, hosted in k8s
  • Services layer written in .net core hosted in a container
  • iOS and android app.

Then, starting from a completely empty Azure subscription, I deploy it all using pipelines and in my release pipeline, I do in parallel

  • provision and configure all the infrastructure needed using Infrastructure as Code (specifically by calling the Az CLI with my own infra versioning framework)
  • Restore DB data from backups
  • Deploy app into all the pieces of infrastructure
  • Deploy mobile app to alpha testers/beta testers/store
  • Set up HTTPS
  • Set up DNS
  • Run automated UI tests against the web front end
  • Run automated ui tests against the mobile app
  • Configure mobile app so there only needs to be one build which can dynamically switch backend service url’s on the fly for different stages

image_thumb[2]

In this blog series, I’ll be breaking down how I do all this in detail.

Infrastructure as Code/Configuration as Code

The IaC for this entire project uses Az CLI commands wrapped in powershell scripts plus my infrastructure versioning framework. This versioning framework versions the script changes and keeps track of what version the infrastructure is currently at. When my pipeline is run, only the versions which have not been run gets run. This does 2 things. 1) This makes my IaC script much cleaner and easier to manage over time (Az CLI is only sort of idempotent, so this removes any need to have messy if statements everywhere) because no matter how many times my pipeline gets run, only new versions of the script gets called. 2) It makes my pipeline run much faster because if the infrastructure is up to date, nothing gets called. To see the how, why, where and what of this infrastructure framework, check out this blog post.

Provisioning and Restoring DB’s from Backup

The script to provision the DB is pretty straight forward. The first thing I do is pass in all the variables I will need later in my script as parameters

[CmdletBinding()]
param(
    [Parameter(Mandatory = $True)]
    [string]
    $servicePrincipal,
    [Parameter(Mandatory = $True)]
    [string]
    $servicePrincipalSecret,
    [Parameter(Mandatory = $True)]
    [string]
    $servicePrincipalTenantId,
    [Parameter(Mandatory = $True)]
    [string]
    $resourceGroupName,
    [Parameter(Mandatory = $True)]
    [string]
    $resourceGroupNameRegion,
    [Parameter(Mandatory = $True)]  
    [string]
    $serverName,
    [Parameter(Mandatory = $True)]  
    [string]
    $dbLocation,
    [Parameter(Mandatory = $True)]  
    [string]
    $adminLogin,
    [Parameter(Mandatory = $True)]  
    [string]
    $adminPassword,
    [Parameter(Mandatory = $True)]  
    [string]
    $startip,
    [Parameter(Mandatory = $True)]  
    [string]
    $endip,
    [Parameter(Mandatory = $True)]  
    [string]
    $dbName,
    [Parameter(Mandatory = $True)]
    [string]
    $dbBackupName
)
Parameter List

Next, I login to Azure with my service principal

#region Login
# This logs in a service principal
#
Write-Output "Logging in to Azure with a service principal..."
az login `
    --service-principal `
    --username $servicePrincipal `
    --password $servicePrincipalSecret `
    --tenant $servicePrincipalTenantId
Write-Output "Done"
Write-Output ""
#endregion
login to azure with Az CLI

Next, I create my resource group

    #region Create Resource Group
    # # This creates the resource group used to house all of Mercury Health
    # #
    Write-Output "Creating resource group $resourceGroupName in region $resourceGroupNameRegion..."
    az group create `
        --name $resourceGroupName `
        --location $resourceGroupNameRegion
    Write-Output "Done creating resource group"
    Write-Output ""
    #endregion
Create Resource Group

And finally, I create my SQL Server and a firewall rule for the SQL Server

    #region Create Sql Server
    # Create a logical sql server in the resource group
    # 
    Write-Output "Creating sql server..."
    az sql server create `
        --name $serverName `
        --resource-group $resourceGroupName `
        --location $dbLocation  `
        --admin-user $adminLogin `
        --admin-password $adminPassword
    Write-Output "Done creating sql server"
    Write-Output ""
    # Configure a firewall rule for the server
    #
    Write-Output "Creating firewall rule for sql server..."
    az sql server firewall-rule create `
        --resource-group $resourceGroupName `
        --server $serverName `
        -n AllowYourIp `
        --start-ip-address $startip `
        --end-ip-address $endip 
    Write-Output "Done creating firewall rule for sql server"
    Write-Output ""
    #endregion
Provision SQL Server

The next step is for disaster recovery. I’m doing scheduled backups of my database. In fact, I store the latest backup of my database in Azure blob storage in a whole separate Azure subscription as a bacpac. So to restore my database using my backpack, I do the following steps.

Download  the latest bacpac file from blob storage

    #region Download bacpac
    # Download backpac from blog storage
    #
    Write-Output "Downloading bacpac..."
    Write-Output ""
    $source = "https://mhlongtermstorage.blob.core.windows.net/backups/$dbBackupName"
    $filename = [System.IO.Path]::GetFileName($source)
    $dest = "./$filename"
    Write-Output "    destination filename: $dest"
    $wc = New-Object System.Net.WebClient
    $wc.DownloadFile($source, $dest)
    Write-Output "Done downloading backpac"
    Write-Output ""
    #endregion
Download bacpac from blob storage

And finally restore the db using the bacpac

#region Restore DB from bacpac
    # Restore db with the bacpac download from blob storage
    #
    Write-Output "Restoring DB from bacpac.."
    Write-Output ""
    & "c:\Program Files\Microsoft SQL Server\150\DAC\bin\SqlPackage.exe" `
        /a:import `
        /tcs:"Data Source=$serverName.database.windows.net;Initial Catalog=$dbName;User Id=$adminLogin;Password=$adminPassword" `
        /sf:$filename `
        /p:DatabaseEdition=Premium `
        /p:DatabaseServiceObjective=P6
    Write-Output "Done restoring DB from backpac"
    Write-Output ""
    #endregion
Restore database using bacpac

Here is the full script

# This IaC script provisions and configures the web stite hosted in azure
# storage, the back end function and the cosmos db
#
[CmdletBinding()]
param(
    [Parameter(Mandatory = $True)]
    [string]
    $servicePrincipal,
    [Parameter(Mandatory = $True)]
    [string]
    $servicePrincipalSecret,
    [Parameter(Mandatory = $True)]
    [string]
    $servicePrincipalTenantId,
    [Parameter(Mandatory = $True)]
    [string]
    $resourceGroupName,
    [Parameter(Mandatory = $True)]
    [string]
    $resourceGroupNameRegion,
    [Parameter(Mandatory = $True)]  
    [string]
    $serverName,
    [Parameter(Mandatory = $True)]  
    [string]
    $dbLocation,
    [Parameter(Mandatory = $True)]  
    [string]
    $adminLogin,
    [Parameter(Mandatory = $True)]  
    [string]
    $adminPassword,
    [Parameter(Mandatory = $True)]  
    [string]
    $startip,
    [Parameter(Mandatory = $True)]  
    [string]
    $endip,
    [Parameter(Mandatory = $True)]  
    [string]
    $dbName,
    [Parameter(Mandatory = $True)]
    [string]
    $dbBackupName
)
#region Login
# This logs in a service principal
#
Write-Output "Logging in to Azure with a service principal..."
az login `
    --service-principal `
    --username $servicePrincipal `
    --password $servicePrincipalSecret `
    --tenant $servicePrincipalTenantId
Write-Output "Done"
Write-Output ""
#endregion
# this defines my time 1 up function which will deploy and configure the infrastructure 
# for my sql server
function 1_Up {
    Write-Output "In function 1_Up"
    #region Create Resource Group
    # # This creates the resource group used to house all of Mercury Health
    # #
    Write-Output "Creating resource group $resourceGroupName in region $resourceGroupNameRegion..."
    az group create `
        --name $resourceGroupName `
        --location $resourceGroupNameRegion
    Write-Output "Done creating resource group"
    Write-Output ""
    #endregion
    #region Create Sql Server
    # Create a logical sql server in the resource group
    # 
    Write-Output "Creating sql server..."
    az sql server create `
        --name $serverName `
        --resource-group $resourceGroupName `
        --location $dbLocation  `
        --admin-user $adminLogin `
        --admin-password $adminPassword
    Write-Output "Done creating sql server"
    Write-Output ""
    # Configure a firewall rule for the server
    #
    Write-Output "Creating firewall rule for sql server..."
    az sql server firewall-rule create `
        --resource-group $resourceGroupName `
        --server $serverName `
        -n AllowYourIp `
        --start-ip-address $startip `
        --end-ip-address $endip 
    Write-Output "Done creating firewall rule for sql server"
    Write-Output ""
    #endregion
    Write-Output "Done with function 1_Up"
    Write-Output ""
}
# this defines my time 2 up fuction which will set up and restore database from backups
function 2_UP {
    Write-Output "In function 2_Up"
    Write-Output ""
    #region Download bacpac
    # Download backpac from blog storage
    #
    Write-Output "Downloading bacpac..."
    Write-Output ""
    $source = "https://mhlongtermstorage.blob.core.windows.net/backups/$dbBackupName"
    $filename = [System.IO.Path]::GetFileName($source)
    $dest = "./$filename"
    Write-Output "    destination filename: $dest"
    $wc = New-Object System.Net.WebClient
    $wc.DownloadFile($source, $dest)
    Write-Output "Done downloading backpac"
    Write-Output ""
    #endregion
    #region Restore DB from bacpac
    # Restore db with the bacpac download from blob storage
    #
    Write-Output "Restoring DB from bacpac.."
    Write-Output ""
    & "c:\Program Files\Microsoft SQL Server\150\DAC\bin\SqlPackage.exe" `
        /a:import `
        /tcs:"Data Source=$serverName.database.windows.net;Initial Catalog=$dbName;User Id=$adminLogin;Password=$adminPassword" `
        /sf:$filename `
        /p:DatabaseEdition=Premium `
        /p:DatabaseServiceObjective=P6
    Write-Output "Done restoring DB from backpac"
    Write-Output ""
    #endregion
    Write-Output "Done with function 2_Up"
    Write-Output ""
}
Install-Module -Name VersionInfrastructure -Force -Scope CurrentUser
Update-InfrastructureVersion `
    -infraToolsFunctionName $Env:INFRATOOLS_FUNCTIONNAME `
    -infraToolsTableName $Env:INFRATOOLS_TABLENAME `
    -deploymentStage $Env:INFRATOOLS_DEPLOYMENTSTAGE
provisionDB.ps1

Conclusion

Being able to use IaC and my pipeline as a disaster recovery tool was super exciting and fun to do. My entire resource group can be deleted and everything can get restored, including the infrastructure AND data! If you don’t have an Azure DevOps subscription, go to dev.azure.com and start building out some fun pipelines!

Leave a Reply

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