High Availability with SQL 2005 זמינות יתרה והמשך פעילות עסקית

1. High Availability with SQL 2005זמינות יתרה והמשך פעילות עסקית Assaf Fraenkel Principal Consultant Microsoft Consulting Services

2. Availability “Stack” • Majority of downtime is ascribed to operators • Application Software can have dramatic impact on fault tolerance • System Software can help at all levels • Hardware is very reliable at this point Operators Application Software System Software Hardware

3. Barriers To Availability • SS 2005 gives you greatly improved tools to overcome these barriers • Database Server Failure or Disaster • User or Application Error • Data Access Concurrency Limitations • Database Maintenance and Operations • Upgrades Only some are addressable by DBMS technologyBe sure to consider people, planning, and procedures If Time Permit

4. Availability SolutionsHow Do You Compare the Alternatives (1/2) • Time to Fail Over • Automatic or Manual Detection • Automatic or Manual Failover • Number of Failures it can survive • Data Currency / Loss

5. Availability SolutionsHow Do You Compare the Alternatives (2/2) • Granularity of Data: Instance, Database, Table, Row • Cost of redundant system(s), additional hardware, additional management • Complexity • Data Consistency • Transparency to clients

6. Availability Technologies in SQL Server 2005

7. Availability Technologies in SQL Server 2005 • Basic: No failover and a potential data loss • Backup / restore • Detach / copy / attach • Better: Manual failover - potential data loss • Peer-to-Peer Replication • Log Shipping • Database Mirroring - high performance mode • Best: Automatic failover - zero data loss • Database Mirroring - high availability mode • Failover Clustering

8. Backup / Restore Detach / Copy / Attach Cold Standby SolutionsBackup / Restore and Detach / Copy / Attach

9. Cold Standby SolutionsBackup / Restore and Detach / Copy / Attach • Both Provide • Manual detection and failover • Potential for some work loss • Whole-database scope • Standard servers • Limited reporting on standby • Duplicate copy of database • Client must know where to re-connect • Slowest failover – Most downtime

10. Backup / Restore What’s New in SQL Server 2005 • Fine-Grained Online Repair • Online Restore – Database remains online; Only data being restored is offline • Piecemeal Restore – Online restore of filegroups by priority • Page-level Restore – Can restore individual pages to repair errors found by page checksum or torn pages • Instant File Initialization – Skips file zeroing, fast DB create / restore • Data backups do not block log backups • Restore read-only filegroups without applying logs • Backup / Restore includes FullText data

11. Log Shipping Replication Warm Standby SolutionsReplication and Log Shipping

12. Replication Warm Standby SolutionsReplication • Multiple copies and Manual failover • Primarily used where availability is required in conjunction with scale out of read activity • Failover possible; a custom solution • Not limited to entire database; Can define subset of source database or tables • Copy of database is continuously accessible for read activity • Latency between source and copy can be as low as seconds

13. Peer-to-Peer Transactional Replication Peer-to-Peer Transactional Replication • All participants are peers • Schema is identical on all sites • Publish the updates made on ‘their’ data • Subscribe to others to pick up their changes • No hierarchy as in ‘normal’ transactional replication • A given set of data can be updated at only one site at a time • Data ‘ownership’ is purely logical; doesn’t prevent conflicts • SQL Server prevents a change from round-tripping • Enables load-balancing and high availability • Warm/hot standby - Small possibility of data loss

14. Log Shipping Warm Standby SolutionsLog Shipping • Multiple copies and Manual failover • Basic idea: Backup, Copy & Restore Log will always be supported • But no more investment in the scripts • Database scope • Database accessible but read-only • Users must exit for next log to be applied • Data backups no longer block log backups

15. Failover Cluster Database Mirroring Hot Standby Failover SolutionsFailover Clustering and Database Mirroring • Both Provide • Automatic detection • Automatic, fast failover • Manual failover • Transparent client redirect • Zero work loss

16. Failover Cluster * Inst1 Failover Clustering Microsoft Server Cluster • Zero work loss, zero impact on throughput • Instance Failover –instance fails as a unit • Single copy of instance databases • Standby is not available for reporting, queries, etc. • May support other instances

