Skip to content

Database Setup

Applies to: All subscriptions

Purpose

Create the database and login Complifly will use, configure connectivity correctly the first time, and verify it from the host that actually needs it. Getting collation and encryption right now avoids corrections that are painful once data exists.

Audience

Database administrators, working with the implementation engineer.

Prerequisites

  • Database Requirements reviewed, including the edition decision
  • A SQL Server instance available and reachable from the application host
  • Agreement on collation, recovery model and file placement

Steps

1. Confirm the edition supports Row-Level Security

Do this before anything else. Tenant isolation is enforced by security policies inside the engine; an edition without that capability cannot run Complifly with its isolation guarantees intact. Discovering this after an environment is built is expensive.

2. Create an empty database

Decision Guidance
Name Distinct and consistent across environments, with the environment in the name so a connection string cannot be confused
Collation Agreed and identical across all environments. A mismatch causes comparison and sorting defects that surface late
Recovery model Full, for production
Data and log files Separate volumes
Initial size Pre-sized to the first-year forecast, so the system is not growing files during business hours
Auto-growth Fixed increments, never a percentage

Create it empty. The schema arrives through migrations, not through a supplied database backup.

3. Create a least-privilege login

Create a dedicated login and map it to a user in the Complifly database.

Requirement Detail
Dedicated to Complifly Not shared with another application
Not an administrative account It must not hold server-level administrative rights
Sufficient rights on this database Read and write application data, and create or alter objects for migrations
Strong credential Generated, stored securely, and its rotation owned

The most common shortcut here is to use an administrative account "for now". It survives into production, and it turns a routine application compromise into a server-level one.

4. Configure connectivity

Setting Requirement Failure if wrong
TCP/IP protocol enabled on the instance Required The application cannot connect at all, while local tools can
Port reachable from the application host Required Connection timeout at startup
Encryption enabled Required Traffic in clear text
Certificate trust A deliberate decision Handshake failures, or an unrecorded weakening of the posture
Instance name or port in the connection settings Must match reality Startup failure that reads as a credential problem

Where the database is a named instance or is behind a listener, the connection settings must reflect that exactly. This is a frequent cause of a startup failure that gets misdiagnosed as a bad password.

5. Write the connection configuration

Set the database settings in the application's environment configuration. See Environment Variables for the exact keys.

Setting Note
Server Hostname or address, and instance name if applicable
Port Explicit; do not rely on discovery
Database The database created in step 2
User and password The login created in step 3
Encryption Enabled
Certificate trust Set deliberately and recorded
Pool sizing and timeouts Start with defaults; tune only against measured evidence

6. Verify from the application host

Test the connection from the application host, using the application's own credentials and the same encryption settings. A successful test from a database administrator's workstation with an administrative account proves almost nothing about whether the application will connect.

7. Configure backup before loading data

Configure backup now, while the database is empty and a mistake costs nothing. Then perform a restore into a scratch environment to prove the mechanism works. See Backup and Restore.

Validation

Check Method Pass condition
Edition supports Row-Level Security Confirm against the edition feature list Supported
Database exists and is empty Inspect the object list Present, with no application objects yet
Collation as agreed Query the database collation Matches the agreed value, and matches other environments
Recovery model correct Inspect the database properties Full, for production
Application login works from the application host Connect using exactly the application's credentials and settings 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
Backup configured Inspect backup history A successful backup exists
Restore proven Restore into a scratch environment Restore completes and the database opens
Auto-growth sane Inspect file growth settings Fixed increments, files pre-sized

Troubleshooting

Symptom Cause Action
Application cannot connect; local tools can TCP/IP protocol disabled on the instance Enable it and restart the instance. This is a common first-installation fault
Connection times out from the application host only Firewall between the tiers, or the wrong port Test from the application host; open the database port between those hosts only
Login fails with the correct password Wrong database context, user not mapped, or a named-instance mismatch Confirm the login is mapped to a user in the Complifly database and that the instance name is right
Connection fails only with encryption enabled Certificate not trusted by the application host Trust the certificate, or make the trust decision explicit and record it
Migrations fail with a permissions error The login lacks rights to create or alter objects Grant the necessary rights within this database, not at server level
Comparisons or sorting behave oddly after go-live Collation mismatch between environments or within objects Correcting collation after data exists is disruptive. Fix it now, before loading
The log file grows without bound Full recovery model with no log backups Configure log backups. Do not switch to simple recovery — that discards point-in-time recovery
Periodic pauses during business hours Percentage auto-growth causing large growth events Switch to fixed increments and pre-size the files
A restored copy shows no data to users Tenant identifiers differ between environments Reconcile scope identifiers. Never disable isolation policies to work around it