Moving to SQL Server

A Core or Relay installed on SQLite can move to SQL Server without a reinstall. It is on Settings > General > Database, and it is the same operation as the API (POST /api/settings/database/migration/start, GET /api/settings/database/migration/status). Only an Administrator can start it or read its progress.

The two choices#

What it does, in order#

1. Installs SQL Server Express (Express option only), checking for a pending Windows restart first (see below), and waits for the MSSQL$FLUXDEPLOYSQL service.
2. Creates the database and tests the connection.
3. Creates the schema and copies every table, keeping row identities.
4. Rewrites appsettings.json to point at SQL Server (written to a temporary file, then swapped in).
5. Renames the SQLite file and its -wal / -shm files to .bak. They are kept, never deleted.
6. Records a DATABASE_MIGRATED audit entry in the new database. The connection string in it has its password masked.
7. Restarts the FluxDeploy service. The Windows service recovery settings bring it back, now on SQL Server.

The page shows the current step. The service is unavailable for a short time at the restart; reload the page once it is back.

When it refuses#

A restart is pending (Install SQL Server Express)#

SQL Server's setup will not install while Windows has a restart pending. Before it starts, FluxDeploy looks at what is waiting for a restart, using the same rule as the installer:

• Pending changes that cannot affect SQL Server (other software's updater leftovers such as Edge or OneDrive, temporary files) are ignored, and setup is told to skip only its restart check.
• Pending Windows servicing or Windows Update changes, or pending file changes under Windows, .NET, the Windows Installer, the ADK, SQL Server or FluxDeploy, stop the migration in seconds with a status like "SQL Server Express was not installed because this server has a restart pending ... Restart the server, then start the migration again. Nothing was changed." The message names the first blocking item and the software it belongs to. The same sentence is recorded as a DATABASE_MIGRATION_FAILED audit entry. Restart the server and start the migration again.

FluxDeploy also reads SQL Setup's exit code. A setup that refuses or fails is reported at once with its exit code and Microsoft's own one-line reason, instead of waiting ten minutes for a service that was never created. Exit code 3010 after a real install means Windows recommends a restart; the migration waits a short, bounded time (90 seconds) for the service and, if it does not appear, tells you to restart the server and try again.

A connection that cannot be made ends the migration as Failed with "Cannot connect to SQL Server". Nothing has been changed at that point: the SQLite database stays in place and FluxDeploy keeps running on it. The details are in the server log, not on the page.

Files left behind by an earlier SQL Server Express instance#

When SQL Server Express is removed (the uninstall wizard's "Also remove SQL Server Express" option, or Add or Remove Programs), SQL Server's own setup removes the instance but leaves the FluxDeploy database's data and log files (FluxDeploy.mdf, FluxDeploy_log.ldf) in the old data directory. The uninstall wizard's final page lists every file it left behind; they are never deleted for you.

A later migration to SQL Server Express creates a new instance with the same name and the same data directory. FluxDeploy checks that directory before creating the database: if those files are there, the new database is created in files of its own (FluxDeploy_<yyyyMMddHHmmss>.mdf and FluxDeploy_<yyyyMMddHHmmss>_log.ldf) and the old files are not touched. The final status and the DATABASE_MIGRATED audit entry say so: "Old database files from a previous SQL Server instance were left in <path>; they were not touched." Delete the old files yourself once you no longer need them.

If a database named FluxDeploy is already attached to the instance and holds tables, the migration refuses rather than copy into it: rename or remove that database, then start again.

When the database cannot be created#

A failure after SQL Server Express was installed names the cause in plain words, never an exception text, and always ends with what to do, for example "SQL Server Express was installed, but the FluxDeploy database could not be created: the disk or the transaction log is full. Nothing was changed in your existing database; retry after freeing some disk space." The causes recognised:

Anything else says which step stopped (creating the database, connecting, creating the tables, copying your data, switching over) and points at the server log. Every failure is recorded as a DATABASE_MIGRATION_FAILED audit entry with the same sentence.

If it fails part-way#

If the SQLite file cannot be set aside after the settings were switched, FluxDeploy puts appsettings.json back so the next start is on SQLite again, and reports the failure. Fix the cause (for example a backup tool holding the file) and run it again.

Going back#

The .bak files are your rollback: stop the service, restore them next to where they were, and set the provider back in appsettings.json. Anything written after the move lives only in SQL Server.

How it is tested#

The install smoke runs this flow for real on a clean machine: an Express migration end to end with a check that the service restarted itself, the refusals above, and an unreachable server.