Note:

• Change the Dates and Developer name etc.
• A Well Documented documentation is enough
• This has 2 documentations ( New + Old )
• Change Names, dates accordingly

• Need Review By Taha

Optimsync – Business Continuity / Disaster Recovery Test Report (Live Incident – May 2025)

1  Purpose

This report documents Optimsync’s most recent BC/DR exercise — triggered by an actual production outage of the Cloud SQL database that feeds the Fivetran → dbt ingestion pipeline. The exercise served as an unplanned live test of the Contingency Plan, Emergency‑Access Procedures, and backup‑and‑restore capabilities to demonstrate compliance with HIPAA and SOC 2 requirements.

2  Scenario & Timeline (UTC)

• 03:17 – Fivetran sync alert: connection_refused
• 03:25 – Cloud SQL instance production becomes UNAVAILABLE
• 03:32 – Engineering declares Disaster Mode (DR‑001)
• 03:40 – Emergency database read‑only access granted via break‑glass IAM role (cloudsql.emergencyReader)
• 04:05 – Attempted automated fail‑over — failed (HA standby unhealthy)
• 04:20 – Point‑in‑time recovery (PITR) clone created (restore point 03:10)
• 05:29 – Service fully restored — total downtime 2 h 12 m

RTO / RPO results / RPO results

• Committed RTO: 1 hour ➜ Actual: 2 h 12 m (✖ Miss)
• Committed RPO: 15 minutes ➜ Actual: 19 minutes (✔ Pass)

3  Impact Analysis

• Data loss: 19 minutes of transactional data between 02:51–03:10 (replayed from upstream logs after cut‑over).
• Customer‑visible downtime: API error rate peaked at 100 % for 72 minutes; degraded performance until 05:29.
• Third‑party impact: Fivetran sync jobs queued > 2 hours; dbt models failed.

4  Response & Emergency‑Access Actions

Detection via automated alerting (Fivetran & Stackdriver).
Emergency‑access procedure invoked: break‑glass IAM role allowed the on‑call engineer to export binary logs (.wal) for forensic replay.
Failed HA fail‑over highlighted standby mis‑configuration.
PITR clone recovery per GCP run‑book, validated checksum integrity.
Customer communications posted every 30 minutes via status page.

5  Lessons Learned & Root‑Cause Analysis

6  Improvements Implemented

• Enable and monitor nightly backup‑verification job — Owner: Data Eng (target 15 Jul 2025; In Progress)
• Rebuild HA standby in us‑central1‑b — Owner: DevOps (22 Jul 2025; Scheduled)
• Update DR playbook with PITR + dbt steps — Owner: Compliance (31 Jul 2025; Not Started)
• Re‑baseline RTO in Business Impact Analysis (BIA) — Owner: Security (31 Aug 2025; Not Started)

7  Compliance Mapping  Compliance Mapping

8  Conclusion

The live incident provided a realistic test of Optimsync’s BC/DR capabilities. While RPO was met, RTO was missed due to a mis‑configured standby. Action items are in place, and a follow‑up simulation is scheduled for 1 Aug 2025 to verify improvements. This report, combined with the attached evidence, fulfills HIPAA Contingency Plan, Testing, and Emergency‑Access requirements as well as SOC 2 CC5 expectations.

Approved by:

[Name]  — Chief Technology Officer / DR Coordinator

Date: 27 Jun 2025

Business Continuity & Disaster Recovery Test Report

• Engineering Team
• Data Team (ETL / Fivetran / dbt)

1. Summary of the Event

2. Impact

• ETL Job Failed: Mid-process failure interrupted data extraction and transformation.
• Data Loss: All unbacked-up data was lost.
• System Downtime: Approximately [insert time, e.g., 2 hours] of unavailability.
• No automated failover or backup restoration was triggered at the time.

3. Response Actions

• The incident was detected via failed Fivetran sync alerts.
• Cloud SQL logs were reviewed to identify the root cause.
• Manual restoration attempts were made but failed due to lack of recent backups.
• The incident was escalated to GCP support.
• Postmortem was conducted to evaluate weaknesses in our DR strategy.

4. Lessons Learned

• Regular automated backups were not enforced or verified.
• Disaster recovery (DR) plan was not up to date.
• No clear RTO/RPO objectives were previously defined.

5. Improvements Implemented

• Enabled automated backups in Google Cloud SQL with 7-day retention.
• Defined Recovery Time Objective (RTO): 1 hour
• Defined Recovery Point Objective (RPO): 15 minutes
• Implemented backup verification jobs to test integrity.
• Integrated incident response playbook for ETL failures.
• Scheduled quarterly disaster recovery simulations moving forward.

6. Next Scheduled Test

• Type: Simulated database failure and recovery
• Planned Date: [e.g., August 1, 2025]
• Responsible Team: Engineering / DevOps