A Database Endpoint Switch Is Not the First Correct Operation

START WITH THE MECHANISM

Interactive companion and laboratory update: 12 November 2026. The editorial date places the topic on the timeline; this current explanation and its measured evidence retain their actual preparation and execution dates.

THE FULL INVESTIGATION

A Database Endpoint Switch Is Not the First Correct Operation

Dates and technology context

This is a retrospective editorial timeline. Topic context: 6 August 2026. First publicly available: 12 November 2026. Current text, visuals and laboratory revisions were prepared on 12 November 2026.

The actual two-instance cutover, transaction replay, first-correct-operation timing and .NET11 client are a 2026-11-12 lab update, not an earlier incident or HA deployment.

Changing an application's database endpoint is a small configuration action with a large implied promise. The promise is not merely that another server accepts a connection. It is that the next operation observes the right history, applies its effect to the intended authority and returns a result the application can trust.

Those conditions can become true at different times. A restored target can be online but older than the source. A client can see a timeout after the source committed a write. An application can point to the new endpoint while an external dependency still belongs to another recovery state. Calling the endpoint change complete can hide all three problems.

This article examines a controlled application cutover between two actual SQL Server 2022 instances. A full backup seeds the target. A later source write commits but its acknowledgement deliberately times out. The client changes its selected endpoint, detects the target's missing transaction, reconciles it with an identity-aware replay and validates a new write on the target.

This is not an availability group, automatic failover or an unplanned regional outage. The two instances run in separate disposable containers on one host. The source remains reachable throughout the reconciliation. The experiment was executed on 12 November 2026 and preserves the commands, transaction checks, probe results and stage timestamps needed to review its narrower claims.

Name the authority change you are actually testing

The source instance contains RecoverySource. The target instance receives a restored copy named RecoveryTarget. The application adapter uses a selected connection endpoint and database name for each operation. Changing that selection is the cutover action in this laboratory.

There is no SQL Server role promotion. There is no listener that automatically redirects connections, no replication stream and no failure detector deciding which server is primary. Backup and restore transfer one known snapshot; application logic handles the later missing transaction. These mechanisms should not be relabelled as a high-availability feature merely because two servers exist.

The distinction matters because different authority mechanisms have different failure models. An availability group has its own synchronization and failover behavior. A manually restored target has the state captured in the backup and any explicit later reconciliation. An application endpoint variable does not create replication or prevent another client from continuing to write elsewhere.

The fixture records an epoch in a database identity table. Before cutover, the source and restored snapshot use epoch one. The harness later marks the old source epoch zero and the selected target epoch two. Its readiness probe expects epoch two and rejects a connection pointing to the old marker.

That marker is an application-level guard, not a database-enforced fencing mechanism. A privileged client that ignores the probe can still write to the source. The example has one controlled writer and no competing producers. The article does not claim split-brain protection or a race-free authority protocol for multiple application instances.

This honest scope gives the experiment a useful purpose. It separates connection selection from data reconciliation and operation correctness without pretending to implement a complete distributed consensus or database failover system. The next operation is the acceptance boundary, and the endpoint switch is only one step toward it.

Establish a snapshot whose missing work can be identified

01 / See the idea

The target can be online and still be older

Enlarge diagram

The source ledger begins with identifiers one through one thousand. Each amount is seven times the identifier. Each row carries a payload hash derived from the identifier and amount. The known initial sum is 3,503,500.

The backup is a full database backup with CHECKSUM and COPY_ONLY. It is copied to the target container, verified and restored into a new database using explicit MOVE clauses derived from FILELISTONLY. The target's exact rows are compared with the independent generator before any cutover work begins.

Backup verification and actual restore are separate gates. VERIFYONLY does not restore a database or establish application readiness. Microsoft's documentation explicitly limits what it verifies. Microsoft: RESTORE VERIFYONLY

At this point, the target is correct for the thousand-transaction snapshot. That statement is precise. It does not say the target will remain current when the source accepts another write. A backup is an artifact of a recovery point, not an ongoing promise to follow the source's future state.

The fixture's sequential identifiers make the gap obvious. When transaction 1001 later appears on the source, the target's expected state can be described exactly. A production system may not have such a convenient sequence, so it needs another durable way to identify missing or ambiguous operations.

That identification mechanism belongs in the design before cutover. Without stable operation identity or an authoritative reconciliation source, a team can know that the target is older while being unable to determine which effects must be replayed. Changing the connection string does not solve that information problem.

Make the acknowledgement ambiguous without making the commit imaginary

02 / Watch the mechanism

A timeout does not reverse a committed effect

Enlarge diagram

Static view. Play to see how the mechanism unfolds.

