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