SQL Server Data Protection and High Availability

1
SQL Server Data Protection and High Availability

• Anil Desai

2
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_at_AnilDesai.net

3
Overview and Agenda

• Overview of Data Protection and HA
• Backup and Recovery
• Log-Shipping
• Database Mirroring
• Database Snapshots
• Clustering Overview

4
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

5
Implementing Backups

• Features
• Minimal performance hit
• Flexible options
• Purposes
• Protecting against user error
• Protecting against hardware failures
• Disaster Recovery, Security, Archival
• Regulatory Compliance

6
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)

7
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

8
Backup Types

• Full Backups
• Differential Backups
• Transaction Log Backups
• Other Types
• Copy-Only Backups
• Partial Backups
• File Backups

9
Restore / Recovery Process

• Restore order
• Full backup
• Latest differential (if any)
• Chain of transaction log backups
• NO RECOVERY / WITH RECOVERY

10
Restore / Recovery Example

• Backups
• Every Sunday (200am)
• Full backups
• Every Evening (700pm)
• Differential Backups
• Every Hour (900am 500pm)
• Transaction Log Backups
• Restore from Failure on Tuesday _at_ 225pm
• Create a tail-log backup
• Full backup from Sunday
• Differential backup from Tuesday
• All transaction logs from Tuesday (900am
  200pm)

11
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

12
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

13
Backup and Recovery Commands

• Transact-SQL
• BACKUP DATABASE
• RESTORE DATABASE / RESTORE LOG
• Database Maintenance Plan Wizard
• Copy Database Wizard
• Attach / Detach databases

14
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

15
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

16
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)

17
Implementing Log Shipping

• Setting up log shipping
• Backup the primary database
• Restore the backup on the secondary server(s)
• NORECOVERY Users cannot connect
• STANDBY Allows read-only access
• Implement log shipping
• Log Shipping Jobs
• Backup
• Copy
• Restore
• Alert (if using a Monitor Server)

18
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

19
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)

20
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

21
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

22
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

23
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

24
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

25
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

26
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

27
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

28
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_D
  ATA_LOSS

29
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

30
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

31
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

32
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

33
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

34
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

35
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

36
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

37
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

38
For More Information

• Resources from Anil Desai
• Web Site (http//AnilDesai.net)
• E-Mail Anil_at_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