Find Single-AZ RDS Databases in Production

{
  "Version": "2012-10-17",
  "Statement": [
    {
      "Sid": "ReadDatabases",
      "Effect": "Allow",
      "Action": [
        "rds:DescribeDBInstances",
        "rds:DescribeDBClusters"
      ],
      "Resource": "*"
    },
    {
      "Sid": "ConvertWithApply",
      "Effect": "Allow",
      "Action": "rds:ModifyDBInstance",
      "Resource": "arn:aws:rds:*:123456789012:db:*"
    }
  ]
}

// find-single-az-rds.ts
// Lists RDS DB instances that aren't Multi-AZ and Aurora clusters whose instances all sit in one
// Availability Zone. Report only unless you pass --apply with --names, which converts the named
// DB instances to Multi-AZ in their next maintenance window.
// Usage:
//   npx tsx find-single-az-rds.ts [--regions us-east-1,eu-west-1] [--env prod] [--names db1,db2 --apply]
import { ModifyDBInstanceCommand, RDSClient, paginateDescribeDBClusters, paginateDescribeDBInstances, type Tag } from "@aws-sdk/client-rds";

const args = process.argv.slice(2);
const flag = (name: string): string | undefined => {
  const i = args.indexOf(name);
  return i >= 0 ? args[i + 1] : undefined;
};
const list = (v: string | undefined) => (v ?? "").split(",").map((s) => s.trim()).filter(Boolean);
const regions = list(flag("--regions") ?? process.env.AWS_REGION ?? "us-east-1");
const envFilter = flag("--env")?.toLowerCase(); // only report databases whose env/environment/stage tag matches
const names = new Set(list(flag("--names")));
const apply = args.includes("--apply");

interface Row {
  Region: string;
  Kind: "instance" | "replica" | "aurora";
  Name: string;
  Engine: string;
  Class: string;
  Env: string;
  AZs: string;
  Finding: string;
  Result: string;
}

const envTag = (tags: Tag[] | undefined) =>
  tags?.find((t) => ["env", "environment", "stage"].includes((t.Key ?? "").toLowerCase()))?.Value ?? "";

async function scanRegion(region: string): Promise<Row[]> {
  const rds = new RDSClient({ region });
  const rows: Row[] = [];
  const azOf = new Map<string, string>(); // DB instance identifier -> Availability Zone
  const classOf = new Map<string, string>();

  for await (const page of paginateDescribeDBInstances({ client: rds }, {})) {
    for (const db of page.DBInstances ?? []) {
      const id = db.DBInstanceIdentifier ?? "?";
      azOf.set(id, db.AvailabilityZone ?? "?");
      classOf.set(id, db.DBInstanceClass ?? "?");
      if (db.DBClusterIdentifier || db.MultiAZ) continue; // cluster members are judged per cluster below
      const replica = Boolean(db.ReadReplicaSourceDBInstanceIdentifier || db.ReadReplicaSourceDBClusterIdentifier);
      rows.push({
        Region: region,
        Kind: replica ? "replica" : "instance",
        Name: id,
        Engine: db.Engine ?? "?",
        Class: db.DBInstanceClass ?? "?",
        Env: envTag(db.TagList),
        AZs: db.AvailabilityZone ?? "?",
        Finding: db.PendingModifiedValues?.MultiAZ ? "Single-AZ (Multi-AZ pending)" : "Single-AZ",
        Result: "",
      });
    }
  }

  // Aurora: availability comes from reader instances in other AZs, not from a MultiAZ flag you set.
  // Multi-AZ DB clusters (non-Aurora) always run three instances in three AZs, so they're skipped.
  for await (const page of paginateDescribeDBClusters({ client: rds }, {})) {
    for (const c of page.DBClusters ?? []) {
      if (!(c.Engine ?? "").startsWith("aurora") || c.EngineMode === "serverless") continue;
      const members = c.DBClusterMembers ?? [];
      const azs = new Set(members.map((m) => azOf.get(m.DBInstanceIdentifier ?? "") ?? "?"));
      let finding = "";
      if (members.length === 0) finding = "No DB instances";
      else if (members.length === 1) finding = "Writer only, no reader";
      else if (azs.size === 1) finding = "All instances in one AZ";
      if (!finding) continue;
      const writer = members.find((m) => m.IsClusterWriter)?.DBInstanceIdentifier ?? "";
      rows.push({
        Region: region,
        Kind: "aurora",
        Name: c.DBClusterIdentifier ?? "?",
        Engine: c.Engine ?? "?",
        Class: classOf.get(writer) ?? "?",
        Env: envTag(c.TagList),
        AZs: [...azs].join(" "),
        Finding: finding,
        Result: "",
      });
    }
  }

  const matched = envFilter ? rows.filter((r) => r.Env.toLowerCase() === envFilter) : rows;
  if (!apply) return matched;

  for (const row of matched) {
    if (!names.has(row.Name)) {
      row.Result = "skipped (not in --names)";
      continue;
    }
    if (row.Kind === "aurora") {
      row.Result = "add a reader in another AZ";
      continue;
    }
    try {
      // ApplyImmediately false: RDS converts the instance in its next maintenance window.
      await rds.send(new ModifyDBInstanceCommand({ DBInstanceIdentifier: row.Name, MultiAZ: true, ApplyImmediately: false }));
      row.Result = "Multi-AZ scheduled";
    } catch (err) {
      row.Result = `error: ${err instanceof Error ? err.name : String(err)}`;
    }
  }
  return matched;
}

