(upbeat music) Hello, and welcome, my name is Sanjay Soni, here's a microlearning, readiness video. To kick off, let me
welcome Bob to the studio. Hi Bob. Hey, what's going on. Gosh, what a packed agenda. (laughing) Of course.
Can we do that today? Yes, it's good to be back, thank you. Great to have you here again, so please, Bob, let's start
with quick interactions again. Sure
What do you do at Microsoft? Yeah, this is almost my 26th year at Microsoft, Sanjay, how crazy is that? All working a SQL server,
I'm in an engineering team, I'm an architect engineering team, and spending a lot of my time
lately talking about 2019. So, I'm excited to be here today. Great, so let's start with our first item. Could you please talk more about the value of SQL server 2019. Yeah, Sanjay, look at this thing. Doesn't this look a
little familiar to you? It looks a little bit like the SQL 17 slide we showed yesterday, and the about the real
value of what SQL 17 is about, but it's different, and it's a big different. And if you look at the
left-hand side here, intelligence over any data, that's a big part of what Buck and Anne are gonna drill into. SANJAY: I see. BOB: Imagine a world where SQL server now, is talking to Oracle and
MongoDB and Cosmos DB, Hadoop with Spark built-in, an HDFS. I mean for folks, who
work with SQL for a while, that just seems like very
foreign things to them, right? SANJAY: Yes. BOB: But you're gonna see with Buck and Anne talk about it, that it's very much
part of SQL server now, and so that's one of the biggest things that we're doing that release in SQL 19. You also look at this
slide from left to right, I mentioned yesterday, our
platform of choice, right? Remember we honed on the fact that SQL Server now runs
analytics in containers but it's not just about running only in this environment, it's about a platform of choice, still
with Windows Server. We're going to start mixing in
more about kubernetes today. We've mentioned just a
little about that yesterday but we're going to dive a
lot more in on that today. SANJAY: Great. BOB: So that's kind of the
platform of choice message. The value of SQL still is
about performance and security. We kept honing in on meat
and potatoes yesterday and the heart of SQL Server. I'm going to talk a
little bit more about the 19 capabilities, specific in that area. But people need to
understand that we still take pride in the fact
that we're the fastest database on the planet and we're the most secure database in the
industry, for the last decade. BI is still a big part of SQL's server so Power BI report server is still a part of what you get with SQL
Server and it's still a functional thing that works with connecting to the engine. As I said yesterday in-memory
workloads, being across public and private cloud platforms and the consistency of T-SQL, you're going to hear a lot more about that today. We're going to use the T-SQL language. As that consistent surface area for you. So when you think about SQL '19 today. We talked a little bit
with Debbie yesterday. Remember that SQL server
in as your database in managed instance and
SQL server is still the same engine running
across these platforms. So as you think about the value of '19 some of the things I'm
going to talk about today exist in both platforms. You can just go and use
them either as your database or in SQL '19 themselves. So it's an amazing story. Probably what I should do
more is focus on the core of what database is today with SQL '19. Absolutely, so let's get
started with more details. Yeah so before I go into
performance and security and things like availability, you know, we talked about Linux a lot yesterday. We spent, I think, an hour
going through that, right? And the reason is that
a lot of people still don't understand exactly what we're doing with SQL on Linux, and
the main message is still, platform of your choice
with database compatibility. But I alluded to a little bit yesterday, there are a few
features on the edge of the engine like replication,
distributed transaction services and machine
learning services that we didn't do in SQL '17. You didn't get those
features in SQL on Linux. And now with SQL '19, already
in our preview releases, you can use that functionality. So that's good. So now, customers, I don't
want them to feel like I cannot make a move to SQL on Linux because I have something
in Windows that I don't get with Linux right? So we're trying to make
sure we did that right. We have other things we're doing, like open LDAP support to make the active directory configuration
experience easier. I mentioned yesterday, containers! It's really interesting, Sanjay, you're going to see containers
used a lot throughout the discussion and Buck and Anna And we have the new container
registry, we'll have Red Hat Container Image or CTP
2.1 and 2.2 and 2.3 for '19. And then availability
groups on kubernetes. And I'll mention a little bit about that again today so
what I'll make sure you understand, if you look at
this slide from yesterday, the one key PIIece added
to this besides our distributor platforms
from Linux is Kubernetes. SANJAY: Yeah And containers and
Kubernetes will be a big part of the foundation of
what we're doing in '19. Awesome, so can you please
talk a little bit more of what the machine critical
performance, security and-- Yeah I have to do that
every time right, don't I? Remember, Sanjay, the
message yesterday was if that we don't enhance
and keep innovative, the performance, security
and availability of SQL engine, we don't have a product. We build so many great things on this platform that surrounds the product but we have to
continue enhancing these core things and, quite
frankly, our customers expect us to do it so yeah let's dive into it a little bit, let's
talk about performance. Yesterday I mentioned a feature called Adaptive
Query Processing. Of course. Right, you remember that? That was a suite of features where we have taken the Query
Processor itself and made sure we can adapt to any workload you provide to us, with
no application changes. That was a theme we talked about a lot. We don't want to have to make app changes, make the QP better for the
workload you're giving to us. If you look at this chart right here, the things that are not bolded is what you got in SQL '17, for adaptive QP. We've added all these
things that are in bold now and so we've kind of rebranded this as an intelligent Query Processing. So in '19 you get all
of this functionality. Let's just PIIck on one, for example. Memory Grant Feedback, we
talked about yesterday. This is where you run a Query in SQL server, we
detect the fact that you didn't have this proper
memory grant thing, you're tired of tuning into bugging that. We recognized that and
for the next execution we make it better. Well we only did that
for batch-mode queries in SQL '17, for column,
store based queries. And now we do it for any queries. Any query that's based there. There's a whole slew of other features in this family that we do now. And again the key is just change the next compatibility level for
SQL server '19 and you just get these features and
you don't make any app changes. SANJAY: I see. BOB: It's pretty good stuff right? SANJAY: Oh yeah, of course. Well we want to make
QP tuning when you need to do it very easy and
in fact we have this concept that we want you
to have insights at a very detailed level. Anywhere, anytime you need it. So there's a new feature called
Lightweight Query Profiling. Imagine you walk into a SQL server. It's got a query that's
taking a lot of CPU utilization and you think
should I kill this thing? But maybe it's powering my VP's report that he's got at the desk running but you don't know to
kill it until it finishes. You're not sure and you
don't want to abort it right? Well one way you could
actually get insights to decide that is to actually
go in with this new feature and see the plan detailed at operator levels of what it's doing
live for any executing query. You can do that today
for a query that finishes with SQL but now you can do it live. SANJAY: Oh wow, why the query is running? While the query is running.
SANJAY: Awesome. We have tools from a graphical perspective to see that or you can
use our dynamic management views to see the same thing. Now you can look and say
oh this plan is so bad. It's going to take like 3 hours to run. I'm going to kill it and go apologize to my VP that will fix the
problem that caused that. So again, anytime, anywhere insights to your query profiling. That's pretty cool. Awesome. We're a big believer in making
sure that we're innovative. We stay ahead of hardware
trends and technology. There's something called
persistent memory, that's been out there in
the industry a little bit. It's staring to become very popular. So we just took a look at that and say hey these persistent
memory devices can give us really accelerated
IO with SQL server. So we're taking advantage of it. We're actually having new technologies we've built in SQL '19 to detect a persistent memory device and either bypass the IO stack of their Kernel to make IO very fast or use a concept called hybrid buffer pool where we just directly map buffer pool pages onto one of these
things because there's just memory but they're
a special memory that's powered in a way that they survive through a power restart. So how's this processing
very different from the cache that we are all used to. Yeah so you know you normally have RAM, which is just your cache, right? This is actually a
device that kind of looks like cache but it's like a drive. SANJAY: Okay But SQL can treat it like a memory card and actually copy
memory back and forth at a very accelerated rate. SANJAY: Awesome. So just an example of us
staying ahead of what's going on on a hardware perspective
and trying to make IO a much more performative experience for the average user. So, Sanjay, you've heard of tempdb SANJAY: For ages, 15 years now. For ages. I was in support for many years and I know it and it's a pain point actually. It's a great feature of SQL
to use for many different reasons but it can become
a pain point from a troubleshooting perspective
and you have this idea called latch contention that can occur and so we saw in many cases over the years by having you spread out
your files across SQL server. So we something called a Tempdb
it just runs, that's faster. That's my name that I created and I'm going to show a
demonstration at the end about what that is. So I'll kind of tease you and
tell you I'm going to show what that's all about. Yes. That's something that is
not in the current CTPs and is something we're
working on though for SQL '19. Okay.
That's a pretty cool set of performance features in my oPIInion. Great. And it's all new to '19 and, besides the tempdb one in bold there, it's all available today for you
to use at our current CTP builds Alright, so how about security? You know we have to keep innovative on security and we built a feature called Always Encrypted in SQL '16 and '17 and we determined, by talking to people that were using this functionality that there was a queue of
limitations for Always Encrypted that we needed to solve. Along comes this technology
called secure enclaves, which can be run at a virtualization level or in a hardware chip
itself and it actually gives us a new way of doing Always Encrypted that's
more performant and allows us to light up things
like rich computing, which are queries that are not just
like seeks to the exact rows. So look for that. It's already available now in
CTPs and I think it's going to actually allow people who
have not been able to use Always Encrypted now to start taking a look at that technology. I was just in Europe, last week, Sanjay and GDPR is a big deal
to everybody in Europe. Which, I get it, right? They should be. SANJAY: It is. So we have new functionality
and data classification. Previous in SQL '17 are
tools that allow you to classify your data from a
management studio perspective but it was a tool solution. Now we've built into SQL the
ability for you to add your own classification labeling system and here's the reason that that's important that there's T-SQL. It's a surface area now
for programmers to use plus you get built in auditing. So you can then classify your data as GDPR from a column
perspective and then you can audit who, when and
what touched that GDPR data. That's going to ease the burden for you to figure out, you know, am I complaint with GDPR type systems? and it's still applicable
to folks that are not having to adhere to GDPR.
And you can do this to the scripts when you're in the studio? Yeah, here's the cool story, so you know, management
studio was a tool solution now classification's a
T-SQL solution and we're going to bring those worlds together. So you can use the script version or you can use the management studio to connect to use this new system. The last thing is interesting, static data masking, that I want to mention. That is a tool based
solution today but there are scenarios where you want
to actually scrub your data. You want to mask
permanently in the database emails or PII information,
things of that nature. We now have a feature
called Static Data Masking with management studio. It actually makes a copy of your data and it masks
data you want based on rules and it's a pretty cool experience because the tool gives you wizards to give you suggestions on how
to mask email addresses and social security numbers
and things of that nature. So that's Static Data
Masking and it's a tool that works with SQL
server '19 and I should say with earlier versions as well. So you can use it on PII type of-- Yeah that's the whole
idea that you want to take a copy of your
database and give it to developers or your
testers but you can't let them see the actual PII
data so pretty cool. Alright, how about availability? Of course, we've got to
talk about availability. We continued down the journey of making resumable online indexing a thing. We've actually done online
indexing for a while now Yeah of course. And so we're just kind of finishing up that feature suite now so we had resumable online rebuild in SQL
'17 and now you can do resumable online index creation. In addition you can do online clustered column store index rebuild and creation. That's a big request our customers have asked for and so that's now in SQL '19. We've also enhanced availability groups. Our flagship feature
for HADR and the product including things like
allowing five sync replicas and a concept called primary
connection redirection allowing you to make your apps insure you're always
connected to your primary. But one of the things I
showed you yesterday was availability groups for Kubernetes. Kubernetes being a platform that provides built-in availability type constructs and so I kind of just
briefly walked through the scenario where in Kubernetes we would automate the deployment
Ags and then use the Kubernetes infrastructure
to redirect your primary app and your reporting
app into this, you know, system and so Kubernetes
is an amazing platform and you're going to see
a lot more this afternoon about how we're taking
advantage of Kubernetes in new and exciting ways.
This is the a way for us to take the core engine with availability groups and make sure that
we can use the built-in capabilities of Kubernetes
and then automate the deployment even of these
systems with a concept called an operator. It's kind of a brave new world for folks that use SQL server on Windows for a while and they're like what is this thing? And so Kubernetes is becoming
a very popular platform in public and private clouds
and we're kind of making a bet that that's a
great platform for us to go develop things like high availability. I see, so now the next question is what about the developers? Yeah so, you know, we have to take care of developers, you know
what, actually, Sanjay I'm just too quick for
you said that it's an available thing and I
forgot to mention two important features of availability! I was just so excited about Kubernetes-- Alright alright, we
have time, we have time. We have time, so this is something we just announced in CTP
2.3 for '19 at SQLBits. It's a feature called accelerated database recovery and it exists also in Azure. So imagine a world, get ready for this, you go delete a million rows
in a table in a transaction, SANJAY: Okay. Okay, you did it and you haven't rolled it back yet okay and you're like uh oh I've got to kill this query. I've got to roll it back
and I know rollback takes as long or longer than that delete. Imagine a world where
the rollback comes back with the snap of your fingers. Imagine a world where you
have really long transactions that don't cause the log to
grow and you can truncate it. Imagine a world where even for huge long transactions,
active transactions, recovery actually comes up in a
matter of minutes now. That is what this feature is. It was actually code
named Constantine recovery and the vision for us is
that your recovery now, you shouldn't worry about recovery. It's just a constant time for you. That's amazing. For people that have used SQL for a while, they
know that those kind of tight pain points are
very very problematic for them and when you use a
concept called persistent version store to make
that happen, you're going to hear a lot more about
this technology as we move on into '19. Then another pain point for availability groups, for some customers
is system databases so you have agent jobs and
log ins and things that are not part of your database
availability group but you want that on your secondaries. So we're working on that right
now. It's not available yet on including availability
groups on your system databases like master, model and so forth. SANJAY: I see. BOB: That's pretty cool, that's coming. SANJAY: Thank you BOB: So this is packing this in right? Performance, security, availabilities. Feature after feature,
again this is us making sure we enhance the core of
the engine of SQL server. I see, thank you so much. But you asked about developers. Of course. We don't want to leave them out. Lots of developers in
the audience I'm sure. Here's a note for the audience right. So graph, we talked about graph yesterday. We continued to iterate on graphing and
you can go look into our documentation of just
features we keep adding on top of that but as I, again, remind you the concept is, don't
buy a third party product anymore that specializes in
graph traversals of queries. Use SQL server. We've extended the T-SQL language so that you can model a graph
type data model, put it in relational systems but not
have to write these massive recursive TT queries to get to the data. So that's graph and we
continue to iterate on that. SANJAY: I see. UTF8 support has been a
big request for many times. our unicode type data
types, you can literally put in these new type supports
for UTF8 and save like fifty percent of the
space in your database so UTF8 support is there
and we're continuing to iterate as we move along
the CTPS on making sure that it's a fully functioning
feature in a product. You remember the stigar yesterday? SANJAY: Yes. Okay, this was the architecture of us running armed Python with SQL server and I know we're going to get into that. I saw the agenda. We're going to get more into
that with Anna later on. SANJAY: Yes. So those are great
technologies but we want to cut around them and offer
you more choices and to extend the T-SQL language,
not just for machine learning. So along comes Java. So now you can do Java in
SQL '19 and it uses the same architecture that I've
talked about that's secure, uses resource governors,
runs in the same computer as SQL server but not in the
SQL server process space so that's coming. That's a big deal and I don't want to steal Anna's thunder. She's got some amazing
demos to talk about that. SANJAY: That's fantastic, thank you so, And also you mentioned
yesterday that the graph capabilities that you have,
you only have to write a few lines of T-SQL code. Yeah we have the product,
it's called match, you know, that are just
something built into the T-SQL language so you can still
keep your T-SQL skills. We have T-SQL syntax for note
and edge tables so you add that to the mix and now you
can start traversing your graph with T-SQL queries
just like you might with a third party product but
you don't have to buy one. You don't have to entail
your data to that product you just use the in SQL
server engine itself SANJAY: Fantastic, alright, It's also in azure by the way. So that's still a scenario
in both the public and private cloud offerings for people. Great, so I know we talked
about availabilities and mission critical
and all the good stuff. Now can we see a live demo? I've got to tell you, you're probably wondering is Bob going
to show anything here? so let me flip over here
and show you a machine I have right here. So I have a temp database
stress type scenario and Connor Cunningham, our
leader architect in SQL server and I were talking about a
problem I had so I'm going to kick off this work load here
that I've got, Tempstress. And here I'm back to perf
mod, I told you right? SANJAY: Yes. BOB: I'm monitoring perf mod batch request pers second and latch weight which is this common bottle-neck you see
with SQL server tempdb. Now look at this chart! The green is the batch requests. That's the through-put. The red is the latch weights. That's not good. It's like 400,000 per second. I know. And processor time is low. This is a massive machine. So my work load doesn't scale
very well and the reason is is this bottle-neck and now I can see that with this new capability
in SQL '19 where I can look into a page header. You too can be Paul Randall. Paul Randall knows about internal pages. You can run queries
now that are documented and supported and you'll notice here that the object
that belongs to these pages is something that, do you
know what that is, Sanjay? You know what is says? SANJAY: Well I remember those
when I was a DVer before BOB: It's like a system table. SANJAY: Yeah yeah. BOB: No one knows what this is. We don't document what that table is. SANJAY: Right right. BOB: Now there's no latch
hint in SQL server so there's no way around this
and people would call support. I would say you just have to reduce your work load in tempdb. That's your solution. You have no way around it because in this case
I've spread out a bunch of files in SQL server so I
don't have Gam or Sgam or PFS allocation problems or
bottle-necks or page latch weights. It's all in the system table and in fact if you go over here and
see it took like 48 seconds to run this so Connor our
architect says Bob I have a fix for you. So I literally call this
the start SQL Connor fix. So I'm going to restart SQL server with a new configuration, something
we're working on, right? I'm going to run that same workload. SANJAY: Yes. BOB: And when I run this work-load what I expect to happen is I expect to see that red line like not
exist and sure enough when I run this look at the
work load changes in the graph. The batch quests per second
are almost two times as fast. Processor time is up
high because I'm pushing the processor harder now. I don't have the bottle-neck. Look at the red line, zip, zero. Almost no latch weight. Imagine a world in tempdb where there are no more latch weights. People watching this live now are doing cartwheels, jumping up and down now. No more latch weights! It's crazy right? SANJAY: It is exciting. BOB: Yeah, so this is
what we're working on. It's not available yet but
we're actually going to release this in SQL '19 and
it's kind of fun stuff to talk about and by the way,
the crew is already done. It was, what, 48 seconds
before, now it's 22 seconds. SANJAY: So great, awesome. BOB: By the way no app changes. I just restarted this SQL
server and I'm good to go. That's a great story. SANJAY: So the app will
currently connect to this? BOB: Yeah the app didn't change. I just ran the same work load and just restarted the SQL and SQL did it all for you. That's the idea. That's the concept. SANJAY: Awesome. BOB: Pretty cool. Yes, awesome, so do you
want to show any more? Do you have any more demo to show? I didn't tell you I was
going to do another one so why not? Yeah we have time, lets do a stretch demo. OK, we'll just try this, okay so I have a database here and
we've got this new feature called accelerated database
recovery so let me run this and I'm going to run a
transaction and I'm going to delete a bunch of rows on this table. Now here I go off and
delete a bunch of rows and it's going to take
about 30 seconds to do this. What's happening. I'm logging all these
deletes in SQL server. It's in an active transaction, correct, and so this is normal. You delete a bunch of rows
and you've got to log this. What if you tried to kill
this right now, Sanjay? What do you think would happen? You try to kill this active running query. What do we have to do to this query? You didn't realize I was
going to test you? (laughing) SANJAY: No it's okay, I
remember the kill command. BOB: You can kill it but
you'd have to roll it back. SANJAY: Yes. BOB: You'd have to be consistent right? SANJAY: Yes yes. BOB: Well as it turns out
in the current logging scenario, without accelerated
database recovery, we have to go log something
called a compensation record. We gotta go log the undo of all of this. So it's a bunch of logging
in there and it's active. So a couple of problems. One, it finished but it's active. I can't truncate the log at this point because it's active even if I checkpoint the database and
if I try to roll it back here, which I will, it's going
to take another 30 seconds. So at this point forty
percent of my log is used, I can't truncate it, I've
got this active transaction. Now, one, I've got this rolling
back, I can now truncate the log right? But this is a pain. Now look at all the time I'm spending in this scenario. Maybe it was just a mistake
and in an app that I had to do. It was a problem right? So when this is done in
thirty seconds, I'm going to turn on accelerated database recovery. The delete is going to take the same time because you still have to delete a bunch of rows but the difference
is that I'm going to use versioning and SQL server. We have a new thing called versioning. Not in tempdb but in
the database to actually go back and to record the
deletes and so this finished. Okay, and I'm just going to
go and show you the fact that the log can get truncated,
I'll do that because again, this is the normal
scenario with SQL server. Lets go turn on this new
feature with ultra database. I'll do that and I'm going
to run this same delete and again, so here's the difference now. I'm still having to log the deletes. No different. The difference is in the SQL
server itself I'm recording versions of the deletes. So if anybody is trying to read this data they could see the version
that's the old version before I did the delete or depending on your isolation level actually see the delete themselves, right? Isolation levels, semantics, are the same. They don't change. The difference is how
we do recovery and undo. So the cool thing here is using this feature is I can truncate
the log anytime I want now. The log will not just grow out of control because of an old active transaction. Your log truncation is based
on the oldest one you have. If somebody just goes
and gets a cup of coffee with an app that's bad that's
why you're going to get a lot of growth problems. So in fact you can see here in this query that the
log is only seven percent where it was 42 percent before. So heres the kicker,
you ready for this one? Here's the roll back. It took thirty seconds and it's done. SANJAY: Instant. BOB: And it's still consistent. (laughing) I have the same scenario
when you look at our workshops here where if I
killed SQL server in the middle of this and I ran
recovery it would normally take about a minute to undo everything. Now you don't even know it's happened, so, SANJAY: Fantastic. So it's both an Azure
database in public preview and now available in SQL
server so that's the kind of innovation we continue to
make on the core engine of SQL server, again. We're building a modern data platform. I feel that term with you earlier like spark and I do and
you're going to hear Buck and Anne are going nuts and
will talk to you all about that because they're the experts on that. SANJAY: Yes. But the core engine is still
the heart of what we have to do and it powers that
platform and so you see some great examples of how
we just keep innovating on that, both yesterday for SQL '17 and now for SQL '19 today. SANJAY: Fantastic. Great stuff right? Yes, thank you. BOB: Pretty exciting. Great, so now can you
share some of the resources that you have for folks to-- Yeah I want everybody to have access to, every time I speak I want
everybody to have access to everything we have. Look at this new website:
AKA.MS/SQLWORKSHOPS SANJAY: Okay.
You're going to love this. We were modifying this yesterday. All of our demos. All of the workshops we have, all of the
presentations I've done across the last decade, they're
all out there for you. So you go to this one
site. It's a one stop shop. We're going to continue
to innovate on that. Everything I showed you
today is available there. Except for the tempdb one. We're not ready to release that one yet. If you want to evaluate SQL
'19, here's a site for you. And then we have a YouTube
channel, actually, where we show things on SQL server
and you can see it there and then, if you want to sign up for their early adopter program. If you're thinking about maybe even running SQL '19 in production,
that may be possible for you soon and so if you
want to sign up for the early adoption program we'll talk
to you and when you hear about big data clusters today, that's
another way for you to get access and use that technology. So, Sanjay, we're continuing
to, besides these videos, you're going to get to
see us on some point. we're continuing to
build and iterate on the content for you and this
is all free because we want everybody that's using SQL to
have access to this and learn more about the technology. Fantastic. Yeah. It's a pleasure to talk to you today. I enjoyed this, Sanjay. Thanks for having me today. I really appreciate it. Thank you so much. Thank you for watching this video. Learn more about this and
other topics at Azure.com/SQLDW