The laboratory creates a real client-visible uncertain outcome. It executes a SQL batch that begins a transaction, inserts row 1001 with amount 7007, commits, then deliberately delays before returning an acknowledgement. The command timeout is one second while the post-commit delay is two seconds.

BEGIN TRAN;
INSERT dbo.Ledger VALUES(1001,7007,@hash);
COMMIT;
WAITFOR DELAY '00:00:02';
SELECT 1;

The client receives SqlException number minus two in the recorded execution. The harness records write_acknowledgement_unknown. It does not interpret the timeout as proof that the transaction rolled back.

A separate connection then reads the source ledger and verifies every row through identifier 1001. The amount sum is 3,510,507. That independent read establishes that the commit occurred in this experiment despite the missing acknowledgement.

The distinction between the client's knowledge and the database's state is the important result. The harness deliberately knows where it placed the delay, but an application receiving an unexpected timeout in a real incident may not know whether the failure occurred before, during or after commit. It needs a reconciliation method that does not rely on guessing from the exception alone.

This is a controlled lost-acknowledgement model. It does not emulate a regional network partition, a broken TCP connection at every possible byte boundary or a server crash during log flush. The SQL command's explicit ordering makes the selected case reproducible and the actual committed state inspectable.

The resulting repair question is still realistic: if the caller retries after an ambiguous acknowledgement, how does the system distinguish a missing effect from one that already exists? The answer in this fixture is stable transaction identity plus payload agreement, checked within a database transaction.

Switch the client and require the target to explain itself

After the source's ambiguous write is verified, the harness changes the application markers and sets its active endpoint to SQL_B with RecoveryTarget. It records client_endpoint_switched at that moment. This is the start of the measured post-switch acceptance interval, not the end of recovery.

The old source fails a probe expecting epoch two. Its database identity is still the intended application, and it contains 1001 rows, but its epoch is zero. The failure reason is epoch_mismatch. That detects the stale routing choice for this cooperating client.

The new target passes the epoch check but fails the expected ledger-count gate. It has only the thousand rows captured in the backup. The reason is ledger_not_reconciled. The target can accept SQL connections and execute queries, yet the application refuses to call it ready for the expected operation history.

The count gate is a quick readiness condition, not the complete correctness proof. The harness performs exact row and hash comparisons at the relevant checkpoints. A target with the right count and wrong data would fail that stronger validation even if a shallow probe alone could miss it.

The probe also checks application identity and an external template hash. Those conditions prevent a familiar database name or reachable server from being mistaken for the complete application state. The shared recovery exercise deliberately introduces a wrong database and wrong dependency later to verify that these gates reject them.

There is no public HTTP service in this fixture. The application adapter is a C# client that executes the real database operations and reads the external file. Its recorded responses are application probe results, not claims that a deployed web tier, routing layer or authentication flow has recovered.

Reconcile by identity and payload, not by blind repetition

03 / A concrete example

Replay has three outcomes, not two

Enlarge diagram

The replay operation accepts a transaction identifier and amount. It begins a serializable transaction and looks for the identifier with update and hold locks. If no row exists, it inserts the identifier, amount and derived hash. If a row exists with the same hash, it returns a duplicate no-op. If the hash differs, it rejects the request as a conflict.

The lookup used by the executed replay is:

SELECT PayloadHash
FROM dbo.Ledger WITH(UPDLOCK,HOLDLOCK)
WHERE TxId=@id;

The ledger's primary key supplies a uniqueness constraint on the identifier. The explicit transaction keeps the lookup and insertion within one consistency boundary. This is a small implementation for one table and one controlled writer, not a universal exactly-once processing claim across external services.

The harness first replays transaction 1001 with amount 7007 into the target. That inserts the missing row. It then submits the same identity and payload again. The second submission is recorded as replay_duplicate_noop, and the row count does not increase.

Next it submits identifier 1001 with amount 999. The payload hash differs, so the replay rejects the operation. This negative case matters because an identifier alone is not enough to justify treating every retry as equivalent. Reusing an identity with different content can conceal an application error or conflicting history.

The chosen hash representation is deliberately simple: identifier, a separator and amount encoded as UTF-8. The controlled inputs are integers with a fixed representation. A production payload needs a stable canonicalization and versioning rule; hashing an ambiguous or changing serialization can create false conflicts or false equivalence.

The exact ledger verification after replay checks identifiers one through 1001 and the expected sum. That confirms the target contains the intended history before a new post-cutover transaction is accepted. The operation is no longer relying on the fact that the INSERT command happened to return successfully.

Do not call a marker a fence

05 / See the idea

Do not mistake a marker for enforcement

Enlarge diagram

