An analysis of Microsoft SQL Server AlwaysOn Availability Group architectures 1 Derik Hammer sqlhammer deriksqlhammercom wwwsqlhammercom Database Administrator TraditionalOperationalProduction ID: 752264
Download Presentation The PPT/PDF document "Architecting Availability Groups" is the property of its rightful owner. Permission is granted to download and print the materials on this web site for personal, non-commercial use only, and to display it on your personal computer provided you do not modify the materials and that you retain all copyright notices contained in the materials. By downloading content from our website, you accept the terms of this agreement.
Slide1
Architecting Availability Groups
An analysis of Microsoft SQL Server Always-On Availability Group architectures
1Slide2
Derik Hammer
@
sqlhammer
derik@sqlhammer.com
www.sqlhammer.com
Database Administrator (Traditional/Operational/Production)Spent a year pretending to be a .NET developer then back to being a DBASpecialize in High-Availability, Disaster Recovery, and Maintenance AutomationRebuilt the Hampton Roads SQL Server User Group in Virginia Beach, VA before moving.Chapter leader of FairfieldPASS in Stamford, CT.BS in Computer Information Systems with a focus in Database ManagementQuerying Microsoft SQL Server 2012 Databases (70-461)Administering Microsoft SQL Server 2012 Databases (70-462)
2Slide3
Goals
Skill level: 200-300, assuming some familiarityNot a “how to”, but there are demosArchitecture: Stand-alone instances
Architecture:
Stand-alones with multiple subnets
Architecture: AG with Failover Cluster Instances
Architecture: Hybrid approach (DR on the cheap)Back-up off-loadingRead-only routing3Slide4
Materials
Slide deck and demo queries available at:http://www.sqlhammer.com/blog/community
/
This material has already been posted.
When I update
the material, the most recent updates will be available.This slide will be shown again at the end of the session.4Slide5
5Slide6
Benefits of Availability Groups
When should you use them?Automatic failover between local replicas.DB Mirroring (
soon to be ‘Basic HA’
)
and FCIs have the same capability.Manual failover between DR sites.
Replication, log shipping, and DB mirroring have the same capability.Group databases together and failover separately from other groups.Off-load backupsOff-load read loads.6Slide7
Stand-alone instances
7Slide8
Stand-alone instances (cont.)
Database level automatic fail-over available with synchronous commit.Data duplication - a complete set of drives and data per replica.Must synchronize server objects between nodes manually.
8
The beauty of this architecture is everything that it is not. Slide9
9Slide10
Stand-alone instances – multi-subnet
10Slide11
Stand-alone instances – multi-subnet (cont.)
11
Nodes synchronize from the primary, remote nodes don’t speak to each other.
Even more data duplication.
One of the few reasons that I might consider favoring a hybrid with FCIs.
Availability Group Listener handles multiple IPs across multiple subnets.
Asynchronous Commit recommended for remote site, which only supports manual failover.Slide12
12Slide13
AG with Failover Cluster Instances
13
Subnet 1
Subnet 2Slide14
AG with Failover Cluster Instances (cont.)
No need to synchronize server objects within subnet.Still need to across the subnets.Instance level failovers within subnets.
Shared storage can’t cross subnets.
Shared storage dependency
.Can’t ever have one AG replica reside on the same node as another.
Forces you to have more nodes to your cluster.Configurations where all nodes are active are no longer as possible.Can’t group DBs for failover, entire instance moves.14Slide15
15Slide16
Hybrid Architecture AKA DR on the cheap
16
Subnet 1
Subnet 2Slide17
17Slide18
Quorum
Prevents “split-brain”Node majority is typicalPotential voters include
Servers (physical or virtual)
File shares
Remote shared disks
Weight your votes for a complete drop of your connection to your disaster recovery site18Slide19
Why you need to use Windows Server 2012 R2 and above
19
Quorum Demo
Dynamic Quorum
Dynamic Witness
Tie breakerSlide20
Why use the Listener?
Read-only routing.It is capable of faster failovers.Your applications do not have to wait for DNS time to live to expire.
One virtual network name (VNN), regardless of where the Availability Group (AG) lives.
Configuration files between DR sites can be identical.
Different VNN for each AG on the cluster.
Allows for groups of databases to failover to different servers.No instance names to worry about.20Slide21
Limitations of the listener bells & whistles
ApplicationIntent and MultiSubnetFailover
requires .NET 4.0.
Or, 3.5 SP1 with hotfix KB2654347.
https://support.microsoft.com/en-us/kb/2654347
Or, JDBC 4.0Not available for OLEDB or ODBC connections (expect in SQL Native Client 2012+ some restrictions apply).Connections must specify a database in the Availability Group in order to perform read-only routing.Changing database context after connection has been established won’t cut it.21Slide22
Listener Demos
SQL Server Management StudioPersist parameters – Supposedly fixed in vNext as per
MS
Connect.
http://bit.ly/1wKPucP
Not fixed for SQL Server 2016 CTP2Reference the workarounds - http://www.sqlhammer.com/blog/store-optional-connection-parameters-in-sql-server-management-studio/ SQLCMD.exeSQLPS module’s Invoke-SqlCmd (Not a demo, hard to show the non-existence of something)Add MultiSubnetFailover and ApplicationIntent options – Vote up on MS Connect! http://bit.ly/1BCbB82
22Slide23
23Slide24
Read-only routing
Manually configured and optional.Must connect using an Availability Group database context.Common stumbling point, thus the 2-slide
emphasis
.
No SSMS wizard for configuration.
Incurs a round-robin connection performance hit.24Slide25
Read-only routing connection flow
25
Step 1:
Client connects using
ApplicationIntent=ReadOnly
Step 2:
Primary replica replies
with IP for redirection
Step 3:
Connection is made with
read-only instanceSlide26
Read-only Routing Demos
Configure - T-SQL(Non-demo reference)
AlwaysOn
Tools - Denny Cherry
and Associates -
http://dcac.co/applications/hosted-by-you/alwayson-toolsVerify - Dynamic Management Views26Slide27
Read-only Routing Demos
Configure - T-SQL(Non-demo reference)
AlwaysOn
Tools - Denny Cherry
and Associates -
http://dcac.co/applications/hosted-by-you/alwayson-toolsVerify - Dynamic Management Views27Slide28
Back-up Off-loading
Transaction log backupsCOPY_ONLY full backupsDifferentials cannot be takenVarious preferred replica configurations available
sys.fn_hadr_backup_is_preferred_replica
28Slide29
Availability Group Monitoring Demos
Availability Group DashboardsAvailability Group state DMVs
29Slide30
References of interest
Syncing server objects between siteshttp://www.sqlhammer.com/blog/synchronizing-server-objects-for-availability-groups/
PowerShell driven desired state Availability Group failover test
http://www.sqlhammer.com/blog/failing-over-alwayson-availability-groups
/ SSMS AG Listener connection work aroundhttp://www.sqlhammer.com/blog/store-optional-connection-parameters-in-sql-server-management-studio/Lazy log truncation and filestreamhttp://www.sqlhammer.com/blog/filestream-garbage-collection-with-alwayson-availability-groups/ Step-by-step work through of the AG + FCI architecturehttp://www.sqlhammer.com/blog/how-to-configure-sql-server-2012-alwayson-part-1-of-7/ 30Slide31
Materials
Slide deck and demo queries available at:http://www.sqlhammer.com/blog/community
/
This material has already been posted.
When I
update the material, the most recent updates will be available.31My Contact Information:@SQLHammer
derik@sqlhammer.com
www.sqlhammer.com