Skip to content

SQL Server : High Availability & Disaster Recovery

Jump to bottom
Sandesh Kota edited this page Mar 8, 2018 · 18 revisions

[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
      • RESTORE DATABASE [db] FROM DISK = ['full recovery path'] WITH NORECOVERY; GO
      • RESTORE LOG [db] FROM DISK = ['full recovery path'] WITH NORECOVERY [, STOPAT='08/02/2017 23:25:01']; GO
  • Bulk-Logged Recovery
    • Offers better performance and a smaller log footprint during bulk load processes
    • Recovery to the point of each log backup is supported offering small recovery windows
    • Point in time recovery is not supported
    • Can be switched between Full & Bulk Recovery model
  • File Based Backups
    • Supports partial database restores
    • Helpful in scenarios where only a single file or filegroup was damaged or lost as it avoids the time to do a complete db restore
    • Managing file based backups and restore is a complex topic

Maintenance Plans

  • Helps to configure the Backups (scheduling)
  • DB Server > Management > Maintenance Plans

Managing Backups

  • SQL Agent / Custom TSQL Scripts
  • Ola Hallengren's Scripts *
  • 3rd Party Management Tools Ex: RedGate, Idera, Dell and others
  • Store backups off site
  • Test your backups regularly. Assume that any backup that hasn't been tested is faulty
    • Option :: Backup with Checksum > restore to DB > And run CheckDB?

Disaster Recovery from Backups:

  • Simple Recovery : can take a very long time to restore large databases
  • Full Recovery : can take even longer..unless you have a DR Server
    • Full Backup > Apply to DR Server
    • Log backup > Apply to DR Server
    • Log backup > Apply to DR Server... (goes on at regular intervals)
    • In case of Disaster, let the DR Server serve the application
    • Can be automated easily: Log Shipping

Failover Clustering

  • Windows Failover Clustering

Database Log shipping - Database level - DB corrupt Failover Clustering - Server Level - Server failure

*suggested

Clone this wiki locally