Canaries, Query Store Baselines, Post-Upgrade Regression Analysis, and Production Acceptance Criteria

Learning outcomes

The last migration risk appears after the target is “up”: performance, error rate, backup/recovery, security or background operations can regress under real traffic. ServiceHub therefore ends the chapter with a production acceptance contract. A canary is a deliberately limited portion of workload exposed to the changed system. Query Store supplies persisted workload evidence, but it is only one layer; acceptance also includes integrity, error logs, waits, authentication, backup/restore, HA/DR, jobs and business transactions.

1. Baseline before change: preserve evidence you can compare

USE ServiceHubUpgradeLab;GOSELECT    SERVERPROPERTY('ProductVersion') AS product_version,    SERVERPROPERTY('Edition') AS edition,    d.compatibility_level,    q.actual_state_desc AS query_store_state,    q.query_capture_mode_desc,    q.wait_stats_capture_mode_descFROM sys.databases AS dJOIN sys.database_query_store_options AS q ON 1=1WHERE d.name=DB_NAME();SELECT TOP (20)    q.query_id,    OBJECT_NAME(q.object_id) AS object_name,    qt.query_sql_text,    SUM(rs.count_executions) AS executions,    CAST(SUM(rs.avg_duration * rs.count_executions) / NULLIF(SUM(rs.count_executions),0) AS bigint) AS weighted_avg_duration_usFROM sys.query_store_query_text AS qtJOIN sys.query_store_query AS q ON q.query_text_id=qt.query_text_idJOIN sys.query_store_plan AS p ON p.query_id=q.query_idJOIN sys.query_store_runtime_stats AS rs ON rs.plan_id=p.plan_idGROUP BY q.query_id,q.object_id,qt.query_sql_textORDER BY weighted_avg_duration_us DESC;

Persist the baseline outside the database too if the database itself may be replaced. Record build, compatibility, hardware/container limits, driver/app version and workload window. Query Store values are aggregates, not individual-request traces, so use the same comparison window and complement them with waits/XEvents/application metrics when needed.

2. Canary means limited exposure with explicit gates

A canary can be a small customer cohort, read-only traffic, a background worker pool or a small percentage of requests routed to the target. It is not “we opened production and watched dashboards.” Before routing canary traffic, define what causes stop/rollback: elevated business errors, unacceptable latency distribution, new deadlocks, excessive waits, failed backups, security/authentication failures, data divergence or replica lag.

USE ServiceHubUpgradeLab;GOIF OBJECT_ID(N'lab24.AcceptanceEvidence',N'U') IS NULLCREATE TABLE lab24.AcceptanceEvidence(    evidence_id bigint IDENTITY PRIMARY KEY,    captured_at datetime2(3) NOT NULL DEFAULT SYSUTCDATETIME(),    phase varchar(20) NOT NULL,    metric_name varchar(100) NOT NULL,    metric_value decimal(28,6) NULL,    unit varchar(20) NULL,    pass bit NULL,    note nvarchar(800) NULL);INSERT lab24.AcceptanceEvidence(phase,metric_name,metric_value,unit,pass,note)VALUES('PRECHECK','work_order_rows',(SELECT COUNT_BIG(*) FROM lab24.WorkOrder),'rows',1,N'Business cardinality baseline'),('PRECHECK','compatibility_level',(SELECT compatibility_level FROM sys.databases WHERE name=DB_NAME()),'level',1,N'Preserve before raising compatibility');

The ledger is evidence, not a monitoring platform. In production, populate it from automated checks tied to your SLOs and change record.

3. Compatibility-level promotion is a separate canary

USE master;GOALTER DATABASE ServiceHubUpgradeLab SET COMPATIBILITY_LEVEL = 170;GOUSE ServiceHubUpgradeLab;SELECT compatibility_level FROM sys.databases WHERE name=DB_NAME();-- Representative workload. Capture actual plan/Query Store/application timings in your harness.SELECT customer_id,status,COUNT_BIG(*) AS order_count,MAX(opened_at) AS latest_openedFROM lab24.WorkOrderWHERE customer_id IN (42,118,122)GROUP BY customer_id,status;GOSELECT name,valueFROM sys.database_scoped_configurationsWHERE name IN (N'OPTIONAL_PARAMETER_OPTIMIZATION',N'PARAMETER_SENSITIVE_PLAN_OPTIMIZATION');

Compatibility 170 can activate newer optimizer behavior for eligible queries, including SQL Server 2025 features such as Optional Parameter Plan Optimization. Do not assume a regression is “SQL Server 2025 is slow” until you compare plans, estimates, waits, parameters and compatibility behavior. Query Store can force a known-good plan as a governed short-term intervention, but root-cause analysis still matters.

4. Acceptance is broader than query latency

A migration is not accepted because “no one complained for two hours.” Acceptance is a signed set of measurements plus known residual risks and owners.

5. Wrong approach: destroy rollback as soon as cutover succeeds

If ServiceHub decommissions the source, rotates keys, deletes old backups and raises compatibility before canary acceptance, every fallback path disappears simultaneously. Keep rollback prerequisites until the acceptance window closes. For side-by-side migration, that usually means the source remains controlled/read-only or otherwise preserved according to the write-consistency plan. For compatibility-only regression, you may return to the previous supported compatibility level or use Query Store plan governance while investigating.

USE ServiceHubUpgradeLab;GOINSERT lab24.AcceptanceEvidence(phase,metric_name,metric_value,unit,pass,note)VALUES('POSTCHECK','work_order_rows',(SELECT COUNT_BIG(*) FROM lab24.WorkOrder),'rows',1,N'Reconciled after compatibility canary'),('POSTCHECK','compatibility_level',(SELECT compatibility_level FROM sys.databases WHERE name=DB_NAME()),'level',1,N'Target compatibility observed');SELECT * FROM lab24.AcceptanceEvidence ORDER BY evidence_id;GO-- Lab rollback demonstrates that compatibility is a separate reversible control.USE master;ALTER DATABASE ServiceHubUpgradeLab SET COMPATIBILITY_LEVEL = 160;GO-- Final Chapter 24 cleanup: remove only the disposable lab database.IF DB_ID(N'ServiceHubUpgradeLab') IS NOT NULLBEGIN    ALTER DATABASE ServiceHubUpgradeLab SET SINGLE_USER WITH ROLLBACK IMMEDIATE;    DROP DATABASE ServiceHubUpgradeLab;END;GO

Do not delete backup media or target/source infrastructure automatically from a lesson cleanup. Retention, evidence preservation and rollback expiry belong to production change governance. Chapter 25 uses these same gates in the full production capstone.

Authoritative references

• Query Store usage scenarios
• ALTER DATABASE compatibility level
• Optional Parameter Plan Optimization
• SQL Server post-migration validation and optimization
• DBCC CHECKDB