Transcript SQL 2016 AlwaysOn AG

SQL Server 2016 AlwaysOn AGs:
Overview & Breakthrough Perf Enhancements
Flipping the /faster Bit:
Jimmy May, MCM
SQL Server Solutions Architect
[email protected]
@aspiringgeek
1
THIS IS AN OVERVIEW


Focus is on new breakthrough perf enhancements
–
Experimental results thus far
–
What’s coming
Still time to find another session!
2
Forward Looking Statements
During our meeting today, we may make forward-looking statements.
Any statement that refers to expectations, projections or other characterizations of future events or circumstances is a
forward-looking statement, including those relating to market position, market growth, product sales, industry trends,
supply chain, future memory technology, production capacity, production costs, technology transitions, construction
schedules, production starts, and future products. This presentation contains information from third parties, which
reflect their projections as of the date of issuance. Actual results may differ materially from those expressed in these
forward-looking statements due to factors detailed under the caption “Risk Factors” and elsewhere in the documents
we file from time to time with the SEC, including our annual and quarterly reports. We undertake no obligation to
update these forward-looking statements, which speak only as of the date hereof.
©2014 SanDisk Corporation. All rights reserved. SanDisk is a trademark of SanDisk Corporation. Lightning, Lightning Eco, Lightning Ascend, Lightning Ultra, Optimus, Optimus Eco, Optimus Ascend, Optimus
Ultra, Optimus Extreme, CloudSpeed, CloudSpeed Eco, CloudSpeed Ascend, CloudSpeed Ultra, CloudSpeed Extreme, FlashSoft, ZetaScale, Guardian Technology, FlashGuard, DataGuard, EverGuard, SanDisk
ION Accelerator, Fusion ioMemory, Fusion ioSphere, Fusion ioTurbine, Fusion ioVDI, and others are trademarks of SanDisk Enterprise IP LLC. Other brand names mentioned herein are for identification
purposes only and may be the trademarks of their respective holder(s).
3
Bio
Jimmy May, MCM
• SanDisk SQL Server Solutions Architect
Formerly:
• Sr. Program Manager, SQL CAT
• SQL Server Customer Advisory Team
• MSIT Principal Architect: Database
Microsoft Certified Master: SQL Server (2009)
MS IT Gold Star Recipient (2008)
Microsoft Oracle Center of Excellence (2008)
SQL Server MVP Nominee (2006)
Indiana Windows User Group www.iwug.net
• Founder & Board of Directors
Indianapolis Professional Association for SQL Server www.indypass.org
• Founder & Member of Executive Committee
SQL Server Pros: Founder & Visionary-in-Chief
SQL Innovator Award Recipient (2006)
• Contest sponsored in part by Microsoft
Formerly Chief Database Architect for high-throughput OLTP VLDB at
ExactTarget (recent IPO)
Senior Database Administrator for OpenGlobe/Escient
www.twitter.com/aspiringgeek [email protected]
http://sqlblog.com/blogs/jimmy_may
[email protected]
4
C:\>sqlservr.exe /faster
Copyright © 2015 SanDisk. All rights reserved.
5
Who: Jimmy May & MS MTC Director Ross LoForte
What: #DataDriven: MS MTC Studio Live Webinar: Mission Critical
Performance leveraging SQL Server 2016 on SanDisk Flash
When: Thursday 7 April 2016 @10a PDT
Where: http://bit.ly/MTCPerf (now on demand)
6
What is your HA/DR Plan?
7
8
Overview

HA/DR

Real-life

FCI vs. AGs

Topology

Quorum

Flexible Failover Policy

Other technologies
9
HA vs. DR


High Availability
–
Ability of a system to quickly recover from a partial or full failure
–
Being able to service a component without shutting down the entire operation
–
System or component that is continuously operational for a long period of time
–
Often quantified as percentage uptime
Disaster Recovery
–
Plan/process to return to normal operations after a catastrophic event
–
Plan/process to limit loss of data and/or operational continuity after a
catastrophic event
–
Often quantified with RPO and RTO
10
HA/DR for Real: People, Processes, & Technology

App Design

Database Software

Connectivity

OS

HW
11
Prototypical Architecture
A
A
A
A
Sync Log
Synchronization
Async Log
Synchronization
12
Listeners

DNS Name

Transparent to app

No change to connection string
–
(unlike Database Mirroring)
13
Other Pivots

On-prem vs. hybrid

Add Azure Replica Wizard

Version limits

Log synchronization

Managment Options

Capabilities

Cautions vs. Benefits
14
Functionality by SQL Server Version
SQL Server 2012
Max Nodes
SQL Server 2014
SQL Server 2016
5
9
9
1 Primary
+ 4 Secondary
1 Primary
+ 8 Secondary
1 Primary
+ 8 Secondary
Max Synchronous
Replicas
2
2
3
Max Automatic
Failover Partners
2
2
3
15
Readable Secondaries

Readable

Read-only Routing

Isolation & Optimization

