> For the complete documentation index, see [llms.txt](https://jadelab.gitbook.io/jadegit/llms.txt). Markdown versions of documentation pages are available by appending `.md` to page URLs; this page is available as [Markdown](https://jadelab.gitbook.io/jadegit/0.18.0/ci-cd/database-agents.md).

# Database Agents

Interacting with a database during a pipeline depends on using an agent that can reach the database to run deployment jobs.

| Approach                                           | Advantages                                                                                                                                                                                                                                                                                                                                                                                                                          | Otherwise                                                                                                                                                                                                                                                                                                                                                                                                                                                     |
| -------------------------------------------------- | ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- | ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- |
| Self-hosted agent                                  | <ul><li>Access to databases on private networks</li></ul>                                                                                                                                                                                                                                                                                                                                                                           | <ul><li>Cloud-hosted agent requires network access to database</li></ul>                                                                                                                                                                                                                                                                                                                                                                                      |
| Self-hosted agent installed on the database server | <ul><li>Shared memory connection (faster)</li><li>Database server binaries available to the agent</li><li>Database can be opened in single user mode</li><li>Local file access</li></ul>                                                                                                                                                                                                                                            | <ul><li>TCP/IP connection (slower)</li><li>Client binaries required on the agent host</li><li>Limited to multi user mode connections</li><li>File access via network shares</li></ul>                                                                                                                                                                                                                                                                         |
| Dedicated agent per database                       | <ul><li>Agent defines environment variables for associated database</li><li>Agent can run under the associated database service account, inheriting its access</li><li>Database access governed by environment security and deployment approval gates</li><li>Deployments are implicitly single-threaded per database</li><li>Agent service acts as an operational gate, stopping it prevents automated jobs from running</li></ul> | <ul><li>Environment variables must be maintained elsewhere for pipeline use</li><li>Shared agent must be granted access to all databases, duplicating service account permissions and risking unintended database access</li><li>Limited governance of database access</li><li>Pipeline jobs may conflict when run concurrently for the same database</li><li>No implicit gate preventing automated jobs when the database or server is unavailable</li></ul> |

## Azure DevOps

Self-hosted agents for Azure DevOps support the definition of environment variables via a `.env` file in the agent folder. The registration process supports using a unique resource name within each environment, making it a good fit for using a local database name as a consistent deployment target per environment.

The same principles apply to other CI/CD platforms, though the setup approach will need adapting to achieve a similar outcome.

### Setup Script

The example setup script below can be customised to adapt to infrastructure or environment requirements not covered here. It performs the following steps:

* Creates an `azagent` folder within the database home folder
* Writes the following environment variables to the agent's `.env` file, making them available to all pipeline jobs the agent runs
* Downloads and installs the Azure Pipelines agent
* Registers the agent using the database name and environment specified via [`config.cmd`](https://learn.microsoft.com/en-us/azure/devops/pipelines/agents/windows-agent), to which any additional parameters are also passed through for authentication and other options needed for configuration (like what user to run agent service under)
* Starts the agent service

| Variable        | Description                             |
| --------------- | --------------------------------------- |
| `JADE_HOME`     | Main folder path                        |
| `JADE_BIN`      | Binaries path                           |
| `JADE_INI`      | Initialisation file path                |
| `JADE_PATH`     | Database path                           |
| `JADE_LOGS`     | Logs directory                          |
| `JADE_JOURNALS` | Transaction journal logs root directory |

#### Prerequisites

* An environment has been created in Azure DevOps that the user has permission to manage
* A Personal Access Token (PAT) is available to authenticate the agent registration
* The script is run from an administrator PowerShell prompt

```powershell
.\SetupDatabaseAgent.ps1 "<jade_home>" -url "<org_url>" -project "<project>" -environment "<environment>" -database "<local database name>" --unattended --auth "PAT" --token "<token>"
```

```powershell
[CmdletBinding()]
Param(
  [Parameter(Mandatory, Position=0)]
  # Path to Jade database home folder
  [string]$jade_home,

  [Parameter(Mandatory)]
  # Azure DevOps organisation URL (e.g. https://dev.azure.com/example)
  [string]$url,

  [Parameter(Mandatory)]
  # Azure DevOps project name
  [string]$project,

  [Parameter(Mandatory)]
  # Azure DevOps environment name (Test, Preview, Staging, Production)
  [string]$environment,

  [Parameter(Mandatory)]
  # Local database name (unique resource name within the environment)
  [string]$database,

  [Parameter()]
  # Logs directory (defaults to $jade_home\logs)
  [string]$logs_directory,

  [Parameter()]
  # Journals directory (defaults to $jade_system\journals)
  [string]$journals_directory,

  [Parameter()]
  # Force setup when agent folder already exists or is previously registered
  [switch]$force,

  [Parameter(ValueFromRemainingArguments)]
  # Passthrough parameters for Azure agent config.cmd
  [string[]]$passthrough
)

$ErrorActionPreference = "Stop"

# Agent version — update when a newer release is available
$agentVersion = "4.266.2"

# Check setup is running in admin mode
if (-not ([Security.Principal.WindowsPrincipal][Security.Principal.WindowsIdentity]::GetCurrent()).IsInRole([Security.Principal.WindowsBuiltInRole]"Administrator"))
{
    throw "Run command in an administrator PowerShell prompt"
}

# Check PowerShell version
if ($PSVersionTable.PSVersion -lt (New-Object System.Version("5.0")))
{
    throw "PowerShell 5.0 or higher is required."
}

if (-not (Test-Path $jade_home))
{
    throw "Jade database home folder not found"
}

# Determine folder structure
function CheckFolders
{
    param($bin, $system)
    return ((Test-Path $jade_home\$bin) -and (Test-Path $jade_home\$system))
}

if (CheckFolders "bin" "system")
{
    $jade_bin    = "$jade_home\bin"
    $jade_system = "$jade_home\system"
}
else
{
    throw "Jade database home folder invalid (unrecognised folder structure)"
}

# Determine logs directory
$jade_logs = if ($logs_directory) { $logs_directory } else { "$jade_home\logs" }

# Determine journals directory
$jade_journals = if ($journals_directory) { $journals_directory } else { "$jade_system\journals" }

# Determine INI file
$inis = @(
    "$jade_home\$database.ini",
    "$jade_system\$database.ini",
    "$jade_bin\$database.ini",
    "$jade_home\jade.ini",
    "$jade_system\jade.ini",
    "$jade_bin\jade.ini"
)

foreach ($ini in $inis)
{
    if (Test-Path $ini)
    {
        $jade_ini = $ini
        break
    }
}
if (-not $jade_ini)
{
    throw "Failed to determine INI file"
}

# Determine agent path
$agent = "$jade_home\azagent"

# Load existing environment variables (preserves values not explicitly set, e.g. any custom additions)
$vars = [ordered]@{}
$env_file = "$agent\.env"
if (Test-Path $env_file)
{
    Get-Content $env_file | foreach {
        $env_name, $env_value = $_.split('=')
        if ("$env_value".trim() -ne "") {
            $vars[$env_name] = $env_value
        }
    }
}

# Update environment variables
$vars['JADE_HOME']     = $jade_home
$vars['JADE_BIN']      = $jade_bin
$vars['JADE_INI']      = $jade_ini
$vars['JADE_PATH']     = $jade_system
$vars['JADE_LOGS']     = $jade_logs
$vars['JADE_JOURNALS'] = $jade_journals

# Ensure agent folder exists
$null = New-Item -ItemType Directory -Force -Path $agent

# Write environment variables to agent file
$vars.keys | % { if ($vars.$_) { Write-Output "$_=$($vars.$_)" } } | Out-File $env_file

# Handle existing configuration
if (Test-Path $agent\.agent)
{
    if (-not $force)
    {
        throw "Agent already configured (use -force to replace)"
    }
    & $agent\config.cmd remove @passthrough
}

# Download agent package to standard cache location
$file = "vsts-agent-win-x64-$agentVersion.zip"
$zip  = "$env:ProgramData\Microsoft\Azure DevOps\Agents\$file"

if (-not (Test-Path $zip))
{
    Write-Host "Downloading agent files"
    $null = New-Item -ItemType Directory -Force -Path (Split-Path $zip)
    (New-Object Net.WebClient).DownloadFile("https://download.agent.dev.azure.com/agent/$agentVersion/$file", $zip)
}

Write-Host "Expanding agent files"
Expand-Archive $zip $agent -Force

Write-Host "Configuring agent"
& $agent\config.cmd --url "$url" --projectname "$project" --environment --environmentname "$environment" --agent $database --replace --runAsService --preventservicestart --addvirtualmachineresourcetags --virtualmachineresourcetags "Jade" @passthrough
if ($lastexitcode) { exit $lastexitcode }

# Recreate service with an environment-qualified name to ensure uniqueness
# when a host serves multiple environments
$service_file = "$agent\.service"
$current_service_name = Get-Content -Path $service_file -ErrorAction SilentlyContinue
if ($current_service_name)
{
    Stop-Service -Name $current_service_name
    Write-Host "Removing previous service ($current_service_name)"
    $null = (sc.exe delete $current_service_name)
    Remove-Item -Path $service_file -Force
}

$service_name = "vstsagent.$($project.Replace(' ','')).$environment.$database"
$null = New-Service `
    -Name $service_name `
    -BinaryPathName "`"$agent\bin\AgentService.exe`"" `
    -DisplayName "Azure Pipelines Agent ($project - $environment - $database)" `
    -Description "Manages $jade_home database"
Set-Content -Path $service_file -NoNewLine -Value $service_name
(Get-Item $service_file -Force).Attributes += [System.IO.FileAttributes]::Hidden
Write-Host "Service created ($service_name)"

$null = (sc.exe failure $service_name reset= 4294944000 actions= restart/0/restart/60000/restart/60000)
Write-Host "Service recovery options set"

$null = (sc.exe config $service_name start= delayed-auto)
Write-Host "Service startup set to delayed auto start"

Start-Service -Name $service_name
Write-Host "Service started"
```