SQL Server Data Protection and High Availability

							SQL Server Data Protection
  and High Availability
         Anil Desai
         Speaker Information
• Anil Desai
  – Independent consultant (Austin, TX)
  – Author of several SQL Server books
  – Instructor, “Implementing and Managing SQL
    Server 2005” (Keystone Learning)
  – Info: http://AnilDesai.net or Anil@AnilDesai.net
        Overview and Agenda
•   Overview of Data Protection and HA
•   Backup and Recovery
•   Log-Shipping
•   Database Mirroring
•   Database Snapshots
•   Clustering Overview
    Data Protection and HA Goals
• Minimize data loss
• Minimize costs
• Minimize performance overhead
• Simplify implementation and
  administration
• Allow fast fail-over
• Implementing transparency for end-users
      Implementing Backups
• Features
  – Minimal performance hit
  – Flexible options
• Purposes
  – Protecting against user error
  – Protecting against hardware failures
  – Disaster Recovery, Security, Archival
  – Regulatory Compliance
       Planning for Backups
• Backup plan should be based on recovery
  requirements
• Factors:
  – Type of data / workload
  – Acceptable downtime
  – Acceptable data loss
  – Performance requirements
  – Administration overhead (manageability)
   Database Recovery Models
• Balances performance vs. recoverability
• Recovery Model Options:
  – Full
     • All transactions are logged
  – Simple
     • Does not allow for point-in-time recovery
     • Automatic log truncation
  – Bulk-Logged
     • Bulk operations are not logged
             Backup Types
•   Full Backups
•   Differential Backups
•   Transaction Log Backups
•   Other Types:
    – Copy-Only Backups
    – Partial Backups
    – File Backups
  Restore / Recovery Process
• Restore order:
  1. Full backup
  2. Latest differential (if any)
  3. Chain of transaction log backups
    • NO RECOVERY / WITH RECOVERY
   Restore / Recovery Example
• Backups:                           • Restore from Failure on
   – Every Sunday (2:00am):            Tuesday @ 2:25pm:
       •   Full backups                 1.   Create a tail-log backup
   – Every Evening (7:00pm):            2.   Full backup from Sunday
       •   Differential Backups
                                        3.   Differential backup from Tuesday
   – Every Hour (9:00am – 5:00pm):
       •   Transaction Log Backups      4.   All transaction logs from
                                             Tuesday (9:00am – 2:00pm)
                                        5.   Restore the tail-log backup (if
                                             available)
                                        6.   Bring the database online
        Restore vs. Recovery
• Restore
  – Copies data from backup media
  – Applies committed and uncommitted
    transactions (“roll forward”)
  – Uses NORECOVERY clause (DB = offline)
• Recovery
  – Rolls backup uncommitted transactions (“undo”)
  – Brings database online
    Database Restore Options
• Full database restore
  – Point-in-Time recovery / STOPAT Marker
• File restore
• Page-level restore
• Partial / Piece-meal restore (Ent. Ed.)
  – Restore read-write filegroups (PARTIAL)
  – Bring database online
  – Restore read-only filegroups
      Backup and Recovery
           Commands
• Transact-SQL
  – BACKUP DATABASE
  – RESTORE DATABASE / RESTORE LOG
• Database Maintenance Plan Wizard
• Copy Database Wizard
• Attach / Detach databases
  Backup and Recovery Notes
• Backup history is stored in msdb
• Can use media sets and families
• Security: May need to recreate logins,
  certificates, etc.
• Can backup to UNC shares
               Log Shipping
• Maintains a “warm” standby server
    – Update interval can be configured
    – Requires Full or bulk-logged recovery model
•   Relies on backup/recovery operations
•   No special hardware requirements
•   Can have multiple secondary databases
•   Roles:
    – Primary, Secondary, Monitor
        Log Shipping Details
• Continuous restore of transaction logs
• Process:
  – Transaction log backup is created on the
    primary server
  – File is copied to the secondary server
     • Log is restored on the secondary server
        – NORECOVERY (database remains offline)
        – STANDBY (database is read-only)
    Implementing Log Shipping
• Setting up log shipping:          • Log Shipping Jobs:
   – Backup the primary                –   Backup
     database                          –   Copy
   – Restore the backup on the         –   Restore
     secondary server(s)               –   Alert (if using a Monitor
      • NORECOVERY: Users cannot
        connect
                                           Server)
      • STANDBY: Allows read-only
        access
   – Implement log shipping
           Log Shipping Tool
• Setup from SQL Server Mgmt. Studio:
  – Prepare the Primary Database
  – Implement a Monitoring Server
  – Initialize the Secondary Database
  – Copy Files
  – Security Settings / Proxy Accounts
  – Can schedule the frequency of backup and recovery
    operations
  – Create all required jobs
     Managing Log Shipping
• Avoid manual backups (except copy-only)
• Log-Shipping information (msdb database)
  – Primary Server (backup details)
  – Secondary Server (copy and restore details)
  – Monitor Server (info about both)
• Transaction Log Shipping Report (SSMS)
        Performing a Fail-Over
• Steps for failing-over:
   – Disable all log shipping jobs
   – Primary Database: Backup and copy transaction log
     files (if possible)
   – Restore transaction log backups on the secondary
     server with NORECOVERY
      • Use RECOVERY for last restore
   – Re-establish log-shipping (optional)
• Swapping primary and secondary roles
   – Can be performed by enabling/disabling jobs
     Understanding Database
             Mirroring
• Maintains a “hot” standby database
  – Synchronization is managed automatically
  – Transactions are sent from a Principal to a Mirror
    database instance
• Can perform quick fail-over
  – Can be automatic or manual
  – Clients are automatically redirected
• Works at the database level
  – Multiple mirrored pairs per server are possible
      Database Server Roles
