SQL-Server 2012 Always On

1. SQL-Server 2012 Always On

2. SQL Server “SQL-Server 2012” Highlights High Availability Scalability & Performance Business Intelligence Web & Breadth Security & Manageability EIM Beyond Relational SQL Server AlwaysOn Up to 15K Partitions/Table User-Defined Server Roles Microsoft Project “Juneau” PHP Driver Unified Semantic Model SSIS Server Reliable, IntegratedFailoverDetection Contained Database Authentication Win32 Access to Database files Local DB Runtime Crescent Data Quality Column Store Index, “Apollo” In-memory BI for corporate Application Centric Failover Distributed Replay FileTable UTF-16 Enhanced MDS Fast FILESTEAM Fast Full-Text Search Multiple Secondaries Audit Enhancements Semantic Search Platform Paging for result sets Alerting MDS add-in for Excel Fast Spatial Performance Readable Secondaries Management Pack for High Availability Full Globe Spatial Support JDBC 4.0 driver Sysprep for AS DBC & OLTP Appliances/RAs Online Operations Backup Secondaries DAC Enhancements Support for ARM processors PowerPivot Enhancements Default Schema for Windows Group HA for StreamInsight ODBC for Linux Reporting as SharePoint Shared Service Windows Server Core Support Hybrid Applications with SQL Azure Active Directory w/ SharePoint for SSRS

3. What is Always On Availability Group • Recommended HA for database availability • Best features of Mirroring • Non Shared Storage • Fast Failover • Automatic Page Repair • Support for High Latency Networks • Plus new features • Failover Group of databases • Multiple secondary replicas • Readable secondary replicas • Virtual names for fast failover • More flexible failover policy • Integration with traditional Failover Clustering

4. AlwaysOn Availability Groups AlwaysOn Availability Groups is a new feature that provides availability for application database(s). • Provides protection from planned and unplanned downtime 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

5. Case Study – Dell DVD Store Enterprises • Need to accept 24 x 7 transactions • For High Availability • Zero down time and data loss (automatic) • SLA to cover storage failure • “I see web site unavailable and you are fired” • “Reporting” • Near real time. No more than 1 minute behind • With minimal impact on live transactions • Disaster Recovery • Have second data centre. No more than 30 mins down time and 5 minutes data loss • Upgrade • Any upgrade needs to be with no down time or data loss

6. Typical HA/DR/Reporting Architecture DR Site

7. SQL-Server 2012 Always On Reports Réplica Réplica Backup SAN éplica Reports Réplica Backup

8. Installation • Always On relies on WSFC • Always On is NOT a clustered install of SQL • Does not need Shared Disk • Windows 2008 Server+ EE needed • Need to Enable HadrService • Via configuration manager • Or via PowerShell • Some Patches and QFE Needed • KB 976097KB 2494036  

9. Migrating to Always On / SQL-Server 2012 • Any normal migration is possible: • Backup / Restore • Attach / Detach • Mirroring • Log Shipping • Replication • Might need some post migration tasks • Compatibility • Use of new features such as Containment

10. Quorum Changes • Required for automatic failover • Built in feature of WSFC • Replaces the concept of “witness” to avoid split brain • Two recommended Steps • 1. Select nodes to vote • 2. Select the Quorum Type • If Odd number use “Node Majority” • If even number use “Node Majority plus File Share Witness” • Requires QFE KB KB 2494036  to enable Voting • May need to adjust quorum after failover outsideautomatic target

11. SecondaryReplica Features • Failover • Backup \ DBCC • Reporting Queries • AlwaysOn AutoStat • Automatic Page Repair

12. SecondaryReplica – Active Log Sync • Secondary read is behind primary • 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 DB1 DB1 Network Log Capture Log Recieve Log Pool Redo Thread Commit Log Cache Log Cache Redo Pages Log Flush Log Harden Page Updated DB1 Log DB1 Data Acknowledge Commit DB1 Log DB1 Data

13. Availability Groups and Failover Cluster Instance Availability Groups integrates seamlessly with AlwaysOn Failover Cluster Instance Failover Cluster Instance used for local high availability • Availability Groups provide ability to failover to a disaster recovery site • Automatic failover of the availability group is disallowed when one of the replicas is an FCI

14. AlwaysOn Resources • “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