Transcript Slide 1

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: or [email protected]
Overview and Agenda
Overview of Data Protection and HA
Backup and Recovery
Database Mirroring
Database Snapshots
Clustering Overview
Data Protection and HA Goals
Minimize data loss
Minimize costs
Minimize performance overhead
Simplify implementation and
• 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
• 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
Restore / Recovery Example
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
• Transact-SQL
• 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
– 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
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
– 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
• 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
– 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
– 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
• 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
– Configure Mirroring endpoints and security
– Enable Mirroring
Implementing Database
• Configure Database Mirroring Security
– Can include Witness Server
– Configures Mirroring Endpoints
– Service Accounts
• SQL Server Management Studio
– Allows starting, stopping, and fail-over
Database Mirroring Commands
• ALTER DATABASE can be used to administer
database mirroring
• Setting the Mirroring Mode:
– Asynchronous (High Performance):
– Synchronous
• High Availability: SET SAFETY FULL
• High Protection: Use SET WITNESS
• 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
Monitoring Database Mirroring
• SQL Server Management Studio
– Database Mirroring Monitor
• System Views / Stored Procedures
• 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
Point-in-Time Views / Historical Views
Protecting against user error
• Should not be used as a backup or highavailability 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:
(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
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 (
– E-Mail: [email protected]
– 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