Transcript SQL Server Section change Segoe UI Light, 48pt.

Luis Vargas
Senior Program Manager Lead
Microsoft
Meet Luis Vargas
• Senior Program Manager Lead in SQL Server
– AlwaysOn (High Availability)
– SQL Server on Windows Azure VMs
– Hybrid scenarios (E.g. Backup/Replicas in Azure)
• Over 10 years of industry experience
– Program Manager at Microsoft
– Developer in Financial Services
• Detect failures reliably
• Able to stand multiple failures
• Unified solution
• Easy to configure, manage, and monitor
• Reuse existing investments
• SAN/DAS environments
• Allow using HA hardware resources
• Fast seamless failover
AlwaysOn
SQL Server HA/DR Technologies
Failover Cluster Instances
(for servers)
Availability Groups
(for groups of databases)
Pre-existent
New
Server failover
Multi-database Failover
Useful in consolidation scenarios
Shared storage (SAN / SMB)
Depends on storage redundancy
Failover takes minutes
Server restart
DBs that app depends on
Direct attached storage
Log synchronization
Failover takes seconds
Secondary replicas are online
Multi-node instance
Multiple Secondary Replicas
Passive secondary nodes
Active Secondary Replicas
Failover Cluster Instances
Enhancements in SQL Server 2012
•
Multi-Site Clusters
Clusters across subnets without stretch vLAN
•
TempDB on local disk
Improved performance, better SAN utilization
•
Indirect Checkpoints
More predictable DB recovery (failover) times
•
Flexible Failover Policy
Sensitivity to failures for automatic failover
Availability Groups
Introduced in SQL Server 2012
Integrated
Multi-database
Failover
Multiple
secondaries (4)
Seamless App
Connectivity
Configuration
Wizard
Efficient
Active Secondaries
Read workloads
Backups
Monitoring
Dashboard
PowerShell
Automation
Compression &
Encryption
Diagnostics
infrastructure
Fast Failover
Manual/Automatic
Failover
System Center
integration
Flexible Failover
Policy
Full cross-feature
support
Sync (max 2) / Async
Automatic Page
Repair
Contained Databases,
FileStream, FileTable,
Service Broker
Sync Log
Synchronization
Async Log
Synchronization
Box Strategy
Continue momentum in the Enterprise with deliberate box innovations and
new hybrid scenarios
Availability Scenario Mission
Provide the best in class and cost-effective HA/DR solutions for on-premise,
cloud, and hybrid
AlwaysOn
SQL Server HA/DR Technologies
Availability Groups
Failover Cluster Instances
Pre-existent
Increased Number of
Secondaries
Server
failover
New
Support for Windows
(for groups of databases)
Useful inAvailability
consolidationof
scenarios
Increased
Readable
Secondaries
Shared
storage
(SAN / SMB)
AddDepends
Azure Replica
Wizard
on storage
redundancy
Failover takes minutes
Server restart
Multi-node instance
Enhanced
Passive secondary nodes
(for servers)
Cluster SharedFailover
Volumes
Multi-database
DBs that app depends on
Direct attached storage
Log synchronization
Failover takes seconds
D i a g nSecondary
o s t i c sreplicas are online
Multiple Secondary Replicas
Active Secondary Replicas
Availability Groups
Increased Number of Secondaries
•
•
•
SQL Server 2012: Customers using (max 4) readable secondaries to
offload read workloads
•
Single technology to configure / manage
•
Higher throughput (~7x) than Replication
Customers asking for more replicas
•
Reduce query latency in geo-distributed environments (e.g. Bwin)
•
Scale-out read workloads (e.g. Baltika)
SQL Server 2014: Max 8 secondaries
•
Max 2 sync secondaries for high availability
•
Secondary delay depends on network latency and I/O
•
~1s within data center
~5s between data centers
Availability Groups
Increased Number of Secondaries
•
•
Minimal performance impact
•
Commits don’t wait for async secondaries
•
Log sender threads share log pool
•
Added transaction latency of 8 async secondaries: <1%
Scoped-out: Load balancing via connection string
•
Read_Only connections still routed to first available readable
secondary
•
Load balancing possible via DNS round-robin or specialized
load balancers (e.g. NLB)
Availability Groups
Increased Readable Secondaries Availability
•
•
SQL Server 2012: Read workloads killed during network failures
•
Geo-distributed environments (e.g. failure/upgrade of network
equipment, ISP failures)
•
Hybrid (on-premise to Azure) deployments
SQL Server 2014: read workloads not impacted during network
failures.. or primary down.. or cluster quorum loss..
•
Readable secondaries remain available during “Resolving” state
•
Requires direct connections to readable secondaries (Read-only
routing not supported yet)
•
Replica state and last commit time available in DMV/Dashboard
Availability Groups
Increased Readable Secondaries Availability
Sync Log
Synchronization
Async Log
Synchronization
Availability Groups
Increased Readable Secondaries Availability
“The increased readable secondaries availability means our users can still find
answers online and the world keeps spinning - StackOverflow
http://nickcraver.com/blog/2013/11/18/running-stack-overflow-sql-2014-ctp-2/
Availability Groups
Increased Readable Secondaries Availability
StackOverflow can now:
•
Offload more critical read workloads to readable secondary in main data center
•
•
Network glitches even within the same DC can happen
Use readable secondary in DR site while data center is down (70% reads)
•
Simpler to change DNS than force failover and failback
•
Doesn’t result in data loss
Availability Groups
Add Azure Replica Wizard
•
Many customers can’t 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
Case studies
•
•
Lufthansa, Thomson Reuters, Buffalo Hospital Supply
SQL Server 2014: “Add Replica Wizard” supports Windows
Azure
E2E: From provisioning VM to starting log synchronization
•
•
Validates environment
•
Handles failures
•
Does cleanup
Availability Groups & Failover Cluster Instances
Enhanced Diagnostics
•
24 Enhancements on Dashboard, Error Messages, DMVs,
XEvents
•
Simplify troubleshooting & prevent issues
•
Based on feedback from customers & CSS
Availability Groups & Failover Cluster Instances
Enhanced Diagnostics
Title
Component
Show
in XEL
output
in UTC
(not (not
adjusted
to client
SSMS SSMS
computer)
Showtimestamps
timestamps
in XEL
output
in UTC
adjusted
to client
computer)
XEvents Viewer
Warning
about
loglog
synchronization
behavior
whenwhen
primary
replicareplica
is async
Warning
about
synchronization
behavior
primary
is async
Dashboard
System
function
IsPrimaryReplica(database_name)
System
function
IsPrimaryReplica(database_name)
System function
Add AG name (and replica name and DB name if relevant) to many more XEvents to
allow better data correlation between the logs
Report major HADRON Manager transitions to AlwaysOn XEvent session
XEvents
Add Replica name context to connection established error log entry
Error Log
XEvents
Dump
output
from
sys.dm_hadr_database_replica_states
to SQLtoerror
when
Dumprelevant
relevant
output
from
sys.dm_hadr_database_replica_states
SQL log
error
log XEvents
replicas
change to
resolving
state
when replicas
change
to resolving
state
Add new error message to detect AG startup failure when quorum is forced
Error Log
Separate
error
msg
41142
(replica
can'tcan't
become
primary)
- raised
for twofor
importantly
Separate
error
msg
41142
(replica
become
primary)
- raised
two
different
reasons
importantly
different reasons
AlwaysOn Functions/DMVs should also support FCIs where applicable
Improve the CREATE AG error message “AG already exists”, to say “It’s possible that a
previous DROP AG operation, executed during cluster quorum loss, didn’t delete the AG
from the cluster. If so, please retry the DROP operation”
Remove FCI setup dependency on cluster.exe (deprecated) – Use Powershell
Error Log
DMVs
Error Message
Error Log
Failover Cluster Instances
Support for Windows Cluster Shared Volumes (Windows Server 2012 & 2012 R2)
•
•
Cluster Shared Volume (CSV)
•
Shared disk accessible to all nodes (over SMB)
•
One or more per physical drive
Failover Cluster Instances on CSV
•
Improves SAN utilization
Removes limitation of 24 drives
•
Increases I/O resiliency
Retry read/write via other nodes
•
Increases failover resiliency
Disks don’t need to be unmounted/mounted
Failover Cluster Instances
Support for Windows Cluster Shared Volumes
AlwaysOn and Windows Server
Windows Cluster Enhancements
Version 2012
•
Dynamic Quorum
Removes votes from unavailable nodes
Enables “last man standing”
•
Increased network resiliency
Handle more exceptions
Avoid node evictions
Version 2012 R2
•
Network names without Active Directory
Avoid Listeners issues: permissions, collisions
JOIN US in San Jose, May 7-9 for our second annual event to get the best learning for analyzing, managing,
and sharing business information and insights through the Microsoft Data Platform of technologies.
$150 off with discount Code: XXXXXX