SQL Server 2012 High Availability and DR Joey

SQL Server 2012 High Availability and DR Joey D’Antoni SQL Saturday #118 Madison, WI 21 April 2011

About Me • • • @jdanton on Twitter Principal Architect SQL Server, Comcast Cable Joedantoni. wordpress. com Videos and Blogs at SSWUG. org Vice President of the Philadelphia SQL Server User Group – SQL Saturday #121 Philadelphia—June 9 th

Agenda • SQL Server 2008 to 2012—What’s Changed in HA and DR • Geo-Clustering • All about Availability Groups

Learning Objectives • SQL Server HA and DR • What’s involved in SQL Clustering • Understand the Difference Between Windows Cluster and SQL Failover Cluster Instances • How clustering and Availability Groups work • What’s new in 2012 HA/DR • Blog Post with Resources-- http: //bit. ly/I 11 d 2 j

Licensing (What’s New) • The Availability Group features will require the Enterprise Edition of SQL Server • The licensing model for SQL Enterprise Edition has changed. Consult your friendly Microsoft sales representative for more details • Always. On read-only replicas will need to be licensed

Windows Core Support • No GUI version of Windows • Allows for fewer patches • Uses Power. Shell and MMCs for support

Windows Core

High Availability (HA) and Disaster Recovery (DR) Options in SQL 2008 • • Backup and Recovery Failover Cluster Instances (FCI) Mirroring Log Shipping Replication SAN Replication* Virtualization*

High Availability (HA) and Disaster Recovery (DR) Options in SQL Server 2012 • Backup and Recovery • Failover Cluster Instances (FCI) • Mirroring • Availability Groups (2012) • • Log Shipping Replication SAN Replication* Virtualization*

What’s new in SQL Server 2012 HA/DR • • Always. On Availability Groups SMB Support for Failover Cluster Instances Multi-subnet clustering is supported Flexible Failover

SQL Server Failover Clustering Architecture

SQL Failover Clustering in 2008 • SQL Clustering required 1 subnet to be used across the whole cluster • Cluster failover is controlled by is. Alive/looks. Alive processes, which check the SQL service and run @@servername

SQL Failover Clustering in 2012 • • Full support for geo-distributed clusters SMB Storage (File Shares) Supported for FCI Flexible failover model based on sp_server_diagnostics Temp. DB on Non-shared Disk Resource – Makes PCI-based Solid State Drive an option

Quorum It’s not just bad cologne anymore

Quorum Are you there? Why Yes I am here

Understanding Quorum • There a several slides on this topic—it is critical! – In a nutshell, you cluster has to be able to talk to itself to keep the cluster service up in running – This applies to both SQL Server Failover Cluster Instances and Always. On Availability Groups

Quorum • Quorum is critical—contains master copy of the cluster’s configuration • Serves as a tiebreaker if network communications between cluster nodes fail • If Quorum fails—cluster is shut down until it’s restored

Quorum Models • • Node and Disk Majority (Default) Node Majority No Majority (Quorum Disk Only) Node and File Share Majority (Good for Geo Clusters)