On-prem vs. Azure
16
Enhanced AlwaysOn Availability Groups
Greater scalability:
Unified HA Solution
• Load balancing readable secondaries
• Increased number of auto-failover targets
• Log transport performance
AG_Listener
• Log redo
Improved manageability:
AG
Asynchronous data
movement
Hong Kong
(Secondary)
• DTC support
AG
AG
New York
(Primary)
Synchronous data
movement
New Jersey
(Secondary)
• Database-level health monitoring
• Group managed service account
• NEW! Standard Edition support
•
Database Mirroring marked for deprecation
17
Using Availability Groups instead of Data Warehouse
Always on Availability Group
Secondary
Replica
Primary
Replica
Key points:
•
Mission-critical operational workloads
are typically configured for High
Availability using AlwaysOn Availability
Groups.
•
You can offload analytics to a readable
secondary replica.
Secondary
Replica
Secondary
Replica
19
May’s I/O Mantra:
Requesting & Monitoring Storage
X capacity (GB)
at Y IOPs (transfers/sec)
at <=10ms latency for OLTP data files
at Z throughput (MB/sec)
at <=30ms latency for DW data files
at 0ms-2ms latency for log files
20
What if I told you...
...the only good I/O is the one
for which you do not ask?
21
The best I/O is the one you don’t have to do.
—Gene Amdahl, author of Amdahl's Law
22
Caching Algorithms: Your Experience May Vary...
23
AlwaysOn Availability Groups Log Transport
High availability using only local storage
–
Changes on the primary server are copied to secondary servers
–
A secondary server takes over if the primary server fails
Limitation in SQL 2012 and SQL 2014:
–
Log transport originally limited to 40 MB/s (up to ~100 MB/s for Hekaton)
–
Changes can accumulate on the primary, waiting to be copied to the secondary
–
As changes accumulate, the risk of data loss increases if the primary fails
24
SQL Server 2012 & 2014 AlwaysOn AG Perf Bottlenecks
AlwaysOn Availability Groups (AGs) functionality is
based on Database Mirroring (DBM)
DBM legacy code had two built-in performance
bottlenecks inherited by AGs:
1. Log transport: This is a process that encrypts and
compresses log traffic to secondary replica(s)
2. Redo thread: Think about it as a continuous restore
thread, applying changes made on the primary replica to
secondary replica(s)
26
AlwaysOn Log Synchronization Perf Bottlenecks
Log transport: Process that
encrypts and compresses log
traffic to secondary replica(s)
Redo applies changes to
primary replica to secondary
replica(s)
27
AlwaysOn AGs: SQL 2016 vs. SQL 2014
~5x log transfer to
secondary
–
–
Enhanced RPO
Data on primary
hardened on secondary
immediately
Server, Application, and SQL Log
Performance
600
501
500
400
300
200
More work done
–
–
4x CPU %
6x trans/sec
498
100
146
91
21
104
103
22
0
CPU (%)
Transactions/sec Bytes Sent to
Log Bytes
(x1000)
Transport (MB/s) Received (MB/s)
SQL2014 SP1
SQL2016 RC1
28
Unleashing the Power of SQL Server 2016
Fusion ioMemory Performance
600
~3x IOPs
>4x throughput
<1ms latency
(~300-700μs)
553
500
400
288
300
200
100
19
35
0
0
Disk: IOPs (x1000)
Disk: Thruput (MB/s)
SQL2014 SP1
1
Disk: Latency (ms)
SQL2016 RC1
29
Key Customer Messages: SQL 2016 AlwaysOn AG
Eliminates risk of data loss
Changed data is received on secondaries without delay
– If the primary fails, the secondary has already received the log of the change
–
Improves data consistency, for better decisions and DR
Reports from secondaries work with up-to-date data
– Backups from secondaries capture up-to-date data
–
Only if your storage system can keep up
–
Use SanDisk flash products
30
#Data Driven AlwaysOn AGs
Always On AGs enhancements in SQL Server 2016
http://bit.ly/1UX3j94 (11:29)
31
AlwaysOn Availability Groups References
www.sqlha.com
–
Allan Hirt @sqlha & Max Myrick
–
Mission Critical SQL Server Book
• Pre-Order today: http://sqlha.contentshelf.com/shop
DPL posts on SanDisk IT Blog
itblog.sandisk.com
Books Online: AlwaysOn Availability Groups (SQL Server)
https://msdn.microsoft.com/en-us/library/hh510230.aspx
AlwaysOn Architecture Guide: Building a High Availability and Disaster Recovery Solution
by Using AlwaysOn Availability Groups
32
Flash: Don’t be this guy. Don’t be in this org.
Hat tip to Bob Pusateri (@SQLBob)
via Jason Horner (@jasonhorner) & Argenis Fernandez (@DBArgenis )
33
SanDisk Flash
SanDisk Fusion ioMemory SX350
SanDisk Optimus MAX
www.sandisk.com/business/datacenter/products/flashdevices/pcie-flash/sx350
www.sandisk.com/business/datacenter/products/flashdevices/ssds/sas-ssd/optimus
34
See for yourself…
35
Thank You!
36