AlwaysOn Availability Groups

Way Too DeepJoey D’Antoni

SQL Saturday #164 Cleveland

18 August 2012

About Me

Principal Architect SQL Server, Comcast Cable

@jdanton

Joedantoni.wordpress.com (Slides will be here and on SQL Sat site)

jdanton1@yahoo.com

Agenda

Overview of AlwaysOn Extended Events Briefly What Happens When?

Turn on AlwaysOn

We Build an Availability Group

We add data to a Primary DB

We backup the transaction log on the secondary

We query the secondary DB

Warning

You are going to see a few things that aren’t fully documented

We aren’t touching anything—just looking to see what’s going on

I’m telling you what I think is happening, I’m still trying to get answers on some of it

Availability Groups

Extended Events

Eventual Replacement for SQL Profiler Tracing 612 Events in SQL 2012 A significant portion of these deal with AlwaysOn

(HADR)

What Happens When…

We enable AlwaysOn

Enable AlwaysOn

Demo

HADR_WSFC_CHANGE_NOTIFIER_STATUS

Creating an Availability Group

Object in AD and DNS Gets Created (Listener) Database(s) get restored onto secondary Lots of Metadata gets created to Manage the

Availability Group

Building Availability Groups

Demo

Moving Data

Database Mirroring used a single thread per database to move data

Always On is different—it uses a request pool that is shared between all AlwaysOn Databases

On the primary messages the active log scanner is the log pole.  

When a secondary is ready to receive log blocks a message is sent to the primary to start the log scanning.  

This message is handled by a worker in the HadrThreadPool. 

Moving Data

Moving Data

Demo

Transaction Log Backup on Secondary

One of the new features of 2012

Allows us to flush the log on the primary database by backing up on the secondary

Secondary Log Backup

Demo

Querying Secondary Instance

Another Big Feature of AlwaysOn Replicas

Runs in Snapshot Isolation Transaction Level to Minimize Blocking Issues

Creates Secondary Stats where missing—in TempDB

Stats on Secondary

Stats on Secondaries

Demo

Summary

AlwaysOn is pretty cool

It’s clear a lot of work went into this

It’s fairly complicated, but the good news is that it’s easy to work on

Questions

Contact

@jdanton

jdanton1@yahoo.com

Joedantoni.wordpress.com