mod 2 - sql iaas ha and dr in windows azure
TRANSCRIPT
Luis Vargas | Senior Program ManagerDarmadi Komo | Senior Product Marketing Manager
SQL Server on Windows Azure VMsHigh Availability & Disaster Recovery
Meet Luis Vargas | @lvargas• Senior Program Manager in SQL Server
– SQL Server IaaS / hybrid scenarios– AlwaysOn (High Availability)
• Over 10 years of industry experience– Microsoft: PM– Financial Services: Dev
• PhD in Database Systems– Cambridge University
Windows Azure VMs Overview
Windows Azure VMsOverview
• VMs hosted on Windows Azure infrastructure (“IaaS”)• From Microsoft images or your own images (VHDs)• Fast provisioning (10 minutes)
• Pay per use• VM: By minute
• Cost depends on size and licensing (e.g. SQL)• Network: only incoming (not outgoing)• Storage: only used (not allocated)
• Elasticity• 1 core / 2GB RAM / 1TB 8 cores / 56GB RAM / 16TB
• You manage the VM• Security, patching, monitoring, etc
• Every VM disk is a BLOB in Azure Storage
• 3 sync local copies• No data loss• highly available (transparent to VM)
• 3 more async remote copies (if geo-replication enabled)• For disaster recovery (manually managed by Microsoft)
Windows Azure VMsStorage
MICROSOFT CONFIDENTIAL – INTERNAL ONLY
Physical Machines
Power UnitRack Switch
Availability Set
VM1VM1 VM2 VM2
Load-Balanced Set
Load Balanc
er
Windows Azure VMsGeneral approach for high availability
SQL Server on Windows Azure VMsHA & DR support• HA
• SLA: No data loss• If VM becomes unavailable, restart in another host• SLA: 1 of 2 VMs in Availability Set:
• 99.95% (<22 min downtime p/month)• Includes
• Planned downtime due to (monthly) host OS servicing• Unplanned downtime due to physical failures
• Doesn’t include servicing of guest OS
• DR• <30 min (No SLA)• Azure Ops recovers VM (No SLA)
Windows Azure VMsExternal Connectivity
• Over the Internet• Public endpoints
• Over a site-to-site tunnel• Extend on-premise network to Azure
• Can join Azure VMs to on-premise domain• Secure tunnel• VPN Device / Windows Server RRAS
SQL Server on Windows Azure VMs• Many SQL versions supported
• SQL 2014 CTP1, SQL 2012 SP1, SQL 2008 R2
• (Almost) 100% compatibility• No SQL Failover Clusters (yet..)
• You manage SQL Server• Security, Backups, HA, DR, etc
SQL Server HA/DR Scenarios withWindows Azure VMs
SQL Server HA/DR Scenarios• HA within Azure
• Availability of SQL Server in Azure VM • Protect from issues impacting SQL Server or VM• Use another SQL Server VM in same Azure DC
• DR between On-Premise and Azure• Availability of on-premise SQL Server (physical or
virtual)• Protect from issues impacting on-premise data center• Use a SQL Server VM in Azure
SQL Server HAwithin Windows Azure
Why?• Azure’s failure detection for VM (not SQL
Server)• SQL Server service could be down or hung• Servicing of guest OS causes downtime• Servicing of SQL Server causes downtime
• Azure’s service healing involves restarting VM in different host
• ~12 min downtime each time
• Azure’s upgrade involves servicing host OS and restarting VM in the host
• ~15 min downtime each time
What to do?Configure a SQL Server HA technology
Windows Azure
S PP S
VM VM
• Failure detection for SQL Server• Failover takes ~15s
VM
Witness
What to do?Supported SQL Server HA technologies
Technology SQL Server Version
Zero Data Loss
Scope # Replicas
AutomaticFailover
ReadableSecondaries
Availability Groups
SQL Server 2012SQL Server 2014
Yes(Sync Mode)
DB(s) 4
9
Yes(w/ additional cluster member)
Yes
Database Mirroring
SQL Server 2008 R2SQL Server 2012
Yes (High Safety Mode)
DB 1 Yes(w/ Witness)
Limited(database snapshots)
Availability Groups• Provide many other capabilities:
• Flexible Failover Policy• Automatic Page Repair• Backups on Secondaries• Improved Manageability• FileStream & FileTable support
• But remember they need:• Windows Cluster
• No shared storage• Same Windows Domain
• Needs an Active Directory Domain Controller
Before starting any configuration..Ensure the VMs are hosted appropriately
• Same Affinity Group• Same Azure cluster• Reduces log synchronization latency
• Same Availability Set• Different Fault Domain (rack) and Upgrade Domain• Ensures that a rack failure or Azure’s upgrade impacts
at most one replica
• Same VNet• Ensures that VM IPs don’t change after Azure’s service
healing (avoids DNS update time: ~12 min)• Requirement for Active Directory
A sample configuration
SQL Server DRbetween On-Premiseand Windows Azure
Why?• An event can cause on-prem SQL Server to
become unavailable• Temporarily (e.g. gateway failure)• Permanently (e.g. flooding)
• A disaster recovery site is expensive• Site rent + maintenance• Hardware• Ops
What to do?Configure a SQL Server DR technology
• Deploy one or more secondary replicas for on-premise SQL Server
• Replicas continuously synchronize• Replica (Azure) storage guarantees no data loss
• At best region• West US, East US, East Asia, Southeast Asia, North
Europe, West Europe• Latency / political considerations
• Low TCO• VM and storage• Free incoming traffic
What to do?Configure a SQL Server DR technology
Deployments may be invertedSometimes people want to keep a replica on-premise
What to do?Supported SQL Server DR technologies
Technology
SQL Server Version
Zero Data Loss
Scope
# Secondaries
AutomaticFailover
ReadableSecondaries
Availability Groups
SQL Server 2012SQL Server 2014
No(Async Mode)
DB(s) 4 No Yes
Database Mirroring
SQL Server 2008 R2SQL Server 2012
No(High Performance Mode)
DB 1 No Limited(database snapshots)
LogShipping
SQL Server 2008 R2SQL Server 2012
No DB N No Limited(standby state)
- Async Mode (High Performance) avoids impacting primary performance- Async Mode only supports Force Failover to make potential data loss a
conscious decision
Availability Groups• Provide additional benefits:
• Integrated HA/DR• Offload Read workloads to Azure
• Connect reporting/BI apps• Migrate apps and run against local secondaries
• Offload Backups to Azure• A solution for policy compliance
• Remember it requires:• Same Windows Cluster • Same Windows Domain
What to do?• Configure site-to-site tunnel
• Using VPN device or Windows Server RAS
• Join VM to the Domain
• Add VM SQL Server as replica of Availability Group
Sample Deployment
SQL Server DRbetween On-Premise and Windows AzureDemo
In SQL 14 CTP2..
• Many customers can’t afford a DR site• Site rent + maintenance, hardware, Ops
• SQL Server 2012: Started supporting replicas on Windows Azure VMs this year
• Offload read workloads • Offload backups (policy compliance)• Disaster recovery
• At best region• West US, East US, East Asia, Southeast Asia, North
Europe, West Europe• Latency / political considerations
Availability GroupsAdd Azure Replica Wizard
Sync LogSynchronization
Async Log Synchronization
Availability GroupsAdd Azure Replica Wizard
• Low TCO• VM and storage• Free ingress traffic
• SQL Server 2014: “Add Replica Wizard” supports Windows Azure
• E2E: From provisioning VM to starting log synchronization
• Validates environment• Handles failures
Availability GroupsAdd Azure Replica Wizard
Add Azure Replica Wizard
Demo
ResourcesSQL Server HA/DR on IaaS• Tutorials and scripts
• http://msdn.microsoft.com/en-us/library/jj870962.aspx
IaaS• Create Virtual Network in Azure
• https://www.windowsazure.com/en-us/manage/services/networking/create-a-virtual-network/
• Create Virtual Network from on-premise to Azure• https://www.windowsazure.com/en-us/manage/services/n
etworking/cross-premises-connectivity/• Set up Active Directory Domain Controller
• http://www.windowsazure.com/en-us/manage/services/networking/active-directory-forest/
Download SQL Server 2014 CTP2 today! Give us feedback at Microsoft Connect!
© 2013 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.