Database
Scripts for managing the eshars Azure SQL databases operationally — outside of schema changes (which live in HISD’s DbUpgrader and SQL Server Database Projects).
Subdirectories
dns-alias/
PowerShell scripts to swap which SQL Server a DNS alias points to. This is the core of the zero-downtime database swap pattern:
| Script | Purpose |
|---|---|
show-current.ps1 | Display current CNAME targets for eshars-primary and eshars-reporting |
swap-primary.ps1 | Redirect eshars-primary.db.eshars.internal to a different SQL Server |
swap-reporting.ps1 | Redirect eshars-reporting.db.eshars.internal to a different SQL Server |
All eshars apps (HISD, mobile-api, mgmt) connect through these aliases. Swapping the alias = swapping the database for all apps, no deployment needed.
snapshots/
Point-in-time restore and database copy scripts:
| Script | Purpose |
|---|---|
create-snapshot.ps1 | Create a copy or point-in-time restore of an eshars database |
restore-snapshot.ps1 | Swap a DNS alias to point at a previously created snapshot |
maintenance/
Scheduled SQL scripts for database health:
| Script | Purpose |
|---|---|
index-maintenance.sql | Reorganize/rebuild fragmented indexes, update statistics |
cleanup-logs.sql | Purge old entries from the log database (90-day retention) |
monitoring/
Ad-hoc diagnostic queries for performance troubleshooting and access auditing:
| Script | Purpose |
|---|---|
active-sessions.sql | Start here during incidents. Shows all running sessions with CPU, elapsed time, IO, current statement, and blocking info |
dtu-resource-breakdown.sql | Breaks down DTU usage by CPU, data IO, and log writes — tells you which resource is the bottleneck |
query-store-top-consumers.sql | Top 20 heaviest queries by CPU over the last 2 hours from Azure SQL Query Store |
blocking-chains.sql | Shows blocked sessions, their blockers, wait types, and both query texts |
session-details.sql | Identifies WHO is running a query — login name, host machine, program (SSMS, app, SQL Agent), stored procedure name |
slow-queries.sql | Find queries averaging > 1 second via dm_exec_query_stats |
database-size.sql | Database and table-level space usage report |
entra-access-audit.sql | Lists all Entra ID (Azure AD) users and groups with their assigned database roles |
Access Control
For the Entra group model governing who has access to which databases, see Database Access Strategy.
TODO
These are generic DBA scripts. They need eshars-specific customization:
-
cleanup-logs.sql— update table/column names to match actual HisdLogDb schema -
index-maintenance.sql— add exclusions for tables that can’t do online rebuilds - Add monitoring queries for eshars-specific concerns (e.g., stuck billing jobs, visit processing queue depth, TMHP submission backlogs)
- Add scripts for common eshars operations (e.g., re-processing failed EDI batches, clearing import locks)