Release gate: confirm #1872's Azure replica attribution through the SHIPPED collector, plus the three axes the validation did not vary

Spun off from #1872 so the caveats recorded there are not buried when it closes.

#1872 flipped Query Store replica attribution ON for Azure SQL Database on live evidence from two tiers. The evidence is strong — but it was gathered by composing the collector's SQL and running it by hand, not by running the shipped collector. Two gaps follow from that, plus three configuration axes the validation deliberately did not vary.

Gap 1: the shipped code path has not hit a live Azure database

The Hyperscale run took BuildPayloadBody from origin/dev, composed it with hasReplicaAttribution = true, diffed the text against the already-verified 55-column Azure file to confirm only the three intended differences, and executed that. That is a very close proxy — but it is not Lite or the Darling service connecting to Azure, running the collector, and storing the result.

What the by-hand run therefore cannot speak for:

the value surviving the reader at ordinal 53 and the positional bulk write into query_store_stats on both stores;
the grids actually rendering Primary where they used to render blank;
the read-side dedup and rollup behaving on real Azure rows (analysis says this is a no-op — every key already partitions on replica_role, so an Azure server moves from one NULL group to one Primary group — but "analysis says no-op" is exactly the class of claim a smoke test exists to check).

Do this as part of the next release's Azure smoke, alongside whatever else that pass covers, rather than provisioning a database for it alone.

Gap 2 through 4: the axes #1872 did not vary

Carried forward verbatim from that issue's caveats, so they survive it closing:

Hyperscale with 0 HA replicas. Provisioned with --ha-replicas 0 for minimum footprint. Binding is a schema property and the view returned its 4 role rows anyway, so this is very unlikely to matter — but a Hyperscale database that actually HAS HA or named replicas was not tested, and it is the one configuration axis that was not varied.
Serverless Hyperscale only. Provisioned Hyperscale (HS_Gen5_2) untested. Same engine, byte-identical version banner, so no difference is expected.
readonly_reason & 8 (readable secondary) unexercised on either tier. Needs geo-replication or read scale-out to reach. Unchanged since Release gate: live Azure SQL DB smoke for the #1833 + #1836 collector fixes (includes two unverified edges) #1848 — this one is about the eligibility gate rather than the attribution, and it predates Query Store replica attribution on Azure SQL DB: General Purpose probe now passes; needs one Hyperscale probe to lift the gate #1872.

Items 1 and 2 would both be answered incidentally by any future Azure work that happens to run on a differently-shaped Hyperscale database; neither justifies a spin-up on its own.

What "done" looks like

One live Azure SQL Database collection cycle through the real app — GP is fine, Hyperscale better — showing replica_role = 'Primary' stored and displayed, plus a note on whichever of the three axes above the target happened to cover. If the attribution turns out NOT to bind on some configuration, the fix is to revert the gate in QueryStoreCollector.BuildPayloadBody to productVersion >= 16 && !context.Target.IsAzureSqlDb and record the finding in the comment — the test pin at Lite.Tests/QueryStoreCollectorDefinitionTests.cs (BuildQuery_Azure_AttributesReplicaRole_ProvenLiveOnBothServiceTiers) is written to be re-flipped, and says so.

Related: #1848 (the GP validation), #1872 (the Hyperscale validation and the flip).

🤖 Generated with Claude Code

Gap 1 closed: the shipped collector has now hit a live Azure database

One live Azure SQL Database, collected by the real Darling service (not composed SQL), stored into a
real PostgreSQL + TimescaleDB store, verified in the store. Zero product changes.

Target: pm1887e2esrv.database.windows.net / pm1887db, East US 2, GP_S_Gen5 (General Purpose,
Serverless, Gen5, 1 vCore max / 0.5 min, 32 GB, auto-pause 60 min, LRS backup, non zone-redundant).
Engine Microsoft SQL Azure (RTM) - 12.0.2000.8, EngineEdition 5.

Code under test: origin/dev @ 4adf3236, built Release, 0 warnings / 0 errors. origin/dev advanced
to 71438f3d during the run; the diff is CI workflows, Darling.Tests, CHANGELOG and README only — no
product code — so this run still speaks for current dev.

