1 / 26

Session Objectives and Takeaways

SQL Server 2012 ALWAYSON: S olution Overview _______________________________ N aveen Samba P remier ISV Consultant Microsoft Corporation. Session Objective(s): Introduce the new SQL Server High Availability and Disaster Recovery solution (AlwaysOn) in SQL Server 2012. Key Takeaway(s):

aleron
Download Presentation

Session Objectives and Takeaways

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. SQL Server 2012 ALWAYSON:Solution Overview_______________________________Naveen SambaPremier ISV ConsultantMicrosoft Corporation

  2. Session Objective(s): Introduce the new SQL Server High Availability and Disaster Recovery solution (AlwaysOn) in SQL Server 2012. Key Takeaway(s): Get the overview of AlwaysOn solution in SQL 2012, understand its benefits and advantages Session Objectives and Takeaways

  3. Customers are running mission critical applications on SQL Server and achieving SLA’s Bwin, ServiceU, Caregroup, Target and many more…… Undertook a holistic view of the space Compete pain points and feature gap analysis Future trends and aligning technology with the future A look back….

  4. Mission Critical High Availability Solution • Provide the fundamentals • RPO and RTO requirements • Planned and unplanned downtime • Local HA and disaster recovery Flexible Efficient Integrated Reuse existing investment SAN vs.. DAS environments • Cost Effective • HA hardware • Utilization • No idle systems • Improve IT efficiency A unified, simplified solution Easy to deploy and Manage

  5. Introducing SQL Server AlwaysOn Integrated, Flexible, Cost-Efficient high availability for mission critical business AlwaysOn provides database level and instance level protection AlwaysOn Availability Groups for database protection AlwaysOn Failover Cluster Instances for instance level protection • Multi-Database Failover • Multiple Secondaries • Active Secondaries • Integrated HA Management • Multisite Clustering • Flexible Failover Policy • Improved Diagnostics • Built for consolidation scenarios

  6. AlwaysOn Availability Groups Providing Database Availability

  7. AlwaysOn Availability Groups is a new feature that enhances and combines database mirroring and log shipping capabilities AlwaysOn Availability Groups Efficient Integrated Flexible • Multi-database failover • Multiple secondaries • Total of 4 secondaries • 2 synchronous secondaries • 1 automatic failover pair • Synchronous and asynchronous data movement • Built in compression and encryption • Automatic and manual failover • Flexible failover policy • Automatic Page Repair • Application failover using virtual name • Configuration Wizard • Dashboard • System Center Integration • Rich diagnostic infrastructure • File-stream replication • Replication publisher failover • Active Secondary • Readable Secondary • Backup from Secondary • Automation using power-shell

  8. AlwaysOn provides the flexibility of different HA configurations AlwaysOn – Availability Group A A A A Direct attached storage local, regional and geo target Shared Storage, regional and geo secondaries A A A Synchronous Data Movement Asynchcronous Data Movement

  9. Deploying AlwaysOn Availability Group

  10. Availability Groups Listener allow applications to failover seamlessly to any secondary; reconnecting through Virtual Network Name AlwaysOn Availability GroupApplication Failover with Availability Group Listener ServerA ServerC ServerB 2 DB 2 DB 2 DB TechAG1 TechListener1 Secondary Secondary Primary Primary Secondary Application retry during failover Connect to new primary once failover is complete and the listener is online Parameter Sample: -server TechListener1;-catalog HRDB

  11. AlwaysOn Availability Group Architecture Windows Server Failover Clustering (WSFC) Database Active Log Synchronization Database Active Log Synchronization WSFC is a Common Microsoft Availability Platform Availability Group uses WSFC for • SQL Server AlwaysOn Failover Cluster Instances • SQL Server AlwaysOn Availability Group • Microsoft Hyper-V • Microsoft Exchange • Built-in WSFC workloads (e.g. file share, NLB, etc.) and third party workloads • Inter-node health detection • Failover coordination • Primary health detection • Distributed data store for settings and state • Distributed change notifications

  12. Cluster and Database Interaction Client connections transparently redirected to primary via IP and network name resources User tells SQL to failover Availability Group 2 to Node1 Clients disconnected from AG2 SQL Server Instance SQL Server Instance SQL Server Instance Availability Group 1 Availability Group 2 Secondaries request primary connection WSFC Service WSFC Service WSFC Service WSFC tells AG Res DLL to bring AG2 online WSFC tells AG Res DLL to bring AG2 offline Notification of new primary SQL confirms and tells WSFC Notification of new primary AG Res DLL AG Res DLL AG Res DLL

  13. Data Movement Architecture Primary Secondary DB1 DB1 Log Capture Network LogRecieve • Log is first hardened and then applied • Redo thread is asynchronous and runs in the background • Latency (typically seconds) can be larger for log intensive operations like bulk import or index create/rebuild • Sync Replica minimizes latency due to network issues Log Pool RedoThread Commit Log Cache Log Cache Redo Pages Log Flush Log Harden Page Updated DB1 Log DB1 Data Acknowledge Commit DB1 Log DB1 Data

  14. AlwaysOn Availability Group Active Secondary • IT efficiency and cost-effectiveness is critical for businesses • Idle hardware is not an option anymore • AlwaysOn Active Secondary enables efficient utilization of high availability hardware resources thereby improving overall IT efficiency • Active Secondary can be utilized for • Balancing read-only workloads • Offloading Backup Operations

  15. Reports Reports AlwaysOn Availability GroupActive Secondary : Making secondary readable Failover SQLservr.exe SQLservr.exe Primary Secondary InstanceA InstanceB Primary Secondary • Readable secondary allow offloading read queries to secondary • Close to real-time data, latency of log synchronization impact data freshness DB1 DB2 DB1 DB2

  16. AlwaysOn Availability GroupActive Secondary : Backup on secondary • Backups can be done on any replica of a database • Backups on primary replica still works • Log backups done on all replicas form a single log chain • Database Recovery Advisor makes restores simple R/W workload Secondary Secondary Primary Backups Backups Backups

  17. Backups from any replica Synchronous or asynchronous secondaries Primary backups still work Log backups done on all replicas form a single log chain Recovery Advisor makes restores simple Restrictions, cautions Differential backups are not supported on secondary Only Copy-only full backups are supported on secondary The only distinction is the differential bitmap clearing Advisable for backups to be stored centrally Backup Capabilities

  18. AlwaysOn Availability GroupActive Secondary : ReadOnly client connectivity • Read-Only client connection behavior determined by Availability Replica Option + ApplicationIntent Property • ApplicationIntent is a connection property • Replica option determines whether a replica is enabled for read access when in a secondary role • Read-Only Routing enables redirection of client connection to new secondary on role change • Enable seamless redirection of application connection across replicas without manual intervention

  19. AlwaysOn Availability Group Active Secondary -Demo

  20. AlwaysOn Failover Cluster Instance Providing Instance Level Availability

  21. AlwaysOn Failover Cluster InstanceKey Enhancements • Fast instance failover through predictable database recovery time • Native support for multi-site clustering across subnets enable DR using failover cluster instances • Flexible Failover Policy • Eliminates false failover • Configurable failure condition levels • Better diagnostics • SMB support enables consolidation of more than 26 instances • Support TEMPDB on local drive

  22. AlwaysOn Failover Cluster InstanceIntroduce Failover Condition Policy Flexible Failover Policy provides administrators control over the conditions when an automatic failover should be initiated. SQL Server 2008 R2 SQL Server 2012 Resource DLL Resource DLL Select @@servername sp_server_diagnostics Diagnostics SQL Server SQL Server Configurable options eliminate false failover Improved logging for better diagnostics

  23. Support for Windows Server Core Reduce OS patching by as much as 50-60% Support for rolling upgrade and patching of SQL Server for both Availability Groups and Failover Cluster Instance Fast failover time for both Availability Groups and Failover Cluster Instances New online operations supported LOB Index Adding of column with default Other AlwaysOn Improvements in SQL Server 2012

  24. Better SLAs Multiple no data loss secondaries Better data loss protection for DR secondaries through continuous replication Faster failover to DR secondaries through virtual name failover Easier deployment/management Unified solution Simple deployment Unified dashboard Rich diagnostics Centralized management of client connection topology Multi-DB failover AlwaysOn Benefits

  25. SQL Server 2012 AlwaysOn Resource Center: http://msdn.microsoft.com/en-us/sqlserver/gg490638(en-us,MSDN.10) Documentation MSDN forums Microsoft Connect AlwaysOn Blog Resource Links

More Related