Skip to content

Database Requirements

Applies to: All subscriptions

Purpose

Specify the database precisely: which edition and configuration Complifly needs, why one particular feature is non-negotiable, and what ongoing maintenance the database requires to stay healthy.

Audience

Database administrators, infrastructure engineers, procurement (for the edition decision).

Prerequisites

Reference

Platform

Complifly runs on Microsoft SQL Server. Both self-managed instances and managed cloud database services are viable, provided the requirements below are met.

Verify: The minimum supported version and edition are not stated in this portal. Confirm with Complifly before purchase — the answer has a direct licensing cost. Recorded as assumption B2 in the Assumptions Register.

The non-negotiable requirement

The edition must support Row-Level Security.

Tenant isolation is enforced by security policies inside the database engine, not only by application code. An edition without that capability cannot run Complifly with its isolation guarantees intact.

Raise this in procurement as a functional requirement, not a technical preference. It is far cheaper to discover during licensing than after an environment has been built.

Configuration

Setting Requirement Why
Database A dedicated, empty database for Complifly Do not share with other applications; isolation policies apply to this database
Collation Agreed before creation and consistent across environments A collation mismatch causes comparison and sorting defects that surface late and are painful to correct
Recovery model Full, for production Required for point-in-time recovery
Encrypted connections Enabled Application-to-database traffic must be encrypted
Certificate trust A deliberate decision Trusting any certificate is acceptable only inside a controlled network, and should be a recorded decision
Auto-growth Fixed increments, not percentage Percentage growth produces increasingly large and disruptive growth events
Data and log files On separate volumes Standard practice; matters more as volume grows
Temporary database Sized and configured per platform guidance Validation and reporting workloads use it heavily

Security

Requirement Detail
Dedicated login The application connects with its own least-privilege login. Never an administrative account
Privileges Enough to read and write application data and to run migrations. Not server-level administration
Network exposure Reachable only from the application and worker hosts. Never from the internet
Credential storage Held in application configuration with restricted file permissions, or in a secret store
Credential rotation Owned, scheduled, and tested. Rotation that has never been rehearsed fails when it is needed
Auditing Aligned to the customer's standard; ensure it does not itself become a growth problem

Isolation policies are part of the schema

Security policies ship with the tables they protect, in the same migration. Two consequences for a database administrator:

  • Restoring a backup restores the policies. A restore does not silently lose isolation.
  • Creating a table by hand for reporting does not inherit isolation. Any table added outside the migration path is unprotected. Prefer views over copies, and if a copy is unavoidable, treat it as containing cross-tenant data.

Never disable a security policy to make a query or a tool work. See Multi-Tenancy and Row-Level Security for the supported approach.

Maintenance

Task Frequency Note
Index maintenance Weekly or per fragmentation thresholds The most common cause of gradual slowdown at constant volume
Statistics update Weekly, or after bulk loads Stale statistics produce sudden, dramatic plan regressions
Integrity check Weekly Detects corruption before a restore is needed
Backup verification Every backup An unverified backup is an assumption
Restore rehearsal Quarterly An untested restore is not a recovery capability
Growth review Monthly Compare actual against forecast; re-forecast on divergence

Backup

Element Recommendation
Full backup Daily
Transaction log backup Hourly, or more frequently if the recovery point objective demands it
Retention 35 days online, with longer-term archival per the statutory retention requirement
Offsite copy Yes; a backup on the same host is not a backup
Encryption Backups encrypted at rest
Restore testing Quarterly, into a scratch environment

Verify: The schedule above is a recommendation, not a Complifly commitment. Replace it with the customer's agreed recovery point and recovery time objectives. Recorded as assumption C1.

Validation

Check Method Pass condition
Edition supports Row-Level Security Confirm against the edition's feature list Supported. If not, stop — do not proceed with an installation
Database reachable from the application host Connect using the application's own login Succeeds with encryption enabled
Login is least-privilege Attempt a server-level administrative action with it Denied
Not internet-reachable Attempt a connection from outside the application network Refused
Policies present after installation List the database's security policies One for every tenant-scoped table
Policies enforced, not just defined Sign in as tenant A and request a tenant B record Not found
Backup runs and verifies Inspect backup history and verification results Recent, successful, verified
Restore actually works Restore into a scratch environment and open the application against it Application starts and data is present and correctly scoped
Maintenance is running Inspect job history Index, statistics and integrity tasks all completing
Growth tracks forecast Compare measured size against the forecast Within 25 percent, or the forecast has been revised

The restore rehearsal is the check most often skipped and the only one that proves recovery. Perform it before go-live, not after the first incident.

Troubleshooting

Symptom Cause Action
Application refuses to start, naming a missing object Startup schema guard found an object absent Run the named migration. This is intended behaviour
Queries return zero rows against a database known to hold data Session scope not set; isolation filtered everything silently Set scope and cross-check counts against storage metadata. Never disable the policy
A reporting tool sees nothing The tool connects without setting scope Request the supported reporting pattern from Complifly. Do not remove policies
Gradual slowdown at constant volume Index fragmentation or stale statistics Run maintenance before considering hardware
Sudden severe slowdown after a bulk load Statistics stale after a large data change Update statistics on the affected tables
Log file grows without bound Full recovery model without log backups Configure log backups. Do not switch to simple recovery to "fix" it — that discards point-in-time recovery
Disruptive pauses at intervals Percentage-based auto-growth producing ever-larger growth events Switch to fixed-increment growth and pre-size the files
Restored environment shows no data to users Tenant identifiers differ between environments Reconcile scope identifiers. Never disable isolation to work around it
Connection failures after a credential rotation Rotation not applied everywhere, or never rehearsed Update every consumer: API, worker, and any operational tooling
Corruption discovered during a restore Integrity checks not running Add weekly integrity checks; assess how far back the corruption extends