SQL Server Infrastructure & High Availability: RAID, Backup & DR
Prerequisites: Understanding of SQL Server basics. This article focuses on DBA/Operations topics.
When your database serves millions of users, you need reliability, redundancy, and recovery. This is the infrastructure layer.
Part A: RAID — Protecting Against Disk Failure
1. What is RAID?
RAID (Redundant Array of Independent Disks) = Multiple disks working together for speed and/or safety.
2. RAID Levels Comparison
| RAID | How it Works | Min Disks | Capacity | Speed | Fault Tolerance | Use Case |
|---|---|---|---|---|---|---|
| RAID 0 | Striping only | 2 | 100% | ⚡⚡⚡ Fast | ❌ None | Temp/scratch |
| RAID 1 | Mirroring | 2 | 50% | ⚡ Normal | ✅ 1 disk | OS drive |
| RAID 5 | Striping + parity | 3 | n-1 disks | ⚡⚡ Good | ✅ 1 disk | General use |
| RAID 6 | Striping + double parity | 4 | n-2 disks | ⚡ Medium | ✅ 2 disks | Critical data, NAS |
| RAID 10 | Mirror + Stripe | 4 | 50% | ⚡⚡⚡ Fast | ✅ 1 per mirror | SQL Server |
| RAID 50 | RAID 5 + Striping | 6 | Varies | ⚡⚡ Good | ✅ 1 per RAID 5 | Large storage arrays |
| RAID 60 | RAID 6 + Striping | 8 | Varies | ⚡ Medium | ✅ 2 per RAID 6 | Enterprise storage |
3. RAID Visual Explanation
RAID 0 (Striping) — Speed, No Safety
- ✅ Read/Write speed: 2x (parallel access)
- ❌ Any disk fails = ALL DATA LOST
RAID 1 (Mirroring) — Safety, Less Space
- ✅ One disk fails? The other has everything
- ❌ Lose 50% capacity
RAID 5 (Striping + Parity) — Balanced
- ✅ Any ONE disk can fail, data survives
- ⚠️ Rebuild is slow and stresses remaining disks
- ⚠️ Write Penalty: Slower writes due to parity calculation. Not recommended for write-heavy OLTP systems.
RAID 6 (Double Parity) — Extra Protection
- ✅ Any TWO disks can fail simultaneously, data survives
- ⚠️ Write performance lower than RAID 5 (double parity calculation)
- 📦 Popular for NAS and backup storage
RAID 10 (Mirror + Stripe) — Best for SQL Server
RAID 10 Advantages:
- ✅ Fast reads AND writes
- ✅ Can lose 1 disk per mirror
- ❌ Need minimum 4 disks, 50% capacity
Microsoft recommends RAID 10 for SQL Server data files
4. SQL Server & RAID Best Practices
| File Type | Recommended RAID | Reason |
|---|---|---|
| Data files (.mdf) | RAID 10 | Heavy random I/O |
| Log files (.ldf) | RAID 1 or RAID 10 | Sequential writes, critical |
| TempDB | RAID 10 or RAID 0* | High I/O, can recreate |
| Backups | RAID 5 or RAID 6 | Large sequential reads |
*RAID 0 only if tempdb can be recreated on restart
☁️ Cloud Context: In cloud environments (AWS EBS, Azure Managed Disk), RAID is typically handled at the infrastructure layer. You focus on selecting IOPS and Throughput tiers instead of managing disk arrays directly.
Part B: Backup Strategies
5. Why Backup?
RAID protects against disk failure, but NOT against:
- ❌ Accidental DELETE
- ❌ Ransomware encryption
- ❌ Application bugs corrupting data
- ❌ Natural disasters
Backup = Your last line of defense
6. Backup Types
| Type | Contains | Size | Restore Need |
|---|---|---|---|
| Full | Entire database | Large | Just this backup |
| Differential | Changes since last Full | Medium | Full + this Diff |
| Transaction Log | Changes since last Log | Small | Full + all Logs |
7. Backup Commands
-- 0. Integrity Check (Critical!)
-- Check for corruption BEFORE backing up
DBCC CHECKDB(MyDB) WITH NO_INFOMSGS;
-- 1. Full backup:
BACKUP DATABASE MyDB
TO DISK = 'D:\Backups\MyDB_Full.bak'
WITH COMPRESSION, INIT, CHECKSUM;
-- CHECKSUM ensures page integrity during backup
-- 2. Differential backup:
BACKUP DATABASE MyDB
TO DISK = 'D:\Backups\MyDB_Diff.bak'
WITH DIFFERENTIAL, COMPRESSION, CHECKSUM;
-- 3. Transaction log backup:
-- IMPORTANT: This truncates the log, allowing space reuse
BACKUP LOG MyDB
TO DISK = 'D:\Backups\MyDB_Log.trn'
WITH COMPRESSION;⚠️ Why DBCC CHECKDB First?
If you backup a corrupted database, your backup is also corrupted. Run
DBCC CHECKDBregularly (weekly minimum) to catch corruption early.
📋 Log Truncation Explained
In Full Recovery Model, the transaction log grows forever until you back it up.
BACKUP LOGmarks old log records as reusable (“truncates” the log). This is why Full Recovery requires regular log backups — or your disk fills up!
8. Common Backup Strategies
Simple Strategy (Small DBs)
Sunday: Full
Monday-Saturday: Full (daily)Standard Strategy (Most DBs)
Sunday 12AM: Full
Mon-Sat 12AM: Differential
Every hour: Transaction LogEnterprise Strategy (Mission-Critical)
Sunday 12AM: Full
Daily 12AM: Differential
Every 15 minutes: Transaction Log
Copy to offsite storage: Daily9. Recovery Models
| Model | Log Backup Possible? | Point-in-Time Restore? | Use Case |
|---|---|---|---|
| Simple | ❌ No | ❌ No | Dev/Test |
| Full | ✅ Yes | ✅ Yes | Production |
| Bulk-Logged | ✅ Yes | ⚠️ Limited | ETL periods |
-- Check recovery model:
SELECT name, recovery_model_desc FROM sys.databases;
-- Change recovery model:
ALTER DATABASE MyDB SET RECOVERY FULL;10. Point-in-Time Recovery
-- Restore to specific time (requires FULL recovery model):
RESTORE DATABASE MyDB FROM DISK = 'MyDB_Full.bak' WITH NORECOVERY;
RESTORE LOG MyDB FROM DISK = 'MyDB_Log1.trn' WITH NORECOVERY;
RESTORE LOG MyDB FROM DISK = 'MyDB_Log2.trn'
WITH STOPAT = '2024-03-15 14:30:00', RECOVERY;Part C: Replication — Keeping Copies in Sync
11. Types of Replication
| Type | How it Works | Latency | Use Case |
|---|---|---|---|
| Snapshot | Periodic full copy | High (minutes-hours) | Reporting, rarely changed |
| Transactional | Stream changes in near-real-time | Low (seconds) | Read replicas |
| Merge | Bi-directional sync | Medium | Mobile/disconnected |
12. Transactional Replication Flow
Part D: Always On Availability Groups — Enterprise HA
13. What is Always On AG?
Always On Availability Groups = Multiple database replicas with automatic failover.
14. Sync vs Async Mode
| Mode | Commit Behavior | Data Loss on Failover | Latency | Use Case |
|---|---|---|---|---|
| Synchronous | Wait for secondary to confirm | ❌ None (zero data loss) | Higher | Same datacenter |
| Asynchronous | Don’t wait | ⚠️ Possible | Lower | DR site (different city) |
15. Failover Types
| Type | Trigger | Downtime |
|---|---|---|
| Automatic | Primary failure detected | Seconds (30-60s typical) |
| Manual Planned | DBA initiates | Seconds |
| Forced | Primary unreachable | Potential data loss |
15.1 Always On AG vs Failover Cluster Instance (FCI)
| Feature | Always On AG | Failover Cluster Instance |
|---|---|---|
| Architecture | Shared-Nothing | Shared-Storage |
| Data Storage | Each node has own copy | Single shared disk |
| Storage Cost | Higher (N copies) | Lower (1 copy) |
| Failover Scope | Per-database | Entire instance |
| Read from Secondary | ✅ Yes | ❌ No |
| Requires SAN | ❌ No | ✅ Yes |
When to choose?
- AG: Modern choice. Better for DR across datacenters, readable secondaries, per-database control.
- FCI: Legacy or when shared storage (SAN) already exists. Simpler but less flexible.
Part E: Disaster Recovery (DR)
16. Key Metrics: RTO and RPO
| Metric | Meaning | Question |
|---|---|---|
| RTO | Recovery Time Objective | How fast must we recover? |
| RPO | Recovery Point Objective | How much data can we lose? |
| Scenario | RTO | RPO | Solution |
|---|---|---|---|
| E-commerce | 1 hour | 5 min | Always On + Log shipping |
| Banking | 0 | 0 | Synchronous Always On |
| Archive system | 24 hours | 1 day | Daily backup to cloud |
17. DR Strategy Tiers
18. Log Shipping — Simple DR
-- On Primary:
BACKUP LOG MyDB TO DISK = '\\FileShare\Logs\MyDB_Log.trn';
-- Copy job runs every 15 minutes to secondary
-- On Secondary:
RESTORE LOG MyDB FROM DISK = '\\FileShare\Logs\MyDB_Log.trn'
WITH STANDBY = 'MyDB_Undo.ldf';Summary: When to Use What
Technology Selection
| Requirement | Solution |
|---|---|
| Protect against disk failure | RAID 10 |
| Recover from accidental DELETE | Backup + Point-in-time restore |
| Read replicas for reporting | Transactional replication or AG |
| Automatic failover (same DC) | Always On AG (synchronous) |
| DR site in another city | Always On AG (async) or Log Shipping |
| Zero data loss requirement | Synchronous Always On + SAN replication |
Cost vs Protection
| Level | Components | Cost |
|---|---|---|
| Basic | RAID + Daily backup | $ |
| Standard | + Differential + Log backups | $$ |
| Professional | + Replication or Log Shipping | $$$ |
| Enterprise | + Always On AG + DR site | $$$$ |
| Mission Critical | + Active-Active + SAN replication | $$$$$ |
Quick Reference: Backup Strategy
Small DB (< 10 GB):
└── Full backup daily
Medium DB (10-100 GB):
└── Full weekly + Diff daily + Log hourly
Large DB (100 GB+):
└── Full weekly + Diff daily + Log every 15 min
Critical DB:
└── Full weekly + Diff daily + Log every 5 min + Offsite copy💡 Practice Questions
Conceptual
-
Compare RAID 5, RAID 6, and RAID 10. Which would you use for SQL Server data files and why?
-
Explain the difference between Full, Differential, and Transaction Log backups. How do they work together?
-
What is the difference between Replication and Always On Availability Groups?
-
Define RPO and RTO. How do they influence your backup/DR strategy?
Hands-on
-- Design a backup strategy for a 50GB database with RPO of 15 minutes and RTO of 1 hour.
-- List: backup types, frequencies, and retention period.💡 View Answer
Backup Strategy:
- Full backup: Weekly (Sunday night)
- Differential backup: Daily (every night)
- Transaction log backup: Every 15 minutes
Retention:
- Keep 4 weeks of full backups
- Keep 7 days of differential backups
- Keep 24 hours of log backups
Recovery scenario (RTO < 1 hour):
- Restore latest full backup (~20 min for 50GB)
- Restore latest differential (~10 min)
- Restore transaction logs up to failure point (~10 min)
- Total: ~40 minutes ✅
Scenario
-
Disaster Planning: Your company’s main datacenter loses power. You have an Always On Availability Group with a secondary in another city. Walk through the failover process.
-
Capacity Planning: A database is growing 10GB per month. Current RAID 10 array is 80% full. What would you recommend?