[MUSIC]
Hi, I'm Mihaela Blendea, and I'm a program manager on SQL Server team.
>> Hi I'm Travis, I'm also a PM on the SQL Server team.
>> Today we're gonna look at several scenarios where availability groups
can be used for read scale capabilities in SQL Server vNext.
>> Hold on.
I thought availability groups were for high availability.
>> Yes, but in addition to bringing together the best in class
HA capabilities, availability groups is a comprehensive solution that
offer scaling solutions as well.
>> Hmm, all right, excited to see this.
>> Let me show you how and
also highlight the enhancements we have in SQL Server vNext.
>> Okay.
>> In a typical database application, there are multiple
clients running various type of workloads against the database.
There are OLTP workloads, analytics reporting and so on.
Sometimes, the workloads will hit bottlenecks
to the resource constraints.
In this case, the customer will want to free up those resources to
increase the throughput for OLTP workloads, or
to achieve higher performance and scale for the read only workloads.
And this can be achieved by creating a group of replicated databases,
and offset the read only workloads to the secondary replicas.
And that's exactly what availability group is.
You can configure one or
multiple secondary replicas to accept read only work traffic.
And free up resources on the primary databases.
The clients will connect directly to the secondary databases or
the customer can configure a read only routing list.
And the primary will forward the connection request to each of
the secondary in the routing list in a round robin fashion for
optimal load balancing.
>> All right, so this is great.
I got read only scaleout, I can offload my read only workloads off
of my OLTP database and get faster for both my OLTP and reporting.
So, lots of times with these reporting things or read-only
workloads, you wanna distribute that data all over the world, so
that people can get local access to that data for wherever they are.
Can Always On availability groups help with that?
>> Definitely, and
the solution to that is to use the distributed availability groups.
In addition to offloading the read-only workloads from
the primary to the secondary replicas,
now the data is closer to the source of the reads workloads.
So you have less network latency, and you have dedicated resources for
higher read throughput.
>> So, I see on this chart here,
that we've got a large number of databases.
How many of these can I actually have?
>> Well, a single distributed availability groups pretty much
doubles the number of secondary replicas that you can have.
If customer wants increased scaling they can daisy chain
multiple availability groups or they can create two distributed
availability groups from the same primary availability group,
so they have infinite scaling.
[LAUGH] [INAUDIBLE] >> Wow, okay, infinite scale,
you heard it here.
All right, so this is awesome.
I've got my read-only workloads, my analytics, and my reporting.
They're offloaded to read-only replicas now.
And my OLTP workload is gonna be faster, too,
because I've got it all dedicated to one server now.
What other kinds of workloads can I offload to these read-only replicas?
In addition to analytics reporting or
any type of legal only workload that can run on secondaries.
We can also run backups or DBCC checks for in again,
off loading workloads from the primary to get better performance.
>> I see, awesome.
Okay, so always on availability group of balance SQL Server for
quite a while now.
With SQL Server v.Next, what's different?
Why are we bringing this up now?
>> Previously customers would have to set up a cluster to take
advantage of these capabilities.
And configuring, operating the cluster,
it was a lot of manageability overhead, if HA was not the goal.
>> Yeah, it's not super easy, that's for sure.
>> Exactly and if the purpose is to
offload the read only workloads and increase the performance
on the primary to have it run mission critical workloads.
Then this is in SQL Server v.Next,
customer will be able to configure availability groups without
any dependency on any cost of technology.
>> Right. >> So, that brings me out
to say that this solution is available both on Windows and Linux.
>> Mm-hm. >> And
customer can even use it today it's in public preview,
so they can try it out.
>> Nice, okay.
So the idea is if I want high availability and read scale,
I use the always on availability groups with the cluster solution
like Windows Server cluster or pacemaker.
But if I don't really care about the high availability I just
use the always on availability groups for replicas.
>> Without a cluster, you don't have a HA solution.
>> I see.
>> So, if customers will want a HA, they will have to integrate with
the cluster manager, Windows Failover Clustering on Windows,
and a cluster technology like Pacemaker on Linux.
>> Gotcha, okay, cool.
So can we see a demo of this?
>> Definitely.
Let's try it out.
>> All right, Maheala, so I've got this application.
It's got an OLTP part to it and it's got an analytics part to it.
And I'm excited to be able to take the analytics part and
move that off to a read-only replica set of nodes for better scaling.
And then that will also improve the performance to OLTP part of my
application.
So I've got a benchmarking tool that I use here to measure the throughput
of the analytics part of the workload.
So let's go ahead and set up an availability group and then I'll
launch the benchmarking tool against it and we'll see how it goes.
>> Yeah, I already had set it up.
It has three replicas, three secondary replicas.
I created the availability group with a cluster type none.
That means I don't have a cluster on top of it.
>> Mm-hm. >> Right?
And if we look at the read-only routing list that I configured for
the primary, it has at this time just one of the three secondary
replicas in the list.
>> Okay. >> So you can start a workload.
>> All right.
Let's start it.
So I'm gonna add 20 or so
connections to this availability group for now.
And so as these are coming up and connecting,
let's talk about what's going on on the availability group side.
>> Definitely, so
the connections will go to the primary which will route down to
the secondary replica that is configured at the primary replica.
>> Okay, so
since we have one replica in the read only routing list.
Now all these connections are going into that one node, right?
So this is sorta our baseline test of the throughput that we get
on a single node.
So what I'm seeing right now is that our transactions per second
are typically around six per second right now.
So let's go ahead and stop this particular test, and
let's go ahead and add an additional replica.
And then we'll run the test again.
>> Let's see.
So if we add a second replica in the routing list,
what it will happen is that the primary will start routing
the incoming connection to the two replicas in a round-robin fashion.
So we're gonna have load-balancing among the two replicas.
So let's just run it.
We can see that the routing disk was updated.
I have two replicas now.
>> Okay. >> In the list.
You can just stop it, yeah.
>> So start the test again here, okay?
So here we go.
We'll add the same 20 connections and see what happens.
So this time, the connections are coming in, and the primary is
sending them to one of the two nodes for each connection.
So it ends up being load balanced across the two.
>> Yeah, so with the 20 connection, one replica have ten and
the other ten, so it's- >> Right.
>> So it should be linear scaling.
>> Okay, perfect.
All right, so we're almost there.
We've got 17 of them up now.
Okay so now all of them are now connected and it looks like we're
getting a throughput on this at about 11 transactions per second.
So like you said, almost double what we saw before.
Which is sorta what we would expect.
Okay so that's awesome.
I can just keep adding replica nodes and
scale my application up as needed.
Sometimes with these analytics workloads,
though, or read-only workloads in general.
Sometimes there's higher demand and
lower demand depending upon the time of day or time of month even.
So is there a way that I can add replicas into my routing list
dynamically without having to stop my application?
And it can just start spreading the load across even more nodes, and
then scale it back down automatically?
>> Definitely.
So in your case, right, you already running
I'm gonna add a fourth, Replica to the group, right?
So it will have three secondary replicas right now.
And any new incoming connections it will start
distributing among the three not only two, right?
So I'm just going to update it right now.
So, let's see.
It's the simple alter command.
And you can see,
there are three secondary replicas in the read only routing list.
>> Okay. >> You can increase the number of
connections to simulate higher.
>> Right.
>> Workload demand.
>> So we'll add another ten connections, so
they're coming online now.
And I didn't have to restart my application or anything,
they're just getting connected.
So, can you see some of these new connections?
>> Exactly, so now if you query the third secondary
replica that we just added.
We can see that it got three of the new connections and
they're running workload right now.
>> Nice. That's perfect.
So this allows me to dynamically scale up and down
the read part of my application, depending on the demand.
>> Yeah, and when you scale down, these connections will continue
running until the workload is complete.
So it's not gonna cut.
>> So you just kind of drain the connections over time.
>> Exactly.
>> If you needed to spin it down.
All right great demo Maheala that was awesome to see all that
in action.
Can definitely see how this can help customers get better performance
by off loading those read only workloads.
So, if you're interested in trying this out for yourself just go grab
the latest SQL Server v.Next CTP bits, starting with CTP1.3.
I think this has all been in there, and so
just go grab whatever the latest release is that's out there now.
And install it, try it out, let us know what you think.
Thanks for joining us.
>> Thank you.
[MUSIC]