17. Multiple Active Instances N+1: N Active, 1 Inactive Instances N+I: N Active, I Inactive Instances * Inst1 Inst2 * Failover Clustering SQL Server 2005 • More nodes • Match operating system limits • Supported scenarios: Multiple Active Instances, N+1, N+I • Two-node Failover Clustering is available in SQL Server 2005 Standard Edition • Unattended setup • Support for mounted volumes (Mount Points) • All SQL Server data services participate • Database Engine, SQL Server Agent, Full-Text Search • Analysis Services supports multiple instances

18. Database Mirroring Database MirroringNew for SQL Server 2005 • Hot Standby • Database Failover • Very fast failover • Less than five seconds in most cases • Zero data loss • Automatic or manual failover • Automatic re-sync after failover • Automatic, transparent client redirect

19. Witness Mirror Database Mirroring • Fault Tolerant Virtual Database Clients Principal

20. Witness Witness and Quorum • Sole purpose of the Witness is to provide automatic failover • To survive the loss of one server you must have at least three • Witness is an instance of SQL Server 2005 • Perhaps even SQL Server Express on WinXP • Consumes very little resources • Can be witness for multiple sessions

21. 4 >3 >2 3 1 5 2 2 Commit Application SQL Server SQL Server Database Mirroring How it works Mirror is always redoing – it remains current Witness Principal Mirror Log Data Log Data

22. Safety / Performance • There is a trade-off between performance and safety • Database Mirroring has two safety levels • FULL – commit when logged on Mirror • Allows automatic failover • No data loss • OFF – commit when logged on Principal • System does its best to keep up • Prevents failover; to make mirror available • Must ‘force’ service • Or terminate Database Mirroring session

23. Witness Mirror Database Mirroring High Protection Mode High Performance Mode • High Availability Mode Clients X Principal

24. Transparent Client Redirect • No changes to application code • Client automatically redirected if session is dropped • Client library is aware of Principal and Mirror servers • Upon initial connect to Principal, library caches Mirror name • When client attempts to reconnect • If Principal is available, connects • If not, client library automatically redirects connection to Mirror

25. Database Mirroring Database Mirroring • Impact to transaction throughput • Depending on environment • Depending on workload • Zero to minimal • Hardware • Works with standard computers, storage, and networks • No shared storage components, virtually no distance limitations • My Tips: 1GB Network, Disk for the Log

26. Geographical Failover Cluster Geographically-Dispersed Clusters and Storage-level Replication • Solutions from many Microsoft partners replicate the local I/Os on a remote system • Hardware and software methods • Synchronous and asynchronous solutions • Solutions must meet Core I/O Requirements • Many solutions use MSCS to provide automatic failover • Other solutions are primarily to duplicate the data at a remote site, often with an independent SQL Server • Similar to Log Shipping or Detach / Attach

27. Replication Failover Solutions Complementary TechnologiesTechnologies can be Combined • Maximize availability for • Scale out • Offload primary data platform • Heavy reporting • Mobile/disconnected users • Autonomous business units that share data • Maximize availability of critical systems • Designed for failover • Fast, automatic • Zero data loss • Transactionally current • Masks planned and unplanned downtime

28. Complementary TechnologiesFailover Solution + Replication Example Fault-Tolerant Publisher and Distributor Publisher Distributor Subscribers

29. Failover Cluster Failover Cluster Combining HA Technologies • Principal Server can be a Failover Cluster • Failover to mirror will occur before failover within the cluster • So Principal will come back up as the Mirror • Mirror can be a Failover Cluster as well Principal Mirror

30. Barriers To AvailabilityAs addressed in SQL Server 2005 • Database Server Failure or Disaster ------------------------------------------------------------ • Application or User Error • Database Snapshots • Data Access Concurrency Limitations • Database Maintenance and Operations • Upgrades

31. Barriers to Availability I’m going to modify this data…right… People here! This job would be great if it weren’t for……the users …the staff …us

32. Database SnapshotNew in SQL Server 2005 • Database Snapshots allow recovery from user errors by allowing the database to go back in time • Works with • Single server • Database Mirroring • Failover Cluster • Does not work with Log Shipping secondary

