DBIB330 Luis Vargas Senior Program Manager Lead Looking back to SQL Server 2012 MissionCritical Availability Unified solution Easy to configure manage and monitor Integrated Flexible ID: 652944
Download Presentation The PPT/PDF document "SQL Server 2014 AlwaysOn" is the property of its rightful owner. Permission is granted to download and print the materials on this web site for personal, non-commercial use only, and to display it on your personal computer provided you do not modify the materials and that you retain all copyright notices contained in the materials. By downloading content from our website, you accept the terms of this agreement.
Slide1Slide2
SQL Server 2014AlwaysOn
DBI-B330Luis VargasSenior Program Manager LeadSlide3
Looking back toSQL Server 2012...Slide4
Mission-Critical Availability
Unified solution
Easy to configure, manage, and monitor
Integrated
Flexible
Efficient
Reuse existing investments
SAN/DAS environments
Allow using HA hardware resources
Fast seamless failover
Reliable
Detect failures reliably
Able to stand multiple failures Slide5
AlwaysOn
SQL Server HA/DR Technologies
Pre-existent
Server failover
Useful in consolidation scenarios
Shared storage (SAN / SMB)
Depends on storage redundancy
Failover takes
30s to couple of minutes
Server restartSQL instance is replica for one FCI
Passive secondary nodes
Failover Cluster Instances(for servers)
New
Multi-database Failover
DBs that app depends on
Direct attached storage
Log synchronizationFailover takes less than 30 secondsSecondary replicas are onlineSQL instance hosts replicas for one or more AG replicasActive Secondary Replicas
Availability Groups
(for groups of databases)Slide6
Failover Cluster Instances
Enhancements in SQL Server 2012Multi-Site ClustersClusters across subnets without stretch
vLAN
TempDB
on local disk
Improved performance & Better SAN utilizationIndirect CheckpointsMore predictable DB recovery (failover) timesFlexible Failover PolicySensitivity to failures for automatic failoverSlide7
Availability Groups
Introduced in SQL Server 2012
Multi-database Failover
Multiple secondaries (4)
Sync (max 2) /
Async
Compression & Encryption
Manual/Automatic Failover
Flexible Failover Policy
Automatic Page Repair
Seamless App Connectivity
Configuration Wizard
Monitoring Dashboard
Diagnostics infrastructure
System Center integration
Full cross-feature support
Contained Databases, FileStream, FileTable, Service Broker
Active Secondaries
Read workloads
Backups
PowerShell Automation
Fast Failover
Flexible
Integrated
EfficientSlide8
An Availability Group Deployment
Sync Log
Synchronization
Async
Log SynchronizationSlide9
SQL Server 2014Slide10
Pre-existent
Server failover
Useful in consolidation scenarios
Shared storage (SAN / SMB)
Depends on storage redundancy
Failover takes minutes
Server restart
Multi-node instance
Passive secondary nodes
Availability Groups
(for groups of databases)
New
Multi-database Failover
DBs that app depends on
Direct attached storage
Log synchronization
Failover takes secondsSecondary replicas are onlineMultiple Secondary ReplicasActive Secondary ReplicasFailover Cluster Instances(for servers)
Increased Number of Secondaries
Increased
Availability of Readable Secondaries
Add Azure Replica Wizard
Support for Windows Cluster Shared Volumes
E n h a n c e d
D
i
a g n o s t
i
c s
AlwaysOn
SQL Server HA/DR TechnologiesSlide11
Availability GroupsIncreased Number of SecondariesSlide12
Availability Groups
Increased Number of SecondariesSQL Server 2012: Customers using readable
secondaries to offload read
workloads
Single technology to configure / manage
Higher throughput (~7x) than ReplicationNeed for additional replicas
Reduce query latency in geo-distributed environmentsScale-out read workloadsSQL
Server 2014: Max 8 secondariesMax 2 sync secondaries for high availability
Secondary delay depends on network latency and I/O: ~1s within data center, ~5s between data centersSlide13
Availability Groups
Increased Number of Secondaries
Minimal performance impact
Commits don’t wait for
async
secondariesLog sender threads share log poolAdded transaction latency of 8 async secondaries: <1%
Scoped-out: Load balancing via connection stringRead_Only connections still routed to first available readable secondary
Load balancing possible via DNS round-robin or specialized load balancers (e.g. NLB)Slide14Slide15Slide16Slide17Slide18Slide19Slide20Slide21
Availability GroupsIncreased Readable Secondaries AvailabilitySlide22
Availability GroupsIncreased Readable Secondaries Availability
SQL Server 2012: Read workloads
can be 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” stateRequires direct connections to readable secondaries (Read-only routing not supported yet)Replica state and last commit time available in DMV/DashboardSlide23
Availability Groups
Increased Readable Secondaries Availability
Sync Log
Synchronization
Async
Log SynchronizationSlide24
Availability GroupsIncreased Readable Secondaries AvailabilitySlide25
Availability GroupsIncreased Readable Secondaries Availability
“The increased readable secondaries availability means our users can still find answers online and the world keeps spinning”
-
StackOverflow
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 lossSlide26
DemoSlide27
Availability GroupsAdd Azure Replica WizardSlide28
Availability GroupsAdd 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
Offload read workloads Offload backups (policy compliance)Disaster recovery
At best regionWest US, East US, East Asia, Southeast Asia, North Europe, West Europe
Latency / political considerationsSlide29
Availability Groups
Add Azure Replica Wizard
Sync Log
Synchronization
Async
Log SynchronizationSlide30
Availability GroupsAdd Azure Replica Wizard
Low TCO
VM and storage
Free ingress traffic
Case studies
Lufthansa
, Thomson Reuters, GameStop, Buffalo Hospital Supply
SQL Server 2014: “Add Replica Wizard” supports Windows Azure VMsE2E: From provisioning VM to starting log
synchronizationValidates environment, handles failures, does cleanupSlide31
DemoSlide32
Availability Groups & Failover Cluster Instances
Enhanced DiagnosticsSlide33
Availability Groups & Failover Cluster Instances
Enhanced Diagnostics
24 Enhancements on Dashboard, Error Messages, DMVs,
XEvents
Simplify troubleshooting & prevent issues
Based on feedback from customers & CSSSlide34
Availability GroupsEnhanced Diagnostics
Title
Component
Show timestamps in XEL output in UTC (not adjusted to client SSMS computer
)
XEvents Viewer
Warning about log synchronization behavior when primary replica is asyncDashboard
System function IsPrimaryReplica(database_name)
System functionAdd AG name (and replica name and DB name if relevant) to many more XEvents to allow better data correlation between the logs
XEventsReport major HADRON Manager transitions to AlwaysOn XEvent session
XEventsAdd Replica name context to connection established error log entry
Error LogDump relevant output from sys.dm_hadr_database_replica_states to SQL error log
when
replicas change to resolving state
XEvents
Add
new error message to detect AG startup failure when quorum is forcedError LogSeparate error msg 41142 (replica can't become primary) - raised for two importantly different reasonsError LogAlwaysOn Functions/DMVs should also support FCIs where applicableDMVsImprove 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”Error MessageRemove FCI setup dependency on cluster.exe (deprecated) – Use PowershellError LogShow timestamps in XEL output in UTC (not adjusted to client SSMS computer)
System function
IsPrimaryReplica
(
database_name
)
Separate error
msg
41142 (replica can't become primary) - raised for two importantly different reasons
Dump relevant output from sys.dm_hadr_database_replica_states to SQL error log when replicas change to resolving state
and 13 more..Slide35
DemoSlide36
Failover Cluster Instances
Support for Windows Cluster Shared VolumesSlide37
Failover Cluster InstancesSupport for Windows Cluster Shared Volumes (Windows Server 2012+)
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 nodesIncreases failover resiliency Disks don’t need to be
unmounted/mountedSlide38
Failover Cluster InstancesSupport for Windows Cluster Shared Volumes (Windows Server 2012+)Slide39
DemoSlide40
SQL Server and Windows Server
Windows Cluster EnhancementsSlide41
SQL Server and Windows ServerWindows Cluster Enhancements
Increased Network Resiliency
Reduces node evictions
Dynamic Quorum
Removes votes from unavailable nodes
Enables “last man standing”
Network Names without Active DirectoryNames (e.g. Listeners) are registered directly to DNS
Avoid permission/collision issuesSlide42
Thanks for your time!
Questions?Slide43
27 Hands on Labs + 8 Instructor Led Labs in Hall 7
DBI Track resources
Free SQL Server 2014 Technical Overview e-book
microsoft.com/sqlserver
and
Amazon Kindle Store
Free online training at Microsoft Virtual Academy
microsoftvirtualacademy.com
Try new Azure data services previews!
Azure Machine Learning
,
DocumentDB
, and
Stream Analytics Slide44
Resources
Learning
Microsoft Certification & Training Resources
www.microsoft.com/learning
Developer Network
http
://developer.microsoft.com
TechNet
Resources for IT Professionals
http://microsoft.com/technet
Sessions on Demand
http://channel9.msdn.com/Events/TechEdSlide45
Please Complete An Evaluation FormYour input is important!
TechEd Schedule Builder
CommNet
station
or PC
TechEd Mobile
app
Phone or Tablet
QR codeSlide46
Evaluate this sessionSlide47
© 2014 Microsoft Corporation. All rights reserved. Microsoft, Windows, and other product names are or may be registered trademarks and/or trademarks in the U.S. and/or other countries.
The information herein is for informational purposes only and represents the current view of Microsoft Corporation as of the date of this presentation. Because Microsoft must respond to changing market conditions, it should not be interpreted to be a commitment on the part of Microsoft, and Microsoft cannot guarantee the accuracy of any information provided after the date of this presentation. MICROSOFT MAKES NO WARRANTIES, EXPRESS, IMPLIED OR STATUTORY, AS TO THE INFORMATION IN THIS PRESENTATION.