This project focuses on the design and implementation of a complete Cloud Backup & Disaster Recovery system for a university management information system, built specifically using Azure SQL Database.
The main objectives are:
- Creating and inserting records and indexes
- Disaster Recovery capability
- Secure and reliable backup strategy
- Monitoring and alerting infrastructure
The system includes the following components:
- Primary Azure SQL Database (production)
- Geo-replicated Secondary Database (DR region)
- Azure Blob Storage for backups
- Azure Monitor & Log Analytics Workspace for monitoring
📌 Architecture Diagram:
A relational database schema was designed with 10 tables including proper primary and foreign key relationships.
📌 ER diagram:
- Created Azure Free Tier subscription
- Created Resource Group
- Created SQL Server (logical server)
- Deployed Azure SQL Database (Basic tier)
- Configured firewall rules (IP whitelisting)
- Connected using SSMS / Azure Data Studio
- Configured Azure Monitor metrics
- Tracked DTU / vCore usage
- Monitored active connections
📌 Monitoring Dashboard (Baseline):
The Azure SQL Database provides automatic backups:
- Full backups
- Differential backups
- Transaction log backups
Retention policies:
- Short-term retention
- Long-term retention
📌 Backup Configuration:
- Exported database as
.bacpacfile - Stored backup in azure blob storage
- Verified file in storage container
📌 Blob Storage:
- Created a secondary server in a different Azure region
- Configured active geo-replication
- Monitored replication status and lag
📌 Geo-Replication Topology:
➱This architecture represents a geo-replication setup where an application interacts with a primary database responsible for all read and write operations, while a secondary database in a different region is asynchronously synchronized as a read-only replica. The secondary database is used for disaster recovery and can take over in case of primary failure, improving system availability and reducing downtime through failover capability.
➱RPO: Near-zero (typically < 1 minute) due to asynchronous replication between primary and secondary regions.
➱RTO: 1–5 minutes due to fast failover capability to the secondary region. RPO and RTO are not fixed values but depend on the Azure SQL Database geo-replication configuration; typically RPO is under 1 minute and RTO is between 1–5 minutes. This ensures minimal data loss and fast recovery in case of failure.
📌 Disaster Recovery Architecture:
- Simulated incident using accidental DELETE/UPDATE operations
- Restored database to a previous safe timestamp
- Verified data integrity using SQL queries
📌 Incident Example:
- In this incident example, a database table named "Grades" was accidentally dropped, causing an issue with key distribution. The table was restored using point-in-time restoration (PITR).
📌 Restore Process:
The database was restored using Azure SQL Database's Point-in-Time Restore feature.
Configuration:
- Source Database: UniversityDB
- Restore Point: Before the incident
- Restored Database: UniversityDB-Restore
📌 Verification
After the restoration completed successfully, SQL queries confirmed that all deleted grades records had been recovered.
Verification included:
- Record count comparison
- Query comparison before and after restore
- Configured Azure Monitor alerts
- Created Log Analytics Workspace
- Built dashboards for key performance metrics
📌 Monitoring Dashboard:
📌 Alert Rules:
- CPU overworking alert which alerts admins if the CPU is usage is 80% or more so as to check the condition of the database
- Data Usage alert indicating excessive usage of data prompting the user to chech the database
This project was created by:
 Dimitra Dernektsi |
 Vladimiros Pozidis |
© 2026 Dimitra Dernektsi & Vladimiros Pozidis. All rights reserved.