Updated: March 2017
SQL
SQL 2016 Licensing guide
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.
Whitepaper:
Performance Guidance for SQL Server in Windows Azure Virtual Machines
http://msdn.microsoft.com/en-us/library/dn248436.aspx
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.
Scenarios
-
Reduce transaction against Azure Storage
-
Reduce disk IO latency when IO rate is low
-
Impr0ve boot time
Network Bandwidth and Delay
Bandwidth
I/O go through separate stack from Guest VM network
Latency
“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
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)
TempDB
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
Backups
- Backup directly to BLOB storage via URL – within the same data center, if another datacenter will be charged egress
- Backup to regular disk
Repurposing of data -??
How to populate test/dev?