Coming up we're gonna take a look at SQL Server 2017
which brings the mission-critical performance security and availability
of SQL Server to Linux and containers.
Now we're going to show you how SQL Server that you know runs on Linux
with a new platform abstraction layer.
And how well it performs using Azure Active Directory authentication on Linux.
And also high availability options as well as failover between Windows and Linux platforms.
And stay tuned because we're going to also show you support for graph analysis
and identify new relationships in your data and more with some of the new data
science that data analyst features that we have.
I'm joined today by Travis Wright from the SQL Server team, give him a big round of applause.
Hey everybody.
All right so we've been covering SQL Server 2017 on Microsoft Mechanics
in November last last year.
We actually had the preview on and you were presenting it then.
But, how's the product evolved since November?
We've done so much since November.
We have built in the HA availability pieces, right?
so we've got Pacemaker underlying that to provide HA.
We've got Active Directory authentication done.
We've got SQL Graph working on Linux.
We have also achieved performance parity between a SQL Server on Linux and Windows.
How did you get then SQL Server to work on Linux?
Are you using emulation or something for that?
No, actually you know we took a different approach to that.
We didn't want to have the developers have to rewrite all the code.
That would be sort of like developer abuse and that's sort of a not frowned upon these days.
So we actually built a platform of abstraction layer.
I can tell you little bit kind of how that works.
So prior to SQL Server 2005, SQL Server ran as a user mode process
just like any other application it depended on the hardware resource management
in the kernel to be able to reach out and interact with the hardware underneath the covers.
So then in SQL Server 2005 we said okay we want to have better performance
So we brought that hardware resource management into SQL Server in the user mode processes
as part of what we call SQL OS.
And that is now responsible for all the processor, scheduling, memory, management
and network and disk i/o
so we can achieve the maximum performance.
A few years ago they started a new project in a Microsoft Research called Drawbridge.
And Drawbridge was intended to be kind of a way to isolate an application.
But one of the things that came out of it was they were able to map
all of the Win32 API surface area down to about 50 application binary interfaces.
So we did is we combined that project with a few Windows binaries.
And the SOS, the SQL OS, into one thing we now call the SQLplatform abstraction layer.
As the SQL platform abstraction layer ships us that small number of Windows binaries
such that SQL Server sits on top of that and thinks that it's running on top of Windows.
But in fact it's running on top of a different operating system.
And we map those application binary interfaces through what we call a host extension.
We have a host extension for Windows and a host extension for Linux.
And it just goes right down through the hardware.
One of the cool things in the benefits here is that you didn't need to rewrite SQL Server from scratch.
Then there is no emulation running right?
Yeah, there's no emulation it's just at the end of the day assembly code.
And one of the really good advantages of this is that every time we ship a new feature,
for example we have it show up on a single server on Windows
and on Linux at the same time.
An example of that that we'll see you later today is SQL Graph.
Now one of the things that is also good about this is every time we do a bug fix
it will show up on both platforms.
In fact we ship the exact same binaries
on both Windows and Linux.
We take the binaries that come out of the Windows build process,
and we package them together with those Windows binaries
into the package that's installed on Linux.
So you can think of it as SQL Server runs on top of Windows on top of Linux.
So very cool.
So when you service it at all the same code so for you as the engineer then it's also easier to build.
Yeah but I bet people want to see how it actually works and see it in action.
Yeah let's take a look at that.
So one of the things that people think about when they think about adding an abstraction layer like this
is it could have an impact on performance, right?
So what I thought we'd do is show you kind of a new demo we've never shown this before.
But we we found a Superdome over in the corner of our test lab
just sitting there I don't know nobody was doing anything with this
and we were like hey let's see what happens we run SQL Server
on Linux on that Superdome.
So here I am SSH into that machine.
The Superdome has 12 terabytes of RAM on it.
Not bad, not bad.
It's actually a couple, like one generation older.
Up here you can see that we have 480 CPUs.
So that's 240 CPUs hyper threaded, right?
Huge machine.
So now let's take a look at what it looks like to run a query on here.
So I've got a query here that's one of these TPC style queries
that will really hammer the box.
You can see us doing a bunch of aggregations and sums.
And you can see that we're pegging all 480 of those cores over here.
we're processing over a 30 terabyte database file.
We're going to calculate 250 billion rows of data in about 18 seconds.
There's a SQL Server running on Linux on rel.
And it's not just what we're seeing here but we also published some great benchmarks in terms of
showing SQL perf from third parties, right?
Right, we definitely want to be measuring and showing our performance in a standardized way
that people can understand.
So with Red Hat and HPE, we published a benchmark for TPCH,
1 terabyte, non-clustered back in May
and that's the new world record for TPCH 1 terabyte.
And we'll continue publishing new benchmarks as we go forward here.
So very cool in terms of performance,
but we've also done a lot of other things in terms of being able to manage the servers a bit differently.
And also the way that in terms of where you're getting from from a PAL perspective.
Maybe things like Windows authentication
that we take for granted on the Windows side in clustering maybe.
We're able to do some of that stuff inside of SQL even on Linux now, right?
Right so you have to do things a little bit differently on the Linux side, right?
You can't just assume the Windows Server cluster service
is going to be there because it's not, right?
So for high availability we depend on a cluster manager called Pacemaker
which is an open source project as one of the most common cluster managers out there
in the Linux ecosystem.
And we use that for both the shared disk failover cluster HA
as well as the replicated always-on availability group HA.
And we also have implemented Active Directory authentication.
So that you can connect to a SQL Server
Should we take a look at a demo?
So for this particular demo I just recorded it ahead of time
because it's a little tricky with the timing for the HA failover.
So here I've got SQL Server management studio running on a Windows machine.
And I'm going to connect to a database engine.
And this machine is a RHEL 2.
Or as the name of the machine, obviously this a RHEL machine.
So we're just going to connect to that using my Windows authentication ID here.
So this is just a standard active directory ID.
And I connect to SQL Server just like I do on any other machine.
So now let's skip forward into showing
the always-on availability group piece here.
So I'm gonna show the always-on availability group dashboard.
You can see that I have an AG here.
I have three machines that are making up this always-on availability group.
And you join them into an always-on availability group just like you do on Windows.
Now I've got a simple node application which simply connects to the cluster IP
and returns back the name of the node that it's currently connected to.
Now I'll just go in and stop the primary right now
So I just do systemctl stop msSQL Server.
And within a couple seconds here it'll lose the connection to the cluster.
But the application is designed of course to retry
And now it retries and connects back to you RHEL 2 now.
So this is how you guys can see that it behaves just like a cluster does on Windows,
except that we're using the pacemaker technology to do this.
So very cool.
And what if I wanted to do some high availability clustering
that spanned maybe the Linux and the Windows platforms.
Is that crazy or can I do that?
It is crazy.
You know first when people ask me about that
I was like why would you ever have an always-on availability group with Linux and Windows machines?
That's like dogs and cats, it's weird.
But as we got to talking to customers about it they actually give us a couple scenarios
where that does make sense.
So one of them is where you maybe want to have an OS level of redundancy
So if there was a zero day vulnerability that came up for example on Windows,
you could fail over all of your databases were running on SQL Server on Windows
over to SQL Server on Linux with minimal downtime,
patch your Windows side and bring it back.
Or vice versa.
And the other scenario that they gave us was well
what if I want to migrate from SQL server on Windows to Linux?
Well this way you may have a cluster already that's running on SQL Server on Windows.
And now you can add some SQL Server on Linux nodes to that cluster.
All the data will be replicated over.
And then when you're ready just simply failover.
And you can actually do that database migration with minimal application downtime.
Just a few seconds while you do that failover.
So It actually is not quite that crazy.
Cool, so what other capabilities then are we bringing that are both
advantageous for the Windows side and the Linux side?
Yeah so one of the features is SQL Graph.
And I'll show that to you here in a moment.
But that allows us to store data in a graph format
and then also query it using graph syntax.
Okay and then another new feature in SQL Server 2017
which isn't on Linux yet but it is available now on Windows is Python, right?
So in SQL Server 2016 we added R in database
so you can execute R scripts in database right there next to the data
and in SQL Server 2017 we've added now Python support
so you can grab any of those open source Python machine learning libraries
and run them in database right next to the data.
Instead of having to pull it out to a different server to do the analysis on it.
And we now have GPU acceleration in SQL Server 2017.
So it can run super fast.
So it's going to save you some hardware footprint as well
because you have had to take out basically reporting tables in order to run against it.
And it's just slower we could transfer all that data, right?
So let's let me show you guys Graph.
So let's take a look at that.
So let's pull up Visual Studio Code, here.
And I'm going to just fire up a Docker container, here.
So you can see I've got a new Docker container.
This just started up here.
And now let's go over here and bring up this graph query file.
Now one of the cool things about Visual Studio Code is we have a new extension, here
that allows us to connect to SQL servers
right from Visual Studio Code.
And then you can use Visual Studio Code to get
Intellisense and syntax highlighting and so on for SQL queries.
You can see that I'm connected now to my local host where my container is now running.
So I've got a data model here to represent sort of a factory
where I have some factories, I have some products that are being produced by some machines and so on.
I've got my schema and ASCII art here, right.
So let's go ahead and create a database where we'll start to store this graph data.
Okay so now we have the database created.
And now let's create some tables inside of here to store the nodes.
Now you'll notice that creating a table is exactly like you would create any other table
except for this additional syntax here as node.
And that just indicates that this is now a node table.
And now let's create some edge tables.
And you'll notice that creating edge tables
is exactly the same as how you would create any other table
except you have as edge here.
And now let's insert some data into these tables.
So I'm going to first insert the node data.
And the node data as you can see is exactly like inserting data into any table.
There's really no difference on how you do this.
Now that we've inserted some node data, let's go ahead and insert some Edge data.
You'll notice the inserting the Edge data
is basically the same except that for edge data you want to put two values in the table.
Basically the node ID on each end of the edge.
So now that I've got all the data in here now we can start to run some sequel graph queries.
And you can see that for the most part this graph query looks the same as any other type of query.
Except for this syntax down here.
And the syntax allows you to easily
indicate the direction that you want to traverse over a particular edge.
So it makes it much simpler for you to write these queries
because you don't have to remember to join in or join out or join left and so on.
You can just go in here and author the queries which with a much simpler syntax.
It's much faster and easier to understand what's going on.
And yet you achieve the same results.
Here we can see the sensors that are reporting some issues with some machines.
And then lastly let's just pop down here.
One of the benefits of having a Graph DB in SQL Server like this
as opposed to having a separate graph DB like Neo4J
is you get all the benefits of SQL Server with your graph data.
So just one example of this is that I can add a clustered columnstore index
to one of my node tables just like I can any other table.
So all the same security benefits that you get with SQL Server,
all of the performance and so on.
All of that now applies to graph data as well.
So you mentioned that Python support is coming.
It's available for building these intelligent data models.
Which would make a lot of data scientists very happy
because we added R last time, right?
Yeah absolutely.
So now they have the choice of either the most popular languages for data science.
Very cool, so it's really interesting to see how we've added all those to SQL.
It's also something that we've got available on both platforms.
It's coming very very soon, right?
Yeah absolutely.
So now it's available on on Windows and will come soon on LInux.
Very cool.
So it was a really great fast tour in terms of SQL Server 2017
including all the new Linux capabilities.
But, where would folks go to learn more information on this?
You know there's so much more that we didn't even really talk about today
that we've added to SQL Server 2017.
So what I recommend and is just easy to remember is just go to Microsoft.com/sql
and explore all the other awesome things that are available now in SQL Server 2017.
And we just announced general availability today.
The bits will be available a week from today
and you can go grab those.
But if you want, you can go grab the RC bits they're available up on the web now.
Awesome, so exciting times for SQL Server 2017.
Thank You Travis and all the people watching here today.
That's about all the time we have for this Microsoft Mechanics Live episode.
we'll see you next time.
Thank you everybody.
[Music]