sql server on windows azure vms : high availability...
TRANSCRIPT
http://francescodiaz.azurewebsites.net
Agenda
• SQL workloads on Azure
• Microsoft Azure IaaS concepts
• Best Practices for SQL running on Azure VMs
• HA/DR
• SQL DB HA/DR
SQL Server Cloud Continuum
Low Control | Low Maintenance
Sh
are
d
Lo
wer
cost
Ded
icate
d
Hig
her
cost
High Control | High Maintenance
Hybrid
SQL ServerPhysical Machines
SQL Server Private CloudVirtualized Machines
SQL Server in Azure VMVirtualized Machines
Virtualized DatabasesAzure SQL Database
SQL Server in Azure VM
You access a VM with SQL Server
You manage SQL Server and Windows: High
Availability, Backups, Patching
You can run any SQL Server version and edition
Full on-premise compatibility
Different VM sizes
VM availability SLA: 99.95%: Can achieve higher
availability (~99.99%) configuring AlwaysOn
Reuse on-premise infrastructure (e.g. Active Directory)
Azure SQL Database & SQL Server in Azure VM
Azure SQL Database
You access a DB
DB is fully managed: High Availability, Backups,
Patching
Runs latest SQL Server version, based on Enterprise edition
Incomplete on-premise compatibility
Different DB sizes and performances (predictable)
DB availability SLA: 99.99%
Executing SQL Server on Microsoft Azure VMs
• To better execute SQL Server in a Azure VM, you need to understand at least 4 Microsoft Azure layers:• Cloud Services
• Virtual Machines
• Storage
• Networking
• And you will probably need to learn a bit of coding and of powershell
http://msdn.microsoft.com/en-us/library/jj152841.aspx
A couple of words on: Cloud Services
• It is a logical container of VMs and/or applications
• A VM will run inside of the context of a Cloud Service
• Every Cloud Service has a Virtual Public IP (VIP)• Up to 5 Reserved IPs (can be increased)
• …and a FQDN name [CloudServiceName].cloudapp.net
• You can add many VMs to the same Cloud Service
• Adding more VMs to the same Cloud Service allows load balancing of specific ports
Cloud Services – Reserved IPs
New-AzureReservedIP – ReservedIPName “MyWebsiteIP”
–Label “WebsiteIP” –Location “Japan West”
New-AzureVMConfig -Name "WebAppVM" -InstanceSize
Small -ImageName $images[130].ImageName | Add-
AzureProvisioningConfig -Windows -AdminUsername
$username -Password $password | New-AzureVM -
ServiceName "MaheshWebApp" -ReservedIPName
"MyWebSiteIP" -Location "Japan West"
A couple of words on Azure Storage• Persistent (Page Blobs)
• Standard Storage• 300 or 500 IOPS per Disk
• 1 TB per disk
• VHD format
• Premium Storage• SSD – up to 5000 IOPS per Disk (it depends on the space assigned to each disk [e.g. 100gb =
500 IOPS]
• 1 TB per disk
• VHD format
• Not Persistent (Page Blobs)• Local Storage• Standard or SSD
A couple of words on: Azure Storage
• Standard – up to 500TB per sa• Locally Redundant, Geo-Redundant, Geo-R with ReadOnly Access
• 3 copies per datacenter, replica is done on paired data centers
• Premium – up to 35TB per sa• Locally Redundant
• Local (SSD) Storage useful for BPE and TEMPDB
A couple of words on Azure VMs
• A Series• Basic• Low disks, no autoscaling, Availability SET (Update Domain, Fault Domain)
• Standard• Std Disks, HA & LB
• D Series (std)• 60% faster CPUs than A, local SSD Storage
• DS Series (std)• Premium Storage ready
• G Series (std)• High Memory and bigger storage per VMs
• Intel E5 v3 CPUs powered
Supports many versions of SQL Server• SQL 2014 (Enterprise / Standard / Web)
‐ Including a DW-optimized image
• SQL 2012 (Enterprise / Standard / Web)‐ Including a DW-optimized image
• SQL 2008 R2 (Enterprise / Standard / Web)
Supported versions of Microsoft Windows• Windows Server 2012 R2
• Windows Server 2012
• Windows Server 2008 R2
Near-100 percent compatibilitySQL Server failover coming very soon
You manage SQL ServerSecurity, backups, high availability, and disaster recovery
SQL Server on Microsoft Azure virtual machines
SQL Server on Azure Virtual Machines – A series
A0 Shared 768 MB 20 GB 20 GB 1 1x300 1x500
A1 1 1.75 GB 40 GB 70 GB 2 2x300 2x500
A2 2 3.5 GB 60 GB 135 GB 4 4x300 4x500
A3 4 7 GB 120 GB 285 GB 8 8x300 8x500
A4 8 14 GB 240 GB 605 GB 16 16x300 16x500
A5 2 14 GB 135 GB 4 4x500
A6 4 28 GB 285 GB 8 8x500
A7 8 56 GB 605 GB 16 16x500
A8 8 56 GB 382 GB 16 16x500
A9 16 112 GB 382 GB 16 16x500
A10 8 56 GB 382 GB 16 16x500
A11 16 112 382 GB 16 16x500
Size CPU Memory
A8Intel® Xeon® E5-2670
8 core da 2,6 GHz
DDR3-1600 MHz
56 GB
A9Intel® Xeon® E5-2670
16 core da 2,6 GHz
DDR3-1600 MHz
112 GB
2 NICS:
10GB to Azure services
RDMA 32GB Infiniband
SQL Server on Azure Virtual Machines – D series
D1 1 3.5 GB 50 GB 1 1x500
D2 2 7 GB 100 GB 2 2x500
D3 4 14 GB 200 GB 4 4x500
D4 8 28 GB 400 GB 8 8x500
D11 2 14 GB 100 GB 2 2x500
D12 4 28 GB 200 GB 4 4x500
D13 8 56 GB 400 GB 8 8x500
D14 16 112 GB 800 GB 16 16x500
SQL Server on Azure Virtual Machines – DS series
VM Size CPU cores Memory Max. IOPSMax. Disk
Bandwidth
STANDARD_DS1 1 3.5 3,200 32 MB per second
STANDARD_DS2 2 7 6,400 64 MB per second
STANDARD_DS3 4 14 12,800 128 MB per second
STANDARD_DS4 8 28 25,600 256 MB per second
STANDARD_DS11 2 14 6,400 64 MB per second
STANDARD_DS12 4 28 12,800 128 MB per second
STANDARD_DS13 8 56 25,600 256 MB per second
STANDARD_DS14 16 112 50,000 512 MB per second
Premium Storage Disk
TypeP10 P20 P30
Disk size 128 GiB 512 GiB 1024 GiB (1 TB)
IOPS per disk 500 2300 5000
Throughput per disk 100 MB per second * 150 MB per second * 200 MB per second *
Azure Virtual Machine G series
VM Size Cores RAM (in
GB)
Local SSD
Storage
Persistent Data
Disks
Standard_
G1
2 28 406 GB 4
Standard_
G2
4 56 812 GB 8
Standard_
G3
8 112 1,630 GB 16
Standard_
G4
16 224 3,250 GB 32
Standard_
G5
32 448 6,500 GB 64
Intel Xeon E5 v3 series
http://www.intel.com/content/www/us/en/processors/xeon/xeon-e5-solutions.html
G-series virtual machines are currently only available in West US and East US 2 regions
A couple of words on: Azure Networks
• You can expose a SQL Server VM on the web using ENDPOINTS (Port redirection, ACL are supported) or a secure connection (VPN, ExpressRoute)
• Each VM has a Private IP• If you set a Virtual Network you can specify subnets (Static IPs available)
• The VIP is associated to the CS for his lifetime, unless you deallocate it
• DHCP (infinite lease)/DNS are provided by VNETs (you could specify your own DNS)
• Virtual Networks are bounded to a specific region
Azure Gateways – high performance
Gateway SKU ExpressRoute
Throughput*
S2S VPN
Throughput
*
Max.
number of
S2S
Tunnels
Default ~500Mbps ~80Mbps 10
High
Performance
~1000Mbps ~200Mbps 30
PS D:\> New-AzureVNetGateway –VNetName
MyAzureVNet –GatewayType DynamicRouting –
GatewaySKU HighPerformance
Macro-steps for hybrid configurations• Open a Microsoft Azure subscription
• An available public IP in the on-premises network (needed for Site-2-Site VPN)
• Point-to-site VPN not supported for advanced SQL Server configurations (e.g. AlwaysOn)
• On-premises Environment ready (e.g. DNS and Domain Controller)
• Virtual Network configuration in Microsoft Azure
• Datacenter selection
• Set your DNS server on the VNET [highly recommended for Hybrid scenarios]
• Virtual Gateway configuration
• IMPORTANT : carefully verify communication between On-Prem and cloud (nslookup, dcdiag, etc.)
• On-premises VPN config (using VPN device script)
• Cisco
• Juniper
• Windows Server 2012 RRAS (software)
• Other (E.G. Watchguard, Citrix, F5 …)
VPN Device info/requirements• Static routing
A Router with manually configured routing tables• VPN s2s only - IKEv1
• Pre-shared key (http://msdn.microsoft.com/en-us/library/windowsazure/jj154114.aspx)
• Encryption algorithms - AES256, AES128, 3DES
• Hashing algorithms - SHA1
• Dynamic routing
A router with dynamically configured routing tables• VPN s2s and p2s (max 240 clients connected) – IKEv2
• Pre-shared key for s2s and certificates for p2s
• Encryption algorithms - AES256, 3DES
• Hashing algorithms - SHA1http://msdn.microsoft.com/en-us/library/windowsazure/jj156075.aspx
http://technet.microsoft.com/en-us/library/cc957844.aspx
Connectivity Best Practices
• Configure Domain Controller
• Configure a VPN tunnel from on-premises and use on-premises Domain Controller
Why SQL Server high availability?
Failure detection for Microsoft Azure virtual machines (not for SQL Server)
Healing of Microsoft Azure Services involves restarting virtual machines in different hosts
Upgrades to Microsoft Azure involves servicing the host operating system and restarting virtual machines in the host
SQL Server service may be down or hung
Servicing of guest operating system causes downtime
Servicing of SQL Server causes downtime
Approximately 12 minutes of downtime each time
Approximately 15 minutes of downtime each time
Why do we need to configure HA for SQL Server?• Storage (Disks) have embedded HA but
• Single VMs DON’T have embedded HA, so a single VM could not be always available in case of Updates/Faults (NO SLA provided with single VMs)
• Azure provides load balancing features• Only few SQL Server services can benefit from AS: e.g. Reporting Services
• Availability Sets allow VMs to be correctly managed by Azure during planned Upgrades and Updates
• For “traditional” SQL Server services you need traditional HA methods
HA scenarios–Microsoft Azure only
• AlwaysOn Av. Groups, Mirroring, Log Shipping (Failover Cluster Instances not yet available)
• Can be INTRA datacenter or between different datacenters
HA scenarios – Hybrid cloudAlwaysOn Av. Groups, Mirroring, Log Shipping
VPN not required with certificates
http://blogs.technet.com/b/francesco_diaz/archive/2014/02/05/configurin
g-database-mirroring-across-two-windows-azure-datacenters.aspx
39
• What’s being delivered
‐ Wizard to add a replica in a Windows Azure VM
• Main benefits
‐ It takes care of:
VM Creation (including data disks and Network Config)
Joining VM to Domain and Cluster
Adding Node to Availability Group
Database backup/restore and AG configuration
• You need to manually configure the listener
On-Premises
http://blogs.technet.com/b/francesco_diaz/archive/2014/0
2/06/sql-server-2014-alwayson-availability-groups-add-
azure-replica-wizard-in-action.aspx - Tutorial
On-prem network
10.1.50.0/24 Azure network
10.0.1.0/24
VPN Device
FRANCEDRRAS
VPN tunnel
RRAS service
Windows ClusterAvailability Group
FRANCEDN1 AZURE
Virtual
Gateway
Virtual
NetworkFRANCEDN2
FRANCEDN3
Windows ClusterAvailability Group
FRANCEDN5
FRANCEDN4
CLIENT
BACKUP
REPORT
S
AlwaysOn Listener configuration in Hybrid Cloud• Step 1- configure endpoint on Azure VMs inside
the Cloud Service• Hotfix required for
win 2008R2/2012http://support.microsoft.com/kb/2854082
AlwaysOn Listener configuration in Hybrid Cloud• Step 2- create listener using Failover Cluster
manager…
• …and add the Dependency
• Then, manually modify it
using powershell
AlwaysOn Listener configuration in Hybrid Cloud• Step 3- set TCP listener port using SSMS
Troubleshooting guide :http://msdn.microsoft.com/en-us/library/windowsazure/dn495646.aspx
Only one Listener per Cloud Service allowed
Standard Geo-replication with offline secondary
US East
LS ABC
Self-service
activation of
secondary
(during incident)
DB
LS XYZ
DB
US West