Slide1 l.jpg
This presentation is the property of its rightful owner.
Sponsored Links
1 / 31

virtual techdays PowerPoint PPT Presentation


  • 79 Views
  • Uploaded on
  • Presentation posted in: General

INDIA │ 28-30 September 2011. virtual techdays. High Availability - A Story from Past to Future. Balmukund Lakhani │ Technical Lead – SQL Support, Microsoft [email protected] | http://SQLServer-Help.com |. INDIA │ 28-30 September 2011. virtual techdays.

Download Presentation

virtual techdays

An Image/Link below is provided (as is) to download presentation

Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author.While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server.


- - - - - - - - - - - - - - - - - - - - - - - - - - E N D - - - - - - - - - - - - - - - - - - - - - - - - - -

Presentation Transcript


Slide1 l.jpg

INDIA │ 28-30 September2011

virtual techdays

High Availability - A Story from Past to Future

Balmukund Lakhani │ Technical Lead – SQL Support, Microsoft

[email protected]| http://SQLServer-Help.com |


Slide2 l.jpg

INDIA │ 28-30 September2011

virtual techdays

  • Working with SQL Technology since 2001 (almost 10 years)

  • Currently working as Technical Lead with Microsoft SQL Support team

  • Premier Field Engineer @ Microsoft

  • ERP Onsite Support @ RamcoSystems

  • DBA @ RamcoSystems

  • Developer @ RamcoSystems

  • Team Blog – http://blogs.msdn.com/SQLServerFAQ

  • Personal Blog – http://SQLServer-Help.com

  • Active Participant in MSDN SQL Forums.

A B O U T M E…


Slide3 l.jpg

INDIA │ 28-30 September2011

virtual techdays

  • Why High Availability?

  • The Past… Quick Recap

    • Log-Shipping

    • Database Mirroring

    • Failover Clustering

    • Replication

    • Database Snapshot

  • The Future… In Depth

    • High Availability and Disaster Recovery Solution in SQL Server “Denali”

S E S S I O N A G E N D A


Slide4 l.jpg

INDIA │ 28-30 September2011

virtual techdays

Why High Availability?


Slide5 l.jpg

INDIA │ 28-30 September2011

virtual techdays

Mission Critical High Availability Solution

Meets mission critical high availability SLA

Integrated

Flexible

Efficient

RPO and RTO

requirements

Planned and unplanned

downtime

Local HA and

disaster recovery

A unified, simplified

solution

Easy to deploy and

Manage

Reuse existing

investment

SAN vs.. DAS

environments

Cost Effective

HA hardware

Utilization

No idle systems

Improve IT efficiency


Slide6 l.jpg

INDIA │ 28-30 September2011

virtual techdays

High Availability Solutions… The past !


Slide7 l.jpg

Copy

Copy

Copy

INDIA │ 28-30 September2011

virtual techdays

L O G – S H I P P I N G

Copy and Restore Backups

Perform Backups

Secondary Database

Copy and Restore Backups

Secondary Database

Primary Database

Copy and Restore Backups

Raise

Alerts

Secondary Database

Monitor Database


Slide8 l.jpg

Commit

>3

>2

3

1

5

4

2

2

Application

SQL Server

SQL Server

INDIA │ 28-30 September2011

virtual techdays

DATABASE MIRRORING

Mirror is always redoing – it remains current

Witness

Principal

Mirror

Log

Data

Log

Data


Slide9 l.jpg

INDIA │ 28-30 September2011

virtual techdays

O T H E R T E C H O N O L I G E S


Slide10 l.jpg

INDIA │ 28-30 September2011

virtual techdays

  • Replication

  • Database Snapshots

  • Failover Clustering

O T H E R T E C H O N O L I G E S


Slide11 l.jpg

INDIA │ 28-30 September2011

virtual techdays

Database Mirroring

E X I S T I N G T O P O L O G Y

Log Shipping


Slide12 l.jpg

INDIA │ 28-30 September2011

virtual techdays

High Availability Solutions… The Future!


Slide13 l.jpg

INDIA │ 28-30 September2011

virtual techdays

  • AlwaysOn Availability Groups is a new feature that enhances and combines database mirroring and log shipping capabilities

AlwaysOn Availability Groups (Future of High Availability)

Flexible

Integrated

Efficient

  • Multi-database failover

  • Multiple secondaries

    • Total of 4 secondaries

    • synchronous secondaries

      • 1 automatic failover pair

  • Synchronous and asynchronous

    data movement

  • Built in compression and encryption

  • Automatic and manual failover

  • Flexible failover policy

  • Automatic Page Repair

  • Application failover using virtual name

  • Configuration Wizard

  • Dashboard

  • System Center Integration

  • Rich diagnostic infrastructure

  • File-stream replication

  • Replication publisher failover

  • Active Secondary

    • Readable Secondary

    • Backup from Secondary

  • Automation using power-shell


Slide14 l.jpg

INDIA │ 28-30 September2011

virtual techdays

Configure AlwaysOn Availability Group

Demo – Setting up AlwaysOn


Slide15 l.jpg

INDIA │ 28-30 September2011

