# The ESP Database

ESP uses a single SQL Server database called **EspBroker**. This is deployed and upgraded as part of the overall ESP deployment process.

# What Is a Dacpac?

A **dacpac** (Data-tier Application Package) is a packaged representation of a SQL Server database **schema**. It is a file with the extension `.dacpac`.

Rather than writing migration scripts that say "ALTER TABLE, ADD COLUMN…" manually, you instead describe the **desired end state** of the database, and the dacpac deployment tool (`sqlpackage`) calculates the delta and applies it automatically.

## How Dacpac Deployment Works

    Your dacpac file (desired schema)
             │
             ▼
        sqlpackage.exe
             │
             ├── Connects to target database
             ├── Compares desired schema to actual schema
             ├── Generates a diff
             └── Applies the diff (ALTER TABLE, CREATE INDEX, etc.)

## Advantages Over Raw SQL Scripts

| Dacpac                                  | Raw SQL Scripts                           |
| --------------------------------------- | ----------------------------------------- |
| Declarative — describe what you want    | Imperative — describe each change step    |
| Tool calculates the diff automatically  | You must track and apply changes manually |
| Idempotent — safe to run multiple times | Can fail if run twice without care        |
| Schema is versioned as code             | Scripts can get out of sync               |

## Relevant PowerShell / CLI

``` powershell
# Deploy a dacpac using sqlpackage
& "C:\Program Files\Microsoft SQL Server\160\DAC\bin\sqlpackage.exe" `
    /Action:Publish `
    /SourceFile:"EspBroker.dacpac" `
    /TargetServerName:"ROMAC-DEV-DB01" `
    /TargetDatabaseName:"EspBroker"
```

# Team Ludo's Dacpac

Team Ludo built a dacpac solution for EspBroker. This is already in the repo and represents the **target schema** that all customer databases should eventually conform to.

# The Critical Problem — Schema Inconsistency

## What the Problem Is

Existing customer databases have drifted from the official schema over time. This drift likely happened due to:

  - Manual hotfixes applied directly to production databases
  - Different versions of ESP deployed to different customers at different times
  - No enforced schema management historically

This means the dacpac's expected schema does not match what is actually in customer databases.

## Consequence

If you attempt to deploy the dacpac against an inconsistent database, `sqlpackage` will either:

  - Fail with errors (best case — nothing is changed)
  - Apply incorrect changes that corrupt data (worst case)

## What Needs to Happen

Before the dacpac can be used for a customer:

1.  A database expert must **manually inspect** the customer's database
2.  Identify all schema differences between actual and expected state
3.  Write and apply **manual SQL scripts** to bring the DB into alignment
4.  Verify the dacpac can then deploy cleanly (ideally against a copy)
5.  Only then mark the customer as `dacpacReady: true` in the manifest

## Pipeline Safeguard

The deployment pipeline **must** check the `dacpacReady` flag before attempting database deployment, and fail clearly if it is `false`:

``` powershell
function Invoke-DatabaseDeployment {
    param($Manifest)

    if (-not $Manifest.Database.DacpacReady) {
        Write-Error "Database for $($Manifest.Customer) $($Manifest.Environment) is not dacpac-ready. " +
                    "Manual schema alignment is required before deployment."
        throw "Database not ready for dacpac deployment."
    }

    # Proceed with dacpac deployment...
    Deploy-Dacpac -DacpacPath $artifactPath -Server $Manifest.Servers.DbServer -Database "EspBroker"
}
```

# Database Deployment in the Deployment Stage

Database deployment sits inside the **DEPLOY stage**. See [[ESP Deploy Stages]] for full stage detail. The database is typically deployed before services are started up to ensure the schema is ready for the new application code.

Suggested order within the DEPLOY stage:

1.  Stop Windows Services
2.  Deploy database dacpac (if dacpacReady)
3.  Handle Citrix sessions
4.  Swap application files
5.  Start Windows Services back up

# Rollback Considerations

If the dacpac deploys successfully but a subsequent step fails, rolling back the database is non-trivial. Dacpac does not natively support rollback — options are:

  - Restore from backup (requires a backup to have been taken immediately before)
  - Write a counter-dacpac that reverts the schema (complex, error-prone)

This is one of the reasons the **rollback plan is still TBC**. See [[ESP OQ]].

A **database backup must be taken before any deployment** — this should be a mandatory step in the PREDEPLOY stage.

# SQL Server Concepts Relevant Here

| Concept        | Relevance                                                    |
| -------------- | ------------------------------------------------------------ |
| Schema         | The structure of tables, columns, indexes, constraints, etc. |
| `sqlpackage`   | Microsoft CLI tool that applies dacpac files                 |
| `BACPAC`       | Like dacpac but includes data — useful for backup/restore    |
| SQL Agent Jobs | Scheduled SQL jobs that may need to be handled during deploy |
| Linked Servers | DB connections to other servers — may be part of ESP's setup |