• Principal Database
  – Active, Read/Write database
  – Requires the full recovery model
• Mirror Database
  – Must be on a separate SQL Server instance
  – Set in “Restoring” state
  – Can use snapshots to allow read-only access
• Witness (optional)
  – Used for establishing a quorum during automatic
    failover
  – Not recommended for High-Protection mode
   Database Mirroring Support
• SQL Server 2005 Standard and Enterprise Ed.
  – Witness can be SQL Server 2005 Workgroup or
    Express Editions
  – For production, should use SP1 or later

• For SQL Server 2005 RTM:
  – Must enable Trace flag 1400
  – -T1400 startup parameter
  – SQL Server Configuration Manager  Advanced
     Startup Parameters
    Database Mirroring Modes
• Asynchronous (High Performance)
  – Principal transactions commit without waiting for
    mirror
  – Some transactions may be lost
• Synchronous
  – Transactions must be committed at mirror
  – Ensures no data loss
  – High Protection
     • No Witness server / manual fail-over
  – High Availability
     • Uses a Witness server; automatic fail-over
      Implementing Database
             Mirroring
• Database Mirror Pairs are independent
  – Each set requires own ports
  – Can use different modes
  – One server can serve as principal, mirror, and
    witness for different mirroring pairs
• Steps:
  – Back up the Principle database
  – Restore the database on the Mirror instance with
    NORECOVERY
  – Configure Mirroring endpoints and security
  – Enable Mirroring
      Implementing Database
             Mirroring
• Configure Database Mirroring Security
  Wizard
  – Can include Witness Server
  – Configures Mirroring Endpoints
  – Service Accounts
• SQL Server Management Studio
  – Allows starting, stopping, and fail-over
    operations
Database Mirroring Commands
• ALTER DATABASE can be used to administer
  database mirroring
• Setting the Mirroring Mode:
  – Asynchronous (High Performance):
    • SET SAFETY OFF
  – Synchronous
    • High Availability: SET SAFETY FULL
    • High Protection: Use SET WITNESS
• Pausing: SET PARTNER SUSPEND/RESUME
• Cause fail-over: FAILOVER
         Forcing a Fail-Over
• Should be used in emergencies
  – Use when mirror is out-of-date
  – May cause data loss
  – If Witness is available, it must be able to
    contact the mirror server


• ALTER DATABASE … SET PARTNER
  FORCE_SERVICE_ALLOW_DATA_LOSS
 Monitoring Database Mirroring
• SQL Server Management Studio
  – Database Mirroring Monitor
• System Views / Stored Procedures
  –   Sys.Database_Mirroring
  –   Sys.Database_Mirroring_Endpoints
  –   Sys.DM_DB_Mirroring_Connections
  –   Sp_DbmMonitorResults
• Other options:
  – SQL Server Agent Alerts
  – Windows Event Logs
  – Windows System Monitor
        Database Snapshots
• Snapshots:
  – Creates a point-in-time view of a database
  – Multiple snapshots can be created
  – Quick to create / Requires minimal disk space
  – Snapshots are read-only
  – Databases can be reverted to a snapshot
  – Stored on same instance as database
• Can be accessed like a “regular” database
      Understanding Snapshots
• Available in the Enterprise Edition
• Supports all recovery models
• Typical Uses
  –   Reporting
  –   Point-in-Time Views / Historical Views
  –   Protecting against user error
  –   Testing
• Should not be used as a backup or high-
  availability solution
• Can be created on a database mirror
       Snapshot Architecture
• Initial snapshot creation
  – Generates an NTFS Sparse File
  – File is initially empty
• Snapshot maintenance
  – Monitors for changes to data pages
  – Uses copy-on-write method
  – Snapshot files will grow based on frequency
    of data modification
 Creating Database Snapshots
• Creating a new snapshot:
  CREATE DATABASE ON
    (NAME = LogicalName,
    FILENAME = PhysicalFilePath)
  AS SNAPSHOT OF SourceDatabase


• Recommendations:
  – Use a consistent naming scheme
  – Treat snapshots like read-only databases
        Managing Snapshots
• Dropping snapshots
  – Deletes sparse files
  – DROP DATABASE SnapshotName


• Reverting to a snapshot
  – Restores a database to the time of the snapshot
  – Snapshot and primary database will go offline
  – RESTORE DATABASE DatabaseName FROM
    DATABASE_SNAPSHOT = SnapshotName
          Monitoring Snapshots
• SQL Server Management Studio
   – Databases  Database Snapshots

• System Databases
   – Sys.Databases
   – Sys.Database_Files
   – Sys.Master_Files

• Viewing File Details
   – Windows Explorer / DIR command
      • File size vs. “Size on Disk”
   – fn_VirtualFileStats
         Clustering Overview
• SQL Server Fail-Over Clusters:
  – Provides automatic fail-over
  – Multiple nodes that work as a logical unit
  – Uses a shared-disk configuration
     • Does not protect against disk failures
• Requirements
  – Enterprise Ed. and specialized hardware
  – Distance limitations
• More difficult to implement and administer
 Data Protection and HA Summary
• Backup and Restore
  – Standard data protection
• Log-Shipping
  – Harder to implement, but more flexible
  – Manual fail-over
• Database Mirroring
  – Easy to implement and manage
  – Automatic fail-over
• Clustering
  – Custom hardware requirements
For More Information
 • Resources from Anil Desai
   – Web Site (http://AnilDesai.net)
   – E-Mail: Anil@AnilDesai.net
   – Keystone Learning Course: “Microsoft
     SQL Server 2005: Implementation and
     Maintenance (Exam 70-431)”
   – The Rational Guide to Managing
     Microsoft Virtual Server 2005
   – The Rational Guide to Scripting
     Microsoft Virtual Server 2005