Exercise 3.9: Database Deployments & Self-Hosted Agents

Objective

Implement database migrations in pipelines and configure self-hosted agents/runners.

Skills Measured

• Implement a deployment that includes database tasks
• Design and implement a GitHub runner or Azure DevOps agent infrastructure

Prerequisites

• Flyway — a database migration tool that applies versioned SQL migration scripts in order. Used to version-control database schema changes.
  • Install: https://flywaydb.org/download
  • Verify: flyway --version
• Azure CLI (az) — used to create VMs for self-hosted agents.
  • Install: https://learn.microsoft.com/en-us/cli/azure/install-azure-cli
  • Authenticate: az login
• Docker (optional) — for running self-hosted agents in containers.
  • Install: https://docs.docker.com/get-docker/

Steps

Part A: Database Migrations in Pipelines

1. SQL Database migration with Azure Pipelines:

   stages:
     - stage: Deploy
       jobs:
         - deployment: DeployApp
           environment: 'Production'
           strategy:
             runOnce:
               deploy:
                 steps:
                   # Run database migrations BEFORE app deployment
                   - task: SqlAzureDacpacDeployment@1
                     inputs:
                       azureSubscription: 'Azure-Connection'
                       authenticationType: 'server'
                       serverName: 'sql-az400.database.windows.net'
                       databaseName: 'appdb'
                       sqlUsername: '$(DB_USERNAME)'
                       sqlPassword: '$(DB_PASSWORD)'
                       deployType: 'SqlTask'
                       sqlFile: 'database/migrations/V1__initial_schema.sql'
                     displayName: 'Run DB migration'
   
                   # Then deploy the application
                   - task: AzureWebApp@1
                     inputs:
                       azureSubscription: 'Azure-Connection'
                       appName: 'myapp-prod'
                       package: '$(Pipeline.Workspace)/drop/*.zip'
   

2. Entity Framework migrations (for .NET):

   steps:
     - task: DotNetCoreCLI@2
       inputs:
         command: 'custom'
         custom: 'ef'
         arguments: 'database update --connection "$(ConnectionString)"'
       displayName: 'Run EF migrations'
   

3. Flyway migrations (platform-agnostic):

   steps:
     - script: |
         flyway -url=jdbc:sqlserver://sql-az400.database.windows.net:1433;databaseName=appdb \
           -user=$(DB_USERNAME) \
           -password=$(DB_PASSWORD) \
           -locations=filesystem:database/migrations \
           migrate
       displayName: 'Run Flyway migrations'
   

4. Database deployment best practices:

   • Always run migrations before application deployment
   • Make migrations backward-compatible (expand-contract pattern)
   • Include rollback scripts
   • Test migrations against a copy of production data
   • Use transactions for atomicity

Part B: Self-Hosted Azure DevOps Agents

1. Set up a self-hosted agent on a VM:

   # Create agent VM
   az vm create \
     --resource-group rg-az400 \
     --name agent-vm \
     --image Ubuntu2204 \
     --size Standard_D2s_v3 \
     --admin-username azureuser \
     --generate-ssh-keys
   
   # SSH into the VM
   ssh azureuser@<vm-ip>
   
   # Download and configure the agent
   mkdir myagent && cd myagent
   curl -O https://vstsagentpackage.azureedge.net/agent/3.232.1/vsts-agent-linux-x64-3.232.1.tar.gz
   tar zxvf vsts-agent-linux-x64-3.232.1.tar.gz
   ./config.sh
   # Enter: server URL, PAT token, agent pool name, agent name
   
   # Run as a service
   sudo ./svc.sh install
   sudo ./svc.sh start
   

2. Use self-hosted agent in pipeline:

   pool:
     name: 'SelfHostedPool'  # Agent pool name
     demands:
       - npm
       - docker
   
   steps:
     - script: npm install
     - script: docker build -t myapp .
   

3. Agent pool with Azure VM Scale Set (auto-scaling):

   # Create VMSS for agents
   az vmss create \
     --resource-group rg-az400 \
     --name agent-vmss \
     --image Ubuntu2204 \
     --instance-count 0 \
     --upgrade-policy-mode manual
   

   Then configure in Azure DevOps: Project Settings → Agent Pools → New → Azure VM Scale Set

Part C: GitHub Self-Hosted Runners

1. Set up a self-hosted runner:

   # Download runner
   mkdir actions-runner && cd actions-runner
   curl -o actions-runner-linux-x64-2.311.0.tar.gz -L https://github.com/actions/runner/releases/download/v2.311.0/actions-runner-linux-x64-2.311.0.tar.gz
   tar xzf ./actions-runner-linux-x64-2.311.0.tar.gz
   
   # Configure
   ./config.sh --url https://github.com/YOUR_ORG --token YOUR_TOKEN
   
   # Install and start as service
   sudo ./svc.sh install
   sudo ./svc.sh start
   

2. Use in workflow:

   jobs:
     build:
       runs-on: self-hosted
       # Or with labels:
       # runs-on: [self-hosted, linux, x64]
       steps:
         - uses: actions/checkout@v4
         - run: npm test
   

3. Runner groups (for GitHub Enterprise):

   • Organization → Settings → Actions → Runner groups
   • Restrict which repos can use which runners
   • Useful for security isolation

Part D: Agent/Runner Decision Matrix

Validation Checklist

• Database migration integrated into deployment pipeline
• Expand-contract migration pattern understood
• Self-hosted Azure DevOps agent configured
• Agent pool demands/capabilities understood
• GitHub self-hosted runner configured
• Microsoft-hosted vs self-hosted decision criteria memorized

Microsoft Learn References

• Self-hosted agents
• GitHub self-hosted runners