Luis Vargas | Senior Program Manager Darmadi Komo | Senior Product Marketing Manager
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 VMs
Overview
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
Windows Azure VMs
Storage
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 VMs
General approach for high availability
SQL Server on Windows Azure VMs
HA & 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 Doesnt include servicing of guest OS DR
<30 min (No SLA) Azure Ops recovers VM (No SLA)
Windows Azure VMs
External 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 with Windows 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 HA within Windows Azure
Why?
Azures 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
Azures service healing involves restarting VM in different host
~12 min downtime each time
Azures 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
Witness
Failure detection for SQL Server Failover takes ~15s
VM
P S
S P
VM
VM
What to do?
Technology
Supported SQL Server HA technologies
SQL Server Version SQL Server 2012 SQL Server 2014 SQL Server 2008 R2 SQL Server 2012
Zero Data Loss Yes (Sync Mode)
Scope
# Replicas
Automati c Failover Yes (w/ additional cluster member) Yes (w/ Witness)
Readable Secondaries Yes
Availability Groups
DB(s)
Database Mirroring
Yes (High Safety Mode)
DB
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 Azures upgrade impacts at most one replica
Same VNet
Ensures that VM IPs dont change after Azures service healing (avoids DNS update time: ~12 min) Requirement for Active Directory
A sample configuration
SQL Server DR between On-Premise and 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
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
Deployments may be inverted
Sometimes people want to keep a replica on-premise
What to do?
Technology Availability Groups
Supported SQL Server DR technologies
SQL Server Version SQL Server 2012 SQL Server 2014 Zero Data Loss No (Async Mode) Scope DB(s) # Secondaries 4 Automatic Failover No Readable Secondaries Yes
Database Mirroring
SQL Server 2008 R2 SQL Server 2012
No (High Performance Mode) No
DB
No
Limited (database snapshots)
Log Shipping
SQL Server 2008 R2 SQL Server 2012
DB
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
In SQL 14 CTP2..
Availability Groups
Add Azure Replica Wizard
Many customers cant 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 Groups
Add Azure Replica Wizard
Sync Log Synchronization
Async Log Synchronization
Availability Groups
Add 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
Resources
SQL 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/enus/manage/services/networking/create-a-virtual-network/ Create Virtual Network from on-premise to Azure https://www.windowsazure.com/enus/manage/services/networking/cross-premises-connectivity/ Set up Active Directory Domain Controller http://www.windowsazure.com/enus/manage/services/networking/active-directory-forest/
Luis.Vargas@Microsoft.com
