====== Runbook: DSN Export ======

<WRAP round info>
**Duration:** ~5 minutes \\
**Role:** Sysadmin, DBA \\
**Application:** Migration, documentation, disaster recovery
</WRAP>

Export and import of DSN definitions (Data Source Names).

----

===== Workflow =====

<mermaid>
flowchart TD
    A[Start] --> B{Goal?}
    B -->|Export| C[Extract DSN from config]
    B -->|Import| D[Insert DSN into config]
    C --> E[Save JSON]
    D --> F[Restart Gateway]
    E --> G[Done]
    F --> H[Test DSN]
    H --> G

    style G fill:#e8f5e9
</mermaid>

----

===== 1. DSN Structure =====

DSN are defined in ''appsettings.json'':

<code json>
{
  "Gateway": {
    "Databases": {
      "demo": {
        "Provider": "sqlite",
        "ConnectionString": "Data Source=data/demo.db"
      },
      "production": {
        "Provider": "sqlserver",
        "ConnectionString": "Server=sql01;Database=ProdDB;User Id=app;Password=***"
      },
      "reporting": {
        "Provider": "postgresql",
        "ConnectionString": "Host=pg01;Database=reports;Username=reader;Password=***"
      }
    }
  }
}
</code>

----

===== 2. Export DSN =====

**With jq (Linux/macOS):**

<code bash>
# Export all DSN as JSON
jq '.Gateway.Databases' appsettings.json > dsn-export.json

# Export single DSN
jq '.Gateway.Databases.production' appsettings.json > production-dsn.json

# List DSN names
jq -r '.Gateway.Databases | keys[]' appsettings.json
</code>

**With PowerShell (Windows):**

<code powershell>
# Load JSON
$config = Get-Content "appsettings.json" | ConvertFrom-Json

# Export all DSN
$config.Gateway.Databases | ConvertTo-Json -Depth 10 | Out-File "dsn-export.json"

# List DSN names
$config.Gateway.Databases.PSObject.Properties.Name
</code>

----

===== 3. Import DSN =====

**With jq (Linux):**

<code bash>
# Insert DSN from export file into config
jq --slurpfile dsn dsn-export.json '.Gateway.Databases = $dsn[0]' appsettings.json > appsettings.new.json

# Backup and replace
cp appsettings.json appsettings.json.bak
mv appsettings.new.json appsettings.json
</code>

**With PowerShell (Windows):**

<code powershell>
# Load existing config
$config = Get-Content "appsettings.json" | ConvertFrom-Json

# Load export
$dsnExport = Get-Content "dsn-export.json" | ConvertFrom-Json

# Replace DSN
$config.Gateway.Databases = $dsnExport

# Save
$config | ConvertTo-Json -Depth 10 | Out-File "appsettings.json" -Encoding UTF8
</code>

----

===== 4. Add Single DSN =====

**With jq:**

<code bash>
# Add new DSN
jq '.Gateway.Databases.newdb = {
    "Provider": "mysql",
    "ConnectionString": "Server=mysql01;Database=newdb;User=app;Password=secret"
}' appsettings.json > appsettings.new.json

mv appsettings.new.json appsettings.json
</code>

**With PowerShell:**

<code powershell>
$config = Get-Content "appsettings.json" | ConvertFrom-Json

# New DSN as PSObject
$newDsn = [PSCustomObject]@{
    Provider = "mysql"
    ConnectionString = "Server=mysql01;Database=newdb;User=app;Password=secret"
}

# Add
$config.Gateway.Databases | Add-Member -Name "newdb" -Value $newDsn -MemberType NoteProperty

$config | ConvertTo-Json -Depth 10 | Out-File "appsettings.json" -Encoding UTF8
</code>

----

===== 5. Migrate DSN Between Environments =====

**Export from production:**

<code bash>
# On prod server
ssh prod-gateway "jq '.Gateway.Databases' /opt/data-gateway/appsettings.json" > prod-dsn.json
</code>

**Import to staging (with password adjustment):**

<code bash>
# Replace passwords with staging passwords
jq '.production.ConnectionString = "Server=sql-staging;Database=ProdDB;User Id=app;Password=staging-pwd"' \
    prod-dsn.json > staging-dsn.json

# Import to staging
scp staging-dsn.json staging-gateway:/tmp/
ssh staging-gateway "jq --slurpfile dsn /tmp/staging-dsn.json \
    '.Gateway.Databases = \$dsn[0]' /opt/data-gateway/appsettings.json > /tmp/new.json && \
    mv /tmp/new.json /opt/data-gateway/appsettings.json && \
    systemctl restart data-gateway"
</code>

----

===== 6. Restart Gateway =====

After DSN changes:

<code bash>
# Linux
sudo systemctl restart data-gateway

# Windows
Restart-Service -Name "DataGateway"

# Docker
docker restart gateway
</code>

----

===== 7. Test DSN =====

<code bash>
# Test all DSN
for dsn in $(jq -r '.Gateway.Databases | keys[]' appsettings.json); do
    echo -n "Testing $dsn: "
    curl -s -o /dev/null -w "%{http_code}\n" "http://localhost:5000/api/v1/dsn/$dsn/tables"
done
</code>

**Expected output:**

<code>
Testing demo: 200
Testing production: 200
Testing reporting: 200
</code>

----

===== 8. Checklist =====

| # | Check | Done |
|---|-------|------|
| 1 | DSN exported | [ ] |
| 2 | Export file backed up | [ ] |
| 3 | Passwords not in clear text in export | [ ] |
| 4 | Import completed | [ ] |
| 5 | Gateway restarted | [ ] |
| 6 | All DSN tested | [ ] |

----

===== Troubleshooting =====

| Problem | Cause | Solution |
|---------|-------|----------|
| ''jq: parse error'' | Invalid JSON | Validate JSON |
| DSN not found | Gateway not restarted | Restart |
| ''Connection failed'' | Wrong connection string | Check credentials |
| Encoding issues | Wrong encoding | Use UTF-8 |

----

===== Security Notes =====

<WRAP round important>
**Passwords in exports:**
  * Never commit passwords to Git
  * Encrypt or delete export files
  * Use environment variables for credentials:
<code json>
"ConnectionString": "Server=sql01;Database=ProdDB;User Id=${DB_USER};Password=${DB_PASS}"
</code>
</WRAP>

----

===== Related Runbooks =====

  * [[.:config-backup|Config Backup]] - Full backup
  * [[..:tagesgeschaeft:dsn-verwalten|Manage DSN]] - DSN administration
  * [[..:tagesgeschaeft:server-starten|Start Server]] - After import

----

<< [[.:config-backup|<- Config Backup]] | [[..:start|-> Operator Overview]] >>

----
//Wolfgang van der Stille @ EMSR DATA d.o.o. - Data Gateway Professional//

{{tag>operator runbook dsn export import migration}}