Designing a Load That Survives Failing Halfway

A restartable load knows what committed before a failure and what can safely be repeated. Design that behavior before adding a retry button, because repetition alone does not make a process reliable.

Keep the Input Stable

Give each source delivery a durable batch identifier and preserve its input until processing is complete. If a retry reads a different source snapshot, it is not repeating the same work. Record the source boundary with the batch.

Decide the business key and what a repeated row means. An invoice replay should not create another invoice merely because the loader restarted. A primary key helps enforce that rule, but the intended update behavior still needs design.

CREATE TABLE #BatchControl
(
    BatchId int PRIMARY KEY,
    Status varchar(12) NOT NULL,
    LastRowId int NOT NULL
);
CREATE TABLE #Landed
(
    RowId int PRIMARY KEY,
    BusinessKey int UNIQUE,
    Amount decimal(12,2) NOT NULL
);
CREATE TABLE #Target
(
    BusinessKey int PRIMARY KEY,
    Amount decimal(12,2) NOT NULL
);
INSERT #BatchControl VALUES (1, 'Pending', 0);
INSERT #Landed VALUES (1, 101, 10), (2, 102, 20);

These temporary tables demonstrate the mechanics within one session. A real restart after connection loss requires durable staging and control tables. Do not confuse a convenient lab example with persistent recovery state.

Commit Data and Progress Together

When the target and control table share a database, update them in the same transaction. That prevents committed data from being paired with an earlier progress marker. It also prevents a progress marker from claiming uncommitted work.

SET XACT_ABORT ON;
BEGIN TRY
    BEGIN TRANSACTION;
    DECLARE @Last int;
    SELECT @Last = LastRowId
    FROM #BatchControl WITH (UPDLOCK, HOLDLOCK)
    WHERE BatchId = 1;
    INSERT #Target(BusinessKey, Amount)
    SELECT BusinessKey, Amount FROM #Landed WHERE RowId > @Last;
    UPDATE #BatchControl
    SET LastRowId = (SELECT MAX(RowId) FROM #Landed), Status = 'Complete'
    WHERE BatchId = 1;
    COMMIT TRANSACTION;
END TRY
BEGIN CATCH
    IF XACT_STATE() <> 0 ROLLBACK TRANSACTION;
    THROW;
END CATCH;

Run this block again in the same session to examine the completed-batch behavior. The example assumes one immutable insert-only batch with nonoverlapping business keys. Updates, overlapping batches, and multiple workers require additional coordination.

Give Each Step an Idempotent Meaning

Idempotent means repeating an operation leaves the same intended business state. Setting a balance to an authoritative value differs from adding that value again. Both statements can execute successfully while only one is appropriate for replay.

For insert-only events, a durable unique event identifier can reject duplicates. For current-state data, compare keys and apply the intended replacement or update. Keep concurrency protection and uniqueness constraints even when application logic checks first.

SELECT BusinessKey, Amount FROM #Target ORDER BY BusinessKey;
SELECT BatchId, Status, LastRowId FROM #BatchControl;

A retry should use the same batch identity, not create a new identity that bypasses duplicate detection. External effects need special treatment too. Sending an email or calling another service is not rolled back by a database transaction.

Choose Batches That Bound Recovery Work

One transaction for a huge load can create substantial logging, locking, and rollback work. Smaller committed chunks can limit that exposure. Each chunk needs a stable boundary and its own durable progress record.

Use a deterministic source key or immutable staging sequence to define chunks. Do not use changing offsets against a source that keeps receiving rows. Record completion only for the range that actually committed.

A worker that stops after a timeout may not know whether the last commit succeeded. Resolve that uncertainty by reading durable state on reconnect. Blindly assuming failure can repeat work that the server already completed.

Consider Full Replacement When It Is Simpler

For a small complete reference dataset, rebuilding a validated replacement can be easier than maintaining complex incremental rules. The replacement still needs a safe publication boundary. Users should not observe an empty or half-loaded target by accident.

Truncate and reload can be reasonable in an appropriate controlled transaction or isolated publication process. Foreign keys, permissions, concurrent readers, and recovery requirements can make it unsuitable. Choose it because the whole design fits, not because the script is short.

Test the Failure Points

Interrupt a lab run before writing, during a chunk, and after commit but before the client receives confirmation. Reconnect and inspect the durable batch state. Then prove that the retry preserves the intended result.

Also test invalid rows and a source delivery received twice. Record whether the process blocks, quarantines, or resumes. A restart procedure should tell the operator what to do without editing progress values by guesswork.

Restartability is not an automatic retry, it is progress that remains true after failure.

This post was rewritten from scratch in September 2026. The original, published on 2011-05-11, was a short announcement about something that no longer exists. The address is the same, the subject is now something worth keeping.

Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.