SQL 2016 Standard – Basic Availability Group

High Availability for SQL 2016 Standard edition now has the “Basic Availability Group”.

The Basic Availability Group is based on AlwaysOn Availability Groups (AAG) that was released in SQL 2012 Enterprise. AAG continues to be an Enterprise edition feature set.

Basic availability groups use a subset of features compared to advanced availability groups on SQL Server 2016 Enterprise Edition. Basic availability groups include the following limitations:

  • Limit of two replicas (primary and secondary).
  • No read access on secondary replica.
  • No backups on secondary replica.
  • No support for replicas hosted on servers running a version of SQL Server prior to SQL Server 2016 Community Technology Preview 3 (CTP3).
  • No support for adding or removing a replica to an existing basic availability group.
  • Support for one availability database.
  • Basic availability groups cannot be upgraded to advanced availability groups. The group must be dropped and re-added to a group that contains servers running only SQL Server 2016 Enterprise Edition.
  • Basic availability groups are only supported for Standard Edition servers.

Basic Availability Group – info on MSDN


TechEd2014 : SQL on Azure VMs

My notes from the TechEd session on SQL inside of Azure VMs


This stuff is confusing. No standard storage terminology being used.

Someone asked if the disks in the BLOBS where Thick or Thin. Answer: We don’t use those terms.



Performance Guidance for SQL Server in Windows Azure Virtual Machines



Azure VMs tie to Azure Storage – need to understand how Azure Storage works

Azure service is a shared service, multi-tenet service


Azure BLOB storage

 VHDs stored in BLOB

3 copies automatic – persistent copies

Shared service – noisy neighbors can be an issue

No direct performance correlation between disk requests made by VM and the underlying physical disk

No performance SLA’s

500TB / BLOB

Total requests rate 20,000 /sec

Geo-redundant storage = 10gb/sec Ingress, 20 gb/sec Egress

Local redundant = 20gb/sec Ingress 30 gb/sec Egress


Two tiers of service for VMs

VM Basic tier = A0-A4 tiers

VM Standard tier = A5-A9 tiers


Disk types

C:- 127GB – persistent

D: Temp drive – 20GB-605GB depend on tier – may be wiped on reboot – local disk, low latency

Data Disks – up to 1TB each (thin)


Where and when you provision your VM matters – the service can move your VM around

Maintenance operations have an effect on performance – notified from Microsoft when they do maintenance


C:- D:are local disks versus Azure disks stored in a BLOB

– need to test to determine scenarios for performance of where to put what


VM Disk Cache

“Host Cache Preference” when creating a disk

Two tier cache:

Tier 1 – Most recently accessed data stored in RAM of physical virtualization host. This cache is sharedby all VMs on that host

Tier 2 – Less recently accessed data stored on the local disk of the physical host (disk based cache). Ther is cache space reserved to each VM OS disk and Data disk based VM size.


  • Reduce transaction against Azure Storage
  • Reduce disk IO latency when IO rate is low
  • Impr0ve boot time


Network Bandwidth and Delay


I/O go through separate stack from Guest VM network


“Cloud Service” is a management, config, security, networking and service model boundary

Affinity group : Compute and storage to be placed close to each other

Going through load balancers adds latency


IO paths on Azure VM


Azure diagram


Disk recommendations:

Use Data disks (not local OS disk) for DBs larger than 10GB

Cache settings:

Use “None” for high OLTP – random I/O

Use “Read” if workload is sensitive to latency

Use “Read/Write” for sporadic disk access – not a write back cache

Static data, seldom accessed

Cache setting can be changed via PowerShell


VM instance size and limits

Place user DB & logs on different data disks

If on OS drive:

Avoid OS disk stripping, use SQL striping (ie. file group)

Warm up disk before measuring performance

After initialize the file by writing some bit patterns sequentially using SQLIO

Consider enabling SQL data compression (row, page) for I/O bound workloads

Reduces I/O

CPU usage can increase

Enable instant file initialization

Don’t use auto-grow, pre-allocate instead

Use 64KB DAU


Placement of data files

Option 1: DB across multiple files with a file group

Option 2: Create an “OS volume” on top of multiple disk

Option 3: Multiple striped volumes each with one or more data disks (based on specific I/O performance requirements)(Host level RAID 0)



Place on OS or Data disks

Don’t use D: temp drive – performance issues can occur


Disk warm up

Disk Load balancers – cold disks get less performance, hot disks get better performance

Idol disks (cold) for 20 mins will reduce your performance. Azure remove disk load balancers from cold disks.

Manually Warm up disks for 10 mins – will get you better performance

Use a Script to warm up


VM Dashboard

Monitor tab for storage account

Storage analytics logging

Minimal or Verbose

Aggregated data place into hidden place in BLOB “&log” – accessible via PowerShell


Moving to the cloud doesn’t solve app design issues

Performance baseline needed prior to moving to Azure in order to understand performance



  1. Backup directly to BLOB storage via URL – within the same data center, if another datacenter will be charged egress
  2. Backup to regular disk


Repurposing of data -??

How to populate test/dev?