FUN WITH AVAILABILITY GROUPS Christopher Wolff SQL Server

FUN WITH AVAILABILITY GROUPS Christopher Wolff SQL Server Database Engineer, Xero

WHO AM I? ▪ Christopher Wolff ▪ Over 14 years using SQL Server and other Database technologies ▪ SQL Server Database Engineer at Xero ▪ We’re hiring! ▪ Twitter: @tickytong ▪ Email: tickytong@gmail. com ▪ Blog: http: //sqlred. blogspot. com/

OBJECTIVES ▪ Overview of Availability Groups (AG) ▪ A Little History… ▪ Info About Availability Groups ▪ Benefits ▪ Limitations ▪ Changes in the 2014 Version ▪ What do you need to create an Availability Group? ▪ Create an Availability Group ▪ Demo ▪ Geosharding ▪ DMV Queries ▪ Demo ▪ Questions?

NOTE ABOUT “FUN” ▪ Can be boring ▪ They are boring G N I R ▪ SELECT * FROM sys. database O B

A LITTLE HISTORY… ▪ Before Availability Groups ▪ ▪ Windows Failover Clustering Database Mirroring Log Shipping Replication

INFO ABOUT AVAILABILITY GROUPS ▪ First available in SQL Server 2012 ▪ In house SQL Server tool ▪ Uses Windows Clustering

BENEFITS ▪ Multiple Availability Modes ▪ Asynchronous-commit ▪ Synchronous-commit ▪ Multiple Failover Types ▪ Automatic Failover ▪ Planned Manual ▪ Forced Manual ▪ Read-Only Replicas

BENEFITS (CONT. ) ▪ Supports AG Listeners ▪ Integrated Tools ▪ ▪ ▪ DDL Statements SSMS Tools Always. On Dashboard Object Explorer Detail Power. Shell cmdlets

LIMITATIONS ▪ Licensing Costs ▪ Enterprise Edition Only ▪ Basic Availability Group available in 2016 in Standard Edition ▪ Limited Number of Replicas

CHANGES IN 2014 ▪ Azure Replica Wizard ▪ Replicas Increased from 4 to 8 ▪ Replicas still read-only when disconnected from the primary ▪ Can now use Cluster Shared Volumes ▪ New or enhanced system functions and DMV’s

CHANGES IN 2016 ▪ Optional setting to fail over based on DB failure ▪ Distributed Transaction Coordinator support ▪ Group Managed Service Accounts fully supported ▪ Load Balancing for Readable Secondaries ▪ Additional synchronous failover targets ▪ Improved log transport performance ▪ Basic Availability Group with Standard Edition

WHAT DO YOU NEED TO CREATE AN AVAILABILITY GROUP? • Domain Controller • 2 SQL Servers • Windows Server 2008 or later • SQL Servers must be in a Windows Server Failover Clustering cluster • Windows up to date • Full Backup

TECHNICAL TERMS • Replica • Each server that is in the AG is referred to as a replica • Other wise known as database mirroring endpoint • The server instance uses this endpoint to listen for Always. On Availability Groups messages from availability replicas hosted by other server instances • Endpoint • Listener • A virtual IP address and DNS name to which clients, such as 3 rd party tools, can connect to the AG

DEMO

GEOSHARDING

GEOSHARDING

DMV QUERIES • 23 Different DMV queries having to do with Availability Groups or Windows Clustering • 4 major categories • • Monitor Availability Groups Monitor Availability Replicas Monitor Availability Databases Monitor Availability Group Listeners

DMV QUERIES – AVAILABILITY GROUPS • sys. availability_groups • Basic info about the AG • sys. dm_hadr_availability_group_states • Gives info about the primary

DMV QUERIES – AVAILABILITY REPLICAS • sys. availability_replicas • Gives information about each replica • sys. dm_hadr_availability_replica_states • Gives information for each local and remote replicas in the same AG

DMV QUERIES – AVAILABILITY DATABASES • sys. availability_databases_cluster • One row for each database that is part of the cluster • sys. dm_hadr_database_replica_states • Extended information about the state of the databases on the primary and secondaries • sys. dm_hadr_database_replica_cluster_states • Information about the states each database is in currently relating to failover

DMV QUERIES – AVAILABILITY GROUP LISTENERS • sys. availability_group_listener_ip_addresses • Gives IP information about the listener

DMV QUERIES – DEMO

QUESTIONS? COMMENTS? Crude Remarks?

▪ High Availability Solutions (SQL Server) (2012 Version) ▪ http: //msdn. microsoft. com/en-us/library/ms 190202(v=sql. 110). aspx ▪ Always. On Availability Groups (SQL Server) (2012 Version) ▪ http: //msdn. microsoft. com/en-us/library/hh 510230(v=sql. 110). aspx#Benefits ▪ Always. On Availability Groups Dynamic Management Views and Functions (Transact-SQL) ▪ http: //msdn. microsoft. com/en-us/library/ff 877943(v=sql. 110). aspx ▪ Monitor Availability Groups (Transact-SQL) ▪ http: //msdn. microsoft. com/en-us/library/ff 878305(v=sql. 110). aspx ▪ Use the Always. On Dashboard (SQL Server Management Studio) (2012 Version) ▪ http: //msdn. microsoft. com/en-us/library/hh 213474(v=sql. 110). aspx ▪ Overview of Always. On Availability Groups (SQL Server) (2012 Version) ▪ http: //technet. microsoft. com/en-us/library/ff 877884(v=sql. 110). aspx ▪ What's New (Database Engine) (Always. On 2014 version) ▪ http: //msdn. microsoft. com/en-us/library/bb 510411. aspx#Always. On ▪ Prerequisites, Restrictions, and Recommendations for Always. On Availability Groups (SQL Server) (2012 Version) ▪ http: //msdn. microsoft. com/en-us/library/ff 878487(v=sql. 110). aspx ▪ SQL Server 2016: Availability Group Enhancements ▪ http: //sqlperformance. com/2015/05/sql-server-2016/availability-group-enhancements CREDITS

THANK YOU SPONSORS!!