Quorum Failure Tolerance Number of Nodes 2 3 4 5 6 7 Node Majority 0 1 1 2 2 3 Node and Disk/File Share Majority 1 2 2 3 3 4 • Assuming Disk is Up Calculation is: Cluster Up = Round. Up(Total # of Nodes/2) • Assuming Disk is Down Calculation is: Cluster. Up = Round. Up (Total # of Nodes/2)-1

Why Do Clusters Failover? • Initiated by failures in hardware or software • Checked by is. Alive/Looks. Alive processes (in 2008 R 2 and below)

Flexible Failover—New for 2012 • Replaces looks. Alive/is. Alive functionality in SQL Clusters (and is used for Availability Groups) • Now runs sp_server_diagnostics – Accepts two parameter • Health. Check. Timeout (Default 60 sec/Minimum 15 sec) • Failover Condition Level

Flexible Failover Policies for Clusters Level 0 1 2 3 (Default) 4 5 Condition Description No automatic • failover or restart Failover or restart • on server down Failover or restart • on server unresponsive Failover or restart • on critical server errors Failover or restart • on moderate server errors Failover or restart • on any qualified failure conditions Indicates that no failover or restart will be triggered automatically on any failure conditions. SQL Server service is down. SQL Server instance is not responsive (Resource DLL cannot receive data from sp_server_diagnostics within the Health. Check. Timeout settings). System stored procedure sp_server_diagnostics returns ‘system error’. (Critical errors > 20) System stored procedure sp_server_diagnostics returns ‘resource error’. (Moderate errors > 17) System stored procedure sp_server_diagnostics returns ‘query_processing error’. (Deadlock)

What is Stretch Clustering • Also known as Geo-Clustering

Geo Cluster

Geo-Distributed Clustering • • Requires SAN replication ($$$$) Two of everything Requires really fast network connection Requires some trickery at the network/DNS level for connectivity • Witness Disk (Quorum) – Can be physical (SAN) disk, or cluster file share

Geo-distributed Failover Clustering • Was available in SQL 2008, but easier to implement in 2012 • Won’t be used by most organizations due to cost and complexity

Review—DR Options in SQL 2008 • Mirroring – Allowed automatic failover, but only one target – Mirror target is unreadable • Log Shipping – Allowed multiple targets, but failover a manual process, requiring a connection string change • Replication

Always. On Availability Groups

Always. On Requirements • • • Windows Enterprise (Clustering is a requirement) SQL Server Enterprise Edition Windows Cluster No shared storage is required Quorum Disk (File Share if multi-site or local storage)

SQL Server Failover Clustering Architecture--Review

Always. On Architecture

Flexible AG Failover • Similar to how a failover clustered instance fails over • Connects to instance every 30 seconds to perform health check • Also, similar quorum model to Windows Failover Clustering

Always. On Architecture-Advanced

Allows for SAN-Less HA/DR • This is not a huge thing for SQL Server in larger organizations, but big win for medium sized businesses • Allows much easier native SQL DR in Virtual Environments

Considerations for Availability Groups • All SQL servers (including the secondary in the DR site) in the same Windows domain • All the databases must be in FULL recovery model • The unit of failover (for local HA, as well as DR) is at the AG level, i. e. , group of databases – not the instance

Failover Scenarios Asynchronouscommit mode Synchronouscommit mode with manual-failover mode Synchronouscommit mode with automatic-failover mode Automatic failover No No Yes Manual failover No Yes Forced failover Yes No

Read Only Replicas • Can have up to 4 (1 synch, 3 asynch) • SQL Client 2012 will allow for this routing specifically • Can take backups from read-only copies* – Copy Only Backups (only full copy, does not affect primary log) – Can backup primary log from replica • Indexing must be same on replicas • Bad queries can affect status of replica

Read-only vs Read Intent • Read only replica databases are open to any client that can connect to SQL Server • Read Intent routing is used for the Application Intent functionality in the SQL 2012 client • Read intent routing automatically directs connections to either the primary or listener to a secondary replica

Client Connections

Client Connections in This Model • Availability Group Listener – Works just like a failover clustering instance (single instance, single IP) – Creates a VCO (AD Virtual Computer Object)—similar to a cluster virtual object • Read-only Connections – Requires 2012 native ODBC client

Backups • You can determine whether the current replica is the preferred backup replica by calling the sys. fn_hadr_backup_is_preferred_replica function • This checks for replica status • Allows for post-failover backup jobs to run unchanged in the event of a failure • Logic is: If (top-priority replica is local) Run backup job Else Exit with success

Client Connections • Always specify Multi-Subnet Failover=True in listener connection • From Books Online “will significantly reduce failover time for single and multi-subnet Always. On topologies. ” • SQL Server Failover Cluster Instances as well

SQL Clusters and Always On • SQL Failover Clusters can be members of an Availability Group • FCI can only be configured for manual failover • Only one (the active) node can own the Always On Replica

Differences—SQL FCI and Availability Groups

Turning On Always. On

Availability Groups Demo

Summary • • Lots of Change in the HA/DR Space Licensing also changes—talk to your MS rep SQL Server Failover Clusters still a good HA option Always. On Availability Groups add a lot more flexibility to DR

Contact Info • Twitter: @jdanton • jdanton 1@yahoo. com • Blog: joedantoni. wordpress. com