Data Migration Runbook

[jirhiker]

# Migration Runbook: Legacy MS Access/SQL Server → GCP Cloud SQL for Postgres/PostGIS

**Owner:** Data Platform Team  
**Runbook Version:** 1.0  
**Last Updated:** 2025-08-26  
**Related ADR:** Migration from Legacy MS Access/SQL Server to GCP Postgres/PostGIS

---

## 0) Overview
This runbook describes the end‑to‑end, repeatable migration of data from legacy Microsoft Access and SQL Server databases to Google Cloud SQL for Postgres with PostGIS enabled. It includes prep, environment setup, schema deployment, data extraction, transformation, load, validation, performance tuning, cutover, and rollback.

> **Assumptions**
> - Target is **Cloud SQL for Postgres** v14+ with **PostGIS** extension.
> - ETL implemented in **Python** using **SQLAlchemy** (Core or ORM), optional **pandas** for staging.
> - Access to legacy data sources (Access .mdb/.accdb files, SQL Server instance) via network or file share.

---

## 1) Roles & Contacts
- **Run Lead:** `<name>` (approver for go/no‑go)
- **DBA / Cloud SQL Admin:** `<name>`
- **Data Engineer (ETL):** `<name>`
- **Geospatial SME:** `<name>`
- **Stakeholders:** `<names>`

---

## 2) Change Ticket & Schedule
- **Change ticket:** `<ID>`
- **Window:** `<date/time in local + UTC>`
- **Freeze period:** No schema changes on sources 24h before cutover.
- **Back‑out window:** `<duration>`

---

## 3) Prerequisites & Tooling
- **Local/CI**: Python 3.11+, `virtualenv` or `uv`, `psql`, `pg_dump`, `pg_restore`.
- **Python libs**: `sqlalchemy`, `psycopg[binary]` or `psycopg2-binary`, `pyodbc`, `pandas` (optional), `shapely`, `geoalchemy2`, `python-dotenv`, `pyproj`.
- **Access connectors**: `mdbtools` (for .mdb) or Access ODBC driver (Windows), or `pyodbc` with ACE OLEDB (Windows).
- **SQL Server**: ODBC driver 18+, or `bcp`/`sqlcmd` (optional).
- **GCP**: `gcloud` CLI, IAM permissions to Cloud SQL Admin/Client, Secret Manager access.

---

## 4) Environment & Secrets
Create a `.env` (or Secret Manager entries) for both source and target connections.

```ini
# Target Postgres (Cloud SQL)
PGHOST=<cloud-sql-proxy-host-or-ip>
PGPORT=5432
PGDATABASE=<target_db>
PGUSER=<app_user>
PGPASSWORD=<password or use IAM/db role>
PGSSLMODE=require

# SQL Server source
MSSQL_DSN="Driver={ODBC Driver 18 for SQL Server};Server=<host>;Database=<db>;UID=<user>;PWD=<pwd>;Encrypt=yes;TrustServerCertificate=yes;"

# Access source (example Windows ODBC)
ACCESS_CONN_STR="Driver={Microsoft Access Driver (*.mdb, *.accdb)};Dbq=C:\\path\\legacy.accdb;"

---

5) Target Database Provisioning (One‑Time)

1. Create Cloud SQL for Postgres instance (v14+), set HA, backups, PITR.
2. Enable PostGIS and related extensions.
3. Configure network and access (Cloud SQL Proxy preferred).

---

6) Schema Deployment

• Maintain schema DDLs or alembic migrations in db/schema/.
• Example tables with geometry columns for PostGIS.

---

7) Data Extraction

• Access: mdbtools (Linux/macOS) or ODBC via Python (Windows).
• SQL Server: pyodbc via Python or bcp for large exports.

---

8) Transformation & Load (Python + SQLAlchemy)

Implement ETL scripts in /etl.

Directory structure:

/etl
  /config
  /mappings
  extract.py
  transform.py
  load.py
  run_pipeline.py

8.1 Example ETL Snippets

• Normalize Access/SQL Server datatypes.
• Use COPY for bulk inserts.
• Convert geometry (lat/lon → PostGIS).

---

9) Data Quality & Validation

• Row counts.
• Checksums.
• Referential integrity.
• Geometry validity and CRS consistency.
• Sample queries.

---

10) Performance Tuning

• Add indexes after bulk load.
• Run VACUUM (ANALYZE).
• Consider partitioning for large tables.

---

11) Idempotency & Reruns

• Use staging tables.
• Implement upserts.
• Add ETL run ledger table.

---

12) Security & Compliance

• Store credentials in Secret Manager.
• Use least privilege IAM roles.
• Enable Cloud SQL Insights/Logging.

---

13) Dry Run (Non‑Prod)

• Run in staging.
• Validate metrics.
• Iterate until clean migration.

---

14) Cutover Plan (Prod)

1. Freeze writes.
2. Snapshot sources.
3. Reset target.
4. Run ETL.
5. Validate.
6. Switch endpoints.
7. Monitor.

---

15) Rollback Plan

• Re-point to legacy.
• Disable Cloud SQL writes.
• Document incident.
• Fix data issues before retry.

---

16) Post‑Cutover Tasks

• Enable backups.
• Baseline performance.
• Archive migration logs.
• Decommission legacy after retention review.

---

17) Example Run Pipeline Skeleton

# etl/run_pipeline.py
import argparse
import logging

from extract import extract_data
from transform import transform_data
from load import load_data

logging.basicConfig(level=logging.INFO, format="%(asctime)s %(levelname)s %(message)s")


def run_pipeline(steps):
    logging.info("Starting pipeline: %s", steps)

    data = None
    if "extract" in steps:
        data = extract_data()

    if "transform" in steps:
        data = transform_data(data)

    if "load" in steps:
        load_data(data)

    logging.info("Pipeline complete.")


def parse_args():
    parser = argparse.ArgumentParser(description="ETL pipeline for migration")
    parser.add_argument(
        "--steps", nargs="+", default=["extract", "transform", "load"],
        help="Steps to run: extract, transform, load"
    )
    return parser.parse_args()


if __name__ == "__main__":
    args = parse_args()
    run_pipeline(args.steps)

---

Sign‑Off

• Run Lead: ____________________ Date: __________
• DBA: _________________________ Date: __________
• SME: _________________________ Date: __________