SQL Server 2017: What’s New in the Database Engine?
Articles,  Blog

SQL Server 2017: What’s New in the Database Engine?

[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]

Leave a Reply

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