Proven Customer Deployed Architectures and Scenarios For SQ - PowerPoint Presentation

Slide1

Proven Customer Deployed Architectures and Scenarios For SQL Server HA/DR

Prem MehraProgram ManagerMicrosoft Corporation

SESSION CODE: DAT401Slide2

Key Takeaways

SQL server 2008 can meet very high HA DR requirementsUpgrades from SQL Server 2005 to SQL Server 2008 can be achieved with downtime limited to minutes

Demanding HA DR deployments require very good documented operational procedures and highly skilled staff Slide3

Content

HA DR Capabilities and TechnologiesArchitectural Solutions and Customer Deployments

Key TakeawaysQuestions & Answers Slide4

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

CapabilitiesSlide5

Current Technologies

Failover Clustering

Local server redundancyDatabase MirroringLocal server & storage redundancyDisaster recovery Log Shipping

Additional disaster sites

for databases

App/user error recovery

Replication

Database reporting

and read scale out

with redundancy

Always On Partner SolutionsHighest hardware reliability

Database Mirroring

Hot

Standby

Warm

Standby

App/User Error Recovery

Log Shipping

Log

Shipping With Restore Delay

Production

Database

Replication

Database

Scale Out

For Queries

Failover ClusterSlide6

Content

HA DR Capabilities and TechnologiesArchitectural Solutions and Customer Deployments

Key TakeawaysQuestions & Answers Slide7

Proven Architectures: Successfully Deployed by Customers

#

ArchitectureKey Distinguishing

Scenario Use & Deployment Characteristics

Examples

1

Failover Clustering for HA and Database Mirroring for DR

Single data copy for HA sufficient

Positive experience with Failover clustering

Comfortable deploying two different technologies for HA & DR

ServiceU and CareGroup2Synchronous Database Mirroring for HA/DR and Log Shipping for additional DR

Require deploying fewer (only one) technology for HA & DR

Avoid costs associated with Failover clustering

For HA, remote data center execution acceptable

bWin

3

Geo-Cluster for HA/DR and Log Shipping for additional

DR

Require deploying fewer (only one) technology for HA & DR Positive experience with Geo-Clustering QR Limited4

Failover Clustering for HA and SAN-based Replication for DR Require deploying single DR technology across multiple DBMSs

A third party DR technology acceptable Progressive 5

Peer-to-Peer Replication for HA and DR (and reporting)Require that all data replicas be readable/updateableApplication architecture permits avoidance / minimization / resolution of update conflictsIn case of DR, potential data loss is acceptable

A Travel Industry EnterpriseSlide8

ServiceU Corporation

ServiceU

Software as a Service (SaaS) providerProvide solutions for reserved seat ticketing, box office management, event management and online paymentsCustomers in 50 states and 15 countriesPCI 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

8Slide9

ServiceU Corporation

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

At the DR SiteThe Development and Test EnvironmentsAsynchronous Database Mirroring used for all critical databases between main datacenter and DR datacenterLog 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

Slide10

Atlanta Standby Data Center

Memphis Primary Data Center

SQL Server

Infrastructure

DNS

Asynchronous Database

Mirroring

Windows 2008 SQL 2008

Windows 2008 SQL 2008

MIRROR

Preferred

PRINCIPAL

DB Connection to Memphis for Regular Test Exercise

DNS

WEB FARM

WEB FARM

DNSSlide11

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, 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

SLA permits up to 45 minutes per year Slide12

Atlanta Standby Data Center

Memphis Primary Data Center

Upgrading Infrastructure

1

Asynchronous Database

Mirroring

DB Connection to Memphis for Regular Test Exercise

Installed Temporary

Windows 2008

SQL 2008 Cluster

Broke Mirroring to

Atlanta

SQL 2005 Cluster

SQL 2005 Cluster

MIRROR

Established

Mirroring

MIRROR

Disabled Log Shipping

Temporary

SQL 2008 Cluster

Setup Log Shipping

Preferred

DNS

DNS

WEB FARM

WEB FARM

DNS

PRINCIPAL

Log

Shipping Slide13

Atlanta Standby Data Center

Memphis 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

DNS

DNS

WEB FARM

WEB FARM

DNSSlide14

Memphis Primary Data Center

Upgrading Infrastructure

3

Temporary

SQL 2008 Cluster

SQL 2005 Cluster

PRINCI

PAL

MIRROR

Switched to a Web Server Delivering Down Time Message

Manual Failover & Broke Mirroring

Quick Testing with SQL 2008

Switched To Web Farm

Connecting to Temporary SQL 2008 Cluster

GO / NO GO

Preferred

DNS

DNS

WEB FARMSlide15

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

PRINCI

PAL

DB Mirroring

SQL Server 2005 Cluster

Preferred

Disabled Log Shipping. Setup Mirroring

DNS

DNS

WEB FARM

SetupSlide16

Memphis Primary Data Center

Upgrading Infrastructure

5

Temporary

Production

SQL 2008 Cluster

MIRROR

PRINCI

PAL

DB Mirroring

Switched to a Web Server Delivering Down Time Message

Manual Failover

Quick Testing

SQL 2008 Cluster

Production Server

GO / NO GO

Preferred

DNS

DNS

WEB FARMSlide17

