Skip to content

OVERVIEW.md

Latest commit

 

History

History
95 lines (70 loc) · 4.52 KB

OVERVIEW.md

File metadata and controls

95 lines (70 loc) · 4.52 KB
Raw

Central SQL Backup Monitoring System

Executive summary

The Central SQL Backup Monitoring System provides one dashboard for monitoring automated SQL Server backups across multiple servers. Each server continues to create and copy backups independently, while securely reporting its status to a central service.

This gives the operations team immediate visibility into successful, failed, delayed, or missing backups without manually checking individual servers and log files.

Business value

  • Central visibility across all SQL backup servers
  • Faster identification of backup and NAS-transfer failures
  • Automatic detection of servers that stop reporting
  • Reduced time spent manually reviewing Windows log files
  • Historical records for troubleshooting and operational review
  • Safer retention: source backups are removed only after the NAS copy is verified
  • Easy expansion when additional SQL servers are introduced

How it works

  1. A scheduled PowerShell job runs on each SQL Server.
  2. The job selects the latest database backups and creates a compressed archive.
  3. The archive is copied to the NAS and verified by filename and size.
  4. The server sends a structured result to the central monitoring API.
  5. The dashboard displays node health, backup status, database count, duration, archive size, and NAS-copy result.
  6. If the monitoring service is temporarily unavailable, the server queues its report locally and retries it during the next run.
SQL Servers ──secure status reports──► Central Monitoring API
                                              │
                                              ├── SQLite history database
                                              └── Protected web dashboard

The monitoring service does not require administrative access to the SQL servers and does not receive database backup files. Only operational status and backup metadata are transmitted.

Dashboard information

The dashboard provides:

  • Total, healthy, warning, failed, and overdue server counts
  • Latest backup status for every server
  • Last report time and backup age
  • Number of databases included
  • Backup duration and ZIP size
  • NAS-transfer and Robocopy status
  • Paginated backup-run history
  • Clear error information for failed jobs

Security and reliability

  • Each server uses its own API key
  • Dashboard access requires separate credentials
  • HTTPS is supported through a reverse proxy
  • API requests are validated and rate limited
  • Security headers and request-size limits are enabled
  • The service runs as a non-root Docker user with restricted permissions
  • Reports are idempotent, preventing duplicate records during retries
  • Failed reports are queued locally instead of being lost
  • Database retention is configurable
  • Health endpoints support service monitoring

Technology

  • Windows PowerShell backup agent
  • Node.js and Express monitoring service
  • SQLite database with WAL mode
  • Responsive EJS web dashboard
  • Docker Compose deployment

The design is lightweight and suitable for the current environment. The database layer can be moved to PostgreSQL later if reporting volume or retention requirements grow significantly.

Deployment plan

  1. Deploy the central service on an internal server behind HTTPS.
  2. Configure unique credentials and expected reporting intervals for each node.
  3. Pilot the updated PowerShell job on one SQL Server.
  4. Confirm backup creation, NAS transfer, dashboard reporting, and failure alerts.
  5. Roll out the agent to the remaining servers in phases.
  6. Add email, Microsoft Teams, or Slack alerts as a later enhancement.

Current status

The production-oriented application and Windows reporting script are complete. Automated verification currently covers API authentication, database readiness, idempotent backup reporting, dashboard protection, and dashboard rendering.

  • All automated integration tests pass
  • JavaScript validation passes
  • Docker Compose configuration validates
  • Production dependency audit reports no known vulnerabilities

Before organization-wide rollout, the PowerShell backup process should be piloted on one Windows SQL Server using the actual SQL backup directory, NAS UNC path, service account, and network security configuration.

Expected outcome

After rollout, the operations team will have a single source of truth for daily SQL backup health. Missing or failed backups will be visible promptly, investigation will require less manual effort, and management will have a clear operational record of backup activity across all monitored servers.