Transcript Fun with Availability Groups

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: [email protected]
▪ 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
▪ SELECT * FROM sys.database
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
AlwaysOn Dashboard
Object Explorer Detail
PowerShell 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 AlwaysOn 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 3rd 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/ms190202(v=sql.110).aspx
▪ AlwaysOn Availability Groups (SQL Server) (2012 Version)
▪ http://msdn.microsoft.com/en-us/library/hh510230(v=sql.110).aspx#Benefits
▪ AlwaysOn Availability Groups Dynamic Management Views and Functions (Transact-SQL)
▪ http://msdn.microsoft.com/en-us/library/ff877943(v=sql.110).aspx
▪ Monitor Availability Groups (Transact-SQL)
▪ http://msdn.microsoft.com/en-us/library/ff878305(v=sql.110).aspx
▪ Use the AlwaysOn Dashboard (SQL Server Management Studio) (2012 Version)
▪ http://msdn.microsoft.com/en-us/library/hh213474(v=sql.110).aspx
▪ Overview of AlwaysOn Availability Groups (SQL Server) (2012 Version)
▪ http://technet.microsoft.com/en-us/library/ff877884(v=sql.110).aspx
▪ What's New (Database Engine) (AlwaysOn 2014 version)
▪ http://msdn.microsoft.com/en-us/library/bb510411.aspx#AlwaysOn
▪ Prerequisites, Restrictions, and Recommendations for AlwaysOn Availability Groups (SQL Server)
(2012 Version)
▪ http://msdn.microsoft.com/en-us/library/ff878487(v=sql.110).aspx
▪ SQL Server 2016: Availability Group Enhancements
▪ http://sqlperformance.com/2015/05/sql-server-2016/availability-group-enhancements
CREDITS