SQL Server 2019 | Query faster from any database. (Microsoft Ignite)

ArticlesBlog

Written by:


(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)

One Reply to “SQL Server 2019 | Query faster from any database. (Microsoft Ignite)”

  1. Diego Stefanon says:

    Sql Server 2019 Is fantastic!
    Top features fantastic

Leave a Reply

Your email address will not be published. Required fields are marked *