Architecting Availability Groups An analysis of Microsoft SQL

Architecting Availability Groups An analysis of Microsoft SQL Server Always-On Availability Group architectures 1

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 DBA Specialize in High-Availability, Disaster Recovery, Performance and Automation Chapter leader of Fairfield. PASS in Stamford, CT. BS in Computer Information Systems with a focus in Database Management Querying Microsoft SQL Server 2012 Databases (70 -461) Administering Microsoft SQL Server 2012 Databases (70 -462) 2

Goals Architecture: Stand-alone instances Architecture: Stand-alones with multiple subnets Architecture: AG with Failover Cluster Instances Architecture: Hybrid approach (DR on the cheap) Read-only Back-up Skill Not routing off-loading level: 200 -300, assuming some familiarity a “how to”, but there are demos 3

Benefits of Availability Groups When should you use them? Automatic DB failover between local replicas. Mirroring 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 read loads. Off-load backups. 4

Stand-alone instances 5

Stand-alone instances (cont. ) Database level automatic failover available with synchronous commit. Data duplication - a complete set of drives and data per replica. Must synchronize server objects between nodes manually. The beauty of this architecture is everything that it is not. 6

7

Stand-alone instances – multi-subnet 8

Stand-alone instances – multi-subnet (cont. ) • 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. 9

10

AG with Failover Cluster Instances Subnet 1 Subnet 2 11

AG with Failover Cluster Instances (cont. ) 1 No need to synchronize server objects within subnet. 2 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. 12

13

Hybrid Architecture AKA DR on the cheap Subnet 1 Subnet 2 14

15

Quorum Prevents “split-brain” Node majority is typical Potential 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 site 16

Quorum Demo Why you need to use Windows Server 2012 R 2 and above Dynamic Quorum Dynamic Witness Tie breaker 17

Why use the Listener? It is capable of faster failovers. Your applications do not have to wait for DNS time to live to expire. Read-only routing. One virtual network name (VNN), regardless of where the Availability Group (AG) lives. Different VNN for each AG on the cluster. Configuration files between DR sites can be identical. Allows for groups of databases to failover to different servers. No instance names to worry about. 18

Limitations of the listener bells & whistles Application. Intent and Multi. Subnet. Failover requires. NET 4. 0. Or, 3. 5 SP 1 with hotfix KB 2654347. https: //support. microsoft. com/en-us/kb/2654347 Or, JDBC 4. 0 Not 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. 19

Listener Demos SQL Server Management Studio Persist parameters – Supposedly fixed in v. Next as per MS Connect. http: //bit. ly/1 w. KPuc. P Not fixed for SQL Server 2016 CTP 2 Reference the workarounds http: //www. sqlhammer. com/blog/store-optional-connectionparameters-in-sql-server-management-studio/ SQLCMD. exe SQLPS module’s Invoke-Sql. Cmd (Not a demo, hard to show the nonexistence of something) Add Multi. Subnet. Failover and Application. Intent options – Vote up on MS Connect! http: //bit. ly/1 BCb. B 82 20

21

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. 22

Read-only routing connection flow Step 1: Client connects using Application. Intent=Read. Only Step 2: Primary replica replies with IP for redirection Step 3: Connection is made with read-only instance 23

Read-only Routing Demos Configure - T-SQL (Non-demo reference) Always. On Tools - Denny Cherry and Associates http: //dcac. co/applications/hosted-byyou/alwayson-tools Verify - Dynamic Management Views 24

Back-up Off-loading Transaction log backups COPY_ONLY full backups Differentials Various cannot be taken preferred replica configurations available sys. fn_hadr_backup_is_preferred_replica 25

Availability Group Monitoring Demos Availability Group Dashboards Availability Group state DMVs 26

Materials Slide deck and demo queries available at: http: //www. sqlhammer. com/blog/community/ This material has already been posted. If I ever update the material, the most recent updates will be available. 27

References of interest Syncing server objects between sites Power. Shell driven desired state Availability Group failover test http: //www. sqlhammer. com/blog/store-optional-connection-parameters-in-sql-servermanagement-studio/ Lazy log truncation and filestream http: //www. sqlhammer. com/blog/failing-over-alwayson-availability-groups/ SSMS AG Listener connection work around http: //www. sqlhammer. com/blog/synchronizing-server-objects-for-availability-groups/ http: //www. sqlhammer. com/blog/filestream-garbage-collection-with-alwaysonavailability-groups/ Step-by-step work through of the AG + FCI architecture http: //www. sqlhammer. com/blog/how-to-configure-sql-server-2012 -alwayson-part-1 -of-7/ 28

My Contact Information: @SQLHammer derik@sqlhammer. com 29 www. sqlhammer. com