SQL Server Data Protection and High Availability Anil

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: http: //Anil. Desai. net or Anil@Anil. Desai. 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: 1. Full backup 2. Latest differential (if any) 3. Chain of transaction log backups • NO RECOVERY / WITH RECOVERY

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

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 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 SP 1 or later • For SQL Server 2005 RTM: – Must enable Trace flag 1400 – -T 1400 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_Endpoints – Sys. DM_DB_Mirroring_Connections – Sp_Dbm. Monitor. Results • 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 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: CREATE DATABASE ON (NAME = Logical. Name, FILENAME = Physical. File. Path) AS SNAPSHOT OF Source. Database • Recommendations: – Use a consistent naming scheme – Treat snapshots like read-only databases

Managing Snapshots • Dropping snapshots – Deletes sparse files – DROP DATABASE Snapshot. Name • Reverting to a snapshot – Restores a database to the time of the snapshot – Snapshot and primary database will go offline – RESTORE DATABASE Database. Name FROM DATABASE_SNAPSHOT = Snapshot. Name

Monitoring Snapshots • SQL Server Management Studio – Databases Database Snapshots • System Databases – Sys. Database_Files – Sys. Master_Files • Viewing File Details – Windows Explorer / DIR command • File size vs. “Size on Disk” – fn_Virtual. File. Stats

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: //Anil. Desai. net) – E-Mail: Anil@Anil. Desai. 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