Store: throwaway PostgreSQL 18.4 + TimescaleDB (the repo's pg-runtime.zip) on 127.0.0.1:5911,
bring-your-own connectionString, fresh database. Migrated to schema v41 on first start.

Seed: 3 stored procedures + 1 table (20,000 rows); a 100-execution burst then one execution of each
procedure every 20s so procedure_stats' 10-minute recency filter always had fresh rows.

1. The service connected and ran the collectors

info: Loaded configuration from ...\darling.json: 1 server(s)
info: Postgres store ready (schema v41, 41 migration(s) applied)
info: TimescaleDB: 38/38 collector table(s) are hypertables
info: TimescaleDB: 22/22 continuous aggregate(s) ready
info: PerformanceMonitor Darling collection loop started
info: [AZURE1887] Connected (major 12, edition AzureSqlDb, server_id 410699517)
info: [AZURE1887] Created and started deadlock XE session (database-scoped)
info: [AZURE1887] Created and started blocked process XE session (database-scoped)

Per-cycle collector lines (3 query_store cycles at the shipped 5-minute cadence, 15 procedure_stats
at 1 minute):

        [AZURE1887] query_store => 4 rows (sql:751ms, pg:29ms)
        [AZURE1887] query_store => 4 rows (sql:909ms, pg:25ms)
        [AZURE1887] query_store => 8 rows (sql:1125ms, pg:14ms)
        [AZURE1887] procedure_stats => 3 rows (sql:102ms, pg:36ms)
        [AZURE1887] procedure_stats => 3 rows (sql:98ms, pg:3ms)
        [AZURE1887] procedure_stats => 3 rows (sql:93ms, pg:4ms)

The only warn/error/fail line in the entire service log was the expected plaintext-password notice
(--encrypt-password not used for a scratch run). PostgreSQL's own log had zero ERROR/FATAL/PANIC.

2. `collection_log` — both collectors SUCCESS, nothing else anything but SUCCESS

                server_name                 | collector_name  | status  | runs | rows_total |         first_run          |          last_run
--------------------------------------------+-----------------+---------+------+------------+----------------------------+----------------------------
 pm1887e2esrv.database.windows.net:pm1887db | procedure_stats | SUCCESS |   15 |         45 | 2026-07-31 04:24:00.76802  | 2026-07-31 04:38:05.189321
 pm1887e2esrv.database.windows.net:pm1887db | query_store     | SUCCESS |    3 |         16 | 2026-07-31 04:25:01.432199 | 2026-07-31 04:35:05.044239

Every non-SUCCESS row, whole store, all collectors:

 collector_name | status | error_message | runs
----------------+--------+---------------+------
(0 rows)

3. THE #1872 FLIP THROUGH THE SHIPPED PATH — `replica_role = 'Primary'`, stored

 replica_role | rows_with_value | null_rows
--------------+-----------------+-----------
 Primary      |              16 |         0

16 of 16 rows attributed, zero NULL placeholders. This is the value surviving QueryStoreCollector's
reader and the positional bulk write into collect.query_store_stats — the step #1848/#1872 could
not speak for.

4. `plan_type` and the #1841 interval identity — populated

   plan_type   | rows_with_value
---------------+-----------------
 Compiled Plan |              16

 total_rows | interval_id_populated | interval_start_populated | distinct_interval_ids | min_interval_start  | max_interval_start
------------+-----------------------+--------------------------+-----------------------+---------------------+---------------------
         16 |                    16 |                       16 |                     1 | 2026-07-31 04:00:00 | 2026-07-31 04:00:00

5. No positional binding / order mismatch

ReadRowsAsync reads compatibility_level at 49, query_plan_text at 50, replica_role at 52,
runtime_stats_interval_id at 53, interval_start_time_utc at 54. Checking all five together is a
stronger test than checking the flipped column alone — a shifted ordinal would corrupt its neighbours:

          module_name          | plan_id | ord49_compat | ord50_plan_xml_prefix  | ord50_plan_len | ord52_replica_role | ord53_interval_id | ord54_interval_start
-------------------------------+---------+--------------+------------------------+----------------+--------------------+-------------------+----------------------
 dbo.pm_get_orders_by_customer |       2 |          170 | <ShowPlanXML xmlns="ht |           7785 | Primary            |                 1 | 2026-07-31 04:00:00
 dbo.pm_order_summary          |       3 |          170 | <ShowPlanXML xmlns="ht |           7919 | Primary            |                 1 | 2026-07-31 04:00:00
 dbo.pm_recent_orders          |       4 |          170 | <ShowPlanXML xmlns="ht |           4526 | Primary            |                 1 | 2026-07-31 04:00:00
 Adhoc                         |       6 |          170 | <ShowPlanXML xmlns="ht |         158091 | Primary            |                 1 | 2026-07-31 04:00:00

Cross-contamination guard — every count is the number of values landing in the wrong domain:

 replica_role_outside_known_replica_names | plan_type_holding_a_replica_name | compat_level_out_of_range | plan_text_not_showplan_xml
------------------------------------------+----------------------------------+---------------------------+----------------------------
                                        0 |                                0 |                         0 |                          0

(The rows showing a NULL query_plan_text are by design — the collector's
ROW_NUMBER() ... = 1 guard stores the plan XML once per plan_id per cycle.)

6. `procedure_stats` for the seeded procedures

 database_name | schema_name |        object_name        | object_type | rows_stored | max_execution_count
---------------+-------------+---------------------------+-------------+-------------+---------------------
 pm1887db      | dbo         | pm_get_orders_by_customer | PROCEDURE   |          15 |                 153
 pm1887db      | dbo         | pm_order_summary          | PROCEDURE   |          15 |                 103
 pm1887db      | dbo         | pm_recent_orders          | PROCEDURE   |          15 |                  78

All three seeded procedures collected on every cycle, with execution counts tracking the workload
(+3 per minute, one execution of each per 20s round).

7. The read-side dedup and rollup — checked, not assumed

The issue flagged "analysis says no-op" as exactly the class of claim a smoke test exists to check. It
holds, and here is the actual shape rather than the assertion:

query_store_stats_hourly (and the corrected/daily tiers above it) do not group on replica_role at
all, so the flip cannot change their grouping.
query_store_stats_interval_hourly — the dedup CAGG — does. After a refresh it carries the flipped
value through, one group:

 replica_role | cagg_rows | distinct_intervals
--------------+-----------+--------------------
 Primary      |         5 |                  1

The viewer grid read (ViewerDataService.QueryStoreTopSql, load-bearing core run verbatim) renders
Primary where it used to render blank:

 database_name | query_id | plan_id | replica_role |          module_name          | total_executions |   plan_type   | compatibility_level
---------------+----------+---------+--------------+-------------------------------+------------------+---------------+---------------------
 pm1887db      |       14 |       3 | Primary      | dbo.pm_order_summary          |               86 | Compiled Plan |                 170
 pm1887db      |        7 |       1 | Primary      | Adhoc                         |                1 | Compiled Plan |                 170
 pm1887db      |       15 |       4 | Primary      | dbo.pm_recent_orders          |                8 | Compiled Plan |                 170
 pm1887db      |       12 |       2 | Primary      | dbo.pm_get_orders_by_customer |                8 | Compiled Plan |                 170
 pm1887db      |       38 |       6 | Primary      | Adhoc                         |                2 | Compiled Plan |                 170

And the grid's row count is genuinely unchanged by the flip:

 grid_rows_grouping_on_replica_role | grid_rows_ignoring_replica_role | grid_rows_if_role_were_still_null | flip_is_a_noop_for_the_grid
------------------------------------+---------------------------------+-----------------------------------+-----------------------------
                                  5 |                               5 |                                 5 | t

The attribution binds correctly on live Azure through the shipped path. No reason to revert the gate.

One defect found, unrelated to the flip: #1907

The total_executions column above is wrong on two of those five rows (8 where the truth is 94). That is
not the replica flip — it is sys.query_store_runtime_stats returning the flushed and still-in-memory
slices of one runtime_stats_id as two rows that share the entire read-side dedup key including
collection_time, so ROW_NUMBER/last() keeps an arbitrary one. Confirmed additive three ways, including
the product's own procedure_stats independently reporting exactly the slice sums (144/94/69).

Filed as #1907 with full evidence. sys.query_store_replicas has one row per replica_group_id, so
the flip's LEFT JOIN cannot fan out and the collision is identical with replica_role NULL. Only a
sustained end-to-end run surfaces it, which is precisely the gap this issue existed to close.

What this run retires, and what it does not

Retired — Gap 1, in full:

the value surviving the reader and the positional bulk write into query_store_stats — verified, plus
its four neighbouring ordinals;
the read-side dedup and rollup behaving on real Azure rows — verified empirically, not by analysis;
the grid rendering Primary where it used to render blank — verified through the viewer's own read SQL
against the real store.

One caveat on the third: I ran ViewerDataService.QueryStoreTopSql's load-bearing core against the store
rather than launching the WPF viewer, so this is the read path proven, not pixels on screen.

Still open — Gaps 2, 3, 4, unchanged. This run varied none of them:

Hyperscale with HA / named replicas — this was General Purpose. Not covered.
Provisioned (non-serverless) Hyperscale — this was serverless GP. Not covered.
readonly_reason & 8 (readable secondary) — needs geo-replication or read scale-out; this database
had readonly_reason = 0 throughout. Not covered.

Leaving this issue open for those three.

Cost and teardown

Database created 2026-07-31T04:16:49Z, resource group deleted ~04:41Z — roughly 25 minutes of
database lifetime. GP_S_Gen5_1 serverless at 1 vCore max bills about $0.12/vCore-hour, so compute is
well under $0.10; storage and LRS backup for under half an hour are negligible. Order-of-magnitude
estimate, not a metered figure.

Resources present immediately before deletion:

pm1887e2esrv           Microsoft.Sql/servers
pm1887e2esrv/master    Microsoft.Sql/servers/databases
pm1887e2esrv/pm1887db  Microsoft.Sql/servers/databases

$ az group delete --name pm1887e2e --yes
GROUP_DELETE_EXIT=0

$ az group exists --name pm1887e2e
false

The local scratch rig was torn down too: service stopped, PostgreSQL stopped, cluster and extracted
runtime deleted, throwaway worktree removed. The generated SQL admin password lived only in the scratch
directory and was never committed.

Note for whoever reads the subscription: resource groups pm1887hs and pm1886mi were present at
teardown time and are not from this run (created 04:32Z, mid-run) — they look like concurrent
Hyperscale/MI validations. I deliberately left them alone.

🤖 Generated with Claude Code

Gaps 2, 3 and 4 retired: provisioned Hyperscale WITH an HA replica, and `readonly_reason & 8` exercised on a live readable secondary

One provisioned (non-serverless) Hyperscale database with an HA replica covers all three unvaried axes at
once, because the HA replica is the readable secondary. Zero product changes; no repo modifications;
no PR.

Target: pm1887hs.database.windows.net / database pmhsdb, East US 2, HS_Gen5_2
(Hyperscale, Provisioned, Gen5, 2 vCore, 1 HA replica, read scale-out Enabled, LRS backup,
non-zone-redundant). Firewall was a single rule for this box's IP only.

src|version_banner|engine_edition|product_major|product_version|edition
identity|Microsoft SQL Azure (RTM) - 12.0.2000.8|5|12|12.0.2000.8|SQL Azure

src|database_name|service_objective|db_edition|updateability|status
database|pmhsdb|HS_Gen5_2|Hyperscale|READ_WRITE|ONLINE

Byte-identical version banner to the GP database (#1848) and the serverless Hyperscale database (#1872).

Seed: the same shape as both prior runs — 3 stored procedures + 1 table (20,000 rows), 100 executions,
then sys.sp_query_store_flush_db:

src|queries|plans|runtime_stats_rows|intervals
query store contents after flush|5|5|5|1

0. How the SQL was obtained — no hand transcription this time

#1872's decisive test composed BuildPayloadBody by hand and diffed the text to prove it matched. This run
removes that step: a throwaway console harness (outside the repo, in a detached worktree at origin/dev
@ 110dfca8) calls the shipped public QueryStoreCollector.BuildQuery() with
Target.IsAzureSqlDb = true and writes its .Text straight to a .sql file. What ran on Azure is the
method's own output, character for character, with one line prepended — DECLARE @cutoff_time datetime2(7),
because the host binds that as a command parameter and sqlcmd needs it declared in-batch.

origin/dev advanced to b7d936a1 during the run. PerformanceMonitor.Collectors/QueryStoreCollector.cs
is byte-identical between the two (git diff 110dfca8 origin/dev -- ... is empty) and the only .cs
changes at all are in Darling.Tests, so this run still speaks for current dev.

Three shapes were generated and all three came back with attribution ON:

azure_darling_probe12.sql:   chars=3917 params=1 hasAttribution=True hasJoin=True hasGate=True
azure_lite_probe12.sql:      chars=3764 params=1 hasAttribution=True hasJoin=True hasGate=True
azure_darling_probenull.sql: chars=3917 params=1 hasAttribution=True hasJoin=True hasGate=True

The third is the probe-failure case (EnumerationProbeResult null, so DefaultProductVersion 13), which
confirms in generated text what BuildQuery_Azure_AttributesReplicaRole_EvenWhenTheVersionProbeFails pins:
the Azure arm of the gate is an OR, so attribution does not depend on the version probe succeeding.

1. Axis "provisioned (non-serverless) Hyperscale" — this is that database

{
  "currentServiceObjective": "HS_Gen5_2",
  "requestedServiceObjective": "HS_Gen5_2",
  "sku": { "name": "HS_Gen5", "tier": "Hyperscale", "family": "Gen5", "capacity": 2 },
  "haReplicas": 1,
  "readScale": "Enabled",
  "minCapacity": null,
  "autoPauseDelay": null,
  "zoneRedundant": false,
  "backupRedundancy": "Local",
  "status": "Online",
  "kind": "v12.0,user,vcore,hyperscale"
}

HS_Gen5_2, not #1872's HS_S_Gen5_2 — the missing S is the whole axis. minCapacity and
autoPauseDelay are both null, which is what provisioned looks like: there is no auto-pause to fail to
take (the thing #1872 had to warn about), it simply bills continuously by design.

2. Axis "Hyperscale WITH HA replicas" — the replica is real and live

sys.dm_database_replica_states on the primary returns two rows, not one:

src|replica_id|group_database_id|is_primary_replica|synchronization_state_desc|database_state_desc
sys.dm_database_replica_states|9eee1f9f-20a6-01dd-0000-482fd9a27f64|00000000-0000-0000-0000-000000000000|0|SYNCHRONIZING|NULL
sys.dm_database_replica_states|9d8218c4-20a6-01dd-0000-482fd9a27f64|00000000-0000-0000-0000-000000000000|1|SYNCHRONIZED|ONLINE

3. The #1836 flip criterion, re-run on the HA-bearing database

probe|query_store_replicas_object_id|replica_group_id_col_length
the #1836 flip criterion|-660|8

Identical to GP (#1848) and serverless Hyperscale (#1872). Same object id, same column length.

4. The decisive question — does the view hold live per-replica rows now?

No. With a live HA replica attached, sys.query_store_replicas holds exactly the same four static role
rows, and nothing else.

src|ordinal|column_name|data_type|max_length
sys.query_store_replicas columns|1|replica_group_id|bigint|8
sys.query_store_replicas columns|2|role_type|smallint|2
sys.query_store_replicas columns|3|replica_name|nvarchar|1288

src|replica_group_id|role_type|replica_name
sys.query_store_replicas contents|1|1|Primary
sys.query_store_replicas contents|2|2|Secondary
sys.query_store_replicas contents|3|3|Geo Secondary
sys.query_store_replicas contents|4|4|Geo HA Secondary

src|replica_rows
sys.query_store_replicas row count|4

That settles #1872's caveat 1 in the direction it guessed: the view is a static enumeration of replica
roles, not instances, so replica count does not change it. Real runtime-stats rows still all carry
group 1:

src|replica_group_id|rows_with_it
replica_group_id values in sys.query_store_runtime_stats|1|5

src|plan_id|replica_group_id|replica_role_would_be
the collector LEFT JOIN|1|1|Primary
the collector LEFT JOIN|2|1|Primary
the collector LEFT JOIN|3|1|Primary
the collector LEFT JOIN|4|1|Primary
the collector LEFT JOIN|5|1|Primary

5. The full shipped payload on the PRIMARY — 55/55 bind, both host shapes

Darling shape (CapturePlanXml = true):

COLUMNS BOUND: 55
--- any error text? ---
(no lines above = no errors)
--- row count line ---
(5 rows affected)

The last five reader ordinals, from the returned header — replica_role lands where the reader expects it
(53rd of 55 one-based, the reader's 0-based 52):

48	force_failure_count
49	last_force_failure_reason
50	compatibility_level
51	query_plan_text
52	query_plan_hash
53	replica_role
54	runtime_stats_interval_id
55	interval_start_time_utc

Sample rows (display width truncated so plan XML does not wrap; the SQL is unchanged):

7|2|Regular|...|dbo.pm_get_orders_by_customer|...|Compiled Plan|NONE|0|0|NONE|170|<ShowPlanXML xmlns="http://schemas.micro|0x69547F59F1FE2F52|Primary|1|2026-07-31 04:00:00.0000000
9|4|Regular|...|dbo.pm_order_totals|...|Compiled Plan|NONE|0|0|NONE|170|<ShowPlanXML xmlns="http://schemas.micro|0x28AFCE8CDDCE0C2A|Primary|1|2026-07-31 04:00:00.0000000
4|1|Regular|...|Adhoc|...|Compiled Plan|NONE|0|0|NONE|170|<ShowPlanXML xmlns="http://schemas.micro|0xC5D8EE2A022C8F1A|Primary|1|2026-07-31 04:00:00.0000000

Lite shape (CapturePlanXml = false) — the shape #1872 did not run:

LITE SHAPE COLUMNS BOUND: 55
(no errors)
(6 rows affected)

…with the nvarchar(1) placeholder where the plan text would be, and the attribution still populated:

...|Compiled Plan|NONE|0|0|NONE|170|NULL|0xC5D8EE2A022C8F1A|Primary|1|2026-07-31 04:00:00.0000000

6. Axis "`readonly_reason & 8`" — exercised at last, on a real readable secondary

Connected with ApplicationIntent=ReadOnly (sqlcmd -K ReadOnly), which read scale-out routes to the HA
replica. Updateability proves which replica the session landed on.

On the secondary:

src|database_name|updateability|transaction_isolation
which replica am I on|pmhsdb|READ_ONLY|1

src|desired_state|desired_state_desc|actual_state|actual_state_desc|readonly_reason|interval_length_minutes|query_capture_mode_desc|actual_state_additional_info
gate inputs|4|READ_CAPTURE_SECONDARY|4|READ_CAPTURE_SECONDARY|8|60|AUTO|

src|readonly_reason|bit_1_database_readonly|bit_2_database_single_user|bit_4_database_emergency|bit_8_secondary_replica|bit_16_qs_size_limit|bit_32_db_size_limit|bit_64_qs_internal|bit_128_qs_free_space
readonly_reason bit decomposition|8|0|0|0|8|0|0|0|0

src|gate_verdict|actual_state_in_1_2_4|readonly_reason_bit8_clear
collector eligibility gate verdict|SKIP - gate RETURNs, no result set|1|0

Bit 8 is set, and it is the only term declining the database. Note actual_state = 4
(READ_CAPTURE_SECONDARY) — which is in the gate's admitted set (1, 2, 4), so
actual_state_in_1_2_4 = 1. The state clause passes. Only readonly_reason & 8 = 0 fails. The comment in
QueryStoreCollector reasons about (state 4, reason 0) — a 2025 secondary capturing real local workload,
which still collects. This run adds the live (state 4, reason 8) case, and the gate handles it correctly.

The shipped query text itself, run on that same secondary connection:

=== SHIPPED PAYLOAD, ApplicationIntent=ReadOnly (the HA readable secondary) ===
exit: 0 ; output bytes: 0

Zero bytes. Not an error, not an empty grid — the gate's RETURN fired and the batch produced no result
set at all, which is exactly what the reader is designed to see as zero rows.

The same predicate on the primary, for contrast:

src|gate_verdict|updateability
collector eligibility gate verdict on PRIMARY|PASS - collector runs the payload|READ_WRITE

src|desired_state|desired_state_desc|actual_state|actual_state_desc|readonly_reason|...
gate inputs|2|READ_WRITE|2|READ_WRITE|0|...

SKIP on the secondary, PASS on the primary, from the same file over the same credentials — the only
difference is the application intent.

7. Counterfactual: the bit-8 term is load-bearing here, not decorative

Since actual_state = 4 passes the state clause on its own, it is worth showing what dropping the bit-8
term would actually cost. Run on the secondary (a probe, not a proposal):

src|verdict
gate WITHOUT the bit-8 term (counterfactual)|WOULD COLLECT

src|plan_id|replica_group_id|replica_role_would_be|count_executions|last_execution_time
rows the secondary would have re-collected|1|1|Primary|1|2026-07-31 04:41:54.2200000 +00:00
rows the secondary would have re-collected|2|1|Primary|100|2026-07-31 04:41:56.2270000 +00:00
rows the secondary would have re-collected|3|1|Primary|100|2026-07-31 04:41:56.2370000 +00:00
rows the secondary would have re-collected|4|1|Primary|10|2026-07-31 04:41:56.0970000 +00:00
rows the secondary would have re-collected|5|1|Primary|80|2026-07-31 04:41:56.2570000 +00:00

Same five plan ids, the primary's execution counts, all attributed Primary. That is #1558's rationale
reproduced live on Azure: without the bit-8 term the secondary hands back duplicate, lagged primary data
under a second server identity.

8. New finding: on Hyperscale read scale-out, secondary workload never reaches the Query Store

Worth recording because it bounds what the flipped column can ever say on Azure. I ran a marked workload on
the readable secondary, then flushed and inspected the primary's Query Store:

-- 30 executions on the secondary of:  SELECT /* PM1887_SECONDARY_MARKER */ ...

src|replica_group_id|replica_name|rows_with_it
replica_group_id distribution on PRIMARY after secondary workload|1|Primary|6

src|marker_queries
did the secondary marker reach the primary Query Store|0

src|replica_rows
replica view row count on primary, after|4

The secondary's work is not captured anywhere: not locally (the replica cannot persist, and
sp_query_store_flush_db on it produced nothing), and not streamed into the primary. So on Azure SQL
Database Hyperscale, replica_role will always read Primary — the docs' "Query Store for secondary
replicas is unavailable on Hyperscale" is confirmed from the data side.

That is not a defect in the flip and does not argue for reverting it. It means the recovered column is
honest but constant on Azure: it says "the server attributed this row to the primary", which is true, and
it replaces a bare NULL that said nothing. Nobody should expect Secondary rows from an Azure SQL DB
target.

What this run retires

Axis (issue body)	Status
2. Hyperscale with 0 HA replicas only	Retired — 1 HA replica, live and SYNCHRONIZING; probe, view and 55-column payload all unchanged
3. Serverless Hyperscale only	Retired — provisioned `HS_Gen5_2`; identical banner and identical results
4. `readonly_reason & 8` unexercised	Retired — live readable secondary reports `readonly_reason = 8`; gate SKIPs there and PASSes on the primary, proven with the shipped query text

Nothing regressed and nothing argues for the revert path in the issue body: hasReplicaAttribution stays
as #1884 shipped it, and the test pin
BuildQuery_Azure_AttributesReplicaRole_ProvenLiveOnBothServiceTiers stays as written. The only claim I
would now add to it is the one in section 8 — Azure will only ever return Primary.

Not in this run's scope: the shipped-collector end-to-end smoke (Gap 1), which is the companion
General Purpose run posted above.

Cost and teardown

Resource group created 2026-07-31T04:31:12Z; database created 04:39:57Z, resource group deletion
returned 04:47:59Z — about 8 minutes of database lifetime, about 17 minutes of resource-group
lifetime. Provisioned Hyperscale has no auto-pause, so it billed continuously for all 8 minutes, and the HA
replica bills as its own compute.

East US 2 retail rate, pulled live from the Azure retail prices API this time rather than estimated:
SQL Database SingleDB/Elastic Pool Hyperscale - Compute Gen5 = $0.18266 per vCore-hour. Two vCores for
the primary plus two for the HA replica is ~$0.731/hour, so ~8 minutes of compute is about $0.10;
Hyperscale storage and LRS backup for eight minutes are rounding error. That is computed from the published
rate, not read off an invoice, but it is a metered rate rather than a guess.

Resources present immediately before deletion:

pm1887hs           Microsoft.Sql/servers
pm1887hs/master    Microsoft.Sql/servers/databases
pm1887hs/pmhsdb    Microsoft.Sql/servers/databases

$ az group delete --name pm1887hs --yes
exit: 0

$ az group exists --name pm1887hs
false

$ az resource list --query "[?contains(name,'pm1887') || contains(resourceGroup,'pm1887')]" -o tsv
(no output)

$ az group list --query "[].name" -o tsv
Compute
NetworkWatcherRG
Networking
pm1886mi

Compute, NetworkWatcherRG and Networking are pre-existing and untouched. pm1886mi is not from
this run — it belongs to the concurrent #1886 Managed Instance validation, and I left it alone. The
groups from both prior runs were re-verified gone (az group exists → false for pm-1872-hyperscale
and pm1872hs). The generated SQL admin password lived only in the scratch directory, was deleted at
teardown, and was never committed; the throwaway worktree and harness were removed.

🤖 Generated with Claude Code

Closing: all four items covered by two live Azure runs, both on 2026-07-31

Item	Run	Result
Gap 1 — shipped collector path against a live Azure database	GP_S_Gen5 serverless, `pm1887e2esrv/pm1887db`, East US 2	The real Darling service collected and stored `replica_role = 'Primary'`, 16 of 16 rows, 0 NULLs, into PostgreSQL + TimescaleDB. Reader ordinals 49-54 all verified together, so a shifted ordinal could not hide. `collection_log` all SUCCESS, zero non-SUCCESS rows store-wide.
Gap 2 — Hyperscale with HA / named replicas	HS_Gen5_2 provisioned, `pm1887hs/pmhsdb`, East US 2	1 HA replica, live (`sys.dm_database_replica_states` returns primary SYNCHRONIZED + secondary SYNCHRONIZING). Probe, view contents and the 55-column payload are all identical to the 0-replica run.
Gap 3 — provisioned (non-serverless) Hyperscale	same HS run	`HS_Gen5_2`, `minCapacity` and `autoPauseDelay` both null. Same version banner, same results as serverless.
Gap 4 — `readonly_reason & 8` on a readable secondary	same HS run, `ApplicationIntent=ReadOnly`	The secondary reports `actual_state = 4 (READ_CAPTURE_SECONDARY)`, `readonly_reason = 8`. The shipped query text returns zero bytes there (the gate's `RETURN` fires) and PASSes on the primary.

Nothing failed, and nothing pointed at the revert path in the issue body. hasReplicaAttribution stays as
#1884 shipped it and the BuildQuery_Azure_AttributesReplicaRole_ProvenLiveOnBothServiceTiers pin stays as
written.

Three things worth carrying forward, none of them blocking:

replica_role will always read Primary on Azure SQL Database. A marked workload run on the
Hyperscale readable secondary reached neither its own Query Store nor the primary's (0 marker queries,
still only replica_group_id = 1). The column is honest but constant there — it now says "the server
attributed this to the primary" instead of a bare NULL, and no blending occurs to disambiguate.
sys.query_store_replicas is a static enumeration of four replica roles, not instances. Adding a
live HA replica did not change its contents. That is why the 0-HA-replica evidence in Query Store replica attribution on Azure SQL DB: General Purpose probe now passes; needs one Hyperscale probe to lift the gate #1872 held.
Grid rendering was proven through the viewer's read SQL, not through pixels. The GP run executed
ViewerDataService.QueryStoreTopSql's load-bearing core against the real store rather than launching
the WPF viewer. The read path is verified; the visual is not, and it rides along with any normal
viewer pass.

Both resource groups were torn down and verified gone (az group exists -> false), with no pm1887
resource left anywhere in the subscription. Combined cost of the two runs is roughly $0.20, dominated by
~8 minutes of provisioned Hyperscale compute at the live retail rate of $0.18266/vCore-hour x 2 vCores x 2
(primary + HA replica).

Closing. Related: #1848 (GP probe), #1872 (Hyperscale probe and the evidence for the flip), #1884 (the
flip), #1886 (the MI basis, validated separately).

🤖 Generated with Claude Code

Merge pull request #1910 from erikdarlingdata/fix/1887-azure-pin-comment

Record that Azure attributes a constant replica_role (erikdarlingdata…

Navigation Menu

Repository navigation

Release gate: confirm #1872's Azure replica attribution through the SHIPPED collector, plus the three axes the validation did not vary #1887

Gap 1: the shipped code path has not hit a live Azure database

Gap 2 through 4: the axes #1872 did not vary

What "done" looks like

Gap 1 closed: the shipped collector has now hit a live Azure database

1. The service connected and ran the collectors

2. `collection_log` — both collectors SUCCESS, nothing else anything but SUCCESS

3. THE #1872 FLIP THROUGH THE SHIPPED PATH — `replica_role = 'Primary'`, stored

4. `plan_type` and the #1841 interval identity — populated

5. No positional binding / order mismatch

6. `procedure_stats` for the seeded procedures

7. The read-side dedup and rollup — checked, not assumed

One defect found, unrelated to the flip: #1907

What this run retires, and what it does not

Cost and teardown

Gaps 2, 3 and 4 retired: provisioned Hyperscale WITH an HA replica, and `readonly_reason & 8` exercised on a live readable secondary

0. How the SQL was obtained — no hand transcription this time

1. Axis "provisioned (non-serverless) Hyperscale" — this is that database

2. Axis "Hyperscale WITH HA replicas" — the replica is real and live

3. The #1836 flip criterion, re-run on the HA-bearing database

4. The decisive question — does the view hold live per-replica rows now?

5. The full shipped payload on the PRIMARY — 55/55 bind, both host shapes

6. Axis "`readonly_reason & 8`" — exercised at last, on a real readable secondary

7. Counterfactual: the bit-8 term is load-bearing here, not decorative

8. New finding: on Hyperscale read scale-out, secondary workload never reaches the Query Store

What this run retires

Cost and teardown

Closing: all four items covered by two live Azure runs, both on 2026-07-31

Metadata

Assignees

Labels

Projects

Milestone

Relationships

Development

Release gate: confirm #1872's Azure replica attribution through the SHIPPED collector, plus the three axes the validation did not vary #1887

Description

Gap 1: the shipped code path has not hit a live Azure database

Gap 2 through 4: the axes #1872 did not vary

What "done" looks like

Activity

erikdarlingdata commented on Jul 31, 2026

Gap 1 closed: the shipped collector has now hit a live Azure database

1. The service connected and ran the collectors

2. collection_log — both collectors SUCCESS, nothing else anything but SUCCESS

3. THE #1872 FLIP THROUGH THE SHIPPED PATH — replica_role = 'Primary', stored

4. plan_type and the #1841 interval identity — populated

5. No positional binding / order mismatch

6. procedure_stats for the seeded procedures

7. The read-side dedup and rollup — checked, not assumed

One defect found, unrelated to the flip: #1907

What this run retires, and what it does not

Cost and teardown

erikdarlingdata commented on Jul 31, 2026

Gaps 2, 3 and 4 retired: provisioned Hyperscale WITH an HA replica, and readonly_reason & 8 exercised on a live readable secondary

0. How the SQL was obtained — no hand transcription this time

1. Axis "provisioned (non-serverless) Hyperscale" — this is that database

2. Axis "Hyperscale WITH HA replicas" — the replica is real and live

3. The #1836 flip criterion, re-run on the HA-bearing database

4. The decisive question — does the view hold live per-replica rows now?

5. The full shipped payload on the PRIMARY — 55/55 bind, both host shapes

6. Axis "readonly_reason & 8" — exercised at last, on a real readable secondary

7. Counterfactual: the bit-8 term is load-bearing here, not decorative

8. New finding: on Hyperscale read scale-out, secondary workload never reaches the Query Store

What this run retires

Cost and teardown

erikdarlingdata commented on Jul 31, 2026

Closing: all four items covered by two live Azure runs, both on 2026-07-31

Metadata

Metadata

Assignees

Labels

Projects

Milestone

Relationships

Development

Issue actions

2. `collection_log` — both collectors SUCCESS, nothing else anything but SUCCESS

3. THE #1872 FLIP THROUGH THE SHIPPED PATH — `replica_role = 'Primary'`, stored

4. `plan_type` and the #1841 interval identity — populated

6. `procedure_stats` for the seeded procedures

Gaps 2, 3 and 4 retired: provisioned Hyperscale WITH an HA replica, and `readonly_reason & 8` exercised on a live readable secondary

6. Axis "`readonly_reason & 8`" — exercised at last, on a real readable secondary