Skip to content
SchemaSmith Learn SchemaSmith

The servers you don't write to

Your fleet query finds every tenant database. It also finds the read-only reporting copy someone restored last week, and the deploy dies trying to add a column to it. Three hundred tenants deployed, one failure, and the run reports red over a database nobody meant to change.

Fleets have targets you deliberately don’t write to: read-only copies, replicas, Availability Group secondaries. A deploy tool has to handle each one on purpose. This module covers the three settings that do it, and says plainly what the lab emulates: a database set READ_ONLY stands in for a replica, and a second SQL Server stands in for a secondary. Nothing fails over and nothing replicates. What you see is the part SchemaSmith owns: which targets a run touches, and what it does with each.

Skip it by name

Section titled “Skip it by name”
"SkipIfReadOnly": true

That’s a template setting. A database the template finds that turns out to be read-only is skipped, and the log says so:

[localhost,11433].[tenant_ro] is read-only; skipping template 'Main' (SkipIfReadOnly)

The writable tenants deploy, the run succeeds, and the summary records the read-only one as Skipped, not failed. Without the flag, SQL Server’s own error fails the run: Failed to update database "tenant_ro" because the database is read-only.

PostgreSQL gets the same treatment. A database whose sessions start read-only (default_transaction_read_only = on), which is how a lot of reporting copies are set up, is skipped with the same line.

Read from a replica, never install on it

Section titled “Read from a replica, never install on it”

Extraction needs SchemaSmith’s helper objects in the database it reads. A replica can’t take them, and trying would fail every write anyway. So against a read-only database SchemaTongs verifies the helpers instead of installing them. Kindled on the primary and replicated across, they’re there and extraction runs. Never kindled, and it stops before writing anything:

FAILED: The SchemaSmith helper objects are not present on this read-only database, and they cannot be created here. Kindle on the primary (any deploy or extraction against a writable copy does it) and let the change reach this replica, then re-run.

Exit 2, the code for a refused target, and the fix is in the message. On a real replica it’s one deploy to the primary, and the helpers arrive the way everything else does.

Primary, secondaries, or both

Section titled “Primary, secondaries, or both”

Some scripts belong on a secondary, such as a local cache table or a login mapped to the secondary’s own SID. Some must never run there, such as linked servers and Agent jobs that should exist once. Product-level script folders say which:

"ScriptFolders": [
  { "FolderPath": "Both", "QuenchSlot": "After", "ServerToQuench": "Both" },
  { "FolderPath": "Primary", "QuenchSlot": "After", "ServerToQuench": "Primary" },
  { "FolderPath": "Secondary", "QuenchSlot": "After", "ServerToQuench": "Secondary" }
]

and the settings name the secondaries, port included:

"SecondaryServers": ["localhost,11435"]
[localhost,11433].[master]    Quenching .\routing\Both\record.sql
[localhost,11435].[master]    Quenching .\routing\Both\record.sql
[localhost,11433].[master]    Quenching .\routing\Primary\record.sql
[localhost,11435].[master]    Quenching .\routing\Secondary\record.sql

In the lab each script writes its folder’s name into a log table on the server that ran it, so you can prove the routing by querying each server, not just by reading the log. Configure no secondaries and a Secondary folder runs nowhere, with a line saying why: Skipping folder 'Secondary' - ServerToQuench is Secondary and no SecondaryServers are configured. The template itself, where your tables live, runs on the primary only. A secondary’s databases get their schema from the Availability Group, not from the deploy.

Check yourself: Your nightly fleet deploy has started failing on one database: a read-only copy of a tenant that a support engineer restored for an investigation. Everything else deployed. What's the right fix: change the identification query to exclude it, or set SkipIfReadOnly?

SkipIfReadOnly, unless that copy shouldn’t be found at all. Excluding it by name fixes tonight and breaks the next time someone restores a copy with a different name. SkipIfReadOnly handles every read-only database the query finds, now and later, logs each one by name, and records it as Skipped in the summary, so the run is green and still tells you the copy exists.

If the copy shouldn’t be in the fleet at all, fix the identification query too, because a skipped database is still one the deploy found. The two answer different questions: which databases are in the fleet, and what to do with the ones you can’t write to.

A smith doesn’t hammer every piece in the shop just because it’s on the bench. Some are for show, some are finished and set aside, and some belong to a different job entirely. Knowing which is half the trade.

That’s this module. Skip the read-only ones by name. Read from the replicas without marking them. Send each script to the server it belongs on, and prove it went there.

Running a fleet with secondaries a deploy didn’t handle the way you expected? Email me at forgebarrett@schemasmith.com.

Next up: Module 9 · The registry and the fleet in one product, where the roster ships in the same package as the fleet it lists.

Until then, may every script land on the server it was meant for, and no other.

— Forge