(logo sliding)
(upbeat music)
- Welcome to Microsoft Mechanics Live.
(audience clapping) Hoo.
Coming up, we take a closer look SQL Server 2019,
now generally available.
We'll look at its expanded role
as a data integration platform
allowing you to bring in data from any source
including on the Edge for analysis.
In fact, we'll show you how data location
becomes irrelevant with a scenario that goes
from the ground to the cloud,
and introduce you to Azure SQL Database Edge.
So, I'm please to be joined today by Buck Woody,
an applied data scientist from the SQL engineering team.
Welcome!
Thanks for having me on the show.
- It's good to have you.
Now, congratulations on the milestone.
We've come full circle since last time around.
When Travis Wright on the show giving us an overview.
So tell us now, we're generally available...
- That's right, it's a huge milestone.
And one of the big improvements
we've delivered on is increasing the shift
from SQL Server being a relational database engine only
to a full data integration platform
where you can access your data without
always having to import it.
And what this does for us, it removes database silos
and it makes it easier to manage and query data at scale.
And it kind of makes SQL Server a data hub.
We've also got a new architecture designed
to give you huge improvements in performance
through caching and parallel processing
as well as elastic scale compute and storage.
Now, this architecture deeply integrates SQL Server's
parallel query engine with big data components
like Spark and HDFS and these are all included
in the box with SQL Server.
And it gives you a single architecture, right,
so it's is easy to deploy and to manage
and it's fully supported
by Microsoft. - Nice.
- Now, even though the engine has several deployment targets
it's the same engine across all of them.
You can deploy to containers
using Container Orchestration systems like Kubernetes
and OpenShift.
And of course we still support bare metal installations
for Linux and Windows,
and now as you pointed out, even ARM-based devices.
This gives us possibilities for
high availability and scale.
And so it's really all about choice,
from the ground and the edge to the cloud.
- Awesome.
Now, I know a lot of people are coming from older versions,
so we'll get a sense from the audience here,
so a little bit of an ad hoc survey.
- [Buck] Yeah.
How many people are running SQL Service 2017?
- [Buck] 2017?
Can you raise your hands? - Show of hands?
- Ah, nice. - Okay, good, congratulations.
What about 2016?
- Okay, getting a little older.
- Okay, a few more hands - okay, that's fine
Yep - okay, okay.
2012? - Uh oh.
Gettin' a little older.
- [Matt] All right, okay.
- [Buck] No, no, don't
- [Matt] 2008?
- [Buck] Oh, oh please.
Oh no.
- [Matt] Older than that?
Older than that, oh wow.
Okay.
- Ah, you're killin' me - Let's make this real
for people then.
So, the changes, what changed for people
and why should people upgrade?
- Yeah, so as a data professional,
for me the first thing I think about is the security
and safety of my platform.
That's the first thing I focus on.
And we call this Mission Critical Security.
Now in addition to general surface updates
on the security, we've made some specific improvements
that will help you secure your data and applications.
So, let me show you
- That'd be great. - sort of a couple of these.
So basically here you can see we can now add classifications
to our data.
And you can do that right inside Transact-SQL.
We've even got a wizard here, right?
So this thing went through and found some things
that I might want to think about for classifications.
And it's found three that it says, look I've found
you've got payment methods and you've got email addresses
and so on.
So I could click here and I could accept these,
or I can add my own classifications and come up
with whatever I want.
I get a information type and a sensitivity level
and then all we have to do once we're done with that is
just to run a report and here's my classification report.
This is ready for the audit.
Now something that's very important to note here
is that I can view all this alongside,
because this is stored right inline with the meta data
inside SQL Server now, so I can run my SQL Server Audit
functions and get the same kind of things.
And get those automated auditing type things done.
- Excellent. - Yeah.
- So are there other new security improvements
we should make people aware of?
- There really are.
There's a new hardware protocol, a chip actually
that's being included with a lot of systems,
even some virtualized hardware,
and it's called Secure Enclaves.
It's for data encryption and decryption on a chip.
And SQL Server 2019 is designed to use these new chips
when they're on the system.
- Cool, so, we know security is always incredibly important,
but for SQL Server so is performance.
So, what are we doing to make SQL Server 2019 even faster?
- Yeah, so we're always looking for ways
to improve SQL Server performance.
But most of the time, you've got
to alter your code to get the new advantages
inside that feature for a performance increase.
So what we wanted to do was be able to increase performance
but you don't have to change your code.
By simply upgrading to the new version,
you get an immediate performance boost.
Now, we call this set of improvements
Intelligent Query Processing.
Can I show you one quick example?
- [Matt] I'd love to see it, yeah.
- [Buck] Okay, let me show you this.
Let me just pop over here to Management Studio.
So what I've done here is I've created a stored procedure
with a table variable.
And that's great, that's good,
but the processor under SQL Server 2016,
I've got that set here, can't quite figure out
the right plan to get.
So when I run this, I get a different kind of plan.
But I can simple change the compatibility level
and see, I've got exactly the same code here,
exactly the same code.
And when I run this, let me show you what it does.
When I run it under the old model, 2016,
you could see here that the plan's kinda high,
and it can't quite figure out, can't guess the number
of rows because it's a table variable
- Right. - that it's trying to do.
But simply by changing the compatibility mode
you can see now that it gets much, much faster
because it can figure out the rows
and it also does some other things inside the query plan.
So the output is basically this.
The query processing engine is more accurate
about the number of rows and the things it needs to do.
And the query just speeds up.
- Awesome.
Now, we've seen quite a few updates this week
for developers and for hybrid with Azure Data Studio
- Yep. - really at the center
of all of that both for developers and admins.
What's new in ADS for SQL Server 2019?
- Okay, that's great, and we'll get to that in a moment.
But, before we get to the new tools
we have made improvements
inside SQL Server Management Studio
to take advantage of these things inside SQL Server 2019.
So that tool's still around.
But as you said there's a completely new
cross-platform developer environment
that works on SQL Server in all its deployment models
and Windows, and Linux and Apple and so on.
It's called Azure Data Studio.
Here, let me kind of show you this.
So you can see here, it's just a tool I can run inside
and hit my clusters and so on.
And if you'll look here we've also actually included
Jupyter Notebooks, if you're familiar with those.
And you can run text and queries and so on.
But something you may not recognize here,
we've actually got a SQL kernel running
in a Jupyter Notebook.
In fact we've got a PowerShell kernel as well,
but we've got a SQL kernel running in a Jupyter Notebook,
and that's a first that we've done there.
And we've also got the tools running on multiple platforms
but we also have the engine running
on multiple new platforms.
For instance, we now have SQL Server running
on Azure SQL Edge, which means you can run the engine
on ARM, like small IOT devices.
Now, so perhaps there's a scenario, right
where you don't have a classic server rack,
but you need to run a connected SQL Database.
So let's think about this.
Imagine if we're running a shipping company, right
and we've got refrigerated trucks in our fleet.
And we know that batteries can fail
and that causes refrigeration to fail, right?
- [Matt] Yep, okay.
So how do we solve for that with SQL?
- Yeah, so normally what we need to do is
something called preventative maintenance,
we replace the batteries every six months or whatever.
But what we really want to do is predictive maintenance
where we replace the battery when we need to,
based on actual data.
So if you'll look inside my Azure Data Studio here,
you'll see I've actually got a Jupyter Notebook
that includes everything I need to do this.
I bring in some data, I've started a Spark application
I'm using the Azure Data Clusters inside here
and I'm reading in from the truck,
the data that it streamed up.
So I know all the features and the labels that I need
and now I can train those
and I can do a little feature engineering
all right inside this Notebook.
And then what I can do is I can create a model
to do the predictions.
I can then test the model.
And then once I'm all done,
I can actually package this up, this model
in a container to deploy out to the trucks.
- [Matt] Okay, so how do we get it running on the truck?
- Okay, you want to run it on the truck.
- Yes. - Okay.
So what we do is we push the container
with the model down to the truck,
and it's got an IoT ARM device running SQL Edge
and it actually does the predictions.
Now here's what can happen.
The truck can inform the driver in real-time
before the battery is predicted to fail.
It can map the location of the truck
and the service center to make sure the parts are shipped.
The service team can be scheduled
to replace it at that location.
- [Matt] Okay, so what does the prediction
actually look like? - Well here, I'll show you.
Let's go back to the Notebook here.
We scroll down and you can see some Transact-SQL.
Now notice it can run wherever Transact-SQL can run
and I'm simply going to query right out of the truck.
I'm gonna get the data I need to do the prediction
and then here we are, I get the truck sensor reading
right back at the truck.
So, that triggers the workflow
to map the location of the truck and the service center,
find the part, schedule the work, notify the driver.
Isn't that cool?
That's awesome.
And that is essentially a full
end to end ground - Yeah, yeah.
to cloud scenario and you didn't have to
actually move the data.
- No, no, you store it right where it is.
- And the Edge scenario we talked about.
Also a great addition.
Now, I know a lot of people watching are probably thinking
"Well, will my existing apps will continue to work?"
- Yeah. - So what,
if anything, changes for them?
- Yeah, we're always careful about compatibility
in SQL Server.
We know you've got a lot of code out there.
Most of the time your application doesn't require
a lot of code changes.
When we do depreciate a feature, it stays
in the next few versions to give you time
to alter your code.
Most everything we do here is additive, right?
Not something we take away.
So, you don't need to necessarily change what you are doing.
Now, going forward, our idea is to certify
on the compatibility level, not the version.
Something you can set like you see I did that right here.
I set the compatibility level here
and then you can upgrade the server version
with the knowledge
that your application's just kinda gonna work.
- Right, and that's at the database level.
- That's right, that's right.
Now another advantage, and I'll show you that here,
is that the Transact-SQL statements
you write for one platform are gonna work on another.
And basically I showed you this kinda earlier.
The general idea is that the code written
for Azure SQL Database Edge will work on SQL Server 2019
for Windows, Linux, in Containers and Kubernetes,
and Azure SQL, 'cause it's the same engine.
So you've got peace-of-mind
that we're doing everything we can
to make SQL Server the most durable, capable platform
for your data, regardless of sort of the shape or size.
- Okay, so just to reiterate and make it clear,
the code that I write is completely transferable
across the different platforms.
- Yeah, in large part that's true,
yeah. - Awesome.
Well, thank you so much for joining us today, Buck,
we've learned a lot.
I'm sure everyone out there is appreciative of that as well.
Where can people learn a little bit more?
- We've got a ton of good documentation
at Microsoft.com/SQL, and we've got full workshops
and example code and all kinds of goodness over
at aka.ms/sqlworkshops.
- Excellent.
Great to see all the updates this week for SQL Server
and all the things we're doing with data.
Remember to keep watching Microsoft Mechanics,
hit subscribe if you haven't already
and we'll see you next time.
Thanks for watching!
(upbeat music)