>> Hey, we're back with
Azure in the enterprise
with Josh Long. Hey
Josh, how are you?
>> Hey, how are you?
>> You are like the
Spring guy in this video.
>> Yeah.
>> Yeah, you are the
spring guy. What are
we going to talk about in this one?
>> Well, let's talk about
Microsoft SQL Server and how to
connect to it from a
Spring application.
Now, I love SQL Server.
As a person that has spent a
lot of time in enterprises,
I really like venerable,
tried, and true pieces of
technology like SQL Server.
I remember a lot of
very positive experiences
with it because
when somebody else was paying for it,
it was really nice as a
developer to be able to run it,
right? I like that.
But, but of course, back then,
running it was very
expensive because you had to buy
the whole thing and all that.
Now, it's just as on-demand,
pay as you go thing in the platform,
and best part, somebody
else is running it, right?
That's a whole team
of people that I used
to remember having to
talk to to negotiate
any kind of interaction
with the database.
No longer do I need that
because the platform run
different, Azure run differently.
That's the really, really
compelling feature here.
Obviously, there's
still value in having
a dedicated DBA who would
understands how to write
queries and all that.
But I just didn't need
so many people involved in
the operations of it anymore,
and that's really compelling.
What I want to do is
I want to talk to it
from my Spring application.
This is not a hard thing to do,
it's just the SQL database.
Spring applications can talk
to SQL databases all day.
What I want to demonstrate is how
easily we can do that
for an instance running
on the platform here.
>> Great, and then
I think at the end,
I'll also tell a little story of
what we've done internally
with SQL that I
think highlights what people
can do when they go
migrate and move to Azure.
>> Okay, I'd love to hear that.
Okay, yeah, don't spoil the surprise.
I wanted to [inaudible].
Let's just build a
simple application.
There's a couple of ways
to provision the instance,
one is to write a script.
I'm using Bash, you can use power
shell. Doesn't matter, right?
Anything will get you
there, so long as you have
the Azure command-line
client on your system.
You can say, "I want to create
a SQL Server instance."
I'm just going to
create that. Then I'm
going to set up the firewall
to allow my client IP,
and then I'll do a SQL
Server database creation,
so I'm going to create that
within the SQL Server instance.
This is a one too many relationship.
I can have one server and
multiple databases within.
I'm configuring using the
password and all this.
This is not my actual password,
but that's the password
you're going to need to talk
to your SQL Server instance.
I've already done all that.
You could also do this
alternatively by going
to SQL databases here
in the Azure portal,
and just click on, "Add" and
it'll give you a Wizard,
Microsoft statements for
their amazing Wizard.
Create a database name and point it
to a SQL Server Instance,
and scale it up.
I can use SQL elastic pool,
and that seems pretty
interesting; what is that?
>> That basically allows
you to stretch and burst.
There's a whole host of stuff
that I'm not deepen on it,
but essentially, it's one of those
things really good if you really
want to take advantage
of Azure's elasticity,
it will move tiers for you to
make sure that your
app is always happy.
>> Nice. Does that scale up as well,
or is that the next thing that
the computing storage support?
>> I'm actually not sure. I'm
more of a Kubernetes Linux guy,
but I used to be a SQL
DBA back in the day,
so I was the one that
maintained all the indexes.
>> That's cool. Either way it
looks like there's some
facilities here where if
I should exceed my
provisioned capacity,
there's some mechanism I
can use to scale up, right?
That's what I think I'm
understanding here.
>> It's cool. It's not a hard limit.
It's one of the nice
things that we do is,
if you've got a runaway query
or if somebody's going
over your limits,
we're not going to just
hammer right on you.
If you're constantly
going over it then,
our advisors may tell you, "Hey,
you should size up."
It's not a hard cap.
>> You can do that. I
can actually just point
and click until I'm scaled up?
>> Yeah. One of the nice
things that we have
here is when you choose a tier,
it's basically an SLA
so it's not like,
sometimes if you're
going to go resize a VM,
or change/take classes of a VM,
it's got to shutdown and
reboot. This is all online.
>> Wow, that's so cool. That is cool.
That's not a thing you
could have easily done,
leaps to my mind 20 years ago when
I first started using SQL Server.
The fact that I can do that
is just sorcery/witchcraft.
I love that. I've got a
SQL database provisioned.
I'm going to go to SQL databases.
It's called the
beautiful-sql-server-db. It's online.
There, I can actually
interact with the database.
If I wanted to, I
can go to the "Query
editor" and I have a username,
I've already provisioned
this, remember?
It's beautiful-admin and I
need to give it a password.
I'm not going to show you my
password because hashtag security,
but I am going to just copy and
paste it into my pasteboard buffer.
Paste it there, logging in,
and so you can see I've got
a view of the database.
I've got my run the database
and I've got a table there.
What I want to do is I want to issue
a select all against that table.
That was nice, convenient for me.
There you go. I've got three records.
What I'd like to do is to build an
app that brings that data down.
I'm going to go to my favorite
place on Internet after production.
I love production.
You should love production.
You should go as early
and often as possible.
Bring the kids, bring the
family. The weather is amazing.
It's the happiest place on Earth.
It's better than Disneyland.
But if you haven't
been to production,
then you can begin your journey there
with startup Spring [inaudible].
What we're going to do
is we're going to use
the Microsoft SQL Server driver.
We're going to use JDBC support.
We could use JPA, anything
that supports JDBC, MyBatis.
Anything that will
use their Java OOQ,
anything would work just fine.
But for our purposes, I think
I'll constrain our choices to
just the JDBC and a slightly
higher level Spring data JPA.
I'm going to bring in
the latest version of
Java that's stable for my purposes.
Azure Spring Cloud, for example,
if you want to run your
application because Java 8 or 11,
Java 14 is the current
supported version of Java,
but it's only for six months.
The current long-term supported
version of Java is Java 11.
We're going to call our
application SQL Server.
When I hit "Generate," and
that'll give us a zip file here.
I'm going to unzip this cd
sql-server mvn install.
Just make sure it downloads
the dependencies and so on.
Looks like it's all there, good.
It failed because, of course,
there's no connection string there.
Let's open this up in our IDE.
I'm going to use IntelliJ,
that's a pretty common choice.
But of course, you could use
Microsoft's amazing open-source
Visual Studio Code,
you could use Eclipse.
Actually, that could be
a whole other topic.
We should have another video on that.
But the Visual Studio Code support,
there's support for building
Spring apps within
Visual Studio code.
That support came from the Eclipse
integration that we had built,
and then extracted out
as a language server.
So now the support that we built for
Eclipse that has served
people for so long,
for decades, is now part
of Visual Studio Code.
It's now part of Emacs, if you want.
You can use it from GitHub's Adam,
I mean, anything, all because of
this amazing language server.
>> Yeah, I think a really
popular thing that I
see right now is people
running it on Windows with Visual
Studio Code and then using WSL2.
Any time I'm working with
Azure, even though I'm
sitting on Windows,
I actually will very routinely
run everything via WSL2.
So I use all of my Linux Glance
from my past days and just go,
I actually open up code from
the Linux prompt and from Bash,
and just do everything that way.
>> That's awesome. WSL2 is
Windows Subsystem for Linux.
>> Yeah.
>> Yeah. Okay. So I've heard
good things. I should try it.
I love Linux. I've got a
dedicated Linux laptop as well.
But yeah, I think I should
try just running Windows
and just going all in.
I've got my instance here,
I've got my database,
I'm going to go to
connection strings area,
sure, discard the data, no problem.
I've got connection strings here
for ADO for .NET, for ODBC,
for plain old Windows applications,
some of these other
non-Java Spring platforms,
nobody knows what they
are, nobody cares.
Let's see. We've got JDBC.
JDBC is for Java. So this
is the Java Database
Connectivity abstraction.
That said, JDBC is a
blocking abstraction.
So when you interact with
the API and you're sitting
there on a thread,
it's going to block until
the bytes have been returned from
the network socket or whatever,
it's going to wait on that thread,
which means that nothing else in
the system can use that thread.
So to map that more to.NET use cases,
we don't have async/await in the JVM.
If you're using languages
on the JVM like Kotlin,
Kotlin has something like that,
it's called a co-routine.
If you're not using
something like co-routines,
but you want non-blocking IO,
then you need to use
something called R2DVC,
which is a different abstraction
for connecting to databases
and it's a non-blocking API.
The point is you can
derive very easily
the R2DVC connection string from
the JDBC connection string.
It's a different API, and
so the connection strings
a little bit different.
But the point is from this,
you have everything you need to able
to write the R2DVC connection.
R2DVC works perfectly with
Spring as well and
Microsoft SQL Server.
There's a SQL server,
there's a reactive,
non-blocking SQL
Server implementation.
So that works nicely for
both traditional blocking and
non-blocking applications.
The connection string has
everything we need
except for the password.
So I'm going to put SQL
Server PW, that's this here.
It in turn is going to reference
that environment variable that I just
used a second ago to
capture my password.
Then I need a user name.
So spring.datasource.username
and the user name
is bootiful-admin, if memory serves.
Then we need the password.
So password equals
sql-server-pw. All right.
So there we go.
That's all we need to
connect our application to
a JDBC centric datasource.
For now, let's just comment out JPA.
So we're using the lowest level.
This is like table stakes for
talking to a SQL database.
It's called the JDBC template
and the JDBC template's
just a very convenient
object that's been
in the Spring framework for 18 years,
basically, since 2002 at least.
The JDBC template is a
thing you can inject.
You can have it injected into the
constructor of your components.
I can create a constructor
manually like this,
or I could just do
@RequiredArgsConstructor
if I were using a lombok,
but I don't have lombok on
the class pack it looks like.
So let's go ahead and add that.
Lombok, it's just a compile-time
annotation processor.
It makes it so that I
can create annotations,
the equivalent of attributes in C#.
I can use annotations
to synthesize getters,
setters, toStrings, hash,
all that kind of stuff.
So NoArgsConstructor. There we go.
Actually, you know what?
I need RequiredArgsConstructor,
there you go,
that'll create a constructor
for that final field.
Then once that's done,
I'm going to make
this a component that listens
for a life-cycle event,
particularly, the
application-ready event.
Public void_ready().
Okay. Once that's done,
I want to make a call to the database
so I'll say this.template.query,
select all from CUSTOMER.
We saw that I could
run the query here.
We saw that it was not hard
to figure out a valid query.
I should've copied and pasted
that while I had the chance.
It's fine, it'll work
either way. So there we go.
I've got this and now I just want
to take each record that comes
back from that row and then
map it into a Java object,
which I'm going to then
accumulate into a collection.
So I'm going to use a RowMapper
and a RowMapper maps,
as you can imagine,
a row to an object,
but I need to give it
a type to map it to,
so I'll create a detail or an
object here called a Customer.
The customer will have an
integer field for the ID,
and it will have a string
field for the name.
We're going to have
getters and setters and
toString and all that stuff here.
Good. There's my basic DTO and
I'm going to map it to customers.
Customer. Customers. Good stuff.
So for each row in the SQL query,
I'm given a result set.
Then for each result set,
I'm going to build up an
object by getting the ID
and the name and then
mapping them to the object.
So now that's a little bit
long in the tooth there,
it's a little bit verbose, so I can
actually turn it into a Lambda.
There we go, there's my
call to the database.
It gives me a collection of
customers and then I could just
visit each one of them by
doing a forEach or whatever.
So for each customer or
print out the customer.
Of course, this can be turned
into a method reference like so.
So there we go.
There's our Java code,
that's the entirety of
the Java application.
I have had to write two lines
to do anything interesting.
Then there's a detail,
this is our domain model.
So let's call that,
what is that, 4 plus
3 is 7, 8, 9, 10.
So 10 lines of interesting code
to get my application
doing something with the
database. Let's try that.
Let's run it and see what we get.
I might have gotten
the table name wrong.
Is it Customer or Customers?
We'll figure it out in a
second, one way or other.
It looks like it was
probably Customers.
It says, invalid
object named customer.
That's a clue. All
right. There we go.
That worked. There's our data.
Of course, that is
the long way around.
If you wanted to do
everything the hard way,
for a lot of people,
they're going to be using an ORM,
an object-relational mapper.
I could use something like JPA here.
JPA, it's a bit heavier.
But if you want the
things that it does,
then you can use that with ease.
You just create a repository.
Here, I'm going to say
it's a CustomerRepository,
extends the JpaRepository,
whose types are type customer,
whose primary keys
are of type integer.
With that done, I'm going to inject,
not the JPA, but the
repository itself.
Then for that, I can just do
customerRepository.findAll.forEach,
System.out, print line.
That's the updated code.
The other thing I need to do is
to map this to a primary key.
So there we go.
Let's run this and see what
we get. It's going to fail.
I forgot to do entity. Now
it should work. Take 2.
Invalid object name. The table.
I have to give it a table name.
It's customers, name equals
customers or CUSTOMERS just
to be sure, and voila.
>> Now, some mappers, if you
would name the class customers,
auto would map that by guessing?
>> Yeah. Exactly. It
would have done that.
If it was greenfield,
I could have just done
the right thing and named
the table customer.
That would have been fine.
But whatever, it worked.
So there you go. You can see we got
the same records using
this low-level repository.
Now, I can do many relationships,
one-to-many, bidirectional,
all that kind of stuff without
writing a single line of SQL code,
if I want to avoid it
for the simple stuff.
>> Yeah. So this is
awesome. Really easy.
You would start the spring.io,
boom, you get all this
stuff up and running.
I think the other nice thing here is,
especially, SQL in itself.
For a lot of customers
where you're running
SQL on-prem or you're
migrating it to SQL Azure,
one of the things that I really
like to highlight is just
the fact that you can have
automatic index tuning on SQL.
>> What's that?
>> This is something
that you can do today in
SQL on-prem or you could do
in SQL on running on ISVMs.
But our manage SQL does this as well.
So it will drop and
create indexes for you.
So I know especially
if you're talking
to your DBA team and
you say, "Hey, look,
your production databases
when you move into Azure,
or just if you are going
to keep them on-prem,
why don't you just flip
this a little bit on
your production databases
that will go and
create and drop indexes
because SQL is smart,
and just let it do
it," especially DBAs.
I remember running
production databases
for customers and running
manufacturing lines,
and it's like you don't
touch the database.
That's just one thing you never do.
Especially, a lot of
people that are moving
to SQL and are doing greenfield,
you don't necessarily know how
your databases are
being used nowadays.
So we did a case study internally
of one group inside of Microsoft
where they were running 348
servers with 830 databases.
They had moved those,
all the SQL paths,
Azure SQL database.
One other things that
we did for them was,
we didn't tell them
we were doing this.
Probably not a great
sysadmin thing to be doing.
But we turned on automatic index
tuning across 330 databases.
We didn't actually know which
of those were prod or non-prod.
We knew a good chunk of them,
probably half or a little bit less
more prob, but we just did it.
After a couple of
weeks, we started to
see the results come in
and people started to freak
out because they were like,
"Hey, my data is gone because
things are running a lot
faster than they should.
Did I lose things?"
We collected the data
after about a month.
Across those 830 databases,
we created 2,200 indexes,
and we dropped 5,700 indexes,
and we reverted 97 of
those changes because,
let me be clear, SQL
automatically reverts it.
So if you just turn this on,
it'll just say, "Look,
I'm going to go change your indexes,
drop or create, or just
manage them for you."
So everything runs faster.
We found over those 830,
after about a month,
we had dropped 263 billion reads
per day off those 830 databases.
A, maybe there was a bad
query in there or B,
frankly, when people
build stuff in SQL
and it sits around for 2, 3,
5, 10 years access patterns change,
DBAs change,
how the application is being used
is changed, the schema changes.
>> Yeah.
>> So I think this is
just one of those things
where even in production,
just the default, if
you're using SQL Azure,
go into your ARM templates,
set an Azure policy,
the default should be automatic
index tuning is just on.
The nice thing here is,
it'll go and create indexes.
Yeah. It uses more
storage on the server,
but we don't charge you anymore
because we keep you within
your same SQL tier
that you saw earlier.
But because your database will
now get so much more performance,
you can typically ratchet down
the tier that you're paying for.
Yeah. It's not great for
our field that's getting
paid for SQL Azure consumption.
It's super good for customers.
So you can go save
yourself a bunch of money,
have your database
perform a lot better just
by trusting the statistics in them,
AI and ML behind it.
>> Yeah. That's a
perfect example of why
you want something
like a hosted platform
to run your database,
because it can do more.
It has more insight. It
has more visibility,
and there's a whole team that's
dedicated to finding winds like
this and just applying it
for you on your behalf.
Suppose this didn't exist.
It's just nice to
know that Microsoft's
working on it and that it
will exist eventually.
It's one less thing for me to
figure out even if I ever had
time to figure out
in the first place.
That's awesome. That's
simple, billions of reads.
>> It frees DBAs to go do more
meaningful work. It's freeing.
It frees devs to go work on
meaningful work as opposed
to everything else that
they have to deal with.
>> Right. We love that,
and gets you to production faster.
>> It saves your kids
and your family, right?
>> Right. Exactly. That is so cool.
>> Well, thanks, Josh. I think we've
got a whole bunch more
that we're going to be
doing with some other
Java people at Microsoft.
>> Yeah.
>> Thanks a lot, and
we'll see you again soon.
>> Thanks.
