SQL SERVER 2012 ALWAYSON DIVE INTO THE NEW

SQL SERVER 2012 ALWAYSON: DIVE INTO THE NEW HIGH AVAILABILITY FEATURES AND ENHANCEMENTS Andy Roberts Data Platform Specialist Microsoft Corporation andy. roberts@microsoft. com

A LOOK BACK… Customers are running mission critical applications on SQL Server and achieving SLA’s Log Shipping, Database Mirroring, Failover Clustering… Undertook a holistic view of the space • Compete pain points and feature gap analysis • Future trends and aligning technology with the future

MISSION CRITICAL HIGH AVAILABILITY SOLUTION Meets mission critical high availability SLA SQL Server and achieving SLA’s Integrated RPO and RTO requirements A unified, simplified solution Planned and unplanned downtime Easy to deploy and Manage Local HA and disaster recovery Flexible Reuse existing investment SAN vs. . DAS environments Efficient Cost Effective • HA hardware Utilization • No idle systems Improve IT efficiency

INTRODUCING SQL SERVER ALWAYSON Integrated, Flexible, Cost-Efficienthigh availability for mission critical business A high availability platform built for the future On-Premise, Private and Public Cloud Always. On provides database level and instance level protection Always. On Availability. Groups for database protection Always. On Failover Cluster Instances for instance level protection • Multi-Database Failover • Multisite Clustering • Multiple Secondaries • Flexible Failover Policy • Active Secondaries • Improved Diagnostics • Integrated HA Management • Built for consolidation scenarios

ALWAYSON – A FLEXIBLE SOLUTION Always. On provides the flexibility of different HA configurations A A A A Direct attached storage local, regional and geo target Synchronous Data Movement Asynchcronous Data Movement Shared Storage, regional and geo secondaries

ALWAYSON AVAILABILITY GROUPS PROVIDING DATABASE AVAILABILITY

ALWAYSON AVAILABILITY GROUPS Always. On Availability Groupsis a new feature that enhances and combines database mirroring and log shipping capabilities 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 Integrated • Application failover using virtual name • • Configuration Wizard • • File-stream replication Dashboard System Center Integration Rich diagnostic infrastructure Replication publisher failover Efficient • Active Secondary • Readable Secondary • Backup from Secondary • Automation using power-shell

Mission Critical Requirements Automatic Failover Database Mirroring Log Shipping Zero data loss is critical for the business Fast application recovery during planned and unplanned downtime Redundancy across 3 datacenters Application databases and dependencies failover together

DEMO DEPLOYING USING ALWAYSON AVAILABILITY GROUPS

APPLICATION FAILOVER Availability Groups Listener allow applications to failover seamlessly to any secondary Application reconnects using a virtual name after a failover to a secondary Server. A HR DB Server. C HR DB Secondary Primary Secondary AG_HR HR_VNN Primary Application retry during failover server HR_Listener; -catalog HRDB Connect to new primary once failover is complete and the listener is online

BACKWARD COMPATIBLE Database Mirroring connection string is supported for a single secondary configuration Datacenter A Server A AG Primary DB 1 DB 2 Connection String Server=Server. A; Failover. Partner=Server. B; Database=DB 1 Server B AG Primary Enables seamless migration from a database mirroring configuration to Availability Groups

WHAT ABOUT SERVER OBJECTS? Introducing Contained Databases or CDB’s Unit of application programmability in SQL Server 2012 A DB which establishes a boundary between application and server CDBs sever the user–login relationship Windows users no longer need matching logins Users with passwords replace SQL logins CDB can be part of an availability group and can failover across replicas Authentication information moves with the CDB Limitation Other objects, like jobs are not contained and has to be managed

AVAILABILITY GROUP ARCHITECTURE Windows Server Failover Cluster Database Active Log Synchronization Availability Group uses Windows Server Failover Cluster (WSFC) for WSFC Common Microsoft Availability Platform § Inter-node health detection, § SQL Server Always. On Failover cluster instances § Failover coordination, § SQL Server Always. On Availability Group § Primary health detection, § Microsoft Hyper-V § Distributed data store for settings and state, § Microsoft Exchange § Distributed change notifications § Built-in WSFC workloads (e. g. file share, NLB, etc) and third party workloads

ALWAYSON ACTIVE SECONDARY IT efficiency and cost-effectiveness is critical for businesses Idle hardware is not an option anymore Always. On 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

ACTIVE SECONDARY – MAKING SECONDARY READABLE SQLservr. exe Primary Secondary Failover Instance. B Instance. A DB 1 DB 2 Reports Readable secondary allow offloading read queries to secondary Close to real-time data, latency of log synchronization impact data freshness

ACTIVE SECONDARY: ENABLING BACKUP ON SECONDARY • Backups can be done on any replica of a database R/W workload Backups • Backups on primary replica still works • Log backups done on all replicas form a single log chain Secondary • Database Recovery Advisor makes restores simple Backups Primary Backups Secondary

READ-ONLY CLIENT CONNECTIVITY Read-Only client connection behavior determined by Availability Replica Option+ Application. Intent Property Application. Intent 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

DEMO ACTIVE SECONDARY DEMO

ALWAYSON FAILOVER CLUSTER INSTANCES PROVIDING INSTANCE AVAILABILITY

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

FLEXIBLE FAILOVER POLICY User sets new Cluster properties Health. Check. Timeout and Failure. Condition. Level (0 to 5) 5 – Failover or restart on any qualified failure 4 – Failover or restart on moderate SQL Server errors SQL Server Failover Cluster Instance 3 – Failover or restart on critical SQL Server errors Diagnostics generated for Health State Components • System • Resource • Query Processing • IO Subsystem • Events 2 – Failover or restart on SQL Server unresponsive 1 – Failover or restart on SQL Server down 0 – No Automatic Failover or restart execsp_server _diagnostics Diagnostics FCI Res DLL Is. Alive/ Looks. Alive result based on diagnostics and Failure. Condition. Level WSFC Service Is. Alive /Looks. Alive WSFC asks Res DLL if SQL FCI alive

REDUCING PLANNED DOWNTIME 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

CONCLUSION SQL Server Always. On is a comprehensive high availability solution • Integrated. Flexible. Cost-Efficient Always. On Availability Groups and Always. On Failover Cluster Instances provide flexibility in HA configuration Windows Server Core support significantly reduces downtime due to patching SQL Server Always. On Availability Groups • Multi-database failover • Multiple secondaries • Synchronous and asynchronous data movement • Built in compression and encryption • Automatic and manual Failover • Flexible failover policy • Automatic Page Repair • Active Secondary • • Readable secondary Secondary backup Automatic application redirection using virtual name Configuration Wizard Always. On Dashboard System Center Integration Automation using power-shell Rich diagnostic infrastructure SQL Server Always. On Failover Cluster Instances • Multi-site clustering across subnets • Flexible Failover Policy • Improved system diagnostics • Support for network attached storage (NAS) using SMB • Support for tempdb on local drive