[MUSIC]
Hi, everyone, I'm Travis Wright,
a program manager on the SQL Server engineering team.
I'm here to talk to you today about what's new in the Database Engine in
SQL Server 2017.
The main improvement that we're making in SQL Server 2017 is
the introduction of support for SQL Server to run on Linux and Docker.
In particular, we support Red Hat, SUSE, and Ubuntu.
So what's going to be available on Linux in this first release?
First of all, we'll have package-based installation for
Red Hat, Ubuntu, and also Docker images,
available for quick and easy installation, and upgrades.
We'll also have support for deploying into container platforms,
like Open Shift, Docker Swarm, Kubernetes, and more.
We also have failover clustering available through Pacemaker
as well as always on availability groups.
We have support for SQL Server agent replication.
Basically, all of the features that you would expect to see from
the Database Engine with a few exceptions which I'll go
over in a few minutes here.
On the programming side,
all of the features that we've introduced in the most recent
releases of SQL Server on Linux that improved the performance and
security of your application are also available on Linux.
This includes things like in memory, OLTP, and Columnstore,
compression, and security features like Always Encrypted,
Row Level Security, and dynamic data masking.
This makes building your applications for
SQL Server whether it runs on Linux or Windows exactly the same.
So what's not available in this first release of SQL Server
on Linux.
Some of the more obscure features of SQL Server engine are not available
such as, database mail, alerts, things like FileTable which has
a dependency on the Windows file system is not available on Linux,
Stretch DB and PolyBase.
Some of the other services that surround the Database Engine
like Analysis Services, Reporting Services, Integration Services, and
R Services are also not available on Linux in this first release.
But we're already starting to work on those pieces for
our future release.
On the tools side,
we have a bunch of new interesting things going on here.
We've already released a new extension for Visual Studio Code,
which is a cross-platform code editing tool that runs on Mac OS,
Windows, and Linux.
And that extension allows you to connect to SQL Server whether it
runs on Windows, or on Linux, and author, and execute queries,
view the results, save them to JSON or to CSV.
And it also includes support for
advanced features like IntelliSense or Peek Definition.
We also have the support for command land utilities natively on Linux.
So things like SQLCMD and BCP are available and run on Linux as well.
We've also been testing third-party tools like JetBrains DataGrip,
DBeaver, and other tools that you may be familiar with, and
making sure those work with SQL Server on Linux as well.
SQL Server Management Studio has had a bunch of improvements to it that
allow it to manage SQL Server running on Linux,
just like you can manage SQL Server on Windows today.
We're also now working on a new cross-platform database admin
GUI tool, which will be available soon,
and that tool will run on Mac, on Linux, and on Windows.
And enable you to do some of the same types of administrative tasks
that you do with SQL Server Management Studio today.
The new database admin GUI tool, and the Visual Studio Code
extension are actually open source projects available on GitHub, and
will accept contributions from the community, and
look forward to working with people on that.
All of the existing drivers and frameworks that exists out there for
the various languages like Java, or .NET, or Ruby, or Python.
We've been testing all of those to make sure that they work with SQL
Server on Linux as smoothly as they do with SQL Server on Windows today.
So let's take a quick look at SQL Server running on Linux, and
just see what that looks like.
Okay, so here I am on my MacBook Pro.
I've got Docker for Mac installed, so
that I can run Linux containers on my MacBook.
And I've created an ASP.NET application,
that's a Docker Compose application actually.
So, I've got one container image which is web here.
And then, I've got another one here db.
And the db container image is mssql-server-linux image.
And when we start this up, it's going to set the system
administrator password, and accept the license agreement.
And on the web side, this will start up in most sets environment
variables that will allow the web application container to connect to
the db container, and connect to the database, create the database, and
then run the migration to create the schema, so the application can run.
So let's go ahead in Docker Compose up our application.
This'll go ahead and create both the web app db container, and
the web app web container, and then connect the two of them up together.
Okay, so most of this output here is SQL Server starting up.
So again, this is SQL Server running on a Linux container on my MacBook.
Okay, so at this point, the application should be ready
to go at localhost:8000, whoops, 8000.
All right, so here's my ASP.NET application, and at this point, it's
the standard ASP.NET application running on top of SQL Server.
I can go ahead, and create log ins, register, do all this kind of stuff.
This is kind of a basic web application people are gonna use as
a starting point for a fully functional ASP.NET Core application.
So now,
what I'll do is, I'll just show you kind of some of the experience
around using the Visual Studio Code extension to connect to SQL Server.
So, I'll run Command+Shift+P which gets me into the command palette,
and then I'll type SQL to filter down the list of available commands.
And you can see some of the commands that I can choose.
For example, to connect, or disconnect, or
execute a query against SQL Server.
So here, I'll choose connect.
And it's prompting me that it wants me to change the file format of this
particular file to be a SQL file.
So, I'll do that.
And now, I can choose to connect to a SQL Server instance.
In this case, I'll go ahead, and
connect to my profile that I've previously created, and
just connect there.
You see down here that it says, that I am now connected to local host and
the master database using the SEO login.
So here I can do things like select name from sys.databases.
And you see the IntelliSense that's coming in there that helps filter
down everything to make it easier to run the queries with some hints.
So at this point, I can do something like use mydb.
And you can see that it's prompting me the different databases that
are available.
So, it's being super smart about looking at the database
structure on the server, looking at the schema inside of it,
to make it easier for me as a developer cuz I'm super lazy.
So then I can execute these queries, switch my database context.
And from here, it's really just SQL Server, right?
You can actually execute your queries just like you can on
a SQL Server running on Windows.
It’s the exact same thing on the SQL Server on Linux.
All right, now that we've seen SQL Server on Linux, let's take a look
at some of the new features of the coming SQL Server 2017.
The first is the Adaptive Query Processing.
Here, we're able to improve the performance of your queries without
you having them modify your code in any way.
By choosing better query execution plans, and
as we're running the query in real time, modifying the execution plan
for that query to better perform and return the results faster.
The next is Graph.
Here, we're introducing Graph data capabilities
into SQL Servers that you no longer have to have a specialized graph
database to store graph type data.
You can now just store it natively in SQL Server in Graph format
using nodes and edges.
And also, be able to query your data using a node and
edge query syntax that you can see an example up here.
Next is resumable online index rebuild.
This allows you to pause and resume your index rebuild as they
are happening without having to completely stop and start over.
You can just pause them and resume you can control
the impact of index rebuilds on the performance of your application.
The next is clusterless read scale with availability groups.
Here, you're able to deploy an always on availability group for
read scale with multiple read only replicas, without necessarily having
to deploy a cluster like Windows Server Cluster, or with Pacemaker.
If all you're interested in is scale out, and HA is not an important
factor, you now have the option of deploying an always on availability
group for read-only scale without having to deploy a cluster.
Next is some enhanced performance for
our natively compiled T-SQL modules.
So in the areas of JSON data, for example, and
Cross Apply, those are now going to run even faster, and again,
without you having to modify any of your code.
We also introduce some new string functions, and make it easier for
you to manipulate string data in your database, and in your queries.
So, if you're excited to go ahead and get hands on the bits, and
try it out yourself, we have the SQL Server Early Adoption Program.
The SQL Server Early Adoption Program is designed for
those customers which would like to go into production
prior to the general availability of SQL Server 2017.
Those customers in the program will get a direct connection to
the product group.
You'll have the full support of Microsoft Support.
You'll have a special licensing amendment that allows you to run
that software in production prior to general availability.
If you just want the bits, please go and grab those.
If you're interested in actually getting into this program,
getting the bits into production prior to GA, please go and
sign up here at the link on the slide, aka.ms/eapsignup.
So, thank you very much for learning more about the new features of
the coming in the SQL Server Database Engine and SQL Server 2017.
Please go grab the bits, and give them a try, and
let us know what you think.
[MUSIC]