HA DR Capabilities and Technologies Unplanned Downtime Planned Downtime Replication Database Mirroring Production Database Log Shipping Database Scale Out For Queries Hot Standby Warm Standby Failover Cluster Log Shipping With Restore Delay App/User Error Recovery.
Download ReportTranscript HA DR Capabilities and Technologies Unplanned Downtime Planned Downtime Replication Database Mirroring Production Database Log Shipping Database Scale Out For Queries Hot Standby Warm Standby Failover Cluster Log Shipping With Restore Delay App/User Error Recovery.
HA DR Capabilities and Technologies Unplanned Downtime Planned Downtime Replication Database Mirroring Production Database Log Shipping Database Scale Out For Queries Hot Standby Warm Standby Failover Cluster Log Shipping With Restore Delay App/User Error Recovery Architectural Solutions and Customer Deployments # Architecture Key Distinguishing Scenario Use & Deployment Characteristics Examples 1 Failover Clustering for HA and Database Mirroring for DR A) B) C) Single data copy for HA sufficient Positive experience with Failover clustering Comfortable deploying two different technologies for HA & DR ServiceU and CareGroup 2 Synchronous Database Mirroring for HA/DR and Log Shipping for additional DR A) Require deploying fewer (only one) technology for HA & DR Avoid costs associated with Failover clustering For HA, remote data center execution acceptable bWin Geo-Cluster for HA/DR and Log Shipping for additional DR A) Require deploying fewer (only one) technology for HA & DR Positive experience with Geo-Clustering QR Limited Failover Clustering for HA and SANbased Replication for DR A) Require deploying single DR technology across multiple DBMSs A third party DR technology acceptable Progressive Peer-to-Peer Replication for HA and DR (and reporting) A) B) Require that all data replicas be readable/updateable Application architecture permits avoidance / minimization / resolution of update conflicts In case of DR, potential data loss is acceptable A Travel Industry Enterprise 3 4 5 B) C) B) B) C) 8 http://sqlcat.com/whitepapers/archive/2009/08/04/high-availability-and-disaster-recovery-at-serviceu-a-sql-server-2008-technical-case-study.aspx 9 DNS Memphis Primary Data Center WEB FARM Atlanta Standby Data Center WEB FARM DNS P R I N C I P A L DNS Preferred DB Connection to Memphis for Regular Test Exercise M I R R O R Asynchronous Database Mirroring Windows 2008 SQL 2008 Windows 2008 SQL 2008 Upgrade production systems from: Windows Server 2003 to 2008, and SQL Server 2005 to 2008 With new hardware New Servers at both Data Centers to accommodate growth, and Add disks to the SANs at both Data Centers and reconfigure LUNs Achieve these goals with least service interruption: No more than 20 minutes Total downtime during the complex upgrade: ~16 minutes DNS Memphis Primary Data Center M I R R O R Atlanta Standby Data Center Temporary SQL 2008 Cluster Installed Temporary Windows 2008 SQL 2008 Cluster Established Mirroring Setup Log Shipping WEB FARM WEB FARM Disabled Log Shipping DNS P R I N C I P A L Log Shipping DNS Preferred DB Connection to Memphis for Regular Test Exercise Asynchronous Database Mirroring SQL 2005 Cluster M I R R O R Broke Mirroring to Atlanta SQL 2005 Cluster DNS Memphis Primary Data Center M I R R O R Atlanta Standby Data Center Temporary SQL 2008 Cluster WEB FARM WEB FARM DNS P R I N C I P A L DNS Preferred SQL 2005 Cluster DB Connection to Memphis for Regular Test Exercise M I R R O R SQL 2005 Cluster Memphis Primary Data Center M I R R O R Temporary SQL 2008 Cluster GO / NO GO Manual Failover & Broke Mirroring Quick Testing with SQL 2008 Switched to a Web Server Delivering Down Time Message DNS WEB FARM DNS P R I N C I P A L Preferred SQL 2005 Cluster Switched To Web Farm Connecting to Temporary SQL 2008 Cluster DNS Memphis Primary Data Center P R I N C I P A L Disabled Log Shipping. Setup Mirroring Temporary Production SQL 2008 Cluster DNS DB Mirroring Setup Log Shipping M I R R O R Preferred SQL Server 2005 Cluster Installed New Windows 2008 SQL 2008 Cluster with Additional Disks WEB FARM Switched to a Web Server Delivering Down Time Message DNS Memphis Primary Data Center P R I N C I P A L Manual Failover Temporary Production SQL 2008 Cluster DB Mirroring WEB FARM DNS Quick Testing M I R R O R Preferred Production Server GO / NO GO SQL 2008 Cluster DNS Memphis Primary Data Center M I R R O R Atlanta Standby Data Center Break Mirror Between Production & Temporary 2008 Cluster WEB FARM WEB FARM DNS P R I N C I P A L DNS Preferred DB Connection to Memphis for Regular Test Exercise Disable Log Shipping and Set Up Asynchronous Mirroring SQL 2008 Cluster Setup SQL Server 2008 Cluster Setup Log Shipping M I R R O R New Windows 2008 SQL 2008 Cluster with Additional Disks SQL 2008 Cluster Experiences & Best Practices Failover of multiple Databases No built-in process for coordinated failover Set up alerts Suspended mirroring state for databases Causes log to continue to grow on principal & can cause problems Set up alerts for databases that go into suspended mirroring state 18 Experiences & Best Practices 19 http://www.microsoft.com/casestudies/Case_Study_Detail.aspx?casestudyid=4000001003 20 Active Windows Server 2003 R2 EE SP2, 64-Bit Passive EMC SQL Server 2005 EE SP2, 64-Bit 21 Step #2: Install Prerequisites: 1- .Net Framework 3.5 SP1 2- Windows Installer 4.5 3- Windows QFE (KB937444) 4- SQL2008 Setup Support files REBOOT ….. Active Step #1: Install Prerequisites: 1- .Net Framework 3.5 SP1 2- Windows Installer 4.5 3- Windows QFE (KB937444) 4- SQL2008 Setup Support files REBOOT…. Passive SQL Instance Manual Failover 22 Step #4: Upgrade to SQL Server 2008 on Active Node Step #3: Upgrade to SQL Server 2008 on Passive Node SQL 2008 Removed from Cluster Group Possible Owners Passive Active No client connection for 1-2 minutes while db is being upgraded to 2008 on the left node SQL 2008 Active Step 5: SQL Instance Automatic Failover 23 Step #1: Upgrade to SQL Server 2008 on Mirrored Instance Step#4: Manual Failover to the database Step#2: mirroring partner for each database SQL 2008 SQL Server Cluster Step #3: Mirroring Mirroring suspended resumed Active Passive 24 PrincipalSQL Mirrored SQL Server 2008 Cisco Global Site Selector (GSS) DNS Alias Name = Green Active IP: 100.10.56.30 100.85.3.10 Connect to: Green\SQL1 Applications: 1- SharePoint 2- SSRS 3- BlackBerry 4- Citrix Server 5- VMware VC SQL Server Cluster SQLHostNameB\SQL1 Passive IP:100.85.3.10 SQLNetworkNameA\SQL1 Mirror Server Mirroring DR Site Principal Server Active IP:100.10.56.30 25 bwin Corporation • http://www.microsoft.com/casestudies/Case_Study_Detail.aspx?casestudyid=4000001470 bwin Mission & Challenge • The Mission: Failure is not an option • Budget: UNLIMITED (not kidding) • bWin definition: VLDB – A database that is business critical • not just it’s size • SLA • Zero data loss • 99.99x% availability @ 24 x 7 • Architecture and operations are designed with an assumption of the worst case scenario: “Full datacenter failure with complete data loss within the datacenter” bwin Solution & the Environment • The Solution: • Standardize everything • Work by the book • Have some clever guys at hand • if the book runs out of pages • Environment (includes all) • 100+ TB Data • 850+ DBs • 100+ Instances • 450K+ SQL Statements per second http://www.microsoft.com/casestudies/Case_Study_Detail.aspx?casestudyid=4000001470 bwin Infrastructure Datacenter B Datacenter A Mirroring Mirror Principal Log Shipping 1h delay Log backup file server Database backup file server Log backup file server LogShipping No delay Database backup file server http://www.microsoft.com/casestudies/Case_Study_Detail.aspx?casestudyid=4000003421 User Community (WAN Users) User Community (WAN Users) DR Production Various Switches Site 1 Various Switches Site 2 SAP App Servers Dual Fiber Link (Dark Fiber) HDS is mirroring the data over the dark fiber HDS SAN This represents numerous SAP databases HDS SAN SAP App Servers • Enterprise Based in Mayfield Village, Ohio, Progressive Insurance is the fifth largest U.S. auto insurance group, with annual revenues of more than U.S.$14 billion • Mission Critical Application Replace a nearly 30-year-old mainframe-based policy management application that is central to its operations Deploying a completely new application using Microsoft® SQL Server® 2005 and .NET framework • Benefits Better view into the business, Easier data management, Enterprise-grade reliability, Scalability, Ease of integration, Faster time to market • SLA For Disaster Recovery RPO: 1 Hour, RTO: 24 Hours • Case Study http://www.microsoft.com/casestudies/Case_Study_Detail.aspx?casestudyid=4000002133 Data Center - Colorado Data Center - Ohio Multiple OC 48 Links http://sqlcat.com/whitepapers/archive/2009/09/23/using-replication-for-high-availability-and-disaster-recovery.aspx ASIA Web ASIA CORE 1 America Web America CORE 1 Data Warehouse Read Only Copy P2P Reference Tran Financial Tran Reference P2P Financial Web Publication Asia Core: IBM x3850 2x6 64 GB Asia DW: IBM x3850 2x6 128 GB ASIA CORE 2 America CORE 2 America Core: HP DL380 G5’s 2x4 64GB Web Servers: IBM x3650 1 x 4 8GB ASIA Web America Web Key Takeaways Questions & Answers References http://sqlcat.com/whitepapers/archive/2009/07/08/sql-server-2008-failoverclustering.aspx http://msdn.microsoft.com/en-us/library/ms191295.aspx http://msdn.microsoft.com/en-us/library/ms179530.aspx http://msdn.microsoft.com/en-us/library/ms191545.aspx http://msdn.microsoft.com/en-us/library/ms144259.aspx 39 •SQL Server 2008 Failover Clustering http://sqlcat.com/whitepapers/archive/2009/07/08/sql-server-2008-failover-clustering.aspx •Cluster Team Site: http://www.microsoft.com/windowsserver2008/en/us/failover-clustering-multisite.aspx •KB Article: Deployment Considerations for Windows Server 2008 failover cluster nodes on different, routed subnets •Webcast: TechNet Webcast: Geographically Dispersed Failover Clustering in Windows Server 2008 Enterprise •Webcast: How You Can Achieve Greater Availability with Failover Clustering Across Multiple Sites (Level 300) •Whitepaper: Multi-site Clustering •Webcast: Multi-Site Clustering in Windows Server 2008 •Guide: http://technet.microsoft.com/en-us/library/cc771509.aspx •Multi-Site Cluster http://download.microsoft.com/download/3/b/5/3b51a025-7522-4686-aa16-8ae2e536034d/WS2008%20Multi%20Site%20Clustering.doc •Mirroring a Large Number of Databases in a Single SQL Server Instance http://sqlcat.com/technicalnotes/archive/2010/02/10/mirroring-a-large-number-of-databases-in-a-single-sql-serverinstance.aspx •Database Mirroring and Log Shipping Working Together: http://sqlcat.com/whitepapers/archive/2008/01/21/database-mirroring-and-log-shipping-working-together.aspx •Asynchronous Database Mirroring with Log Compression in SQL Server 2008 http://sqlcat.com/technicalnotes/archive/2007/12/17/asynchronous-database-mirroring-with-log-compression-in-sqlserver-2008.aspx •Using Replication for High Availability and Disaster Recovery •High Availability and Disaster Recovery at ServiceU: A SQL Server 2008 Technical Case Study •Database Mirroring Best Practices and Performance Considerations •Database Mirroring Log Compression in SQL Server 2008 Improves Throughput •Asynchronous Database Mirroring with Log Compression in SQL Server 2008 http://sqlcat.com/technicalnotes/archive/2007/12/17/asynchronous-database-mirroring-with-log-compression-in-sqlserver-2008.aspx www.microsoft.com/teched www.microsoft.com/learning http://microsoft.com/technet http://microsoft.com/msdn Sign up for Tech·Ed 2011 and save $500 starting June 8 – June 31st http://northamerica.msteched.com/registration You can also register at the North America 2011 kiosk located at registration Join us in Atlanta next year