Transcript Achieving High Availability and Disaster Recovery using

Narasimha Reddy Gopu
Jisha J
Agenda







Introduction to AlwaysOn
* AlwaysOn Availability Groups (AG) & Listener
* AlwaysOn Failover
* AlwaysOn Active Secondaries
Improved Failure Detection
Beyond AlwaysOn
Summary
*Demo
Mission Critical High Availability
Solution
Meets the Business SLAs(Service Level Agreements)
• RPO and RTO
• Planned and Un-planned Downtime
Flexible
• A unified, simplified solution
• Easy to deploy and Manage
Integrated
Efficient
• Improved Hardware Utilization
• Improved IT Efficiency
AlwaysOn
HADR(High Availability and Disaster Recovery) feature
To be released as part of SQL Server 2012(Denali)
AlwaysOn Availability Groups
for
Database Protection
AlwaysOn Failover Instances
for
Instance Protection
AlwaysOn Availability Groups
 Combines the best of Database Mirroring and Log
shipping
Windows Server Failover Cluster
Database
Active Log Synchronization
Database
Active Log Synchronization
A Flexible Solution
•
•
•
•
•
•
•
•
•
•
An Integrated Solution
•
•
•
•
•
•
An Efficient Solution
•
•
•
•
Demo
Application Failover
 Availability Groups Listener allow applications to
failover to any secondary node/s
 Application uses a virtual name for the failover
AlwaysOn Failover
Server A
Listener
Availability
Group
DB
Server C
Primary
DB
DB
Secondary
DB
Server B
DB
Secondary
AlwaysOn Cluster
DB
Demo
AlwaysOn – Active Secondary
 Improve IT efficiency by avoiding idle hardware
 Multiple Secondaries may be used for
 Offloading Backup operations
 Balancing Read-only workloads
Offloading Backup Operations
 Backups can be done on the primary as well as any of
the secondaries
 Log backups done on all replicas form a single log chain
Demo
Balancing Read-only Workloads
 Readable secondary feature enable offloading read
queries to multiple secondary nodes
 Latency of log synchronization can impact data
freshness on Asynchronous Secondary Databases.
Demo
Improved Failover Detection
 Monitoring Failure
 Dedicated connection on a new Thread for monitoring Health Status
 SP_Server_Diagnostics @Interval proc execution in Repeat mode to gather (
SQLDIAG logs)
For Failure Detection
a) System
b) Resource
c) Query Process
For Diagnostic Only
d) IO-Subsystem
e) Events
 Determining Failure: Basic resource health check interval(Looks Alive
Poll) and Thorough resource health check interval (Is Alive Poll)
 Action:- FailoverConditionLevel setting value (0-5, 3 default) takes required
action (Restart or Fail Over) based on the detected health status`.
Ex: 3 :- SQL Server Down + SQL Server Un responsive(Timeout for
SP_Server_Diagnostics) + ‘System Error ‘ for SP_Server_Diagnostics
Beyond AlwaysOn
 Integrating Contained Databases with AlwaysOn
 No more orphaned DB users , Logins
 DB users with passwords replace the SQL Logins
 Native Support for Multi-site Clustering
 ‘OR ‘ Logic in Dependency tab
 3rd party Replication between the Storage/servers
 Support TEMPDB on local drive
 Support for Windows Server Core
 Reduce OS patching by as much as 50-60%
Summary
 HA & DR Solution
 Flexible, Integrated, Efficient technology
 Offloading Read Only/Reporting operations
 Offloading Backup Operations
References
 Microsoft SQL Server site
http://www.microsoft.com/sqlserver/
 SQL Server 2012 Release Candidate 0
http://msdn.microsoft.com/en-us/library/bb418432(v=SQL.10).aspx
 SQL Server 2012 high availability site
http://www.microsoft.com/sqlserver/en/us/future-editions/missioncritical/SQL-Server-2012-high-availability.aspx
 Microsoft TechEd 2011 Events
http://channel9.msdn.com/Events/TechEd/NorthAmerica/2011/DBI404
Contact US
 Narasimha Reddy Gopu
[email protected]
 Jisha J
[email protected]