Database administration for agency systems on a small team

Reference article · 6 min read

Most state and local agencies run their core systems on databases they did not design and cannot modify. The permitting system, the computer aided dispatch platform, the utility billing package: each one sits on a SQL Server, Oracle, or PostgreSQL instance that the vendor specified and the agency now owns. When the IT shop is two people, database administration becomes whatever happens after the help desk queue is clear, which in practice means it happens when something breaks.

That works right up until it does not. Failures from neglected database maintenance rarely arrive gradually. A transaction log fills a volume and the application stops accepting writes. A restore is attempted and the newest usable backup turns out to be weeks old. A month end report that used to take two minutes takes forty. What follows is the routine work a small shop still owes the database under a vendor application, and the ways to get it done without hiring a full time DBA.

What still belongs to you behind a vendor application

A support agreement covers the application. It rarely covers the health of the database instance underneath, and almost never covers the operating system, the storage, or the backup chain. Read the agreement and draw the line explicitly. In a typical on premises deployment, the agency usually keeps:

• Instance configuration. Memory ceilings, parallelism settings, tempdb placement, and service account rights.
• Backup and recovery. Schedule, retention, offsite or offline copy, and the restore test that proves the chain works.
• Capacity. Disk free space, file growth settings, and alerting that fires before a volume fills.
• Patching. Database engine updates and host operating system patches, applied in a window the vendor supports.
• Access control. Who holds elevated rights, and whether application service accounts are more privileged than they need to be.

Write this as a one page division of duties and have the vendor acknowledge it. Ambiguity here is what produces the outage where each party believed the other was watching.

Backups and log management

Confirm the recovery model of every production database and make sure it matches what you actually do. A database set to full recovery with no scheduled log backups will grow its log until the volume fills, and shrinking the file afterward treats the symptom rather than the cause. If the application tolerates losing work since the last full or differential backup, simple recovery may be the honest choice. If it does not, log backups have to run on a schedule and be retained with the fulls.

Two habits matter more than the schedule itself. First, restore something on a regular cadence, to a separate server, and time how long it takes. An untested backup is a hypothesis. Second, keep at least one copy that a compromised administrator account cannot delete or encrypt, whether that is immutable object storage, tape, or a separate credential domain. Ransomware response plans frequently fail at exactly this point. Also verify that backup job failure alerts reach a person who is on duty, not a distribution list nobody reads.

Index, statistics, and space maintenance

Query plans degrade quietly. Statistics go stale after large loads or purges, and the optimizer starts choosing badly. Index fragmentation matters less than it once did on modern storage, but statistics maintenance and a sane reorganize or rebuild policy are still worth scheduling. On a small team, adopt a well known maintenance script set rather than hand building maintenance plans, and run it in a window that does not collide with the application's own nightly jobs.

Track file size and free space monthly so growth is a trend you can forecast rather than a surprise. Set autogrowth to fixed increments rather than percentages, and pre size files where you can. Purging and archiving belong in the same conversation: find out what retention and archive features the application ships, and align them with your records retention schedule. Retention requirements vary by state and record type, so confirm the schedule with your records officer or counsel rather than assuming.

Version currency and what the support agreement forbids

Vendors certify their applications against specific database engine versions. Running an engine past its vendor supported life means no security fixes, and that tends to surface during audits, cyber insurance renewals, or reviews tied to criminal justice or health data requirements. Check the engine's published lifecycle dates against the vendor's certification matrix at least annually, and treat an upgrade as a project with a test restore, not a weekend task.

At the same time, know what you are not allowed to touch. Many agreements void support if the agency changes the schema, adds triggers, edits data directly through the database, or installs agents on the instance. Adding a nonclustered index is often the exception that a vendor will approve in writing, and that written approval is what you want in the ticket history. Keep a dated change log of every instance level change you make.

Keeping reporting off the production workload

Read access for reporting is the most common way a stable transactional system becomes an unstable one. An analyst running an unfiltered join at 10 a.m. can block the intake screen everyone else is using. The durable fixes are architectural:

• Copy the data. A nightly restore to a reporting server, log shipping, replication, or a readable secondary gives analysts their own instance to strain.
• Build a reporting layer. Even a modest extract into a separate database, shaped for the questions people actually ask, removes both the load and the need to understand vendor table structures.
• Constrain direct access. If queries must hit production, use read only logins, cap query time, block ad hoc access to the application's own service account, and schedule heavy extracts outside business hours.

How to staff it, and what to ask for in writing

A generalist can own most of this with a runbook, defined scope, and a few days of focused training: backups and restores, alerting, space monitoring, patching, and knowing when to stop. Remote DBA hours are worth buying for the work that is occasional and consequential: upgrade planning, performance triage, security review, and an annual health check. A modest retainer with a named escalation path is usually better value than an open ended block of hours nobody remembers to use.

Before signing or renewing, ask the vendor: which engine versions and patch levels are certified today, who performs backups and who verifies them, what maintenance the application performs on its own schedule, what changes void support, whether customer added indexes can be approved in writing, and what read replica or reporting options are supported. Put the division of duties, the supported version matrix, and the restore responsibility into the contract rather than the implementation email thread.

HQT works with state and local agencies on cloud and enterprise IT operations, including database platform work behind vendor supplied systems.

Frequently asked questions