Atlanta Standby Data Center

Memphis Primary Data Center

Upgrading Infrastructure

6

SQL 2008 Cluster

MIRROR

MIRROR

Break Mirror

Between 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 2008

Cluster

Setup Log Shipping

DNS

DNS

WEB FARM

WEB FARM

DNSSlide18

ServiceU

DeploymentExperiences & Best Practices 1

Setup log shipping first, then convert to DBMStart 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

18Slide19

ServiceU Deployment

Experiences & 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 neededBackup Compression

Reduces disk space, allowing for disk cost savings or more backups being retained on the same disks

Backup times reduced by ~ 50%

Speeds up Log Shipping (less data to transfer)

19Slide20

Caregroup Healthcare System

Four Hospitals located in Boston16,000 Employees

146 Mission Critical Clinical Applications2 Million Patient Medical RecordsAnnual Revenue : $2 BillionHA/DR requirements for clinical databases: RTO : 0 downtimeRPO: No data lossAll mission-critical SQL Servers are Clustered and Mirrored

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

20Slide21

Existing SQL Server 2005 Cluster

21

Passive

Active

Windows Server 2003 R2

EE SP2, 64-Bit

SQL Server 2005 EE SP2, 64-Bit

EMCSlide22

22

Passive

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….

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 …..

SQL Instance Manual Failover

In-Place Upgrade 1Slide23

23

Active

Passive

Step #4:

Upgrade to SQL Server 2008 on Active Node

Step #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 2008

SQL 2008

Active

In-Place Upgrade

2

Removed from Cluster Group Possible OwnersSlide24

24

Passive

Active

Mirrored SQL

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

SQL

Server 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 databaseSlide25

25

Mirror Server

SQL Server Disaster Recovery

SQL Server Cluster

Cisco Global Site Selector (GSS

)

DNS

SQLNetworkNameA

\SQL1

Active IP:100.10.56.30

Alias Name =

Green

Active IP

:

100.10.56.30

100.85.3.10

Connect to:

Green\SQL1

SQLHostNameB

\SQL1

Passive IP:100.85.3.10

DR Site

Mirroring

Principal Server

Applications:

1- SharePoint

2- SSRS

3- BlackBerry

4- Citrix Server

5- VMware VCSlide26

bwin Corporation

Online gaming company

Gibraltar-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=4000001470Slide27

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”Slide28

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 secondhttp://www.microsoft.com/casestudies/Case_Study_Detail.aspx?casestudyid=4000001470Slide29

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 cacheSlide30

QR National

Based in Brisbane, AustraliaProvides passenger and freight train service

Moved SAP off mainframe to SQL Server 2005GeoCluster InfrastructureDell Power Edge 6950Hitachi Data System SANMajority Node Set ClusterOne Terabyte ERP Database

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

QR National Solution ArchitectureSlide32

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 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=4000002133Slide33

Progressive HA / DR Architecture

Data Center - Colorado

Data Center - Ohio

Multiple OC 48 Links

SQL Server Failover Clustering with Dell

PowerEdge

6650, 6850 and 2950 servers at each data center

EMC

Symmetrix

DMX-3 and 4 Series SANs

SRDF/A SAN replication between the data centers

OC 48 links between data centers

Network and SAN replication shared by Windows Servers and MainframesSlide34

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 Europe

Currently two data centres – in USA & AsiaPlans to deploy another one in Europe Mission critical applications500 tables and over 3,000 stored procedureshttp://sqlcat.com/whitepapers/archive/2009/09/23/using-replication-for-high-availability-and-disaster-recovery.aspx

.

Slide35

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 Financial

Web 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 Reference

Tran FinancialSlide36

Content

HA DR Capabilities and TechnologiesArchitectural Solutions and Customer Deployments

Key TakeawaysQuestions & Answers Slide37

Key Takeaways

SQL server 2008 can meet very high HA DR requirementsUpgrades from SQL Server 2005 to SQL Server 2008 can be achieved with downtime limited to minutes

Demanding HA DR deployments require very good documented operational procedures and highly skilled staff Slide38

Content

HA DR Capabilities and TechnologiesArchitectural Solutions and Customer DeploymentsKey Takeaways

Questions & AnswersSlide39

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 cluster

http://msdn.microsoft.com/en-us/library/ms179530.aspx

How to add node to a SQL Server 2008 failover cluster

http://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

39Slide40

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 RecoveryHigh Availability and Disaster Recovery at

ServiceU: A SQL Server 2008 Technical Case StudyDatabase 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.aspxSlide41

Required Slide

Track PMs

will supply the content for this slide, which will be inserted during the final scrub.

DAT Track Scratch 2 Win

Find the DAT Track Surface Table in the Yellow Section of the TLC

Try your luck to win a Zune HD

Simply scratch the game pieces on

the DAT Track Surface Table and

Match 3 Zune HDs to winSlide42

Resources

Required Slide

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

LearningSlide43

Complete an evaluation on

CommNet

and

enter to win!

Required SlideSlide44

Sign up for Tech·Ed 2011 and save $500

starting June 8 – June 31sthttp://

northamerica.msteched.com/registration

You can also register at the

North

America 2011

kiosk

located at

registration

Join us in Atlanta next year Slide45

©

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.Slide46

Required Slide