SQL Server : High Availability & Disaster Recovery

[HA] High Availability -> minimum downtime [DR] Disaster Recovery -> Quick time to recover

SQL Server Transaction Log:

Purpose:

• Critical to the integrity of DB
• Critical for the mission of HA/DR
• Critical for the operation of HA/DR

Transaction: An individual unit of work (ACID - Atomic, Consistent, Isolated, Durable)

DB Recovery:

• Recovery is a process by which SQL Server ensures a DB is brought online in a consistent state after:
  • Crash Recovery
  • Restore from Backup

Recovery Types: [Recovery Model] in SQL Server

• Simple Recovery
  • Log is mainly used for crash recovery
  • Transaction logs are marked inactive after checkpoint
  • Supports Full Backup & Differential Backup
  • Full Backup :: All data pages copied + Tail of log backup (transaction log data - at the time of backup)
  • Differential Backup :: Only Changed data pages are copied (after full data backup)
  • Easy to Manage
  • Loss of all transactions that have occurred after last Full/Diff backup
• Full Recovery
  • Log supports crash recovery and log backups
  • After Full Backup, transaction logs are backed up regularly (at a certain time). The transaction logs are marked active until it is backed up
  • Allows us to restore DB to a specific point in time OR to a Specific LSN (Log Sequence Number)
  • As backups are regular, smaller data loss windows
  • Best: Full backups weekly, Differential backups nightly, Log backups every 15 minutes (backup files are moved off-site)
  • Point in Time Restores
    • BACKUP DATABASE [db] TO DISK = ['full recovery path']; GO
    • BACKUP LOG [db] TO DISK = ['log backup path']; GO
• Bulk Logged Recovery