The epoch check is useful in this exercise because it catches a client configured for the old endpoint. It is not sufficient to prevent all writes to the old database. The harness does not revoke credentials, make the old database read-only or implement an atomic authority lease enforced by every writer.

That limitation should be visible in both code review and operational prose. A design can look protected because it contains a variable named epoch while its mutation path never checks that value. The marker's actual consumer here is the readiness probe. Its protection is limited to the controlled client following that gate.

In a multi-writer production system, authority needs an enforceable contract. The appropriate mechanism depends on the architecture, but it must address stale clients and concurrent transitions rather than assume that configuration changes reach every participant at once. This article does not implement or benchmark such a mechanism.

The source remains reachable for reconciliation. The final report shows 1001 rows on the source and 1002 on the target after the new target write. That proves where this controlled writer placed the new effect. It does not prove that another process could not have written to the old source during the exercise.

This boundary is a reason to avoid the word automatic failover. The test demonstrates a deliberate application cutover with explicit gates and a single actor. Its evidence is valuable for reasoning about uncertain outcomes and recovery points, but it cannot establish the availability properties of a coordinated cluster.

Keeping the distinction explicit also clarifies what a future test would need. Add a competing stale writer, enforce the intended authority rule at the mutation boundary and verify which writes are accepted or rejected. That would be another experiment with another acceptance condition, not an inference from the current one.

Count the first correct operation after the switch

04 / A concrete example

The completion event comes after the switch

Enlarge diagram

Once the target has been reconciled, the probe validates epoch two, the expected ledger count and the external template hash. The harness then writes a new transaction, identifier 1002 with amount 7014, through the selected target endpoint.

The target ledger is checked exactly through 1002. Its sum is 3,517,521. The source still has 1001 rows, and the target has 1002. The harness records first_correct_post_cutover_write only after those checks complete.

That event is deliberately later than client_endpoint_switched. The interval includes validation, reconciliation and several deliberate negative runbook tests. It is not simply the time required to assign a new connection string, and it is not a clean estimate of production failover latency.

In the dedicated measured run, the endpoint selection occurs at 1880.6512 milliseconds on the recovery process's stopwatch. The first correct post-cutover write is recorded at 2142.7670 milliseconds. Their difference is 262.1158 milliseconds for this scripted sequence, including its intentionally inserted wrong-database and wrong-dependency checks.

The unknown acknowledgement appears earlier at 1863.0065 milliseconds. The target is observed stale at 1892.5343 milliseconds. The missing transaction is applied at 1907.5779 milliseconds, a duplicate is accepted as a no-op at 1913.9281 milliseconds and the conflicting replay is rejected at 1916.3529 milliseconds.

Those timestamps make the ordering concrete. They are not service-level objectives, percentiles or estimates for a geographically distributed application. The test uses two local containers, one small ledger and one client. The result shows why acceptance needs a later event than endpoint change, not how fast every cutover will be.

Keep dependency readiness in the same acceptance conversation

The restored database references a synthetic external template by hash. The template is copied separately after the initial restore probe detects its absence. Later, the shared exercise deliberately replaces the target content with incorrect text and confirms dependency_hash_mismatch.

This negative case is relevant to cutover because routing can select a database whose external references are not satisfied by the target environment. The database may contain the right transaction history while the application uses the wrong template, configuration or blob version. A successful SQL query would not necessarily reveal that mismatch.

The fixture models the dependency with a local file. It does not claim cloud-storage replication, credential recovery or a distributed content-addressed store. Its purpose is to make the consistency edge explicit: the target application must have content matching the database's expected hash.

After the correct template is restored, the probe passes. Only then does the harness proceed to the new post-cutover write. This ordering prevents a target with internally correct ledger data but incompatible external state from being called ready.

A real service may need several such dependencies. The useful design exercise is to derive them from the operation contract rather than produce an indiscriminate inventory. Which external value would make the next operation wrong if it were missing or stale? Which version must agree with the restored database? Which probe can distinguish those states without causing an uncontrolled side effect?

The answers belong in the cutover plan because they affect the first correct operation. Leaving them to a generic infrastructure health check can produce a green deployment whose application behavior is still inconsistent.

Separate demonstrated recovery from unavailable-source recovery

The source remains available in this experiment. That makes it possible to verify the uncertain transaction directly after the timeout. A genuine source loss could remove that reconciliation path. The current results do not prove that the system could recover transaction 1001 if the only evidence of it disappeared with the source.

This is a central limitation, not a footnote. Application replay requires a trustworthy description of what to replay. Here the fixture has a known operation identifier and payload, and the source confirms that the effect committed. A production design may require durable client receipts, an upstream command log or another independently retained record.