virtual techdays

  • Replace Database Mirroring and Log Shipping with Availability Group

  • Advantages:

    • One integrated solution for HA and DR

    • Groups of databases failing over together

    • Multiple secondaries

    • Listener for Client Connectivity

    • Backup on secondary

    • Readable secondary

  • Restrictions:

    • No delayed apply on the secondary

What we did in demo?


Slide16 l.jpg

INDIA │ 28-30 September2011

virtual techdays

  • All SQL servers (including the secondary in the DR site) in the same Windows domain.

  • One Windows Server Failover Cluster spreads over the primary and DR sites

  • All the databases must be in FULL recovery model

  • To create WFSC cluster user running creation must have:

    • Domain account

    • Administrator on all machines

    • Have Create Computer objects and Read All Properties rights in AD

    • Detailed Article: http://technet.microsoft.com/en-us/library/cc731002(WS.10).aspx

  • KB 976097

    • Supports SQL Server Failover Cluster Instances with AlwaysOn Availability Groups

  • KB 2494036

    • Supports configuring optimal quorum with non-automatic failover targets

P r e – r e q u i s i t e s


Slide17 l.jpg

INDIA │ 28-30 September2011

virtual techdays

N E W T O P O L O G Y

A

A

A


Slide18 l.jpg

INDIA │ 28-30 September2011

virtual techdays

  • Better SLAs

    • Multiple no data loss secondaries

    • Better data loss protection for DR secondaries through continuous replication

    • Faster failover to DR secondaries through virtual name failover

  • Easier deployment/management

    • Unified solution

    • Simple deployment

    • Unified dashboard

    • Rich diagnostics

    • Centralized management of client connection topology

    • Multi-DB failover

N E W T O P O L O G Y A D V A N T A G E


Slide19 l.jpg

INDIA │ 28-30 September2011

virtual techdays

SQL Server AlwaysOn – BoS(Backup on Secondary)


Slide20 l.jpg

INDIA │ 28-30 September2011

virtual techdays

  • Backups are only allowed on Primary

  • Secondary cannot be leveraged for backups

  • Backup Workload impacts production Server

Database Mirroring Backups


Slide21 l.jpg

INDIA │ 28-30 September2011

virtual techdays

  • Backups are allowed on any replica.

    • Synchronous or asynchronous secondaries

    • Primary backups still work

  • Backup workload does not impact production server.

  • Highly requested feature.

  • Log backups done on all replicas form a single log chain

  • Recovery Advisor makes restores simple

Active Secondary: Offloading Backup


Slide22 l.jpg

INDIA │ 28-30 September2011

virtual techdays

  • Differential backups are not supported on secondary

  • Only Copy-only full backups are supported on secondary

    • The only distinction is the differential bitmap clearing

  • Advisable for backups to be stored centrally

Restrictions, cautions, and gotchas


Slide23 l.jpg

INDIA │ 28-30 September2011

virtual techdays

Backup on Secondary

&

Use them to create new Replica

Demo - Backup on Secondary


Slide24 l.jpg

INDIA │ 28-30 September2011

virtual techdays

SQL Server AlwaysOn – Readable Secondary


Slide25 l.jpg

Reports

INDIA │ 28-30 September2011

virtual techdays

SQLServr.exe

SQLServr.exe

Primary

Secondary

Readable Secondary

InstanceA

Database Log Synchronization

InstanceB

DB1

DB2

DB1

DB2


Slide26 l.jpg

INDIA │ 28-30 September2011

virtual techdays

Readable Secondary Data Latency

Replica

Primary

DB1

DB1

Log Recieve

Log Capture

Network

Log Pool

Log Cache

Redo Thread

Commit

Log Cache

Redo Pages

Log Harden

Log Flush

Page Updated

DB1 Data

DB1 Log

DB1 Log

DB1 Data

Acknowledge Commit


Slide27 l.jpg

INDIA │ 28-30 September2011

virtual techdays

Reading Data From Secondary

&

Creating Secondary Routing

Demo – Readable Secondary


Slide28 l.jpg

Reports

INDIA │ 28-30 September2011

virtual techdays

A

F i n a l T o p o l o g y

A

A

A

Backups

Synchronous

Data Movement

Asynchcronous Data Movement


Slide29 l.jpg

INDIA │ 28-30 September2011

virtual techdays

  • SQL Server AlwaysOn is a comprehensive high availability solution

    • Better application availability,

    • Higher return on investment and

    • Simplified deployment and management

  • AlwaysOnAvailability Group provide flexibility in HA configuration

S U M M A R Y


Slide30 l.jpg

INDIA │ 28-30 September2011

virtual techdays

  • Monitoring Performance in Cloud for SQL Azure Applications

    • Vinod Kumar

    • 3:00pm - 4:00pm

  • Managing SQL Server Made Easy - Features and Concepts

    • Amit Banerjee

    • 4:15pm - 5:15pm

RELATED CONTENT in VTD Sep 29th 2011 – DBA Track


Slide31 l.jpg

THANKS│28-30 September2011

virtual techdays

Please do give your Feedback, Complete evaluation at the end of this session. You could also write to be at :

[email protected] http://twitter.com/blakhani


  • Login