Skip to content
SymmetricareRoutes

The database

The central service keeps everything in one database: the configuration and its version history, the history of every instance and delivery, HL7 messages, the audit log, and the encrypted secrets. Nodes never connect to it.

It runs on PostgreSQL 18 or later, or SQL Server 2019 or later (any edition). Both work the same; pick one per installation. The two cannot share one installation’s data.

The central service creates the database and its schema on first start, when its account may create databases. Otherwise create an empty database owned by that account, and it creates the schema.

PostgreSQL

Section titled “PostgreSQL”

PostgreSQL is free, has no size limit, and runs on Windows, Linux and in containers.

  • Version 18 or later, built with ICU (the usual packages and the official images are). Names, AE titles and patient names then compare without regard to case, as they do on SQL Server.

  • A user for the central service that may create databases, or that owns an empty one:

    CREATE USER routes WITH PASSWORD '<password>' CREATEDB;
    -- or, without CREATEDB:
    CREATE DATABASE routes OWNER routes;

  • The connection string: Host=db01;Port=5432;Database=routes;Username=routes;Password=<password>. Add SSL Mode=Require to encrypt the connection.

Set DatabaseProvider to PostgreSql and ConnectionString to it: on the Windows installer’s Database page, or in the registry, appsettings.json or the environment (Routes__Central__DatabaseProvider, Routes__Central__ConnectionString).

SQL Server

Section titled “SQL Server”

  • SQL Server 2019 or later, any edition. Express works for small sites, but its 10 GB limit caps how much history you can keep (see Plan and size).

  • Keep the default collation (case-insensitive).

  • Permissions: by default the central service connects with Windows authentication as its service account, LocalSystem, which SQL Server sees as:

    • NT AUTHORITY\SYSTEM when SQL Server is on the same server;
    • the computer account, such as CONTOSO\CENTRAL01$, when it is on another server.

    Give that login the dbcreator role, or create an empty Routes database and make the login its db_owner:

    CREATE LOGIN [CONTOSO\CENTRAL01$] FROM WINDOWS;
    ALTER SERVER ROLE dbcreator ADD MEMBER [CONTOSO\CENTRAL01$];

  • SQL authentication instead: give a full connection string, Server=sql01;Database=Routes;User Id=routes;Password=<password>;TrustServerCertificate=true.

  • Another server: enable TCP/IP in SQL Server Configuration Manager, and open TCP 1433 (or the instance’s port) in its firewall.

High availability

Section titled “High availability”

The database is the one piece every central server shares, so make it as available as the console needs to be. Nodes keep routing while it is down: they cache their configuration and keep their history on disk until it is back. Only the console, configuration changes and HL7 worklist updates wait.

  • SQL Server: an Always On availability group, with its listener in the connection string: Server=router-ag;Database=Routes;MultiSubnetFailover=True;Integrated Security=true;TrustServerCertificate=true.
  • PostgreSQL: streaming replication with automatic failover (Patroni, or your platform’s managed PostgreSQL), with the connection string pointing at the primary’s address or listing the hosts: Host=db01,db02;Target Session Attributes=primary;....

How big it gets

Section titled “How big it gets”

The history is most of it: about 2 KB per instance plus 1 KB per destination it goes to, kept for History retention (days) (30 by default), and HL7 messages for Keep HL7 messages (days) (90 by default). Plan and size has the formula and examples. Old history is deleted automatically every ten minutes.

The console’s charts add little: the nodes’ figures, sampled every minute and kept 7 days (about 1 MB per node and destination), and usage counted per hour (kept 400 days) and per day (kept for good), a few tens of MB a year.

Backups

Section titled “Backups”

Back up the database as you would any important application database:

  • SQL Server: nightly full backups plus log backups (full recovery model), or at least nightly fulls.
  • PostgreSQL: nightly pg_dump, or continuous archiving (WAL) for point-in-time recovery.

The database holds the encrypted secrets, but not the key to them. Keep that too:

  • on Windows with one central server, the secrets are tied to that server’s machine key: a database restored to another server cannot read them, so re-enter the SMTP password and webhook, and generate a new enrollment key;
  • with SecretProtection=Certificate (always on Linux and Docker), back up secret-protection.pfx from the central service’s data folder.

See Upgrades and backups.