async function main(): Promise<void> {
  if (apply && names.size === 0) {
    console.error("--apply needs --names with the DB instance identifiers to convert.");
    process.exit(1);
  }
  const rows: Row[] = [];
  for (const region of regions) {
    try {
      rows.push(...(await scanRegion(region)));
    } catch (err) {
      console.error(`${region}: ${err instanceof Error ? `${err.name}: ${err.message}` : String(err)}`);
    }
  }
  if (rows.length) console.table(rows);
  console.log(`${rows.length} single-AZ databases in ${regions.join(", ")}${envFilter ? ` tagged ${envFilter}` : ""}.`);
  if (!apply) console.log("Report only. Re-run with --names and --apply to schedule Multi-AZ for named instances.");
}

main().catch((err) => {
  console.error(err);
  process.exit(1);
});

npm install @aws-sdk/client-rds
npm install --save-dev tsx typescript @types/node

# Report production databases in two Regions
AWS_PROFILE=readonly npx tsx find-single-az-rds.ts --regions us-east-1,eu-west-1 --env prod

# Schedule Multi-AZ for one named instance in its next maintenance window
AWS_PROFILE=rds-admin npx tsx find-single-az-rds.ts --regions us-east-1 --env prod --names payments-pg --apply

┌─────────┬─────────────┬────────────┬─────────────────┬─────────────────────┬─────────────────┬────────┬──────────────┬────────────────────────────────┬────────────────────────────┐
│ (index) │ Region      │ Kind       │ Name            │ Engine              │ Class           │ Env    │ AZs          │ Finding                        │ Result                     │
├─────────┼─────────────┼────────────┼─────────────────┼─────────────────────┼─────────────────┼────────┼──────────────┼────────────────────────────────┼────────────────────────────┤
│ 0       │ 'us-east-1' │ 'instance' │ 'payments-pg'   │ 'postgres'          │ 'db.m6g.large'  │ 'prod' │ 'us-east-1a' │ 'Single-AZ'                    │ 'Multi-AZ scheduled'       │
│ 1       │ 'us-east-1' │ 'instance' │ 'reports-mysql' │ 'mysql'             │ 'db.r6g.large'  │ 'prod' │ 'us-east-1b' │ 'Single-AZ (Multi-AZ pending)' │ 'skipped (not in --names)' │
│ 2       │ 'us-east-1' │ 'aurora'   │ 'orders-aurora' │ 'aurora-postgresql' │ 'db.r6g.xlarge' │ 'prod' │ 'us-east-1a' │ 'All instances in one AZ'      │ 'skipped (not in --names)' │
│ 3       │ 'us-east-1' │ 'aurora'   │ 'search-aurora' │ 'aurora-mysql'      │ 'db.r6g.large'  │ 'prod' │ 'us-east-1b' │ 'Writer only, no reader'       │ 'skipped (not in --names)' │
└─────────┴─────────────┴────────────┴─────────────────┴─────────────────────┴─────────────────┴────────┴──────────────┴────────────────────────────────┴────────────────────────────┘
4 single-AZ databases in us-east-1 tagged prod.