Inhaltsverzeichnis

Runbook: DSN Export

Runbook: DSN Export

Duration: ~5 minutes
Role: Sysadmin, DBA
Application: Migration, documentation, disaster recovery

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

Workflow

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

1. DSN Structure

DSN are defined in appsettings.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=***"
      }
    }
  }
}

2. Export DSN

With jq (Linux/macOS):

# 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

With PowerShell (Windows):

# 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

3. Import DSN

With jq (Linux):

# 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

With PowerShell (Windows):

# 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

4. Add Single DSN

With jq:

# 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

With 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

5. Migrate DSN Between Environments

Export from production:

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

Import to staging (with password adjustment):

# 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"

6. Restart Gateway

After DSN changes:

# Linux
sudo systemctl restart data-gateway
 
# Windows
Restart-Service -Name "DataGateway"
 
# Docker
docker restart gateway

7. Test DSN

# 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

Expected output:

Testing demo: 200
Testing production: 200
Testing reporting: 200

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

Passwords in exports:

Never commit passwords to Git
Encrypt or delete export files
Use environment variables for credentials:

"ConnectionString": "Server=sql01;Database=ProdDB;User Id=${DB_USER};Password=${DB_PASS}"

Related Runbooks

Config Backup - Full backup
Manage DSN - DSN administration
Start Server - After import

« <- Config Backup | -> Operator Overview »

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

operator, runbook, dsn, export, import, migration