Transcript Architecting_Availability_Groupsx

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, Performance and Automation

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

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 routing

Back-up off-loading

Skill level: 200-300, assuming some familiarity

Not a “how to”, but there are demos
3
Benefits of Availability Groups

When should you use them?

Automatic failover between local replicas.
 DB

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
Server
Server
Availability
Replica A
Availability
Replica B
Local Disk(s)
Local Disk(s)
5
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.
6
7
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)
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
Shared Disks
Server
Server
Server
Availability
Replica B
Server
Availability
Replica A
Subnet
2
Shared Disks
11
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.
12
13
Hybrid Architecture AKA DR on the cheap
Subnet
2
Subnet
1
Server
Shared Disks
Server
Server
Availability
Replica B
Availability
Replica A
Local Disks
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 R2 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

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.
19
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
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
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)
23
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
24
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
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


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/
28
My Contact Information:
@SQLHammer
[email protected]
29
www.sqlhammer.com