Proven Customer Deployed Architectures and Scenarios For SQL Server HA/DRPrem MehraProgram ManagerMicrosoft Corporation

SESSION CODE: DAT401

Key TakeawaysSQL server 2008 can meet very high HA DR requirementsUpgrades from SQL Server 2005 to SQL Server 2008 can be achieved with downtime limited to minutesDemanding HA DR deployments require very good documented operational procedures and highly skilled staff

Content

HA DR Capabilities and TechnologiesArchitectural Solutions and Customer DeploymentsKey TakeawaysQuestions & Answers

Unplanned Downtime

Planned Downtime

Protection against failures

Machine level: hardware outages, service failures, data corruption …

Site level: hurricanes, fires ..

Recovery from errors (User or Application)

Incorrect data modifications, accidental changes…

Online administration

Software/hardware upgrades, index rebuilds, …

Predictable Resourcing Features that enhance concurrency

HA DR Capabilities

Current TechnologiesFailover Clustering

Local server redundancy

Database MirroringLocal server & storage redundancy

Disaster recovery

Log Shipping

Additional disaster sites for databases

App/user error recovery

Replication

Database reporting and read scale out with redundancy

Always On Partner Solutions

Highest hardware reliability

Database Mirroring Hot Standby

Warm Standby

App/User Error Recovery

Log Shipping

Log Shipping With Restore Delay

ProductionDatabase

Replication DatabaseScale OutFor Queries

Failover Cluster

Content

HA DR Capabilities and TechnologiesArchitectural Solutions and Customer DeploymentsKey TakeawaysQuestions & Answers

Proven Architectures: Successfully Deployed by Customers # Architecture Key Distinguishing

Scenario Use & Deployment CharacteristicsExamples

1 Failover Clustering for HA and Database Mirroring for DR

A) Single data copy for HA sufficient B) Positive experience with Failover clustering C) 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

B) Avoid costs associated with Failover clustering C) For HA, remote data center execution acceptable

bWin

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 SAN-based 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/updateableB) Application architecture permits avoidance /

minimization / resolution of update conflictsC) In case of DR, potential data loss is acceptable

A Travel Industry Enterprise

ServiceU Corporation

ServiceU

Software as a Service (SaaS) provider

Provide solutions for reserved seat ticketing, box office management, event management and online payments

Customers in 50 states and 15 countries

PCI Level 1 Service Provider (credit card compliance)

HA/DR requirements

No service = no revenue

PCI requires same security measures at DR site; needs to be set up prior to emergency in order to meet same strict guidelines

Goal: eliminate all single points of failure: network, servers, data, data centers

8

ServiceU Corporation

Usage of SQL Server HA technologiesAll SQL Servers are clustered, including

At the DR SiteThe Development and Test Environments

Asynchronous Database Mirroring used for all critical databases between main datacenter and DR datacenter

Log shipping – used to ‘seed’ databases in order to start Database Mirroring

9

http://sqlcat.com/whitepapers/archive/2009/08/04/high-availability-and-disaster-recovery-at-serviceu-a-sql-server-2008-technical-case-study.aspx

Atlanta Standby Data CenterMemphis Primary Data Center

SQL Server Infrastructure

DNS

Asynchronous Database Mirroring

Windows 2008 SQL 2008 Windows 2008 SQL 2008

MIRROR

PreferredPRINCIPAL

DB Connection to Memphis for Regular Test Exercise

DNS

WEB FARM WEB FARM

DNS

ServiceU Upgrade Goals

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, andAdd 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 minutesSLA permits up to 45 minutes per year

Atlanta Standby Data CenterMemphis Primary Data Center

Upgrading Infrastructure 1

Asynchronous Database Mirroring

DB Connection to Memphis for Regular Test Exercise

Installed TemporaryWindows 2008

SQL 2008 Cluster

Broke Mirroring toAtlanta

SQL 2005 Cluster SQL 2005 Cluster

MIRROR

Established Mirroring

MIRROR

Disabled Log Shipping

Temporary SQL 2008 Cluster

Setup Log Shipping

Preferred

DNSDNS

WEB FARM WEB FARM

DNS

PRINCIPAL

Log Shipping

Atlanta Standby Data CenterMemphis Primary Data Center

Upgrading Infrastructure 2

Temporary SQL 2008 Cluster

SQL 2005 Cluster

PRINCIPAL

DB Connection to Memphis for Regular Test Exercise

SQL 2005 Cluster

MIRROR

MIRROR

Preferred

DNSDNS

WEB FARM WEB FARM

DNS

Memphis Primary Data Center

Upgrading Infrastructure 3

Temporary SQL 2008 Cluster

SQL 2005 Cluster

PRINCIPAL

MIRROR Sw

itche

d to

a W

eb S

erve

r D

eliv

erin

g D

own

Tim

e M

essa

ge

Manual Failover & Broke MirroringQuick Testing with SQL 2008

Switched To Web Farm Connecting to Temporary SQL 2008 Cluster

GO / NO GO

Preferred

DNS

DNS

WEB FARM

Memphis Primary Data Center

Upgrading Infrastructure 4

