SESSION CODE DAT 401 Prem Mehra Program Manager

SESSION CODE: DAT 401 Prem Mehra Program Manager Microsoft Corporation

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 A) Single data copy for HA sufficient B) Positive experience with Failover clustering C) Comfortable deploying two different technologies for HA & DR Examples 1 Failover Clustering for HA and Database Mirroring for DR 2 Synchronous Database Mirroring for A) HA/DR and Log Shipping for B) additional DR C) 3 Geo-Cluster for HA/DR and Log Shipping for additional DR A) Require deploying fewer (only one) technology for HA & DR B) Positive experience with Geo-Clustering QR Limited 4 Failover Clustering for HA and SANbased Replication for DR A) Require deploying single DR technology across multiple DBMSs B) A third party DR technology acceptable Progressive 5 Peer-to-Peer Replication for HA and DR (and reporting) A) Require that all data replicas be readable/updateable B) Application architecture permits avoidance / minimization / resolution of update conflicts C) In case of DR, potential data loss is acceptable A Travel Industry Enterprise Require deploying fewer (only one) technology for HA & DR Avoid costs associated with Failover clustering For HA, remote data center execution acceptable Service. U and Care. Group b. Win

8

http: //sqlcat. com/whitepapers/archive/2009/08/04/high-availability-and-disaster-recovery-at-serviceu-a-sql-server-2008 -technical-casestudy. 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

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 Temporary SQL 2008 Cluster Installed Temporary Windows 2008 SQL 2008 Cluster Established Mirroring Disabled Log Shipping DNS P R I N C I P A L Atlanta Standby Data Center Setup Log Shipping WEB FARM 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 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

DNS P R I N C I P A L Manual Failover Switched to a Web Server Delivering Down Time Message Memphis Primary Data Center 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 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 R 2 EE SP 2, 64 -Bit Passive EMC Ø SQL Server 2005 EE SP 2, 64 -Bit 21

Step #2: Install Prerequisites: 1 -. Net Framework 3. 5 SP 1 2 - Windows Installer 4. 5 3 - Windows QFE (KB 937444) 4 - SQL 2008 Setup Support files REBOOT …. . Active Step #1: Install Prerequisites: 1 -. Net Framework 3. 5 SP 1 2 - Windows Installer 4. 5 3 - Windows QFE (KB 937444) 4 - SQL 2008 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#4: Manual Failover to the database Step#2: mirroring partner for each database SQL 2008 SQL Server Cluster Step #3: Upgrade Cluster to SQL Server 2008 Active Mirroring suspended resumed Passive 24 Principal. SQL Mirrored SQL Server 2008 Step #1: Upgrade to SQL Server 2008 on Mirrored Instance

Cisco Global Site Selector (GSS ) DNS Alias Name = Green Active IP: 100. 10. 56. 30 100. 85. 3. 10 Connect to: GreenSQL 1 Applications: 1 - Share. Point 2 - SSRS 3 - Black. Berry 4 - Citrix Server 5 - VMware VC SQL Server Cluster SQLHost. Name. BSQL 1 Passive IP: 100. 85. 3. 10 SQLNetwork. Name. ASQL 1 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) • b. Win definition: VLDB – A database that is business critical • not just it’s size • SLA • Zero data loss • 99. 99 x% 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 • 450 K+ SQL Statements per second http: //www. microsoft. com/casestudies/Case_Study_Detail. aspx? casestudyid=4000001470

bwin Infrastructure

http: //www. microsoft. com/casestudies/Case_Study_Detail. aspx? casestudyid=4000003421

• 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 P 2 P Reference Tran Financial Tran Reference P 2 P Financial Web Publication Asia Core: IBM x 3850 2 x 6 64 GB Asia DW: IBM x 3850 2 x 6 128 GB ASIA CORE 2 America Core: HP DL 380 G 5’s 2 x 4 64 GB Web Servers: IBM x 3650 1 x 4 8 GB 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/ms 191295. aspx http: //msdn. microsoft. com/en-us/library/ms 179530. aspx http: //msdn. microsoft. com/en-us/library/ms 191545. aspx http: //msdn. microsoft. com/en-us/library/ms 144259. 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/windowsserver 2008/en/us/failover-clustering-multisite. aspx • KB Article: Deployment Considerations for Windows Server 2008 failover cluster nodes on different, routed subnets • Webcast: Tech. Net 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/cc 771509. aspx • Multi-Site Cluster http: //download. microsoft. com/download/3/b/5/3 b 51 a 025 -7522 -4686 -aa 16 -8 ae 2 e 536034 d/WS 2008%20 Multi%20 Site%20 Clustering. 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 Service. U: 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 31 st http: //northamerica. msteched. com/registration You can also register at the North America 2011 kiosk located at registration Join us in Atlanta next year