390 likes | 408 Views
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)
E N D
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: • Full backup • Latest differential (if any) • Chain of transaction log backups • NO RECOVERY / WITH RECOVERY
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)
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