Introducing SQL Server 2019 | Data Exposed

[MUSIC].>>Hey everyone. Welcome to
this episode of data exposed. I’m Travis Wright, Group
Product Manager for the SQL Server and Azure data
engineering team at Microsoft. Today I’m excited to introduce
to you a SQL Server 2019, the most recent released SQL Server. SQL Server is celebrating its
25th anniversary this year. That’s quite awhile. By look back
on the early days of my career, I started on SQL Server 2000. In that 25-year history, SQL Server has really
come a long ways. It’s really expanded to meet the needs of our
customers over time as the different types of data
that customers need to collect and process
and query has changed, and as there’s been more
and different kinds of database engine requirements
that have come along. So let’s take a trip back
down memory lane for a moment and just look at where
SQL Server has come from, and then we’ll take a look
at where SQL Server is going next with SQL Server 2019. Let’s start with SQL Server 2008. SQL Server 2008 is actually out of extended support
just this year. If you fast forward a bit to look
at SQL Server 2012 and 2014, we really made some big improvements
in terms of performance and high availability by
introducing always on availability groups
for high availability, and in memory capabilities to really boost the performance
of your databases. In SQL Server 2016 and 2017, we really change the game a lot by introducing some
new capabilities in SQL Server to store and query
JSON and graph as well, and we also did something
very surprising by bringing SQL Server to Linux and
containers in SQL Server 2017. In SQL Server 2019, we’re changing the game yet again, and really expanding and redefining the definition
of what SQL Server is. SQL server of course is still the relational database
that was 25 years ago. You can still store
your data in SQL Server and query it in the same
way that you always have. But at the same time, we’re
redefining SQL Server and extending it well beyond just
the relational database space. So let’s take a look at what
we’re doing in SQL Server 2019. In SQL Server 2019, we’re giving you access to query and process data outside of the boundary of a
traditional SQL Server instance. By taking PolyBase a feature we first introduced in SQL Server
2016 to the next level. PolyBase allows you to create a
data virtualization layer across multiple different
data sources such as Oracle other SQL server instances. Tera data, MongoDB and much more. We’ve also taken HDFS and
spark and build it in the box. So now with SQL Server, you can process and store
data the petabyte scale and process and store data that’s
are also even unstructured data. You can use SQL Server with
virtually any programming language. You can run it on pretty
much any platform now. With SQL Server 2019, you can run it on Windows of course. You can also run it on
Linux on Red Hat, on Susa, or Ubuntu, you can run
it in a container, you can run it on Kubernetes. You can run it on a different
processor architectures now. With the Azure SQL Database edge, you can run it on an arm 64
device like a Raspberry Pi, and you can run it in the
Cloud and Azure SQL Database, or you can run it on-premises, or you can run it and
other public Clouds. There’s a lot of versatility there. You can use SQL Server wherever
it suits you the best. SQL Server 2019 continues to expand our industry-leading
performance. SQL Server has established itself
for many years now as the number 1 in terms of OLTP performance
with TPC-H Benchmarks, and as the number 1 in terms of data warehouse performance
with TPC-H Benchmarks. We’ve also led the industry by having the fewest number of
vulnerabilities reported out of any of the major database engines
across the last eight years according to the National Institute
of Standards and Technology. So let’s take a closer
look at just some of the highlights of SQL Server 2019. Let’s start with some
improvements we’re making in the performance space. So first of all, persistent memory as a new technology that’s
entering the hardware market. We’ve taken advantage
of persistent memory to really boost the performance. You don’t have to make any
changes to your application, and you can store your data and persistent memory for
faster performance. Secondly, for intelligent
query processing, we’ve really expanded the
family of features here as you can see in this
chart to include lots of new ways where the
query optimizer can learn over time based on the
execution of how queries go, how future executions of those
queries can be improved, boosting the performance
of your applications over time without you having to change
anything in your applications, and lastly, we’ve put the TempDB in memory for even faster
performance of the temp database. Next, let’s take a look at
some improvements we’re making in security and compliance. First of all, especially with GDPR, customers are faced with even more regulatory requirements
that they have to meet. To make that easier, we provide data classification
capabilities out of the box. You can point the data classification
engine at your database, and it will automatically discover the different types
of data you have in your database such as
PCI data or GDPR data, and automatically
classify that and produce reports for you like you see
in this screenshot here, and you can define your own
classification rules as well. Next in terms of security, we’ve improved Always Encrypted
our client-side encryption technology that allows you to separate the encryption
from the database. So that way, the
database administrators cannot decrypt the data in
the database that allows you to separate duties here between the database administrators and the application developers and users, and lastly just as an example here of improvements that we’re
making as we have also added performing the encryption of all the data inside of enclaves. Now, in the space of developer
and DBA tools, hopefully, you’ve all learned about and tried Azure Data Studio a
new cross-platform open-source tool for all types of data person as whether you’re
a database administrator, a database engineer,
or a data scientist. This tool is available for you
to download for free and use, and it is designed to be Multi database engine so you can
use it not just with SQL Server, but also with SQL server in the Cloud such as
Azure SQL Database or with Azure SQL data
warehouse also with other database engines
like PostgreSQL and MySQL. One of the improvements that
people are most excited about and Azure Data Studio is
the notebook experience. Notebooks allows you to create
a file that contains mark down as well as code cells. In the markdown, you can describe some analysis that you’re doing or
steps that should be performed, and then in the code cells that are intermingled with
those markdown cells, you can have some code that you
or somebody else can execute. We have notebooks for
TSQL, for PowerShell, for Python, and you can run it either locally
or you can run it in Spark. It’s a very powerful
way to collaborate with other people by capturing this
information and notebooks, and these notebooks
can be used to capture samples or maybe some standard
operating procedures or troubleshooting guides and share
those with other people through the Git integration that we have
built-in to Azure Data Studio, and lastly, we’ve integrated some really cool technology from Microsoft Research called
SandDance which allows you to do ad hoc data
visualization and exploration using some really cool
charting capabilities right there inside of
Azure Data Studio. So definitely, go grab Azure Data
Studio if you haven’t already. It’s a super powerful tool, and the innovation is coming
there on a monthly basis as we release every month
for Azure Data Studio. So we continue to double-down on our new approach to how we look at different
platforms for SQL Server. In SQL Server 2017, we introduced support for Linux. But SQL Server 2019, we’re taking that to the
next step by creating even greater feature parody
between SQL Server on Windows, and SQL Server on Linux by bringing
PolyBase and all services, distributed transaction coordinator
and replication to Linux, and that pretty much checks off all the boxes for the
database engine features. So you have near 100
percent compatibility between SQL Server on Windows
and SQL Server on Linux. In partnership with Red Hat, we’ve also created rel
based container images which are available on the
Microsoft Container Registry, and you can discover them in the Red Hat Container
catalog as well. Lastly in preview right now, we have support for always on
availability groups in Kubernetes, so that you can get the
benefits of having always on availability groups
for scale out reads or for high availability living right there on top of the
Kubernetes layer underneath. Lastly, probably the
most significant area of improvements and just spreading out the tent
of SQL server if you will to handle new
types of scenarios, is the improvements that
we’re making in PolyBase and data virtualization as I
mentioned at the beginning, where we can create a data virtualization layer across many different data
sources like Oracle, other SQL Server
instances, and Teradata. That allows us to bring
together data across multiple data sources at query time, and really minimize
the need for using ETL as a way to integrate
our data together. Nobody likes building and
maintaining ETL pipelines. So we want to give you an another
option that you can use in addition to ETL for how you
integrate your data together. In SQL Server 2019, we’ve introduced a new
pattern for how we deploy SQL Server by introducing a new
pattern called big data clusters, and big data clusters allows you to deploy a SQL Server
instance with all of its typical capabilities
along with HDFS and Spark in one integrated solution
as deployed on Kubernetes, that provides you the ability to take SQL server and do all the things
that you do a SQL Server, but then easily integrate that together with HDFS and
sparks so you can do queries over high volume
data that may scale out 1000 times greater than you could possibly store
and SQL Server today, up into the tens or even
hundreds of petabytes of data as well as being
able to store and query and process
unstructured data like video files or audio files in HDFS, and you have the benefit
of having the Spark engine there for data preparation
activities or for doing Machine Learning model training or operationalization of those
models inside of Spark. So by Microsoft providing an integrated solution and supporting that one integrated solution
and big data clusters, you get a shared scalable
data lake built on HDFS that either SQL Server
or Spark can access. This really provides you
a complete AI platform for doing everything
from the ingestion of the data by storing it
in HDFS or in SQL Server, and then doing data preparation tasks using either Spark or SQL Server, and then doing Machine
Learning model training using either the built-in Machine
Learning libraries in Spark or by using the Machine Learning
services built into the SQL Server Master instance
and then you can operationalize those either in the Spark Runtime by doing batch Machine
Learning scoring, or you could do it inside
of a store procedure in SQL Server for example, or we have a way where you
can actually take a model and automatically wrap it up
in a rest API container, and provision that
container on top of the big data cluster so that it’s easy for application
developers to call in and use that
container as a way to submit some data habits scored
and get a score value back. So it makes for a really a
complete AI platform end to end to be able to do
everything you need to do around AI and Machine Learning. So hopefully, that gives
you a quick introduction into SQL Server 2019. This is really just one
video in a series of videos on the SQL 2019 channel that you see linked here at
the bottom of the screen, and we really hope that you have a chance to go
through all these videos. We hope to publish maybe around a hundred videos that go into lots of details about everything
that’s new in SQL Server 2019. If you have any feedback, please post that in
the comments below and subscribe to the channel. So thanks for joining us today to learn more about SQL Server 2019, and we’ll see you out
there at the next event or SQL Saturday. Thank you. [MUSIC]


  1. I have been a SQL developer for over 20 years. However, I had a baby and got outdated. I need to learn all the new stuff SQL has since 2008. What books and video series do you guys recommend for me to catch up? I highly appreciate the guidance. Thank you

Leave a Reply

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