Transcript Slide deck (only)

Architecting
Availability Groups
An analysis of Microsoft SQL Server Always-On Availability Group architectures
1
Derik Hammer
@sqlhammer
[email protected]
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, and Maintenance Automation

Rebuilt 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 Management

Querying Microsoft SQL Server 2012 Databases (70-461)

Administering Microsoft SQL Server 2012 Databases (70-462)
2
Goals

Skill level: 200-300, assuming some familiarity

Not a “how to”, but there are demos

Architecture: 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-loading

Read-only routing
3
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.
4
5
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 backups

Off-load read loads.
6
Stand-alone instances
Server
Server
Availability
Replica A
Availability
Replica B
Local Disk(s)
Local Disk(s)
7
Stand-alone instances (cont.)
Server
Server
Availability
Replica A
Availability
Replica B
Local Disk(s)

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.
Local Disk(s)
The beauty of this architecture is everything that it is not.
8
9
Stand-alone instances – multi-subnet
Server
Server
Availability
Replica C
Subnet 2
Availability
Replica D
Local Disk(s)
Local Disk(s)
Server
Server
Subnet 1
Availability
Replica A
Local Disk(s)
Availability
Replica B
Local Disk(s)
10
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.
11
12
AG with Failover Cluster Instances
Subnet
1
Shared Disks
Server
Server
Server
Availability
Replica B
Server
Availability
Replica A
Subnet
2
Shared Disks
13
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.
14
15
Hybrid Architecture AKA DR on the cheap
Subnet
2
Subnet
1
Server
Shared Disks
Server
Server
Availability
Replica B
Availability
Replica A
Local Disks
16
17
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
18
Quorum Demo
Why you need to use Windows Server
2012 R2 and above

Dynamic Quorum

Dynamic Witness

Tie breaker
19
Why use the Listener?

Read-only routing.

It is capable of faster failovers.


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.


Your applications do not have to wait for DNS time to live to
expire.
Allows for groups of databases to failover to different servers.
No instance names to worry about.
20
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.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.
21
Listener Demos

SQL Server Management Studio

Persist parameters – Supposedly fixed in vNext as per MS Connect.
http://bit.ly/1wKPucP

Not fixed for SQL Server 2016 CTP2
 Reference
the workarounds http://www.sqlhammer.com/blog/store-optional-connectionparameters-in-sql-server-management-studio/

SQLCMD.exe

SQLPS module’s Invoke-SqlCmd (Not a demo, hard to show the nonexistence of something)

Add MultiSubnetFailover and ApplicationIntent options – Vote up on
MS Connect! http://bit.ly/1BCbB82
22
23
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.
24
Read-only routing connection flow
Step 1: Client connects using
ApplicationIntent=ReadOnly
Step 2: Primary replica replies
with IP for redirection
Primary Replica
(Includes Listener)
Client
Step 3: Connection is made with
read-only instance
Secondary Replica
(Read-Only)
25
Read-only Routing Demos

Configure - T-SQL
 (Non-demo
reference) AlwaysOn Tools - Denny
Cherry and Associates http://dcac.co/applications/hosted-byyou/alwayson-tools

Verify - Dynamic Management Views
26
Read-only Routing Demos

Configure - T-SQL
 (Non-demo
reference) AlwaysOn Tools - Denny
Cherry and Associates http://dcac.co/applications/hosted-byyou/alwayson-tools

Verify - Dynamic Management Views
27
Back-up Off-loading

Transaction log backups

COPY_ONLY full backups

Differentials cannot be taken

Various preferred replica configurations available
 sys.fn_hadr_backup_is_preferred_replica
28
Availability Group Monitoring Demos

Availability Group Dashboards

Availability Group state DMVs
29
References of interest

Syncing server objects between sites


PowerShell 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/
30
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.
My Contact Information:
@SQLHammer
31
[email protected]
www.sqlhammer.com