Temporary Production

SQL 2008 Cluster

Installed New Windows 2008 SQL 2008 Cluster with Additional Disks

Log Shipping

MIRROR

PRINCIPAL

DB Mirroring

SQL Server 2005 Cluster

Preferred

Disabled Log Shipping. Setup Mirroring

DNS

DNS

WEB FARM

Setup

Memphis Primary Data Center

Upgrading Infrastructure 5

Temporary Production

SQL 2008 Cluster

MIRROR

PRINCIPAL DB Mirroring

Switc

hed

to a

Web

Ser

ver

Del

iver

ing

Dow

n Ti

me

Mes

sage

Manual Failover

Quick Testing

SQL 2008 Cluster

Production Server

GO / NO GO

Preferred

DNS

DNS

WEB FARM

Atlanta Standby Data CenterMemphis Primary Data Center

Upgrading Infrastructure 6

SQL 2008 Cluster

MIRROR

MIRROR

Break MirrorBetween Production &

Temporary 2008 Cluster

Disable Log Shipping and Set Up Asynchronous Mirroring

DB Connection to Memphis for Regular Test Exercise

New Windows 2008 SQL 2008 Cluster with Additional Disks

PRINCIPAL

Preferred

SQL 2008 Cluster

Setup SQL Server 2008Cluster

Setup Log Shipping

DNSDNS

WEB FARM WEB FARM

DNS

ServiceU DeploymentExperiences & Best Practices 1

Setup log shipping first, then convert to DBM

Start the process & walk away; convert to DBM on your schedule

Relatively easy and keeps log files up to date and applied without manual copy

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

ServiceU DeploymentExperiences & Best Practices 2

DBM Log Compression ~ 85% compression (Log Bytes Sent/sec vs. Log Compressed Bytes sent /sec) Tremendously reduces send queue, data latency between sites, network traffic and long haul bandwidth needed

Backup Compression Reduces disk space, allowing for disk cost savings or more backups being retained on the same disksBackup times reduced by ~ 50% Speeds up Log Shipping (less data to transfer)

19

Caregroup Healthcare System

Four Hospitals located in Boston16,000 Employees146 Mission Critical Clinical Applications2 Million Patient Medical RecordsAnnual Revenue : $2 BillionHA/DR requirements for clinical databases:

RTO : 0 downtimeRPO: No data loss

All mission-critical SQL Servers are Clustered and Mirrored

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

20

Existing SQL Server 2005 Cluster

21

PassiveActive

Windows Server 2003 R2 EE SP2, 64-Bit

SQL Server 2005 EE SP2, 64-Bit

EMC

22

PassiveActive

Step #1:Install Prerequisites:1- .Net Framework 3.5 SP12- Windows Installer 4.53- Windows QFE (KB937444)4- SQL2008 Setup Support filesREBOOT….

Step #2:Install Prerequisites:1- .Net Framework 3.5 SP12- Windows Installer 4.53- Windows QFE (KB937444)4- SQL2008 Setup Support filesREBOOT …..

SQL Instance Manual Failover

In-Place Upgrade 1

23

ActivePassive

Step #4:Upgrade to SQL Server 2008 on Active NodeStep #3:

Upgrade to SQL Server 2008 on Passive Node

Step 5: SQL Instance Automatic Failover

No client connection for 1-2 minutes while db is being upgraded to 2008 on the left node

SQL 2008SQL 2008

Active

In-Place Upgrade 2

Removed from Cluster Group Possible Owners

24

PassiveActive

Mirrored SQ

L

Step #1:Upgrade to SQL Server 2008 on Mirrored Instance

Step#2: Manual Failover to the database mirroring partner for each database

Mirroring suspended

SQL 2008

SQL Server Cluster

Principal

SQLServer 2008

Mirroring resumed

In-Place Upgrade With Mirroring

Step #3:Upgrade Cluster to

SQL Server 2008

Step#4: Manual Failover to the database mirroring partner for each database

25

Mirror Server

SQL Server Disaster Recovery

SQL Server Cluster

Cisco Global Site Selector (GSS) DNS

SQLNetworkNameA\SQL1Active IP:100.10.56.30

Alias Name = GreenActive IP: 100.10.56.30 100.85.3.10

Connect to: Green\SQL1

SQLHostNameB\SQL1Passive IP:100.85.3.10

DR SiteMirroring

Prin

cipa

l Ser

ver

Applications:1- SharePoint2- SSRS3- BlackBerry4- Citrix Server5- VMware VC

bwin Corporation

Online gaming companyGibraltar-based, listed: Vienna Stock Exchange Sports betting, Soft & skill games

1 million bets per day on > 90 Sports Offers audio & video streaming of major sporting eventsCase Study• 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

Principal: 32 IA64 Dual Core CPU’s (64 cores)

Mirror 32 IA64 Single Core

>64 Core Test

64 Network Ports (1 Gbps)

400 local SAS drives on 16 RAID controllers (for OS, TempDB and Log files – low latency)

16 HBA’s

SANs with 256 Disks and 256GB cache

Principal

Log backup file server

Mirror

LogShippingNo delay

