Hide Notes 

Slide 1: HA cover Copyright 2009 – Database Architechs www.dbarchitechs.com SQL Saturday 2009 – Portland, Oregon
Slide 2: Paul Bertucci • Founder Database Architechs – www.dbarchitechs.com – Specializing in HA, Database Design, Data Architecture, Data Replication, and P&T for SQL Server, Sybase, DB2 and Oracle – Over 28+ years experience in Data Base industry • • • • • • • • • • Co-Author of SQL Server 2000 Unleashed! (SAMS) Co-Author of SQL Server 2005 Unleashed! (SAMS) Co-Author of SQL Server 2008 Unleashed! (SAMS) – Summer 2009 ! Co-Author of ADO.NET in 24 hours (SAMS) Author MS SQL Server High Availability (SAMS) Author Sybase Performance & Tuning Author Sybase Physical DB Design Veritas SQL Server Performance Series Former Chief Data Architect Symantec Corporation Current Chief Architect Autodesk Corporation pbertucci@dbarchitechs.com Copyright 2009 – Database Architechs www.dbarchitechs.com
Slide 3: Agenda What is High Availability? How do you assess your HA Requirements? What are the MS SQL Server related options for HA? How each option delivers HA… Performance and Tuning is critical too – SQL Shot! Q&A Copyright 2009 – Database Architechs www.dbarchitechs.com
Slide 4: Test 1. What is the quickest way to test if your SQL Server Clustering configuration is failing over properly? 2. What is the SQL Server feature in SQL Server 2005/2008 that replaces Log Shipping? Copyright 2009 – Database Architechs www.dbarchitechs.com
Slide 5: What is Availability? Application Availability Failure causes: - Human - Hardware - Software Uptime Planned Unplanned Downtime Downtime Recoverable Disaster Copyright 2009 – Database Architechs www.dbarchitechs.com
Slide 6: The cost of Un-Availability Airline Reservation Systems - $67K to $112K per hour ATM Service Fees - $12K to $17K per hour Brokerage (Retail) - $5.6M to $7.3M per hour What is your cost of downtime? Copyright 2009 – Database Architechs www.dbarchitechs.com
Slide 7: Across all layers of your systems Network Application Middleware Database Operating System HARDWARE Network Components Servers Disk Systems Memory Copyright 2009 – Database Architechs www.dbarchitechs.com
Slide 8: Availability across planned operation 100% Availability Goals Availability (%) 90% Feb 14-28 Starting Date Date of Failure 2/14/2008 2/28/2008 3/1/2008 4/15/2008 4/16/2008 4/20/2008 2/14/2008 Mar 1 – Apr 15 Days 15.00 46.00 5.00 Apr 16 – 20 TU 38.00 68.00 442.00 548.00 Avail % 99.82407 99.89734 93.86111 99.4234 Period 1 Period 2 Period 3 Overall Hours Minutes MBU (minutes) 24.00 60.00 21600.00 24.00 60.00 66240.00 24.00 60.00 7200.00 95040.00 4/20/2008 66.00 24.00 60.00 Copyright 2009 – Database Architechs www.dbarchitechs.com
Slide 9: Availability Continuum Characteristic Extreme Availability High Availability Near zero downtime! Availability Range (99.5% - 100%) Minimal downtime (95% - 99.4%) Standard Availability With some downtime tolerance (83% - 94%) Acceptable Availability Non-critical Applications (70%-82%) Marginal Availability Non-production Applications (up to 69%) Availability Range describes the percentage of time relative to the “planned” hours of operations 8,760 hours/year | 168 hours/week | 24 hours/day 525,600 minutes/year | 7,200 minutes/week | 1,440 minutes/day Copyright 2009 – Database Architechs www.dbarchitechs.com
Slide 10: Applications and Availability Zero Planned Downtime Extreme Availability High Availability ATM Standard Availability eCommerce 911 email HR Accounting Inventory Mgmt Acceptable Availability Marginal Availability Marketing Mailers Five 9’s (99.999%) ~ 6 minutes/year downtime Copyright 2009 – Database Architechs www.dbarchitechs.com Zero Unplanned Downtime
Slide 11: What do you need? It’s as simple as 1, 2, 3 + Step One – Launch of a brief “Phase 0” HA Assessment Step Two – Complete an HA Primary Variables gauge Step Three – Match your need to the optimal HA solution Step + (optional) – Determine the ROI of the HA solution Copyright 2009 – Database Architechs www.dbarchitechs.com
Slide 12: Assessing HA with Primary Variables 0% 100% Uptime Requirement Long Short Time to Recover High Low Tolerance of Recovery Time Low High Data Resiliency Low High Application Resiliency Low High Degree of Distributed Access/Synchronization Often Never Scheduled Maintenance Frequency Low High Performance/Scalability Low High Cost of Downtime ($$ lost/hr) Low Copyright 2009 – Database Architechs Cost of the High Availability Solution ($$) www.dbarchitechs.com High
Slide 13: Development Methodology “With High Availability built in” Assessment Assessment (scope) (scope) Requirements 0. Assessment Requirements - Project Planning - Project Sizing - Deliverables Identified (SOW) Design Design - Schedules/milestones - High-Level Requirements (scope) - Estimate HA Primary Variables (gauges) 1. Requirements - Detail Requirements (process/data/technology) - Early Prototyping (optional) - Detailed HA Primary Variables - Detailed Service Level Agreements/Rqmts - Detailed Disaster Recovery requirements 3. Code & Test - Code Development/Unit Testing - Fully integrate the HA solution with the application 4. System Test & Acceptance - Full system Test/User Acceptance - Full HA Test/Validation/Acceptance 5. Implementation - Production Build/Implementation - Production HA build/monitoring begins Code & Test Code & Test System Test & System Test & Acceptance Acceptance Implementation Implementation 2. Design - Detail Design (data/process/technology) - Choose and design the matching HA solution for the application Copyright 2009 – Database Architechs www.dbarchitechs.com
Slide 14: Spiral/Rapid Methodology Iterative approach 7. Implementation - Production Build/Implementation - Production HA build/monitoring begins 0. Initial assessment - Project Planning - Project Sizing - Deliverables Identified (SOW) - Schedules/milestones - High-Level Functions (scope) -Estimate HA Primary Variables (gauges) 3. Requirements - Detail Requirements (process/data/technology) - Detail HA Primary Variables - Detailed SLA/Rqmts - Detailed Disaster Recovery requirements Transition Inception 6. System Test & Acceptance - Full system Test/User Acceptance -Full HA Test/Validation /Acceptance 5. Code & Test - Code Development/Unit Testing -Fully integrate the HA solution with the application 1. High-level Rqmts/Prototyping - High-level requirements (process/data/technology) - High-level HA Primary Variables Construction Elaboration/Prototype 4. Design - Detail Designs (process/data/technology) - Choose and design the matching HA solution for the application (verified via prototypes) 2. Early Code & Test - Early code and testing of apps/DI (process/data/technology) Copyright 2009 – Database Architechs www.dbarchitechs.com - Prototyping of HA options
Slide 15: Valid High Availability Options Disk Methods Disk Methods Other HW Cluster Services Data Replication Other HW Cluster Services Data SQL DB Replication Clustering Mirroring Log Shipping SQL Clustering DB Mirroring Log Shipping Copyright 2009 – Database Architechs www.dbarchitechs.com
Slide 16: MSCS Cluster Services C: Local Binaries Node A Windows 2003 Enterprise Edition Cluster Group Resources D: SCSI Shared Disk Q: Quorum Node B Windows 2003 Enterprise Edition Copyright 2009 – Database Architechs www.dbarchitechs.com C: Local Binaries
Slide 17: SQL Connections SQL Clustering C: COLTST1 Local Binaries Windows 2003 Enterprise Edition SQL Server 2008 (physical) E: Cluster Group Resources MS DTC SQL Server 2008 (Virtual SQL Server) VSQLDBARCH\VSQLSRV1 SCSI Master DB TempDB Appl 1 DB Quorum Disk SQL Agent Q: SQL Server 2008 (physical) Windows 2003 Enterprise Edition Copyright 2009 – Database Architechs www.dbarchitechs.com COLTST2 C: Local Binaries
Slide 18: Data Replication “Primary” SQL Server 2008 Can be used as a Warm Standby and/or for Reporting needs “Replicate” SQL Server 2008 Publication Server Adventure Works Subscription Server AdventureWorks Distribution distribution Server SQL Server 2008 “Replicate” SQL Server 2008 Subscription Server Central Publisher/ Remote Distributor Replication model Copyright 2009 – Database Architechs www.dbarchitechs.com AdventureWorks
Slide 19: Database Mirroring Client Client Client Client Network A Database Principal MirroringAdventure Works DB Server translog SQL Server 2008 SQL Server 2008 Mirror Server D B C Adventure Works DB D translog SQL Server 2008 Witness Server MSDB DB
Slide 20: Database Mirroring with DB Snapshots SQL Server 2008 SQL Server 2008 20FIG34 Principal Server Mirror Server AdventureWorks AdventureWorks DB DB translog translog Database Snapshot SQL Server 2008 Witness Server MSDB DB Ne Ne Re tw tw po oo rk rti rk ng Us er s
Slide 21: Log Shipping “Source” SQL Server 2008 “Destination” TxnLog backups SQL Server 2008 Primary Server CallOne DB translog Secondary Server CallOne DB \Backup\CallOne_tlog_200405141120.TRN Last log shipped Delay Answer TxnLog Copies \LogShare\CallOne_tlog_200405141120.TRN TxnLog Restores “Monitor” SQL Server 2008 Delay between logs loaded Delay Answer MSDB DB Monitor Server Copyright 2009 – Database Architechs www.dbarchitechs.com
Slide 22: RAID Disk I/O Summary RAID Level Fault Tolerance Logical Reads 1 Physical I/Os per Read 1 Logical Writes 1 Physical I/Os per Write 1 RAID 0 None RAID 1 or 10 Best (Optimal for OLTP) Moderate (Optimal for mostly READ ONLY systems) 1 1 1 2 writes RAID 5 1 1 1 2 reads + 2 writes (that’s 4 per write!) NOTE: Several RAID vendors are now showing RAID 5 and RAID 10 performance almost equivalent now via Cache/Buffer advancements on their RAID controllers Copyright 2009 – Database Architechs www.dbarchitechs.com
Slide 23: Fault Tolerance and SQL DB Files Description Quorum Drive Fault Tolerance RAID 1 or RAID 10 RAID 10 The quorum drive used with MSCS should be isolated to a drive by itself (very often mirrored as well for maximum availability) For OLTP (online transaction processing) systems, the database data/index files should be placed on a RAID 10 disk system. For DSS (Decision Support Systems) systems that are primarily READ ONLY, the database data/index files should be placed on a RAID 5 disk system. Highly volatile disk I/O (when not able to do all it’s work in cache) SQL Server Database files (OLTP) SQL Server Database files (DSS) RAID 5 Temp DB RAID 10 RAID 10 Or RAID 1 SQL Server The SQL transaction log files should be on their own Transaction mirrored volume for both performance and Log files database protection. (for DSS systems, this could be RAID 5 also). Copyright 2009 – Database Architechs www.dbarchitechs.com
Slide 24: Example DB data files configuration E: RAID 5 Master DB log DSS - DB (read only) F: TempDB RAID 10 log G: OLTP X - DB log OLTP Y - DB H: RAID 1 or RAID 10 Copyright 2009 – Database Architechs www.dbarchitechs.com Q: Quorum
Slide 25: Decision Tree approach Condition/Question Case A Case B Case C Case D Action Action Action Action W X Y V . . . Case n . . . Action Z Disk Methods Other HW Cluster Services Data SQL Database Log Distributed Replication Clustering Mirroring Shipping Transactions Copyright 2009 – Database Architechs www.dbarchitechs.com Database Snapshots
Slide 26: Decision-Tree Path Traversal SQL Clustering 1a2c3 1a2b3 1a2a3 1a2d3 1a2e3 bcd a 1a2 e a b 1b2 1c2 e1c d 1d2 Database Snapshots Cluster Services Database Mirroring HA Not Needed 1e2 Log Shipping HW/Disk Redundancy Distributed Transactions Copyright 2009 – Database Architechs www.dbarchitechs.com Data Replication
Slide 27: 1 A% <= 70% Decision-Tree: ASP Questions 1-3 What % of availability must your application have? 70% < A% < =83% 83 < A% < =95% 95% < A% < =99.5% A%> 99.5% Marginal Availability Acceptable Availability Standard Availability High Availability Extreme Availability 2 Very High How much tolerance of downtime by end-users? High Medium Low Very Low Not Critical Low Criticality Standard Criticality High Criticality Extremely Critical 3 What is the per hour cost of downtime for this application? $C<= $3K $3K < $C < =$7K $7K < $C < =$12K $12K < $C < =$20K $C > $20K Very Low Cost Low Cost Moderate Cost Copyright 2009 – Database Architechs www.dbarchitechs.com High Cost Very High Cost
Slide 28: 4 Decision-Tree: ASP Questions 4-6 How long does it take to get the application back online? Very Long Long Average Short Very Short Fast Extreme Marginal Acceptable Standard Recoverability Recoverability Recoverability Recoverability Recoverability 5 None How much of the application is distributed? A Little Medium A Lot All NonDistributed Low Distribution Moderately Distributed High Distribution Extremely Distributed 6 Very Little How much data inconsistency can be tolerated? A Little Medium A Lot Very Much Very High Consistency High Consistency Moderate Low Consistency Consistency Minimal Consistency Copyright 2009 – Database Architechs www.dbarchitechs.com
Slide 29: 7 Very Often Decision-Tree: ASP Questions 7-9 How often is scheduled maintenance required? Often Average Not Often Rarely Very High Downtime High Downtime Reasonable Downtime Low Downtime Minimal Downtime 8 How important is high performance and scalability? Not Very Somewhat Moderately Very Much Extremely Reasonable High Extreme Very low Low Performance Performance Performance Performance Performance 9 How important is the application connection to the end-user? Not Very Somewhat Moderately Very Much Extremely Not Needed Establish new Connection Connection Re-established easily Connection Retry process Connection Fail-over Copyright 2009 – Database Architechs www.dbarchitechs.com
Slide 30: 10 Decision-Tree: ASP Question 10 What is the estimated cost of the HA Solution (budget)? C$ < $10K $10K <= C$ < $100K $100K <= C$ < $250K $250K <= C$ < $500K C$ >= $500K Very Low Cost Low Cost Moderate Cost High Cost Extreme Cost 1. 2. 3. 4. 5. 6. 7. 8. 9. 10. 1e Extreme Availability goal 1e+2d Very low tolerance of downtime 1e+2d+3e $15k/hr cost of downtime (High Cost) 1e+2d+3e+4c Average recovery time 1e+2d+3e+4c+5a No distributed components or synchronization 1e+2d+3e+4c+5a+6b A little data inconsistency can be tolerated 1e+2d+3e+4c+5a+6b+7c Average amount of scheduled downtime 1e+2d+3e+4c+5a+6b+7c+8d Performance is very much important 1e+2d+3e+4c+5a+6b+7c+8d+9b Connection can be re-established 1e+2d+3e+4c+5a+6b+7c+8d+9b+10c Moderate HA Cost/Good budget Best fitting HA Solution (together) Disk Methods Other Cluster Copyright 2009 – Database Architechs HW Services www.dbarchitechs.com SQL Clustering
Slide 31: Basic “one-two” Punch approach 1 Build the proper foundation first Hardware/Network Redundancy Disk Backups DB Backups Vendor SLA’s Training, QA, & Standards Software Upgrades 2 Disk Methods Then, build within the appropriate HA solution that your application requires Other HW Cluster Services Data SQL Database Log Distributed Replication Clustering Mirroring Shipping Transactions Database Snapshots Copyright 2009 – Database Architechs www.dbarchitechs.com
Slide 32: ASP – Scenario #1 with SQL Clustering C: ASPProd1 Local Binaries Windows 2003 MSCS Enterprise Edition JRUN/WebServices/IIS SQL Server 2005 (physical) E: Cluster Group Resources Master DB Network SQL Server 2005 (Virtual SQL Server) ASQL\ASPSERV1 TempDB SCSI F: G: Q: Quorum Disk HOE DB MS DTC SQL Server 2005 (physical) SQL Agent Active/Passive Configuration Windows 2003 Enterprise Edition MSCS Copyright 2009 – Database Architechs www.dbarchitechs.com C: ASPProd2 Local Binaries
Slide 33: Log Shipping “Source” SQL Server 2000 “Destination” TxnLog backups SQL Server 2000 Primary Server CallOne DB translog Secondary Server CallOne DB \Backup\CallOne_tlog_200405141120.TRN Last log shipped Delay Answer TxnLog Copies \LogShare\CallOne_tlog_200405141120.TRN TxnLog Restores “Monitor” SQL Server 2000 Delay between logs loaded Delay Answer MSDB DB Monitor Server Copyright 2009 – Database Architechs www.dbarchitechs.com
Slide 34: North America (Reporting & “warm/hot” spare) Headquarters (Santa Clara) Live REPL Publication solution Server SQL Server 2000 SQL Server 2000 Subscription Server MktgDB MktgDB Europe (Reporting) SQL Server 2000 Distribution distribution Server SQL Server 2000 Subscription Server MktgDB Far East (Reporting) SQL Server 2000 Central Publisher/ Remote Distributor Replication model Copyright 2009 – Database Architechs www.dbarchitechs.com Subscription Server MktgDB
Slide 35: SQL Server 2000 Subscription Server Northwind Central Publisher (default option) SQL Server 2000 SQL Server 2000 Subscription Server Northwind Publication Server Northwind Distribution distribution Server Oracle SQL Server 7.0 Subscription Server Northwind Copyright 2009 – Database Architechs www.dbarchitechs.com Subscription Server Northwind
Slide 36: SQL Server 2000 Subscription Server Northwind Central Publisher Remote Distributor SQL Server 2000 SQL Server 2000 Subscription Server Northwind Publication Server Northwind Distribution distribution Server SQL Server 2000 Oracle SQL Server 7.0 Subscription Server Northwind Copyright 2009 – Database Architechs www.dbarchitechs.com Subscription Server Northwind
Slide 37: Data Access Latency Autonomy Distributing Data Sites Frequency (locations) Network Machines Owner many high fast/ stable many low fast/ stable fast/ stable Other REPLICATION Read Only Reporting short Database Mirroring Database Mirroring high < 10 high high 1 1 OLTP needs regional server/site data site 1 1 OLTP needs regional server/site data site 1 1 OLTP on one table server/site site All all tables update All all tables update 1 report site Regional updates Each site only Each site only Central Publisher Transactional repl filter by region Read Only Reporting Read Mostly A few updates long Central Publisher Snapshot repl filter by region short medium Central Publisher Transactional repl Updating Subs Read Mostly A few updates medium Read equal Equal updates short high < 10 medium slow/ 1 unreliab server/site fast/ stable 1 server/site Regional update Central Publisher Merge repl Regional update high < 10 medium Peer-to-Peer Transactional repl Inserts (new orders) short high many high fast/ stable 1 server/site Each site only needs regional data Central Subscriber Transactional repl Hot/Warm Spare Very short high <2 high Database Mirroring fast/ stable 1 1 OLTP server/site site Fail-over Central Publisher Remote Distributor Transactional repl
Slide 38: Foundation, Foundation, Foundation Piecing it together Disk Backups DB Backups Vendor SLA’s Hardware/Network Redundancy Training, QA, & Standards Software Upgrades Network Application Middleware Database Operating System tem ys S ck Sta Copyright 2009 – Database Architechs www.dbarchitechs.com HARDWARE Network Components Servers Disk Systems Memory
Slide 39: ROI Calculati on Copyright 2009 – Database Architechs www.dbarchitechs.com
Slide 40: Database Mirroring Transparent Client Redirect Network A SQL Server 2008 SQL Server 2008 Mirror Server Principal Server Applx DB D B C Applx DB D translog translog “Copy-on-Write” technology SQL Server 2008 Witness Server MSDB DB Copyright 2009 – Database Architechs www.dbarchitechs.com
Slide 41: 20FIG30 SQL Server 2005 Database Mirroring
Slide 42: 20FIG31 SQL Server 2008 Database Mirroring
Slide 43: 16 43 Copyright 2009 all rights reserved
Slide 44: Database Mirroring with DB Snapshot SQL Server 2005 SQL Server 2005 Mirror Server Principal Server Applx DB Applx DB translog translog Database Snapshot SQL Server 2005 Witness Server MSDB DB Copyright 2009 – Database Architechs www.dbarchitechs.com Ne Ne Re tw tw po oo rk rti rk ng Us er s
Slide 45: Source Data Pages Snapshot Users SELECT …..data……. FROM AdventureWorks SNAPSHOT 04 SQL Server 2008 SQL Server Snapshot AdventureWorks DB AdventureWorks DB System Catalog of changed pages Sparse File Pages
Slide 46: Instance: SQL2008xyz Endpoint Name: “endpoint4mirroring” Role: PARTNER SQL Server 2008 PH Topology With Snapshots Critical Report Users Network Network Principal Server OLTP Application Active Clustered Replication Adventure Works DB Instance: SQL2008zzz Endpoint Name: “endpoint4mirroring” Role: PARTNER SQL Server 2008 Mirror Server Adventure Works DB translog translog Database Snapshot Passive PH Topology Principal Server SQL Server 2008 Network Network Less Critical Reporting Users
Slide 47: Publisher SQL Server 2008 SQL Server 2008 The Combo Pack SQL Server 2008 Principal Server Mirror Server Subscriber Distributor SQL Server 2008 SQL Server 2008 Principal Server SQL Server 2008 Mirror Server SQL Server 2008 Witness Server Subscriber Copyright 2009 – Database Architechs www.dbarchitechs.com
Slide 48: Restart Stage Transactions Rolled Forward time SQL Server 2000 SQL Server 2005/2008 SQL Server SQL Server Transactions Rolled Back SQL Server 2005/2008 database is available SQL Server 2000 Restart complete database is available DB Availability Improvement ! Copyright 2009 – Database Architechs www.dbarchitechs.com
Slide 49: Performance and Tuning counts in HA SQL SHOT – MS SQL Server
Slide 50: Questions Is there any time left???? Copyright 2009 – Database Architechs www.dbarchitechs.com
Slide 51: SQL Server Resources Stop! Copyright 2009 – Database Architechs www.dbarchitechs.com
Slide 52: Fail-Over via Move Group ANSWER to question #1 Copyright 2009 – Database Architechs www.dbarchitechs.com
Slide 53: Distributed Transactions “Primary Location” SQL Server 2000 Reads If Try primary first Northwind 1 no ta va ila ble , MS DTC try Updates sec on da ry “Secondary Location” SQL Server 2000 Northwind 2 Must succeed together, or be both rolled back (two-phase commit) Copyright 2009 – Database Architechs www.dbarchitechs.com