>> Learn how you can build easy APIs with Azure SQL database.
In addition, learn how you can use things
like Azure Automation, Logic Apps,
and all about the data API builder
this week on Data Exposed MVP addition.
[MUSIC]
>> Hi, I'm Anna Hoffman and welcome to
this episode of Data Exposed MVP edition.
Today I'm joined once again by Joey though it's been awhile.
Joey, Thanks so much for coming on Data Exposed MVP addition.
Can you tell us a little bit about yourself and what you do?
>> Sure. I'm a Principal Consultant
at Denny Cherry and Associates Consulting.
We do a lot of work with a variety of customers with Azure SQL,
the Cloud in general,
all architectural database,
all fun things and we have a cool demo share today.
>> Awesome. I'm really excited.
The title of this episode you submitter was
easy APIs with Azure SQL database.
I don't know a ton and what that means,
I have not seen this demo,
so I'm just super excited.
But before we get into, I want us to
share what led you to creating this,
or what's the scenario like,
how did you come up with this?
>> This is a really funny story.
I was cruising the Azure subreddit one day in
January and someone was asking
about how to write a PowerShell script,
or are not necessarily a Powershell script,
but how do you get a list of all the Azure VM sizes.
I thought I'm bored, it's cold outside.
I fired up Visual Studio Code and started writing some PowerShell.
Eventually got that to work and then I wanted to persist it.
Then I talked to [inaudible] on the SQL team
about Data API builder
and we were able to build a really cool app.
Without a whole lot of effort, I'm not a developer.
I wanted to walk through that and share it with folks today.
>> Awesome, cool. I love the Data API builder.
We've been hearing a lot of excitement about
it so it's cool to hear that you were able
to easily pick it up and get started. Let's say Look.
>> Cool. First we're going to start out here and this
is Azure automation for those of you who are not familiar.
This isn't really super fancy,
but it's just a way to connect in
one PowerShell in Azure and in this case on a schedule.
You're on this weekly
and I'm going to talk
you through a little bit of the code I wrote here.
Just online 29-331,
I'm resizing an Azure SQL database
and we'll explain why after we're done.
This is a strictly a cost management operation.
This is our DCEC trial sub so I'm increasingly size for
data processing and then eventually it will
shrink it back down to serve data.
But what we're doing here is going against
the Azure Resource Provider, starting Online 35.
In getting for each region in Azure,
because each region doesn't necessarily have the same VMs
available so we're getting the name of the VM size,
the CPU, the memory, and gigabytes,
the IOPS, which is the interests of DBAs,
number of network cards,
number of disks, and then finally the region name,
a couple of other attributes.
Then we're writing those into
an Azure blob storage account so we get one file for each region.
This will matter eventually,
I promise I'll show you.
I started down this path and I got this working,
and I read all this data and then I wrote it into
an Azure SQL database because I'm a database person,
we write things into databases so it can mess with them.
One last bit I want to show you here,
I write finally,
when this is complete because it moves
through each region one by one.
I write a file that's just called a status.
That processing is done
and I'll explain why that matters in a minute.
We then have an Azure SQL database and we have
a procedure and I did a lot of
terrible things in this procedure and DBAs.
I just want to tell you, forgive me,
it was the only real way to do this.
Those files, that Cloud,
I'd put a storage JSON data.
I'm running a cursor to loop through each of those files
for each region and then I'm using dynamic SQL.
This is not my favorite combination of technologies,
but it was either do it this way, or doing PowerShell,
which effectively would have been using a cursor.
What this does, that data's and JSON,
it slices and dices,
it makes it in a tabular data and then ingested into
the database noobs 30j region and then completes it.
We have a holding cable that gets
truncated and we insert into our data.
This brings us to the Data API builders so
we have data in a table,
but we don't have it in a way that we can view it.
I'm on my Mac here and here I'm in the Data API builder directory.
All I have to do in order to build
my Data API App and then I'm going to do
this locally just to show you the demo,
is I just have to add
a connection string cycled through my history here a second.
I'm just adding a connection string here you can see my username.
Don't use a local and I
use Azure Active Directory for this in real.
Then I'm also adding a VM size.
The VM sizes thing is my API object,
and that's just my table name.
I'll Control C there and then we'll look
at this dab-config.JSON file.
We can see here that my entity is called dbo.VMSizes.
I can connect to it anonymously.
You can also configure security.
Right now it's just anonymous.
I can go ahead and run this App locally on
my machine so I'm going to do that by typing DAB
start and here we can see it starting in my console.
I'm going to navigate over to a tool called
Postman and this is just a tool that lets you explore APIs.
I'm going to make a call here,
and we can see that we're getting data back.
I can do a bunch of things to filter this.
It's just coming back in JSON,
or often the API in any order.
After we talk about how we built the app,
I'm going to show you a better way to do this using GraphQL.
Because one of the cool things about
the Data API builder is it lets you interrogate the data,
either just using rest, or GraphQL.
On GraphQL feels a little closer to
SQL so it may sound a little more and more comfortable with it.
The rest of the components to this app are interesting.
I run this PowerShell runbook as an automation job,
it runs on a schedule so here I can click,
I can see it runs weekly.
The natural runs on 18th May,
but that's neither here nor there.
I needed a way to trigger this.
What I wanted to do that,
I used an Azure Logic App,
and this is a little bit overly complicated,
I'll admit, but it was neat demo.
What this does is when that status BOB that I
mentioned earlier gets updated so
when the automation job has finished running,
it triggers the stored procedure that
I showed you which ingest the data.
Finally, this calls another automation job called cleanup,
that reduces the size of the Azure SQL database back to
its normal size and then deletes
all those BOB file so I'm
not spending any more money than I need to,
so this is a cost-effective clean up.
Finally, to show this to the Internet,
I built a Web App, so I
deployed the Data API builder as a Docker container.
I did point that to the Azure Container Registry
and then I built a Web App
using that and here you can see it's a very small basic Web App,
but it's hosted and here's the GraphQL endpoint.
We even have custom DMS so I can see VMSizes,
dcc.com/GraphQL and this gets
me this GraphQL endpoint where I can query the data.
Here, I'm querying
VM sizes for VMs that have greater than 16 CPUs,
memory greater than, or equal to 512 gig,
more than 80,000 IOPS,
and in this case the West US 2 region.
I can see what my results set is.
I can see how long my query took so if I run this again,
took 152 milliseconds,
I think because it's probably cached on the server,
so it's a very nice one.
But this all brings us app together so
we're using all whole lot of components on Azure
to do this and we're bringing it nice and tightly together and
it's something we can showcase
to users and it makes for a nice demo.
>> Nice. This is super cool.
I love how you used a bunch of
different technologies and then
it all came together with this nice way,
and easy way to start to use GraphQL,
get your toes into it.
I see a lot of spaces where a viewer might be like, okay,
not only am I going to learn all these cool technologies,
but I'm going use all these different things.
Couple of questions for you. One,
maybe this is a little bit of a complicated workflow,
or maybe it's just my first time seeing it.
What do you think? Why do you build it like this?
>> Well, it started out as a read of test.
Then I was like, hey,
I can do a presentation on this.
I could have done everything in the automation runbook,
in a single run book where I do all that cleanup.
This lets me demo a bunch of different functionality in Azure
to highlight like Logic Apps
are a neat way and a lot of people aren't too familiar with them.
Especially for admins who don't have a lot of code experience,
but they want to automate some more flows in
their environment so it was a nice way to showcase that.
>> Definitely and personally working with Logic Apps are super
easy to use so that's also a nice plug.
But I guess what you're saying is the Logic App
maybe wasn't totally needed for this scenario.
>> The Logic Apps probably a little bit extraneous here.
In a lot of the demos,
or the Data API,
or using Static Web Apps and I just said
more fun using a regular Web App
because there was more air my comfort level was.
>> Sure, awesome, that's cool.
You mentioned earlier that you can access this anonymously,
or that's how you set it up.
If you wanted to add security,
like how does that work between these services?
>> Synthase, I'm going to break those down into two parts.
The security between the two services is really awesome.
I'm taking advantage of managed identity and
Azure so it's very easy to use lease privileges.
For example that Logic App can connect to the database,
but it only has permission to execute that one stored procedure.
Likewise, the Web App can connect to the database,
but only to read that single table.
The second part of that question,
how would we add authentication to this?
That would require a little bit more work.
We'd have to include
some authentication libraries into
the Docker container just to present an AD offspring.
However, since this is all on Azure,
it would be fairly straightforward to make it
AD Authentication and just to hit Boost GraphQL endpoint.
>> Cool, awesome. This has been great Joey,
really interesting way and fun way to just
get started and play with Data API builder.
For folks, maybe getting started
with any of these technologies because
you showed quite a few like any final tips, or tricks?
>> All I can say is I think play around
a lot. It's really important.
I feel like for even
database professionals to get comfortable with
Azure Automation, writing lab PowerShell,
and being able to interact with
the Azure control plane is definitely
something even just as a database
professional as important to have as a skill.
Then something like Logic Apps,
you may not think about it every day,
but it can really help you automate some basic workflows.
Even things like if your head to do database maintenance
across a poor Azure SQL databases,
that can be something that's really easy to do.
Then finally, the Data API tool there is an amazing tool.
Speaking as somebody who doesn't have a lot of
experience building APIs myself.
I'm forward and how quickly I can build it.
It's like three lines of code, or four.
If you count the get clone statement,
and then you're up and running at least on my machine so it's
all really nice solution and
I've really impressed with what the team-built.
>> Awesome, cool, Joey,
thanks so much again for coming on Data Exposed MVP addition.
To our viewers if you like this episode,
go ahead and give it a like,
leave us a comment and let us know what you think.
Try out all these cool technologies.
We'll put some links in the description for you to learn
more and we hope to see you next time on Data Exposed.
[MUSIC]