Log Shipping1h delay

Log backup file server

Database backup file server Database backup file server

Datacenter A Datacenter B

Mirroring

QR NationalBased in Brisbane, AustraliaProvides passenger and freight train serviceMoved SAP off mainframe to SQL Server 2005GeoCluster Infrastructure

Dell Power Edge 6950Hitachi Data System SANMajority Node Set Cluster

One Terabyte ERP Database

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

QR National Solution Architecture

HDS SAN

User Community (WAN Users)

User Community(WAN Users)

Dual Fiber Link (Dark Fiber)

Various Switches

This represents numerous SAP databases

Various SwitchesSite 1

HDS SAN

Site 2

Production

HDS is mirroring the data over the dark fiber

DR

SAP App Servers

SAP App Servers

Progressive Insurance Company• 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 ApplicationReplace a nearly 30-year-old mainframe-based policy management application that is central to its operationsDeploying 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

• SLAFor Disaster Recovery RPO: 1 Hour, RTO: 24 Hours

• Case Study http://www.microsoft.com/casestudies/Case_Study_Detail.aspx?casestudyid=4000002133

Progressive HA / DR ArchitectureData Center - ColoradoData Center - Ohio

Multiple OC 48 Links

SQL Server Failover Clustering with Dell PowerEdge 6650, 6850 and 2950 servers at each data centerEMC Symmetrix DMX-3 and 4 Series SANs

SRDF/A SAN replication between the data centersOC 48 links between data centers

Network and SAN replication shared by Windows Servers and Mainframes

An Enterprise in Travel Industry

One of the Asia's leading and fast growing online hotel reservation companySQL Server instances spread out over different locations in Asia, the United States and EuropeCurrently two data centres – in USA & Asia

Plans to deploy another one in Europe Mission critical applications

500 tables and over 3,000 stored procedures

http://sqlcat.com/whitepapers/archive/2009/09/23/using-replication-for-high-availability-and-disaster-recovery.aspx.

Topology Deployed

ASIA CORE 1

ASIA CORE 2

Data Warehouse

ASIA Web

America Web

America Web

ASIA Web

Read Only Copy

America CORE 1

America CORE 2

P2P Reference

P2P FinancialWeb Publication

Asia Core: IBM x3850 2x6 64 GB

Asia DW: IBM x3850 2x6 128 GB

America Core: HP DL380 G5’s 2x4 64GB

Web Servers: IBM x3650 1 x 4 8GB

Tran ReferenceTran Financial

Content

HA DR Capabilities and TechnologiesArchitectural Solutions and Customer DeploymentsKey TakeawaysQuestions & Answers

Key TakeawaysSQL server 2008 can meet very high HA DR requirementsUpgrades from SQL Server 2005 to SQL Server 2008 can be achieved with downtime limited to minutesDemanding HA DR deployments require very good documented operational procedures and highly skilled staff

Content

HA DR Capabilities and TechnologiesArchitectural Solutions and Customer DeploymentsKey TakeawaysQuestions & Answers

SQL Server 2008 Failover Clustering References

Useful pointersSQL Server 2008 Failover Clustering http://sqlcat.com/whitepapers/archive/2009/07/08/sql-server-2008-failover-clustering.aspx

Rolling in-place cluster upgrade process http://msdn.microsoft.com/en-us/library/ms191295.aspx

How to create a single node SQL Server 2008 failover clusterhttp://msdn.microsoft.com/en-us/library/ms179530.aspx

How to add node to a SQL Server 2008 failover clusterhttp://msdn.microsoft.com/en-us/library/ms191545.aspx

An advanced cluster installation option, which prepares cluster nodes first and then completes the cluster across prepared nodes http://msdn.microsoft.com/en-us/library/ms144259.aspx

39

Appendix•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-server-instance.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-sql-server-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-sql-server-2008.aspx

DAT Track Scratch 2 Win

Find the DAT Track Surface Table in the Yellow Section of the TLCTry your luck to win a Zune HDSimply scratch the game pieces on the DAT Track Surface Table and Match 3 Zune HDs to win

Resources

www.microsoft.com/teched

Sessions On-Demand & Community Microsoft Certification & Training Resources

Resources for IT Professionals Resources for Developers

www.microsoft.com/learning

http://microsoft.com/technet http://microsoft.com/msdn

Learning

Complete an evaluation on CommNet and enter to win!

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 registrationJoin us in Atlanta next year

© 2010 Microsoft Corporation. All rights reserved. Microsoft, Windows, Windows Vista and other product names are or may be registered trademarks and/or trademarks in the U.S. and/or other countries.The information herein is for informational purposes only and represents the current view of Microsoft Corporation as of the date of this presentation. Because Microsoft must respond to changing market conditions, it should not be interpreted to

be a commitment on the part of Microsoft, and Microsoft cannot guarantee the accuracy of any information provided after the date of this presentation. MICROSOFT MAKES NO WARRANTIES, EXPRESS, IMPLIED OR STATUTORY, AS TO THE INFORMATION IN THIS PRESENTATION.

JUNE 7-10, 2010 | NEW ORLEANS, LA