33. 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 Database Snapshot How it really works CREATE DATABASE mydbSnap AS SNAPSHOT OF mydb USE mydb UPDATE (pages 4, 9, 10) mydb – Database Page 1 2 3 4 4 5 6 7 8 9 10 11 12 13 14 15 16 9 10 mydbSnap – Read-Only Database Snapshot USE mydbSnap SELECT (pages 4, 6, 9, 10, 14)

34. Database SnapshotTechnology • Extremely space efficient • Does not require a complete copy of the data • Shares unchanged pages of the database • Requires extra storage only for changed pages • Uses a “copy-on-write” mechanism • Database Snapshot may affect performance on the base database

35. Reporting on a Mirror • Use Database Snapshots on Mirror Database Mirroring OLTP Clients Witness Mirror Principal Snapshot2 at 2PM Snapshot1 at 1PM Reporting Clients

36. Barriers To AvailabilityAs addressed in SQL Server 2005 • Database Server Failure or Disaster • Application or User Error • Data Access Concurrency Limitations • Snapshot Isolation • Online Index Operations • Database Maintenance and Operations • Upgrades

37. Online Index Operations • Online Index Operations allow concurrent modification of the underlying table or index • Updates, Inserts, Deletes • Online Index Maintenance • Create, Rebuild, Drop • Index-based constraints (PrimaryKey, Unique) • Data definition language (DDL) is simple • Online/Offline are both supported • Updates incur some additional cost during an online index operation • Maintains old and new indexes

38. Barriers To AvailabilityAs addressed in SQL Server 2005 • Database Server Failure or Disaster • Application or User Error • Data Access Concurrency Limitations • Database Maintenance and Operations • Upgrades

39. Redo Undo Available Fast RecoveryRestart of a Database • SQL Server 2000 • Database is available after Undo completes • SQL Server 2005 • Database is available when Undo begins Redo Undo Available Time

40. Database Maintenance & Operations • Partial Availability • Database is available if primary filegroup is available • Online Restore • Restore while database remains available • Works with all recovery models • Backup and Restore • Data backups don’t block log backups • Full-Text Catalog is backed up and restored as part of the database

41. More Operational Improvements… • Dedicated Administration Connection • Provides DBA access to server regardless of load • No server restart to kill a runaway session • More configuration is dynamic • No server restart for CPU affinity, AWE • Address Windowing Extensions (AWE) • Changes to physical size don’t require downtime • Dynamically configurable (Min / Max) • Dynamically adjusts to “hot-add” memory • AWE is available in Standard Edition (Win2003) • Instant file initialization • With appropriate security, can bypass zeroing • For create database, add file, file grow, restore

42. Barriers To AvailabilityAs addressed in SQL Server 2005 • Database Server Failure or Disaster • Application or User Error • Data Access Concurrency Limitations • Database Maintenance and Operations • Upgrades

43. Upgrade Enhancements • Software Upgrade • Re-architected – greatly reduces down time • Side-by-side installation • Resource database is pre-built • No maintenance is needed; new is substituted for old one • DBA can ignore the mssqlsystemresource.mdf and ldf • Phased • Engine and Databases (< 3 minutes) • Other components complete upgrade online after databases are available (Replication, Workbench, etc.) • Hardware Upgrade • Hot-add memory supported without server restart • Database Mirroring minimizes downtimes for other hardware upgrades, excluding disk

44. SQL Server 2005Capabilities by Edition

45. איך ממלאים משוב? ב - email בסוף כל יום ב -Beat Center מה מקבלים? חולצת Feel The Beat השתתפות בהגרלת כרטיסי טיסה מכשירי i-mate ועוד...(לממלאים משוב לכל יום) New York! New York!

46. The Prime Grill - Steakhouse הסטייקים הכשרים הטובים בניו- יורק ועוד במנהטן!! 60 east 49th st. New York, NY 2126929292 Assaf Fraenkel

47. הרצאות מומלצות בנושא HA • היום ב 11:30 עמי לוין בנושא Isolaion Level • מחר ב 8:30 מאיר דודאי בנושא replication

48. Summary – What is Availability?