1 / 38

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)

Ava
Download Presentation

SQL Server Data Protection and High Availability

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  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@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

  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

  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_DATA_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@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

More Related