Drive replication topology from the model

You’ve got a reporting replica. Somebody built it by hand once — a subset of the tables, just enough for the dashboards — and it was right the day they built it. Then the publisher grew a column. Then it grew a table. Nobody re-cut the subscriber DDL, because that’s a separate script nobody remembers to run. So the replica falls behind, quietly, one deploy at a time, until a report throws an error and you find out the hard way which tables never made the trip.

The subset itself isn’t the problem. The problem is that which tables replicate lives in someone’s head and a hand-maintained script, off to the side of the schema it’s copying. So it drifts. Let’s move that decision onto the model — right on the table — and let a deploy-time hook read it and rebuild the subscriber every single quench. This one’s SQL Server only, and I’ll show you why at the end.

Declare the intent on the table

You mark a table for replication the same way you’ve hung every other fact in this course — in Extensions, right on the definition:

{ "Name": "[Orders]", "...": "...", "Extensions": { "ReplicationEnabled": true, "ReplicationTarget": "Shop_Replica" } }

ReplicationEnabled is the switch the hook reads. ReplicationTarget is declarative intent — it names where this table’s copy belongs. Now, be honest with yourself about what that second key does today: the script in this recipe targets the single Shop_Replica database directly. It doesn’t yet route by ReplicationTarget — that key is the seam you’d extend when you grow to more than one subscriber, reading it to fan each table out to its named target. For this recipe, one replica, and the flag that matters is ReplicationEnabled.

The point is the same as every recipe before it: the decision now rides with the table, in the same file, the same commit, the same review. It can’t fall out of step with the schema, because it is part of the schema.

Provision from the whole graph

Marking tables is half of it. Something has to read those marks on every deploy and shape the subscriber to match. That’s a two-template package and one deploy-time hook.

Here’s the jig — Product.json, two templates in order:

{ "Name": "Shop", "MinimumVersion": "2017", "TemplateOrder": [ "ReplicaKindle", "Main" ], "Platform": "SqlServer" }

ReplicaKindle runs first. It kindles the empty subscriber — stamps SchemaSmith’s own procedures into Shop_Replica so the cross-database call in the next template has something to land on. Then Main deploys the publisher and fires the hook. Order matters: the subscriber needs SchemaSmith.TableQuench in place before anyone calls it, so kindle-first isn’t a preference, it’s the sequence.

The hook is an After-script named replicate [ALWAYS].sql, and that filename is the whole trick. An After-slot script is tracked — it runs once, gets recorded, and never runs again. That’s exactly wrong for a replica: you’d provision the subscriber on the first deploy and then watch it drift forever after. The [ALWAYS] marker in the filename overrides that — a script with [ALWAYS] in its name runs on every deploy, tracked or not. That one word is what turns this from a one-time copy into a standing mirror. Hold onto it; it’s the linchpin of everything below.

Here’s the body:

DECLARE @graph NVARCHAR(MAX) = N'{{TableSchema}}';
DECLARE @replicated NVARCHAR(MAX);
SELECT @replicated = N'[' + STRING_AGG(CAST(t.[value] AS NVARCHAR(MAX)), N',') + N']'
FROM OPENJSON(@graph) t
WHERE JSON_VALUE(t.[value], '$.Extensions.ReplicationEnabled') = 'true';
IF @replicated IS NOT NULL
  EXEC Shop_Replica.SchemaSmith.TableQuench @ProductName = N'{{ProductName}}',
      @TableDefinitions = @replicated, @WhatIf = 0, @DropUnknownIndexes = 0,
      @DropTablesRemovedFromProduct = 0, @UpdateFillFactor = 1;

Read it top to bottom. {{TableSchema}} hands the hook the Main template’s table set as a JSON array — every table this template defines. OPENJSON shreds it, the WHERE keeps only the ones flagged ReplicationEnabled = 'true', STRING_AGG rebuilds those back into an array, and a cross-database EXEC Shop_Replica.SchemaSmith.TableQuench quenches exactly that subset into the subscriber. The model is the input; the subscriber is the output; the hook is the wire between them.

Let’s run it and watch the two templates fire in order:

[localhost,11433].[Shop_Replica]   Kindling the forge
[localhost,11433].[Shop_Replica] Successfully Quenched
[localhost,11433].[Shop_Primary]   Kindling the forge
[localhost,11433].[Shop_Primary]     Quenching .\Package\Templates\Main\After Scripts\replicate [ALWAYS].sql
[localhost,11433].[Shop_Primary] Successfully Quenched

Publisher gets the full schema. Subscriber gets only what the model marked:

-- Shop_Primary user tables:        Customers, Inventory, Orders
-- Shop_Replica user tables:        Customers, Orders          (Inventory is ReplicationEnabled:false)
-- Shop_Replica foreign keys:       FK_Orders_Customers        (the FK-closed replicated set materialized)

Three tables on the publisher, two on the subscriber. Inventory isn’t marked, so it doesn’t make the trip. That filter did its job.

Mark the set, not a lone table

Look at that last line again — FK_Orders_Customers landed in the subscriber. That’s not luck, and it’s a rule worth branding on the inside of your skull: a replicated table drags its foreign-key parents along. Orders references Customers, so a copy of Orders with no Customers to point at is a broken table — the FK has nothing to resolve against.

