Database Maintenance

What is in there#

The Infohub database holds the model, the configuration, the calculated values, the chart and dashboard definitions, the deployment layout, and the log.

That has one important implication worth stating early: backing up this database backs up Infohub. There is no separate content store to remember. See Backup and Recovery.

Routine maintenance#

Infohub's write pattern — many small inserts and updates across a moving calculation window — fragments indexes steadily. Standard SQL Server maintenance applies, and it matters more here than for a mostly-read database.

A standard maintenance solution scheduled through SQL Server Agent covers the first three. There is nothing Flow-specific about them; the point is that they must actually be scheduled, and on a good many instances nobody has.

Growth#

Infohub's growth is driven by interval count and retention, and it is predictable enough to forecast.

Rough annual value counts per measure:

So one minutely measure costs roughly as much as sixty hourly ones. Multiply across the model, add index overhead, and you have a forecast.

This is why minutely measures are worth being deliberate about — they dominate both storage and calculation cost. See Engine Performance and Tuning.

Track the size monthly. A growth curve tells you when you will need more storage, and a step change in the curve tells you somebody added a large amount of model on a particular date.

Retention#

Infohub does not keep everything forever unless you tell it to. Several retention settings need deciding rather than defaulting.

Event schemes carry an Archive Age and a Purge Age. Archive moves older events out of the active set; purge removes them.

Live-source connections — MQTT, OPC UA live — carry a Purge Age for buffered values, which must comfortably exceed the interval you aggregate to or values are discarded before the engine uses them.

Set these against your actual reporting horizon:

• If people compare against last year, retention must exceed a year comfortably.
• If quality traceability is a regulatory obligation, that period is the constraint.
• If a scheme is purely operational — machine states for a live dashboard — shorter retention is reasonable and keeps things fast.

That last point deserves emphasis. If events are purged after two years but backups are kept for one, then two-year-old manually entered reasons are gone from both — and nobody discovers it until an audit asks.

The freeze point earns its keep#

The freeze point on a time scheme marks everything before it as closed. It is protection against late values silently changing signed-off periods, and it is also a performance control: the engine stops re-examining history on every pass.

An instance with years of history and no freeze point is doing avoidable work continuously.

Move it forward as periods are finalised, typically after month-end sign-off — and not so close to the present that legitimately late source values are discarded. See Interval Types and Time Schemes.

Storage layout#

For anything beyond a small deployment:

• SSD or better. Infohub's write pattern handles poorly on spinning disks.
• Data and log on separate volumes.
• Size the log for the workload. Large backfills generate substantial log activity. A log left to grow in small increments fragments and slows.
• Pre-size the data file rather than relying on frequent small autogrowth events.

Permissions#

Infohub needs an account with rights to read and write its own database, and to upgrade its schema when a newer Flow Config opens it.

Two notes:

Schema upgrades need elevated rights. The account performing an upgrade needs to alter the schema. A tightly restricted account that works day to day may fail at upgrade time — which is discovered during a maintenance window, at the worst moment.

The Bootstrap service account is what matters at run time. Where integrated security is used, that account needs the database rights, not the modeller's account.

Before and after an upgrade#

The schema upgrade performed by a newer Flow Config is not reversible.

1. Back up, and verify the backup restores. Not just that it completed.
2. Confirm you have somewhere to restore it to.
3. Perform the upgrade.
4. Verify the model, a sample of values, and deployment status.
5. Keep the pre-upgrade backup until you are confident.

See Upgrading to Flow 7.2.0.

A monthly review#

Fifteen minutes, worth scheduling:

1. Database size, against last month.
2. Maintenance jobs succeeded — not just scheduled.
3. Backups succeeded, and a restore has been tested recently.
4. Deployment status — nothing Different or Faulted.
5. Freeze point moved forward as periods finalised.
6. Retention settings still match the reporting horizon.
7. The Flow event log for recurring errors.

Items 2 and 3 are the ones that turn out not to be true on instances nobody has reviewed.

Diagnosing#

See also#

• The Maintenance Window
• Backup and Recovery
• Engine Performance and Tuning
• Interval Types and Time Schemes