Without such evidence, a team may have to choose among uncertainty, possible duplication and possible omission. No clever retry loop can manufacture missing history. The cutover procedure should state which source of truth remains available under the failure it claims to handle.

The full backup's recovery point is likewise explicit. It contains only the first thousand transactions. There is no continuous log transport closing the gap. Calling the target synchronized would be incorrect until the specific missing operation has been reconciled and verified.

The distinction suggests a useful way to design future drills. Start with one declared failure model. Identify which evidence survives it. Define the acceptable ambiguity and the method for resolving it. Then test the operation contract under that model. Combining several failure stories without preserving their evidence assumptions produces broad claims that no single run supports.

This laboratory intentionally chooses the feasible model: two live isolated instances, a known stale backup, a controlled acknowledgement timeout and explicit replay. It makes those responsibilities visible before anyone relies on a more ambitious failover mechanism.

Decide what happens to operations already in flight

The fixture executes its stages in a controlled sequence. It does not leave a population of application requests concurrently using old pooled connections while another population discovers the new endpoint. That makes the single writer's outcome explainable, but it leaves an important production question open: what happens to operations that began before the routing decision changed?

An endpoint configuration update does not necessarily change the connection already held by an operation. A request can have acquired its database session before the new configuration became visible. Another request may be queued inside the application and only acquire a session afterward. Treating both as post-cutover work based on their response time would mix different ownership histories.

A production plan should define its admission boundary. It may pause new work, drain selected operations, reject stale writers or use another explicit authority mechanism. Each choice affects availability and uncertainty. The current exercise does not recommend one universal policy because it does not execute those competing strategies.

The ambiguous write demonstrates why draining is not merely waiting for every client to report success. Some clients may report timeout after a committed effect. The system needs a durable operation identity and a way to classify that outcome even when the transport response was lost. A list of exceptions is not the same thing as a list of unapplied transactions.

The new target must also be evaluated against the intended cutover boundary. If the boundary says all accepted operations through a known point must survive, the target needs evidence for that set. If the contract explicitly accepts a recovery-point gap, the application must explain which operations may be missing. Neither policy can be inferred from a server's online status.

The laboratory's single missing identifier makes this reasoning concrete without claiming to solve the general case. It exposes the questions a larger cutover must answer: which operations were admitted, which effects committed, which acknowledgements were observed and which destination is authorized to accept the next effect.

Prefer explicit uncertainty to a convenient success flag

The harness records the timed-out acknowledgement as unknown before it performs the independent source read. That ordering preserves what the client knew at the moment of failure. Once the source is inspected, the report can add the fact that transaction 1001 committed. It does not rewrite the earlier timeout into a successful acknowledgement.

This distinction helps when several teams own different parts of a recovery. The database team may know that a transaction exists while the application team only knows that a request timed out. A useful shared record can represent both facts without forcing one team to discard its observation.

It also prevents misleading retry metrics. A second delivery can be a duplicate of a committed operation rather than a second failed business action. The duplicate no-op result in this fixture is a successful reconciliation outcome, even though no new row is inserted. A dashboard that counts every retry as a new transaction would describe the wrong business volume.

The conflicting-payload branch deserves a different label. It is not another harmless duplicate and should not be absorbed into a generic retry-success count. The runbook stops because the same identity refers to different intended content. Keeping that distinction visible protects correctness even when it makes the recovery report less uniformly green.

Review the cutover as a sequence of claims

A defensible cutover report should say which target was selected, which state it initially contained, which operations were uncertain, how they were reconciled and which new operation proved the selected path. The evidence package supplies those facts for this fixture rather than reducing them to a single success flag.

The negative outcomes are part of that report. The old epoch is rejected. The new endpoint initially lacks a required transaction. Duplicate replay has no new effect. Conflicting replay is rejected. A decoy database and wrong dependency are rejected. These gates show that the harness can distinguish several plausible wrong states from the intended result.

The package also records the resource boundary. Both SQL containers are limited to two CPUs and four GiB of memory, expose only ephemeral loopback ports and are removed in finally. Passwords are generated in memory and passed through child environments, with connection strings redacted from logs. The retained backup contains only synthetic fixture data.

The final conclusion is therefore specific: this application adapter switched between two real SQL Server instances, recognized that the restored target was stale, reconciled a committed-but-unacknowledged transaction without duplicating it, rejected a conflicting payload and verified a new write on the target.

That is not automatic failover. It is a concrete explanation of why automatic-looking configuration changes do not by themselves establish application correctness. The useful completion event is the first operation whose identity, history, dependencies and effect all satisfy the contract. The endpoint change is only the point where the application begins proving that promise on another destination.

Source code and execution evidence

Download the complete lab, source record and raw execution results. This package supports the specific execution claims in this article. It is not a production certification.