So the subset has to be FK-closed: if you mark a child, you’d better mark its parents too, or the quench into the subscriber has a dangling reference and fails. In the lab, both Orders and Customers carry ReplicationEnabled: true, so the pair travels together and FK_Orders_Customers materializes intact on the other side. Mark the set the relationships require, not a lone table you happened to want. The graph doesn’t let you cheat that.

The model is the control surface

Now the payoff — and this is where [ALWAYS] earns its keep. You want Inventory in the replica now. You don’t touch the hook. You don’t touch Product.json. You don’t hand-edit any subscriber DDL. You flip one flag on the model:

{ "Name": "[Inventory]", "...": "...", "Extensions": { "ReplicationEnabled": true } }

Redeploy. Because the After-script is [ALWAYS], it re-reads the whole graph and re-filters — it doesn’t remember last time, it recomputes from scratch:

[localhost,11433].[Shop_Primary]     Quenching .\Package\Templates\Main\After Scripts\replicate [ALWAYS].sql

And the subscriber:

-- Shop_Replica user tables:        Customers, Inventory, Orders

Inventory joined the replica. No config edit, no separate script, no ceremony — the model flag is the control surface, and the hook reads it fresh every quench. That’s the whole design paying off: change the declaration, and the topology follows on the next deploy.

And because it recomputes every time, you can preview it safely. Run a WhatIfONLY deploy against a fresh reset and SchemaQuench lists every slot it would touch and executes nothing:

  WhatIfONLY: True
[localhost,11433].[Shop_Replica]   [WhatIf] Object scripts without unresolved tokens:
[localhost,11433].[Shop_Replica]   [WhatIf] Before database scripts:
[localhost,11433].[Shop_Replica]   [WhatIf] After table scripts:
[localhost,11433].[Shop_Replica]   [WhatIf] After database scripts:

After that run, Shop_Replica has no user tables. WhatIf previewed the [ALWAYS] hook right alongside the built-in slots and ran none of it — the subscriber stayed empty. Your custom provisioning hook is a first-class citizen of the dry run: safe to preview, exactly like everything the engine ships.

Why the package says 2017

One detail on that Product.json you shouldn’t skip past: "MinimumVersion": "2017". That’s not cosmetic. The hook rebuilds the filtered array with STRING_AGG, and STRING_AGG needs compatibility level 140 — SQL Server 2017 and up. So the package declares its own floor. Point it at an older server and SchemaSmith refuses before it runs a line, instead of letting the hook explode mid-deploy with a syntax error nobody expects. The package carries the requirement its own scripts create. Declare the floor where the code that needs it lives.

And that floor is also why I told you up front this one’s SQL Server only. The whole recipe hangs on that cross-database EXEC Shop_Replica.SchemaSmith.TableQuench — the hook reaches out of the publisher and into a second database on the same connection. SQL Server lets you address another database that way; PostgreSQL and MySQL/MariaDB can’t — a connection there is scoped to one database, so there’s no Shop_Replica.SchemaSmith.TableQuench to call. The pattern — declare intent on the model, read it with an [ALWAYS] hook — is universal. The one move that lands it in the subscriber isn’t. So this recipe stays on SQL Server, and that’s the honest reason.

One authoring gotcha while you’re in that script, and it’ll bite you if you don’t know it: never put {{TableSchema}} inside a single-line -- comment. Token substitution is plain text, and that token expands into multi-line JSON — so the second line of the expansion escapes right out of your comment and lands as live SQL. If you want to annotate what the token does, do it on a line of its own, well clear of the token itself.

Check yourself: You marked a new table for replication, but the deploy fails when it tries to provision the subscriber — or worse, the replica stopped updating after the very first deploy. Where do you look first in each case?

Two different culprits. If the deploy fails on the subscriber quench, suspect the FK-closed rule: you marked a child table but not the parent it references, so the copy has a dangling foreign key with nothing to resolve against. Mark the whole related set, not the lone table. If the replica provisioned once and then stopped updating, look at the filename — the hook has lost its [ALWAYS] marker (or was never named with it). An After-slot script is tracked and runs exactly once; only [ALWAYS] in the filename makes it re-read the model and re-provision on every deploy.

That’s the replication recipe, and it’s a course’s worth of ideas in one move. A reporting replica used to be a thing you maintained beside the schema — a hand-cut subset that fell behind the moment you weren’t watching. Now it’s derived. The tables that replicate are declared on the model, the hook reads that declaration on every quench, and the subscriber is shaped to match — FK-closed, previewable, always current. You don’t maintain the copy. You maintain the truth, and the copy follows.

That’s been the trade all along. Stop scripting every strike by hand; shape the metal so it holds its own edge. Put the fact where it’s true — on the table — and let a hook read it and do the work. Topology is just one more thing you can compute from metadata instead of babysitting by hand.

Got a replica you’ve been re-cutting by hand every time the publisher changes — a subset that’s already fallen behind? Email me at forgebarrett@schemasmith.com — I read every one. More’s coming from the forge.

Until then, may your model stay the master, and every copy hold true to the source that cast it.

— Forge

Mark this lesson complete