alwaysondemo - lisa gardner (1)
TRANSCRIPT
![Page 1: AlwaysONDemo - Lisa Gardner (1)](https://reader035.vdocuments.us/reader035/viewer/2022062309/55cf947c550346f57ba25f7d/html5/thumbnails/1.jpg)
SQL Server 2012 Always On
Premier Field Engineer
Microsoft Corporation
Lisa Gardner
http://blogs.msdn.com/sqlgardner
![Page 2: AlwaysONDemo - Lisa Gardner (1)](https://reader035.vdocuments.us/reader035/viewer/2022062309/55cf947c550346f57ba25f7d/html5/thumbnails/2.jpg)
High Availability OutcomeOptions
Understanding of High Availability Options in SQL Server 2012Benefits of AlwaysOn HA design patterns utilizing AlwaysOn
![Page 3: AlwaysONDemo - Lisa Gardner (1)](https://reader035.vdocuments.us/reader035/viewer/2022062309/55cf947c550346f57ba25f7d/html5/thumbnails/3.jpg)
Agenda
High Availability OptionsAlwaysOn DemoReadable SecondariesReadable Secondaries DemoAlwaysOn Design Patterns
![Page 4: AlwaysONDemo - Lisa Gardner (1)](https://reader035.vdocuments.us/reader035/viewer/2022062309/55cf947c550346f57ba25f7d/html5/thumbnails/4.jpg)
High Availability Options
Pre-SQL Server 2012What's New in SQL Server Failover Clustering?SQL Server 2012 AlwaysOn
![Page 5: AlwaysONDemo - Lisa Gardner (1)](https://reader035.vdocuments.us/reader035/viewer/2022062309/55cf947c550346f57ba25f7d/html5/thumbnails/5.jpg)
Pre-SQL Server 2012 High Availability Options
Backup/RestoreWindows/SQL Server Failover ClusteringLog ShippingDatabase MirroringThird Party
SAN Replication
![Page 6: AlwaysONDemo - Lisa Gardner (1)](https://reader035.vdocuments.us/reader035/viewer/2022062309/55cf947c550346f57ba25f7d/html5/thumbnails/6.jpg)
AlwaysOn Technologies – Managed by WSFC
WSFC
AGFCI
![Page 7: AlwaysONDemo - Lisa Gardner (1)](https://reader035.vdocuments.us/reader035/viewer/2022062309/55cf947c550346f57ba25f7d/html5/thumbnails/7.jpg)
What’s New in SQL Server Failover Clustering?
AlwaysOn Failover Cluster Instance provides instance level failoverKey SQL Server 2012 Clustering Enhancements
Multi-site geo-clustering across subnetsFlexible Failover PolicyImproved system diagnosticsSupport for network attached storage(NAS) user SMBSupport for TempDB on local drive (SSD)
![Page 8: AlwaysONDemo - Lisa Gardner (1)](https://reader035.vdocuments.us/reader035/viewer/2022062309/55cf947c550346f57ba25f7d/html5/thumbnails/8.jpg)
Flexible Failover Policy (SQL Server 2012)
Control over when automatic failover should be initiatedConfigurable options eliminate false failoverImproved logging for better diagnosticsNew Cluster Properties
HealthCheckTimeoutFailureConditionLevel
![Page 9: AlwaysONDemo - Lisa Gardner (1)](https://reader035.vdocuments.us/reader035/viewer/2022062309/55cf947c550346f57ba25f7d/html5/thumbnails/9.jpg)
Failure Condition Levels
0 – No Automatic Failover or restart
Service is down
1 – Failover or restart on server down
No response from sp_server_diagnostics
2 – Failover or restart on server unresponsive
System errors3 – Failover or restart on critical SQL Server errors
Resource errors4 – Failover or restart on moderate SQL Server errors
Query Processing errors5 – Failover or restart on any qualified failure conditions
![Page 10: AlwaysONDemo - Lisa Gardner (1)](https://reader035.vdocuments.us/reader035/viewer/2022062309/55cf947c550346f57ba25f7d/html5/thumbnails/10.jpg)
Always On Availability Groups
Allows a group of databases to failover as a logical unitUtilizes Windows Failover Cluster to report healthDefines a primary instance and up to four secondary instancesProvides automatic client redirection
![Page 11: AlwaysONDemo - Lisa Gardner (1)](https://reader035.vdocuments.us/reader035/viewer/2022062309/55cf947c550346f57ba25f7d/html5/thumbnails/11.jpg)
AlwaysOn Concepts
Availability GroupAvailability ReplicaAvailability DatabaseAvailability Group Listener
![Page 12: AlwaysONDemo - Lisa Gardner (1)](https://reader035.vdocuments.us/reader035/viewer/2022062309/55cf947c550346f57ba25f7d/html5/thumbnails/12.jpg)
SQL Server High Availability Options RecapHigh Availability and Disaster Recovery SQL Server Solution
Potential Data Loss
(RPO)
Potential Recovery
Time (RTO)
Automatic Failover
Readable Secondaries (1)
AlwaysOn Availability Group – synchronous-commit
Zero Seconds Yes(4) 0-2
AlwaysOn Availability Group – asyncronous-commit
Seconds Minutes No 0-4
AlwaysOn Failover Cluster Instance
NA(5) Seconds-to
minutes
Yes NA
Database Mirroring(2) – High-safety(sync + witness)
Zero Seconds Yes NA
Database Mirroring(2) – High Performance (async)
Seconds(6) Minutes(6) No NA
Logshipping Minutes(6) Minutes-to-
hours(6)
No Not during a restore
Backup Copy Restore(3) Hours(6) Hours-to-days(6)
No Not During a restore
![Page 13: AlwaysONDemo - Lisa Gardner (1)](https://reader035.vdocuments.us/reader035/viewer/2022062309/55cf947c550346f57ba25f7d/html5/thumbnails/13.jpg)
DemonstrationCreating an Availability Group
![Page 14: AlwaysONDemo - Lisa Gardner (1)](https://reader035.vdocuments.us/reader035/viewer/2022062309/55cf947c550346f57ba25f7d/html5/thumbnails/14.jpg)
Readable Secondaries
Mirrored copy of data on secondary serverActive Secondary servers provide off-loading functionality
Reading of data for reportingBackupsDBCC
Connect via Instance name
![Page 15: AlwaysONDemo - Lisa Gardner (1)](https://reader035.vdocuments.us/reader035/viewer/2022062309/55cf947c550346f57ba25f7d/html5/thumbnails/15.jpg)
Active Secondary – Readable Routing
Allows for application to specify read Intent on ConnectionApplicationIntent – A New Connection PropertyConnect via listener
Read-Only RoutingOptimized for automatic routing of read only applicationsRoutes must be create created manually
![Page 16: AlwaysONDemo - Lisa Gardner (1)](https://reader035.vdocuments.us/reader035/viewer/2022062309/55cf947c550346f57ba25f7d/html5/thumbnails/16.jpg)
Readable Secondary – Data Latency
Secondary reads are behind primaryLog is first hardened and then applied
Redo thread is asynchronous and runs in the backgroundLatency (typically seconds) can be larger for log intensive operations like bulk import or index create/rebuildSync Replica minimizes latency due to network issues
![Page 17: AlwaysONDemo - Lisa Gardner (1)](https://reader035.vdocuments.us/reader035/viewer/2022062309/55cf947c550346f57ba25f7d/html5/thumbnails/17.jpg)
DemonstrationLeveraging Active Secondaries
![Page 18: AlwaysONDemo - Lisa Gardner (1)](https://reader035.vdocuments.us/reader035/viewer/2022062309/55cf947c550346f57ba25f7d/html5/thumbnails/18.jpg)
Query Performance on Secondary
SQL Server Uses Cost based optimizerRelies on object Statistics
If statistics are missing SQL Server creates and persistsAuto-stat on readable secondary will require updates?
![Page 19: AlwaysONDemo - Lisa Gardner (1)](https://reader035.vdocuments.us/reader035/viewer/2022062309/55cf947c550346f57ba25f7d/html5/thumbnails/19.jpg)
Active Secondary : Enabling Backup on Secondary
Backups can be done on any replicaMust be able to communicate with primaryLog backups done on all replicas form a single log chainSend all backups to a single UNC pathDatabase Recovery advisor makes restores simple
Must include backups from other instances manually
Differential Backups are not supportedCopy-Only backups are the only type
I have a 4 part blog series on this topic for more details
![Page 20: AlwaysONDemo - Lisa Gardner (1)](https://reader035.vdocuments.us/reader035/viewer/2022062309/55cf947c550346f57ba25f7d/html5/thumbnails/20.jpg)
AlwaysOn Troubleshooting
AlwaysOn DashboardSp_server_diagnosticsCatalog Views Examples
Sys.availability_groupsSys.availability_replicas
DMV are named sys.dm_hadr*New Performance Monitor Counter Objects
SQLServer:Database ReplicaSQLServer:Availability Replica
New Information Logged to the System Event Logs
![Page 21: AlwaysONDemo - Lisa Gardner (1)](https://reader035.vdocuments.us/reader035/viewer/2022062309/55cf947c550346f57ba25f7d/html5/thumbnails/21.jpg)
AlwaysOn Design Patterns
White Board/Flip Discussion
![Page 22: AlwaysONDemo - Lisa Gardner (1)](https://reader035.vdocuments.us/reader035/viewer/2022062309/55cf947c550346f57ba25f7d/html5/thumbnails/22.jpg)
Questions?
![Page 23: AlwaysONDemo - Lisa Gardner (1)](https://reader035.vdocuments.us/reader035/viewer/2022062309/55cf947c550346f57ba25f7d/html5/thumbnails/23.jpg)
Conclusion
AlwaysOn provides many High Availability OptionsEnables Multi Site Failover with minimal effortAllows maintenance and read activity to be distributed