# Lab: Serial vs parallel redo in SQL Server availability groups

Scripts and results from the lab behind the DBA: From Accidental to Pro post *Serial vs Parallel Redo: What Slows Redo Inside and Outside SQL Server*.
The step-by-step runbook (what each step does, why, the results and recommendations) is at `runbook/index.html`.

> **Disclaimer:** these scripts are provided "as is", without warranty of any kind. Use them only in non-production environments (development, test or lab), review them before you run them, and run them at your own risk. You are responsible for any changes they make to your systems and for any costs they incur.

## What you need

- Four SQL Server on Linux VMs, in two availability groups joined by a distributed availability group (`CLUSTER_TYPE = NONE`, certificate authentication, automatic seeding, manual failover):
  - **node-1**: global primary (the workload runs here) and **node-2**: its secondary, in AG A.
  - **node-3**: forwarder (primary of AG B) and **node-4**: its secondary, in AG B. Redo is measured on **node-4**.
- The lab's secondaries had 8 vCPUs (4 cores), which gave 4 parallel redo workers. The primary had 8 vCPUs in Lab 1 and 16 in Lab 2.
- Fast storage, if you want to see the hot-tail result. With Standard HDD data disks the log never passed 27 MB/s and every scenario but one tied. Lab 2 used Ultra Disk: 512 GB, 20,000 IOPS, 500 MB/s, no host caching.
- `sqlcmd` (`/opt/mssql-tools18/bin/sqlcmd` on the nodes, or go-sqlcmd on a client that can reach port 1433 on every node), and root on the secondaries to set the trace flag with `mssql-conf`.
- Room for the log: Lab 2 legs wrote up to 11.5 GB of log each, and the database was pre-sized to a 24 GB log and 16 GB of data. The orchestrator stopped if a node had less than 15% free space.

## Run it

```bash
export PRIMARY=node-1 FORWARDER=node-3 SECONDARY=node-4 SQL_USER=SA SQLCMDPASSWORD='<password>'

# once, on node-1
sqlcmd -S "$PRIMARY" -U SA -No -C -i scripts/00_create_database.sql -v AGNAME="<AG on node-1>"
sqlcmd -S "$PRIMARY" -U SA -No -C -d dag-redo-lab -i scripts/01_workload_schema.sql
sqlcmd -S "$PRIMARY" -U SA -No -C -i scripts/02_scenario_schema.sql

# on node-2, node-3 and node-4 (as root): pick the mode, then verify it
sudo ./scripts/10_set_redo_mode.sh serial
sqlcmd -S "$SECONDARY" -U SA -No -C -i scripts/11_verify_redo_mode.sql -v EXPECT=serial

# one backlog-drain leg: reset, suspend node-4, workload, wait for node-3, resume and time the drain
./run_leg.sh Hs1 H
```

Repeat `run_leg.sh` for each leg. The lab ran every scenario in the order serial ×6, parallel ×6, parallel ×6, serial ×6 (one leg per scenario H to M each time), then four more hot-tail legs per mode. It switched the mode only between blocks, because every switch is a restart.

## Files

| File | What it is | Run in the lab? |
|---|---|---|
| `scripts/00_create_database.sql` | Lab database, full backup, add to the AG | Yes |
| `scripts/01_workload_schema.sql` | Tables for runs A to E | Yes |
| `scripts/02_scenario_schema.sql` | Tables for scenarios H to L | Yes |
| `scripts/03_scenario_reset.sql` | Reset before each drain leg | Yes |
| `scripts/10_set_redo_mode.sh` | Trace flag 3459 on or off with `mssql-conf`, then restart | Yes (the lab's version also switched a third mode, left out here) |
| `scripts/11_verify_redo_mode.sql` | Confirms the mode after the restart | Yes, plus the redo-threads query from the collector |
| `scripts/20_log_rate_monitor.sql` | Log generation rate every 5 s | Yes |
| `scripts/21_workload_runs_a_e.sh` | Bulk, single-row and spread workloads | Yes |
| `scripts/30_workload_scenarios_h_m.sh` | Workloads H to M | Yes |
| `scripts/31_drain_measure.sql` | Resume + 500 ms sampling + drain summary | Yes |
| `scripts/40_dmv_collector.sql` | 1 to 5 s DMV collector into `RedoDiag` | Yes |
| `scripts/41_analysis_queries.sql` | Drain, waits and redo-thread CPU per run | Yes |
| `run_leg.sh` | One drain leg from a client | **No**: the lab ran the same steps through an orchestrator that called each node with `az vm run-command` |
| `scripts/90_production_checks.sql` | The three checks from the post's runbook | **No**: for your own servers |
| `scripts/99_cleanup.sql` | Drops the lab and collector databases | **No** |
| `results/*.txt` | Real output from both labs | — |

The workload and leg scripts read the connection from `SQL_HOST`, `SQL_USER` and `SQLCMDPASSWORD`. That is the only change from what ran on the nodes, where they read a local password file.

## Clean up

```bash
sqlcmd -S node-1 -U SA -No -C -i scripts/99_cleanup.sql -v ROLE=primary AGNAME="<AG on node-1>"
for n in node-2 node-3 node-4; do sqlcmd -S $n -U SA -No -C -i scripts/99_cleanup.sql -v ROLE=secondary AGNAME=x; done
# on each secondary: back to parallel redo
sudo /opt/mssql/bin/mssql-conf traceflag 3459 off && sudo systemctl restart mssql-server
```

Then delete the VMs and disks. Ultra Disks are billed for provisioned IOPS and throughput even while the VM is stopped.

## Tested

- SQL Server vNext CTP 1.2 on Linux (build 18.0.110.3), a pre-release build. A released version may behave differently.
- Lab 1: Standard HDD data disks, two regions (West US 2, West Central US), November 9–10, 2026.
- Lab 2: Ultra Disk data disks, one zone (East US 2), November 10–11, 2026.
- 80 measured runs in 13 scenarios. Results: `RESULTS.md`.
