>> Learn about a very interesting query
tagging technique that allows you to really
achieve scale out with your apps using
Azure SQL database Hyperscale named Replicas.
This week on Data Exposed.
[MUSIC]
Hi, I'm Anna Hoffman and welcome to
this episode of Data Exposed.
Today I have joined with me
Davide Mauri from the Azure SQL Database Team.
Davide, thanks so much for joining us today.
>> Hey Anna, nice to be back.
Yeah. Thanks a lot for having me again.
>> It's always great to have you on the show.
You always show us really cool things related
to developer experiences and building really scalable apps,
which is actually what we're going to be talking about today.
You helped me build
this application really cool shorted SQLBits I think
you also uses recently at Dev Intersections.
Can you tell us a little bit
about this app and the reason you came up with it?
>> Yeah, absolutely. I like to try to
do something called lateral thinking or thinking out of the box.
I think that's the most fun part of being a developer,
trying to come up with clever ideas using the tools we have,
and so I was wondering how we can make
an API scale as much as we can.
Also, with the help of
Azure SQL database and specifically a feature that I worked
on which is called named Replica that help
us to have up to 30 read Replicas,
which means we can really have a lot of course at
our service and then we can really
scale out our database just like we do with application.
I was trying to find a way to make
it easier to understand how someone can build
such an API layer so that they can take
advantage of that many named Replicas.
>> Awesome. Cool. Yeah. And I got to see this,
and I think there was a lot of
excitement among a lot of people that saw this.
I had no idea you could use like,
almost like a query tagging type of technique.
>> Yeah, exactly.
>> I knew Hyperscale could scale up.
I didn't really know it could scale out in this way.
>> Yeah.
>> Can you tell us a little bit more about how that works?
>> Yeah, and I've prepared
a couple of slides just to make it easier.
First of all, just to justify the fact that
I bought an expensive tablet to do this,
but to my wife, but also because I think
it's easier to visualize what's happening.
Basically we have an API layer on the top.
It could be our starting point for example,
a back end API and what we want to do is
build some or put in some routing logic in
there so that we can take advantage of all the Replicas
we may have with Hyperscale.
You know that Hyperscale has compute and storage to separated?
Basically we have the primary Replica
here where all the read and write goes,
but the whatever it is Primary Replica
will do in terms of modifying data,
will be written in this page server
that are here on the bottom of the slide.
If we spin up
a new Replica that can only be a "Read Only" Replica,
but we have many of them as you can see here in the major.
All these Replicas we immediately
see whatever has been changed in the data in this page server.
Whenever we write something in the primary Replica,
all the other Replicas immediately see the changes.
There is literally no data movement.
It's everything is always in sync.
This allows us to build
some interesting architecture if we put some
basic or even complex depending on
the need routing logic here in the API layer.
For example, we can decide to send all the insert and updates,
of course to the only Replica that
can also write status with the primary.
But then what happens when you have a maybe,
mobile phones that start to query
your database to update the dashboard
or to update our gaming score list?
You want to send these tiny queries maybe
to other Replicas that you may have
created just to sustain the workload.
You have what I call a single tone reader, like,
very simple read by a primary key, for example,
and you want to spread them out over a couple of Replicas here.
At some point, maybe you
also need to start to do some analytics again,
if I'm doing something related to online gaming,
I want to understand how I'm performing against
my friends or myself last month.
I need also to run some analytical query,
even if this is maybe a gaming application.
But so in that case, you don't want to have
your API that are serving the game to basically be
affected by the performance of
a more complex analytical query that needs to
harvest data in order to produce
maybe a simple report that then will be shown in the phone.
You want to sell all these data in another Replica that you
may want to create and maybe it's
a bigger Replica so that it can sustain
a bigger complex query and you want to send all the queries here.
The idea here is to build something that once we
send a query in our API layer, for example,
to retrieve some data, the API layer is smarter or
intelligent enough to understand
where is best to execute that query.
If it's a read query or a read write query,
sorry, we just have to go to Replicas.
But if is trying to read a small amount of data,
it may send the query to these smaller Replicas,
but many of them and instead if it's trying to read a lot of data,
it's just going to send the query to this bigger Replica.
We only have one, but with a lot of
CPU so we can crunch number what we do.
That's the overall idea,
and probably maybe you are
asking what's inside this API layer, what's the magic?
>> Yeah.
>> It's pretty easy. Pretty easy.
Basically, let's imagine that these are Rest API,
we are building a Rest API to solve
a mobile application or
desktop application or any kind of application today.
We get the request like get data for
reporting and we know that behind
the scene we need to connect to a database,
and so we run a select.
We run a "Select", but we know that the "Select"
or at least the developer who's writing or who has
wrote that "Select" knows that that "Select" or
that command will actually be used to build the report.
He or she can target the query.
That's why we call this technique query tagging or tag query,
tag based routing where basically,
let's say for now, the simplest way
to visualize this is with a caller.
Let's say that we call
this complex query light blue and the light blue means
that those light blue queries are those that will
be used to serve a reporting dashboard in your mobile phone.
If we could have some meta data somewhere,
maybe in a table, maybe in a cache,
maybe in a file, whatever you think it's worth,
that says all the light blue queries
must be or if the Replica is available,
should be served by the biggest Replica that you have, well,
we can figure out that we have a light blue query coming in,
we have some metadata and then we can decide,
okay, I have the light blue query,
I know that the best place where to serve
the light blue queries are the 16 bigger Replica,
so let's send it to the 16 bigger Replica.
Right now what I'm doing is
exactly this and actually I prepare the code.
Let me switch to the code right away,
and actually everything is available here already,
and you can go to this website to
the GitHub repository and whatever we'll be showing in
a few seconds is something you can actually or anyone can download
and deploy on Azure so they can try it right away.
The code is super simple at the end of the day.
Basically here we have a regular.net
application that implements rest API.
We have our shopping cart control here.
We are simulating a shopping cart.
We have our endpoint,
our controller, and when we want to do a get, for example,
to retrieve the information about a specific shopping cart,
I'm calling this method call query, and as you can see,
sometimes I actually tag my query.
I'm a developer that is building the controller.
I know that behind a scene I should use or I
have to use this kind of query method that someone built for me.
This query method accept a tag and in this case is trying to get
something by passing a package
here or just by getting the cart ID in
this case or in this case by searching something.
>> Since I'm a good developer in my team,
I tag my queries as has been told by architect to say,
this query is going to be used for search.
Why this can help the routing engine behind the scene?
Let's take a look at how the query thing work behind the scene.
My query method here just accepts,
of course, basically,
information on what you want to retrieve and then this tag,
which by default, if I don't say anything,
it is called generically.
The first thing that my query method here does is try
to understand if we want to read or write.
Basically, if you are solving a get request,
of course, we want to read.
If you are solving a poster,
output, Adelite, or Apache.
In this case, I'm assuming we want to write.
Now, if you want to write that, it's very easy.
We just have to go to the primary
and there's not really much to decide.
But if we want to read then maybe we can get
the best connection string to read data from that specific time,
and this is where the magic happened basically.
Here if I go and do get connection string and we go here,
instead of just returning
the connection string I have in my configuration file,
which is exactly what I do if I detect that you want to write,
I start to query.
I start a procedure here that returns
all the available scale-out Replicas and
my story procedure is doing nothing
more than reading from this table,
which right now is completely empty.
This means that right now I don't know how to write my queries,
which means that they all go to the primary Replica.
Let me actually show that to you.
I can run my application.
This is the GitHub repository I downloaded,
so let's me see what I'm reading
up so I can just put dotnet run and run the application.
Then while application is started,
I already have some sample shopping cart here.
The shopping cart is randomly generated,
so let's say it is a single shopping cart where you have one,
two, three, six products in the shopping cart.
Each product has a price and
some details and to simulate a real-world scenario,
the details inside the product
put in the shopping cart can be anything.
We just save it as adjacent formats,
so we can store any properties in there. This is how it will work.
Let's try to take exactly that shopping cart.
In just a few seconds here, here we go.
Now, nothing new or nothing special,
but if I go to the header,
I changed my application so that it returns from
which Replica this date is coming
from and this is the primary Replica,
so this is NR OLTP advanced is
the only Replica at the moment and
is the primary what I can also write.
For example, I can go to the shopping cart,
add something here,
and now if I take this cart and go here, change it.
It's now returned and then, of course,
it's coming from the same Replica because it's the only one with.
Now, let's say that we noticed that during the weekend
or during the black Friday or some specific days,
the workload increase,
and our database is very busy and we want to
make sure we can scale out as pretty
much as we are doing with our API
because we deploy our API maybe in
a container and we just increase
the number of container that are able to serve our API,
so we would like to be able to do the same with the database.
Within the Replicas, we can
because the only thing I have to do is,
and let me show you.
It's very easy. I have the code here.
To basically create a copy or a read-only Replica,
but of my database,
I only have to alter my database,
add the secondary server.
Specified the secondary type must be named.
Give it the database name to avoid
conflict with existing database name and that's it.
That's how I can create a Replica of my data.
Let's say usually in 15,
20 second is available.
Once this is available,
I can just take one of the Replica I've created,
and of course, I already have created
the here some Replicas and I've created four of them already.
I can just add them to my metadata table that will be
used by routing logic to figure out where queries should be sent.
Any query together with the generic red tag will
be sent to Replica 1 or Replica
2 randomly chosen in my simple logic.
If I send a query that needs search data,
it will be sent to the Replica
3 and this is something we are not using at the moment,
but let's assume that I also have
complex reporting queries that will be sent to Replica 4.
Now, I can just insert my data.
I've already done it probably.
Let me see. Yes, I did before, so here we go.
Replica are in the table right now and I
can expect that right now if I
re-execute this query just like I did before,
usually the Replica name will be different,
will be the first or the second.
Let's try. Yes, that's it.
>> Nice.
>> This is nice because it means,
you see, it's completely random,
so we will get one or two at random.
This means that I can now go on,
create a new name with the Replica
while my application is running.
Please note that my application didn't stop executing.
I didn't have to stop, scale
up the database, and then reconnect.
Everything is running and
completely unaware of what's happening behind the scene.
I could spin a new Replica,
add it to my metadata,
or I can even do something like that.
Let me just remove everything
and say that right now I want to change
the usage of the other Replica and put
everything generically because I
discover that that's what I need to do.
Now, I can go here. My application is still running.
But you will see that at some point it will take
three to five seconds to update the metadata in
memory and at some point here we see that we
start to use a three and four hopefully.
Here we go. Yeah, here we go.
Nice. I added some delay in order to keep that a memory,
so it's not that we always have to query the metadata table,
it stays in memory for
three to five seconds
randomly to avoid also the thundering if you have a problem.
That's it. You can easily add or
remove Replicas as you see fit in order to,
serve your workload without any performance issues.
That's very simple.
It's literally just getting
a connection string based on the target that is coming in
and the metadata that tells you
which Replica should be used to serve a specific target.
It's pretty easy to implement.
It can be made much more
intelligent than that simple, random here.
If we go and take a look at here,
you will see I'm randomly taking data from my Replica.
But for example, discussing with
the attendees in the last conference,
someone came up with the idea,
or maybe I can query
the status of the resource availability of my Replica.
Like maybe I have 10 Replicas and one
is using for some reason 80 percent of the CPU.
Well, maybe I don't want to send query there just to avoid.
You can really fine-tune this algorithm to even take into account
how much each Replica is already
used so to do a more intelligent routing piece.
Again, just a random, but it's an example.
I think if we add these with the ability to
do some prediction with machine learning,
this allows developers to create
some interesting scenario where your endpoint starts to
create named Replicas and scale out even before you actually
have a spike in the workload based on maybe historical trends.
Your application can always be snappy or available,
and provide the best solution to your customer.
I thought to share that because I think
it's interesting architectural scenario.
>> Yeah, it's super interesting
this idea of one with name Replicas in general,
just you get this scale on
the fly that you can have up to 30 name Replicas.
It only takes a, a minute or so to create them,
and then now you've added this extra layer of intelligence
really that allows you to run
between your different Replicas and at the end of the day,
keep your right one safe and
not going to be disrupted by any read or uploading workloads.
As we wrap up, Davide,
do you have any final tips and tricks for people who
are just getting started with hyper-scale and named Replicas?
>> Yeah. Well, download the repository
and just run on your local machine
because everything done here can easily
be used even with just one Replica.
Even your local SQL Server maybe where you develop locally,
and then it's very easy to deploy in the repository.
You have here all the scripts to deploy everything,
including a load tester built with
the local stuff so you can actually simulate
not just one client like I am right now,
but I don't know, 100.
I don't know how many we simulated.
I think will be split like 100 or even 1,000 to actually test
the workload that you expect to have to deal with,
so that's pretty cool.
>> Awesome. Well, thanks so much Davide for coming on the show.
I learned a lot. I think the samples are just so awesome.
I mean, even I was able to go through the instructions
and set it all up like it's super easy to use.
Super interesting technology that you've
implemented to get more bang for your buck in a sense.
Thanks so much for coming on the show,
sharing the sample with our viewers.
To our viewers, go check out the sample.
We'll put a bunch of links in
the description for you to learn more.
Let us know in the comments what you're going to use this for,
what you think of the sample.
We hope to see you next time on Data Exposed.
[MUSIC]