microsoft sql server 2019contentit.ingrammicro.eu/webmarket/imfoemail_pm/alessia/...microsoft sql...
TRANSCRIPT
Microsoft SQL Server 2019
Technical white paper
Published: November 2019
Applies to: Microsoft SQL Server 2019
Microsoft SQL Server 2019 2
Copyright
The information contained in this document represents the current view of Microsoft Corporation on the issues discussed as of the date of
publication. This content was developed prior to the product or service’ release and as such, we cannot guarantee that all details included
herein will be exactly as what is found in the shipping product. Because Microsoft must respond to changing market conditions, it should not
be interpreted to be a commitment on the part of Microsoft, and Microsoft cannot guarantee the accuracy of any information presented after
the date of publication. The information represents the product or service at the time this document was shared and should be used for
planning purposes only.
This white paper is for informational purposes only. MICROSOFT MAKES NO WARRANTIES, EXPRESS, IMPLIED, OR STATUTORY, AS TO THE
INFORMATION IN THIS DOCUMENT.
Complying with all applicable copyright laws is the responsibility of the user. Without limiting the rights under copyright, no part of this
document may be reproduced, stored in, or introduced into a retrieval system, or transmitted in any form or by any means (electronic,
mechanical, photocopying, recording, or otherwise), or for any purpose, without the express written permission of Microsoft Corporation.
Microsoft may have patents, patent applications, trademarks, copyrights, or other intellectual property rights covering subject matter in this
document. Except as expressly provided in any written license agreement from Microsoft, the furnishing of this document does not give you
any license to these patents, trademarks, copyrights, or other intellectual property. Information subject to change at any time without prior
notice.
Microsoft, Active Directory, Azure, Bing, Excel, Power BI, SharePoint, Silverlight, SQL Server, Visual Studio, Windows, and Windows Server are
trademarks of the Microsoft group of companies.
All other trademarks are property of their respective owners.
© 2019 Microsoft Corporation. All rights reserved
Microsoft SQL Server 2019 3
Contents
Introduction ............................................................................................................................................................................................................ 4
SQL Server 2019 enhancements ..................................................................................................................................................................... 5
Database engine enhancements in SQL Server 2019 ....................................................................................................................... 5
Intelligent performance with SQL Server ......................................................................................................................................... 6
Business continuity .................................................................................................................................................................................... 8
Enterprise security and compliance .................................................................................................................................................. 11
SQL Server on Linux, Containers, and Kubernetes ..................................................................................................................... 14
Enhancing the developer experience .................................................................................................................................................... 15
Machine Learning Services ........................................................................................................................................................................ 17
More features based on customer feedback ..................................................................................................................................... 17
Data virtualization ......................................................................................................................................................................................... 17
SQL Server Big Data Clusters .................................................................................................................................................................... 19
Enterprise Information Management .................................................................................................................................................... 23
Business Intelligence .................................................................................................................................................................................... 24
Management and Developer Tools for SQL Server .............................................................................................................................. 26
Migrating and Modernizing to SQL Server 2019 ................................................................................................................................... 27
Conclusion ............................................................................................................................................................................................................. 28
Additional resources .......................................................................................................................................................................................... 28
Microsoft SQL Server 2019 4
Introduction
The changing demands of data
The world of data is changing faster than ever with an exploding number of sources creating an estimated 1.7MB
of data every second for every person on earth.1 In order to remain competitive, organizations must be able to
harness different data types. It’s no wonder more than 95% of businesses have expressed a need to manage some
form of unstructured data.2
At the same time, development teams across industries are moving from a monolith to a microservices approach
for software and application development to capitalize on this increase in data volume and variety. They’re
looking for ways to use data to better understand their customers’ experiences and innovate faster.
In order to stay relevant and competitive, organizations need a database solution that
makes new data sources and new development processes part of business as usual
without compromising security, compliance, or customer privacy.
Organizations must be able to ingest, store, and analyze big data alongside their core relational data at the speed
it is generated and react to it in that same moment. The database solution they choose needs to provide
developers the flexibility to work the way that works best for them—across on-premises and cloud deployments,
with the languages and platforms they prefer. And it needs to grow with them over time.
The evolution of SQL Server
Microsoft SQL Server evolves as the world of data continues to change. With each release, SQL Server has added
features and capabilities that keep step with industry trends and address customer needs.
In SQL Server 2012, we provided a built-in High Availability
solution through Always On availability groups and improved
analysis capabilities by adding in-memory tabular mode in SQL
Server Analysis Services. Our 2014 release included memory-
optimized tables and natively compiled T-SQL procedures for
high-throughput and low-latency transaction processing. The
2016 version shipped with new security features like dynamic
data masking and Always Encrypted as well as in-database
Machine Learning with R. And, most recently, SQL Server 2017
opened the door to more platform options and DevOps
functionality with its availability on Linux and Linux containers
as well as support for Python.
The vision behind SQL Server 2019 enhancements
SQL Server 2019 challenges the status quo of relational databases by making it possible to ingest, manage, store,
and analyze any type of data from anywhere. It expands the working definition of the data environment by
1DOMO, Data Never Sleeps 6, https://www.domo.com/solution/data-never-sleeps-6
2Forbes, Big Data Goes Big, https://www.forbes.com/sites/rkulkarni/2019/02/07/big-data-goes-big/#1b570e8720d7
Our goal is to help you stay ahead of the
changing world of data by providing:
• Intelligence over any data and all your data
• Your choice of language, platform, and
deployment location
• Industry-leading performance
• The most secure database
• Insights in minutes and rich reporting
capabilities
Microsoft SQL Server 2019 5
enabling a consistent experience of security, identity, and data management across deployments all the way out
to the edge.
SQL Server 2019 is fully compatible with SQL Server on Azure VMs,
Azure SQL Database and managed instance, and now Azure SQL
Database Edge, a small-footprint, edge-optimized SQL Server that
runs on edge devices and supports ARM processors (currently in
preview). With this latest advancement in SQL Server, you can
leverage your current SQL Server skillset to bring together your entire data environment and run the same
application on the same intelligent database engine, anywhere.
Through the course of this paper, we’ll highlight some of the key features in SQL Server 2019, including, but not
limited to: database engine enhancements that boost performance, security, and management capabilities; data
virtualization improvements that make it easier to integrate external data in your queries; and SQL Server 2019 Big
Data Clusters that allows you to combine and analyze high-value relational data with high-volume big data using
scalable clusters of SQL Server, Spark, and HDFS.
SQL Server 2019 enhancements
Database engine enhancements in SQL Server 2019
SQL Server is the most popular database in the world. In tests against competing databases, it has set benchmarks
for performance and records for the fewest security vulnerabilities. It’s the database you can trust to run your
business-critical workloads.
• Performance—SQL Server owns the top TPC-E3 performance benchmarks for transaction processing, the top
performance benchmarks in non-clustered TPC-H4 for data warehousing at 1,000 GB, 3,000 GB, and 10,000
GB.
• Security—According to the National Institute of Standards and Technology (NIST) public security board, SQL
Server continues to have the lowest number of reported security vulnerabilities across the major database
vendors (NIST, 2010-2019).
SQL Server 2019 builds on the industry-leading capabilities of SQL Server 2017 to give you even more of the
speed, security, and flexibility you need to run your business in the modern world. We chose the new features and
capabilities based on industry trends and the challenges we see ISVs, customers, and partners wrestle with today.
Many of the new features are also based on customer feedback.
In this section, we review some of the major categories of database engine improvements, including performance,
scalability, high availability, and security. This is not a comprehensive list; however, it describes some key new
features, enhancements and capabilities.
To read the full list of new features, visit the documentation page.
3 TPC-E Top Ten Performance Results, TPC.org (link)
4 TPC-H - Top Ten Performance Results - Non-Clustered, TPC.org (link)
Build once, deploy anywhere—
on-premises, hybrid, in the cloud, and
on the edge—with SQL Server 2019.
Microsoft SQL Server 2019 6
Intelligent performance with SQL Server
The SQL Server 2019 database engine has enhancements across many different features and capabilities that
enable greater scalability, offer performance gains without changing the application, and drive specific, modern-
day scenarios.
Intelligent Query Processing
We know you spend a lot of valuable time optimizing the performance of your workloads. Adaptive Query
Processing was introduced in SQL Server 2017 to make your applications run faster. In SQL Server 2019, we have
expanded Adaptive Query Processing to make it part of a feature family called Intelligent Query Processing (IQP).
By enabling your database compatibility level for 150, your workloads can automatically access IQP features with
minimal implementation effort, meaning your applications run even faster with SQL Server 2019.
Using IQP, performance issues are quickly identified and automatically tuned without administration by your
team—optimizing your overall query execution and providing better value from your existing environment. When
you upgrade your SQL Server environment to 2019 and set the compatibility level of your databases to 150, your
database workloads may immediately see better performance on the same hardware or virtualized environment
you used with previous versions without adding capacity in CPUs or servers.
New SQL Server 2019 IQP capabilities include:
• Batch mode on row store—enables batch mode execution for analytic workloads without requiring
columnstore indexes. Ideal for scenarios in which creating a columnstore index adds too much overhead to a
transactional workload (like HTAP) or it’s not a good option for the workload
• Memory grant feedback, row mode—recalculates the actual memory required for repeating queries and
then updates the grant value for the cached plan to optimize resource usage and lower costs (previously
available only in batch mode for tables with columnstore index)
• Table variable deferred compilation—improves plan quality and overall performance for queries that
reference table variables
• Scalar UDF inlining—transforms scalar UDFs (user-defined functions) into relational expressions and embeds
them in the calling SQL query
Microsoft SQL Server 2019 7
• Approximate query processing—aggregates large datasets where responsiveness is more critical than
absolute precision to reduce the cost and memory footprint needed. For example, Approximate COUNT
DISTINCT—provides the approximate number of unique non-null values
Lightweight query profiling
Lightweight query profiling collects runtime statistics and information about execution plans with minimum
performance overhead. In SQL Server 2019, lightweight query profiling infrastructure is enabled by default. This
infrastructure offers a query execution statistics collection mechanism with a maximum overhead of 2% CPU,
compared to an overhead of up to 75% CPU for the legacy collection mechanism.
Sequential key insert performance
A high number of concurrent inserts in a workload may cause a throughput issue commonly known as last-page
contention. Indexes are stored as B-trees in SQL Server and in a clustered index, the data pages are in the leaf
level, ordered by the index key. When the key is an ever-increasing value, such as an identity or datetime column,
all the new rows will be inserted on the last page of the B-tree. Contention for this page in memory will increase
as concurrency increases, limiting the workload’s scalability.
A new feature in SQL Server 2019, OPTIMIZE_FOR_SEQUENTIAL_KEY, keeps throughput consistent as concurrency
increases. When set to ON for an index, a new mechanism controls the flow of traffic for all the threads that
request to enter a section of SQL Server protected by a page latch. This control is based on a set of conditions
such as which CPU the thread is on, the state of the thread, the rate at which the thread is completing work, or any
combinations thereof.
In-memory improvements
Earlier releases of SQL Server introduced several in-memory features that provided greater performance gains
such as memory optimized tables, natively compiled stored procedures, and in-memory clustered columnstore
indexes. Using these features together allows you to query operational data without affecting its transactional
speed. This scenario, also known as Hybrid Transactional and Analytical Processing (HTAP), can be implemented
with memory optimized tables and clustered columnstore indexes to
improve transactional speed and enable real-time visibility and
insights into the transactions and operational data.
Innovation in memory and storage technology has led to a more
customized use of hardware. By leveraging a very special kind of
memory called persistent memory (PMEM), also known as Storage
Class Memory, new layers in the server-memory hierarchy have been
created that allow for control, down to the individual memory cell.
PMEM persists data in memory and retains its content through power
cycles while improving the throughput with extremely low latency.
Support for PMEM
SQL 2016 SP1 introduced support for Persisted Log Buffer. SQL Server has been enhanced to detect a Direct
Access (DAX) based volume on a PMEM device. SQL Server can create a non-volatile, tail of log cache of fixed size
for the database to optimize the tail of the transaction log.
SQL Server 2019 extends the support for persistent memory (PMEM) devices to Linux, providing full
enlightenment of data and transaction log files placed on PMEM. Enlightenment refers to the method of access to
SQL Server 2019 supports
Persistent Memory (PMEM) and
Hybrid Buffer Pool
Leverage In-Memory Database, a family
of in-memory based technologies in SQL
Server 2019 to boost your database
workloads
Microsoft SQL Server 2019 8
the storage device using efficient user-space memcpy() operations. Rather than going through the file system and
storage stack, SQL Server leverages DAX support on Linux to directly place data into devices, which reduces
latency.
Hybrid buffer pool
With this new release comes enhancements that enable another performance-improving feature, hybrid buffer
pool, available both on Windows and Linux. Using hybrid buffer pool, your database engine can directly access
data pages in database files stored on persistent memory (PMEM) devices and skip the step of copying the page
into the DRAM-based portion of the buffer pool. Read access to data files on PMEM devices for hybrid buffer pool
is performed directly by following a pointer to the data pages on the PMEM device.
The PMEM device must be formatted with a filesystem that supports DAX (DirectAccess). The XFS, EXT4, and NTFS
file systems all have support for DAX. SQL Server 2019 will automatically detect if data files reside on an
appropriately formatted PMEM device and perform memory mapping in user space. This mapping happens upon
startup, when a new database is attached, restored, created, or when the hybrid buffer pool feature is enabled for
a database.
Memory-optimized TempDB
SQL Server 2019 introduces a new feature called memory-optimized TempDB metadata, which effectively removes
contention bottlenecks and unlocks a new level of scalability for TempDB-heavy workloads. In SQL Server 2019,
the system tables involved in managing temporary table metadata can be moved into latch-free non-durable
memory-optimized tables.
Business continuity
Built-in high availability (HA) in the form of Always On availability groups was first introduced in SQL Server 2012.
This was a big change from using Failover Cluster Instances (FCIs) for business continuity. Although FCI results in
zero data loss, the database instance isn’t available until recovery is fully complete, creating noticeable downtime.
Additionally, FCI is susceptible to a single point of failure where shared storage connects hot and cold database
instances.
By contrast, Always On availability groups (AGs) maximize the availability of business-critical databases by
supporting a set of read-write primary databases and one to eight sets of corresponding secondary databases.
Optionally, secondary databases can be made available for read-only access and/or some backup operations. The
availability group replicas are hosted by specific instances of SQL Server, and they maintain a local copy of each
availability database that belongs to the availability group. Applications are configured to connect to an
availability group using the availability group listener, which has a virtual IP/DNS address provided by the
underlying cluster. If a primary replica fails, one of the secondary replicas in the automatic failover group becomes
the new primary, and client connections are redirected to the new primary replica.
With SQL Server, you can set up AGs between data centers or enable hybrid scenarios whereby your AGs span on-
premises data centers and Azure VMs. By having readable secondaries running on an Azure VM as part of your
AG, you can manually failover to the cloud if your on-premises datacenters black out. Furthermore, by strategically
choosing the Azure regions where you run your secondaries, you can give users in different regions faster access
to your data and faster time to insight. For example, users can quickly view reporting services such as Power BI
dashboards from their local region, instead of encountering higher latency when retrieving reports from a
datacenter in a different region.
Microsoft SQL Server 2019 9
Always On availability groups use multiple secondaries across two locations for high availability and disaster recovery
The latest release of SQL Server 2019 includes several improvements in AGs and other new capabilities that
increase the HA of a business-critical workload/solution.
Five synchronous replica pairs
SQL Server 2019 increases the limit for synchronous replica pairs. Users can now configure up to five synchronous
replicas (one primary and up to four secondary replicas) with automatic failover between these replicas. In SQL
Server 2017, the limit was three.
Secondary to primary replica connection redirection
Prior to SQL Server 2019, the availability group listener and the corresponding cluster resource redirect user traffic
to the primary replica to ensure reconnection after failover. SQL Server 2019 continues to support the AG listener
functionality, and adds replica connection redirection for scenarios that cannot include a listener. There are for
example cases where the cluster technology that SQL Server availability groups integrates with does not offer a
listener like capability. This new feature will also make multi-subnet configurations in the cloud less prone to
errors and easier to troubleshoot. It will also ensure transparent reconnection of the client connections in specific
clustering scenarios.
Enhanced database-level health for AGs
Database-level health detection for AG databases has been improved to detect additional errors conditions
related to the availability of a database and initiate a failover (or restart) of the AG resource if required.
New AG Wizard
A new Availability Group Wizard offers read-only routing configuration capabilities using SQL Server Management
Studio 18.x
Accelerated Database Recovery
Accelerated Database Recovery (ADR) is a new SQL database engine feature that greatly improves database
availability, especially in the presence of long running transactions, by redesigning the SQL database engine
recovery process. With ADR, long running transactions do not impact the overall recovery time, enabling fast and
consistent database recovery irrespective of the number of active transactions in the system or their sizes.
Microsoft SQL Server 2019 10
Accelerated Database Recovery supports instantaneous transaction rollback, irrespective of the time that the
transaction has been active or the number of updates that has performed. With ADR, the transaction log is
aggressively truncated, even in the presence of active long running transactions, which prevents it from growing
out of control.
Accelerated Database Recovery process
Accelerated Database Recovery introduces new SQL database engine mechanisms such as Persistent Version Store
(PVS) for persisting the row versions generated in the database itself instead of the traditional TempDB version
store. PVS enables resource isolation as well as improves availability of readable secondaries.
To read more about Accelerated Database Recovery, see the documentation page.
Online index maintenance and resumable operations
Online index operations allow concurrent user access to the underlying table or clustered index data and any
associated nonclustered indexes during these index operations. For example, while a clustered index is being
rebuilt by one user, that user and others can continue to update and query the underlying data. Online index
operations are recommended for business environments that operate 24 hours a day, seven days a week, in which
the need for concurrent user activity during index operations is vital.
SQL Server 2019 now supports online clustered columnstore index build and rebuild. Workload will not be
blocked and all changes made on the underlying data are transparently added into the target columnstore table.
sp_estimate_data_compression_savings now has two new options: COLUMNSTORE and
COLUMNSTORE_ARCHIVE which will allow you to estimate the space savings if a columnstore index is created on
the table using either standard or archive columnstore compression. Additionally, online rowstore index rebuild is
now a resumable operation.
When enabling Transparent Data Encryption (TDE) on a database, SQL Server must perform an encryption scan
that reads each page from the data files into the buffer pool, and then writes the encrypted pages back out to
disk. To provide the user with more control over the encryption scan, SQL Server 2019 introduces suspend and
Microsoft SQL Server 2019 11
resume syntax in T-SQL so that you can pause the scan while the workload on the system is heavy, and then
resume the scan later.
HA with remote storage on Kubernetes
With this option for high availability, you may use a container orchestrator, such as Kubernetes, instead of a
shared disk failover cluster and deploy SQL Server in a configuration like a shared disk FCI; however, you will use
the container orchestrator capabilities instead of a failover cluster.
This deployment model does not require specific enhancements from a SQL Server perspective, and it is not a new
capability in SQL Server 2019—a tutorial is available on how to implement this configuration using SQL Server
2017.5 However, SQL Server 2019 enables enhanced instance health check monitoring using the same operator
pattern as Availability Group health checks.
Failover with containers and Kubernetes
Enterprise security and compliance
The stakes are higher than ever for ensuring customer privacy, data security, and industry compliance. In the past
5 years, data security breaches have increased 67%.6 Each of those breaches cost organizations, on average, $4
million USD, including the direct results of criminal activity as well as fees or penalties incurred and lost business
5 https://docs.microsoft.com/en-us/sql/linux/tutorial-sql-server-containers-kubernetes?view=sql-server-linux-2017
6Accenture, Ninth Annual Cost of Cybercrime Study, https://www.accenture.com/us-en/insights/security/cost-cybercrime-study
Microsoft SQL Server 2019 12
from customers who no longer trust them. Within six months of a cyber-attack, it’s been shown that 60% of small
businesses are forced to close.7
Governments and agencies have stepped in to help by developing and
enforcing strict industry regulations, such as PCI-DSS, HIPAA, GDPR, that
require organizations to institute safe practices for handling sensitive
customer data. Diligent companies that are already working hard to
avoid security breaches must also navigate complicated regulations,
updates, and changes. That’s a big job for database administrators
(DBAs) who oversee hundreds or even thousands of databases and need
to be able to identify the sensitive data within them all.
It’s no wonder businesses look for help from technology providers with a proven security record. National Institute
of Standards and Technology maintains a database on the number of security vulnerabilities documented for
major commercial and open source databases. For nine years, SQL Server has had the fewest vulnerabilities of any
of its competitors. The reason for this is that we introduce new and enhanced security technologies with each
release of SQL Server with the goal of helping you protect all sides of your business from threats.
With that in mind, SQL Server 2019 builds on the enterprise security features that have been tested, proven, and
refined over the years, such as:
• Transparent Data Encryption (TDE) for real-time encryption and decryption of the data and log files
• Always Encrypted for protecting sensitive data and making encryption transparent to applications
• Dynamic Data Masking for limiting sensitive data exposure by masking it to non-privileged users
• Row-Level Security to use group membership or execution context to control access to rows in a table
• SQL Server Auditing for enabling, storing, and viewing audits on various server and database objects
The latest release of SQL Server 2019 includes more feature enhancements and tooling that make it even easier
for your database administrators and information protection officers to secure sensitive data and comply with
data protection policies and legislation.
SQL Data Discovery and Classification
Added in SQL Server Management Studio (SSMS) 17.5, the SQL Data Discovery and Classification tool allows you
to classify columns in your database that contain sensitive information. You can classify columns by the type of
information in them—names, addresses, social security numbers, and so on—and by their data sensitivity level—
including levels such as public, general, and confidential. You can easily generate reports from the classification
you have applied for the purpose of meeting statutory and regulatory requirements, such as EU GDPR.
SQL Vulnerability Assessment
SQL Vulnerability Assessment is an easy to use tool that can help you discover, track, and remediate potential
database vulnerabilities. It will give you visibility into your security state, and includes actionable steps to resolve
security issues and enhance your database security. It can help you to meet compliance requirements that require
database scan reports and data privacy standards. It will also help you with monitoring a dynamic database
environment where changes are difficult to track. SQL Vulnerability Assessment is available on SQL Server
7Inc.com, https://www.inc.com/joe-galvin/60-percent-of-small-businesses-fold-within-6-months-of-a-cyber-attack-heres-how-to-protect-
yourself.html
All editions of SQL Server 2019
include enterprise-level security
features built in—even the free
SQL Server Express edition
Microsoft SQL Server 2019 13
Management Studio (SSMS) v17.4 or later. Vulnerability assessment is also available for Azure SQL Database
instances through the Azure Portal.
Always Encrypted with Secure Enclaves
Always Encrypted enabled a big leap forward for enterprise security, especially in the cloud, by making it possible
to store and transmit sensitive data without worrying about data handlers accessing it unless they had permission.
Always Encrypted allows clients to encrypt sensitive data inside client applications and never reveal the encryption
keys to the database engine. As a result, Always Encrypted provides a separation between those who own the data
and can view it, and those who manage the data but should not have access to it.
Always Encrypted makes encryption transparent to applications. An Always Encrypted enabled driver installed on
the client computer achieves this by automatically encrypting and decrypting sensitive data in the client
application. The driver encrypts the data in sensitive columns before passing the data to the database engine, and
automatically rewrites queries so that the semantics to the application are preserved. Similarly, the driver
transparently decrypts data, stored in encrypted columns, contained in query results.
In some cases, the client-side aspect of the encryption presents challenges on the server side. An application must
use an Always Encrypted enabled client driver to access data stored in an encrypted column in plaintext, which
means that some server-side actions will not work. Also, with Always Encrypted, cryptographic operations or rich
computations require the movement of data out of the database so they can be carried out in the application
layer. And, equality comparison is the only operation the SQL Server Engine can perform on encrypted data (which
is only available with deterministic encryption).
With SQL Server 2019, we are introducing Always Encrypted with secure enclaves. This enhancement provides
additional functionality to Always Encrypted. A secure enclave is a protected region of memory within the SQL
Server process, and acts as a trusted execution environment for processing sensitive data inside the SQL Server
engine. A secure enclave appears as a black box to the rest of the SQL Server and other processes on the hosting
machine. There is no way to view any data or code inside the enclave from the outside, even with a debugger.
With Always Encrypted with secure enclaves, you no longer have to move data out of the database for initial data
encryption or key rotation. Instead, you issue in-place encryption, and these actions are done inside the secure
enclave. How does this work? Basically, when it processes a query, SQL Server 2019 checks to see if any encrypted
data operations need a secure enclave. If so, the client driver sends the secure enclave the column encryption keys
for those operations over a secure channel and then submits the query and its encrypted parameters for
execution.
Always Encrypted with Secure Enclaves
Microsoft SQL Server 2019 14
With secure enclaves, Always Encrypted protects the confidentiality of sensitive data by removing the need to
move the data outside the database during initial encryption of the database, or when rotating a column
encryption key. Always Encrypted with secure enclaves will also allow rich computations or operations on
encrypted columns, including pattern matching (the LIKE predicate) and range comparisons.
To read the full requirements or learn more about Always Encrypted with secure enclaves, review the
documentation here.
Certificate Management functionality in SQL Server Configuration Manager
SSL/TLS certificates are widely used to secure access to SQL Server. With earlier versions of SQL Server,
organizations with large SQL Server estates have devoted considerable effort to maintaining their SQL Server
certificate infrastructure—often through developing scripts and running manual commands. With SQL Server
2019, certificate management is integrated into the SQL Server Configuration Manager UI, simplifying common
tasks such as:
• Viewing and validating certificates installed in a SQL Server instance
• Reporting on certificates close to expiration
• Deploying certificates across machines that participate in Always On Availability Groups (from the node
holding the primary replica)
• Deploying certificates across machines that participate in a failover cluster instance (from the active node)
The user executing these tasks requires administration privileges in all the affected nodes.
SQL Server on Linux, Containers, and Kubernetes
SQL Server 2017 on Linux marked the first time SQL Server was available on a platform other than Windows, and it
broke all download records in the history of SQL Server products within the first few months. It’s the same SQL
Server database engine, with many similar features and services regardless of your operating system. SQL Server
on Linux is very efficient in terms of deployment time and patching. SQL Server on Linux and containers caters to
customers and developers running Linux and containers as part of DevOps, providing them with the ability to
leverage the power of SQL Server on any platform.
SQL Server 2019 brings the Linux version closer to feature parity with SQL Server on Windows with the addition of
key capabilities, like support for replication, Microsoft Distributed Transaction Coordinator (MSDTC), PolyBase and
Machine Learning Services as well as OpenLDAP support for third-party Active Directory providers.
Replication
In SQL Server 2019 on Linux, instances can participate in transactional, merge, and snapshot replication topologies
in the publisher, distributor, or subscriber roles.
Change Data Capture (CDC)
Now available in SQL Server 2019 on Linux, Change Data Capture (CDC) records insert, update, and delete activity
that is applied to a SQL Server table. This makes the details of the changes available in an easily consumed
relational format. CDC will create a reliable stream of change data that is structured so that consumers can apply it
to dissimilar target representations of the data.
Microsoft SQL Server 2019 15
Distributed transactions
SQL Server on Linux instances can now initiate and participate in distributed transactions. This is achieved with a
Linux version of the Microsoft Distributed Transaction Coordinator (MSDTC) that runs in the SQL Server process to
enable it to participate in distributed transactions.
Machine Learning Services on Linux
SQL Server Machine Learning Services lets you execute Python and R scripts in-database. You can use it to prepare
and clean data, do feature engineering, and train, evaluate, and deploy machine learning models within a
database The feature runs your scripts where the data resides and eliminates transfer of the data across the
network to another server or to the desktop of a data scientist. Native scoring using PREDICT function allows you
to generate prediction values or scores for new data inputs in real-time.
With the release of SQL Server 2019, we have added support for Machine Learning Services on Linux. Machine
Learning Services is also supported on Linux containers.
Data virtualization with PolyBase on Linux
PolyBase, the data virtualization functionality in SQL Server, has been available on Windows since 2016. It is now
available on Linux, enabling you to create external tables using connectors that link to even more data stores,
including SQL Server, Oracle, Teradata, MongoDB, or any data source with an ODBC driver. More information
about PolyBase is available in the Data Virtualization section of this paper.
Automatic configuration of TempDB files now available on Linux
By default, a new installation of SQL Server on Linux creates multiple TempDB data files based on the number of
logical cores (with up to 8 data files). This behavior is similar to the default SQL Server installation on Windows.
Microsoft Container Registry
The primary publishing location for SQL Server container images is the new Microsoft Container Registry (at
mcr.microsoft.com)—now the official container registry for the distribution of Microsoft product containers. In
addition to Ubuntu-based containers, we now also have Red Hat Certified container images. SQL Server 2019 also
introduces the ability to create safer containers by starting the SQL Server process as non-root user by default.
SQL Server on Kubernetes
In SQL Server 2019 on Linux containers, shared storage HA is managed by a container orchestrator such as
Kubernetes. If you’re running SQL Server in Kubernetes, you can build an HA/DR orchestration with no clustering
required.
Enhancing the developer experience
Language Extensions and support for Java
In addition to R and Python runtimes, SQL Server 2019 adds Language Extensions. Language Extensions is a
feature of SQL Server used for executing external code. The relational data can be used in the external code using
the extensibility framework. Code execution is isolated from the core engine processes, but fully integrated with
SQL Server query execution. Language Extensions let you execute code where the data resides, eliminating the
need to pull data across network.
Microsoft SQL Server 2019 16
In SQL Server 2019, Java is supported. The default Java runtime is Zulu Open JRE supported by Microsoft. You can
also use another Java JRE or SDK. You will be able to create your own Java classes, functions, and business logic
that is not available in Transact-SQL. The new Microsoft Extensibility SDK for Java for SQL Server provides an
interface for the Java language extension that is used to exchange data with SQL Server and to execute Java code
from SQL Server.
UTF-8 Support
SQL Server 2019 includes full support for the widely
used UTF-8 character encoding as an import or
export encoding, or as database-level or column-
level collation for text data.
UTF-8 is allowed in the CHAR and VARCHAR
datatypes, and is enabled when creating or changing
an object’s collation, to a collation with the “UTF8”
suffix, such as LATIN1_GENERAL_100_CI_AS_SC to
LATIN1_GENERAL_100_CI_AS_SC_UTF8. UTF-8 is only
available to windows collations that support
supplementary characters, as introduced in SQL Server 2012. Note that NCHAR and NVARCHAR allow UTF-16
encoding only, and remain unchanged.
Significant storage savings can also be achieved, depending on the character set in use. For example, changing an
existing column data type from NCHAR(10) using UTF-16 to CHAR(10) using an UTF-8 enabled collation, translates
into nearly 50% reduction in storage requirements. This is because NCHAR(10) requires 22 bytes for storage,
whereas CHAR(10) requires 12 bytes for the same Unicode string.
SQL Graph capabilities
Graph data offers developers the ability to represent natural relationships between entities using many-to-many
relationships that don’t fit well into tables and a more traditional relational schema. This is especially useful for
handling data from non-relational data sources such as social media and online retail.
SQL Server 2019 supports UTF-8 character encoding
Achieve highly significant storage savings just by changing
your character set.
For example, NCHAR(10) NCHAR(10) using UTF-16
requires 22 bytes for storage, whereas CHAR(10) using an
UTF-8 enabled collation requires 12 bytes for the same
Unicode string, resulting in nearly 50% storage reduction.
Microsoft SQL Server 2019 17
Graph data represents relationships between entities
SQL Graph provides Graph database capabilities to SQL Server, allowing easier modelling of data sets with
many—often complex—relationships as a set of nodes (or vertexes) and edges (or relationships). It was first
introduced in SQL Server 2017, but with SQL Server 2019 come many new enhancements, making it more
powerful and easier to work with. Some of the enhancements include support for edge constraints, support of
MATCH predicates in a MERGE statement, SHORTEST_PATH inside MATCH, and support for derived tables or view
aliases in graph match query.
Machine Learning Services
Machine Learning Services is a feature in SQL Server that gives the ability to run Python and R scripts with
relational data. You can use open-source packages and frameworks, such as PyTorch and TensorFlow, and the
Microsoft Python and R packages for predictive analysis and machine learning. The scripts are executed in-
database without moving data outside SQL Server or over the network. We have made the following
improvements in SQL Server 2019 Machine Learning Services:
Input data partitioning for Machine Learning Services
Without changing your R or Python scripts, you can process data at the table partition level, enabling you to train
a model for each table partition and parallelize model training per partition.
Failover cluster support for Machine Learning Services
You can install SQL Server 2019 Machine Learning Services (In-Database) on a Windows failover cluster to meet
your requirements for redundancy and uptime should your primary server fail over.
More features based on customer feedback
Many of the new features and enhancements mentioned in this white paper have been added based on customer
feedback. There are dozens of additional improvements in SQL Server 2019 Database Engine that make
configuration, management, performance, and troubleshooting of SQL Server easier and more intuitive for DBAs.
Some of them include:
• Columnstore stats in DBCC CLONEDATABASE
• Diagnostics for auto stats blocking
• Page resource waits troubleshooting with new built-in T-SQL
• Custom capture policy for the Query Store
• MAXDOP and memory configuration during setup
• Table and column names and truncated value in truncation error message
• And many more
You can read more about what’s new in SQL Server in the online documentation.
Data virtualization
Almost all modern enterprises have a mixture of applications and relational and non-relational data stores—often
from several different vendors. This presents a challenge for developers, data scientists, and business analysts who
want to combine these disparate sources for analysis and reporting.
Microsoft SQL Server 2019 18
In the past, the only way to reliably do this was to move copies of the data to a single platform via extract-
transform-load (ETL) processes. However, ETL isn’t right for every scenario, especially when a data warehouse isn’t
in the long-term plan because:
• ETL is expensive to develop, maintain, and support.
• Data from an ETL pipeline lags 2-7 days behind business in 80% of cases.8
• Running multiple copies of data poses security threats and requires expensive disk space.
Data virtualization is a fast, cost-effective alternative to ETL. Based on the newest technology, which has been
added to PolyBase, data virtualization works by integrating data from disparate sources, locations, and formats,
without replicating or moving the data to create a single "virtual" data layer in SQL Server. The virtual data layer—
sometimes referred to as a data hub—allows users to query up-to-date data from many sources through a
consistent interface controlled from a single location.
Data movement by data virtualization is further enabled by enhancements to PolyBase in SQL Server 2019
PolyBase enhancements
First added to the SQL Server database engine in SQL Server 2016, PolyBase enables applications and users to
query big data stored in HDFS-compatible Hadoop distributions and file systems such as HortonWorks, Cloudera,
and Azure Blob Storage by using T-SQL to define an external table to represent HDFS data in SQL Server. Users or
applications can run T-SQL queries that reference the external table as if it were a normal SQL Server table. When
the query is executed, data from the external data source is retrieved and returned to the user, but it is not stored
in persistent storage in SQL Server.
Using this approach of querying data from the source ensures that there are no data latencies, duplicated storage
costs, or data-quality issues which may be introduced using ETL pipelines. Once you have created external tables
in SQL Server, you can control access to data sources by granting access to external tables to Active Directory
users and groups, thus centralizing the data access policies to a single location.
8 3rd Platform Information Management Requirements Survey, IDC, October, 2016, n=502
Microsoft SQL Server 2019 19
SQL Server 2019 extends the capabilities of PolyBase with new connectors to create external tables that link to a
variety of data stores. Given these enhancements to PolyBase, a big data cluster can act as a data hub by
integrating structured and unstructured data from across the entire data estate– SQL Server, Azure SQL DB, Azure
SQL DW, Oracle, Teradata, MongoDB, Azure Cosmos DB, HDFS, and more–using familiar programming
frameworks and data analysis tools.
The latest updates to PolyBase make it easier than ever to optimize query performance by automatically pushing
computations to Hadoop when necessary and scaling out compute resources on demand. In a push-computation
scenario, only the results of the query are returned to SQL Server.
You can further increase performance and capacity using PolyBase scale-out groups, which leverage many SQL
Server instances to a PolyBase group controlled by a head node. The PolyBase queries are issued to the head
node, which distributes the workload across the PolyBase group’s compute instances and enables parallel
ingestion and processing of external data.
PolyBase scale-out groups querying Hadoop clusters
SQL Server Big Data Clusters
Many enterprises use both big data and traditional relational data to understand their business and gain new
insights. The challenge is that big data is stored in one place—the Apache Hadoop Distributed File System
(HDFS)—and enterprise data in another—relational database systems. These different storage types use different
technology and systems for management, security, structure, and queries. To be able to take advantage of big and
relational data, complicated solutions have developed over the years that include moving data from one place to
another and combining it for analysis or using special technology to run programming languages and open
source code on both types to build analytics dashboards and machine learning models.
The introduction of Big Data Clusters in SQL Server 2019 solves the compute and storage challenges of scaling to
petabytes and exabytes of data while offering more flexible processing through Spark, which is an open-source
engine that can process streaming data from sensors, IoT, or financial systems, and machine learning tasks. Big
Microsoft SQL Server 2019 20
Data Clusters combine multiple instances of SQL Server with Spark and HDFS, making it much easier to unite
relational and big data for use in reports, predictive models, applications, and AI.
Containers, big data services, and Kubernetes provide the framework for the architecture of Big Data Clusters,
which take the form of a compute cluster of containers running SQL Server and big data services and marshalled
by a SQL Server master instance. These scalable clusters contain SQL Server, Spark, and HDFS containers running
on Kubernetes as a single package supported by Microsoft. Kubernetes is an open-source orchestrator that makes
scaling container deployments easier. Pools enable independent, customizable compute and storage scale-out.
Big Data Clusters run as pods, which are logical groups of one or more containers and associate resources needed
to run an application, in different types of pools:
• Compute pools—consist of one or more compute pods running in Kubernetes.
• Storage pools—provide scalable storage tiers along with the co-located compute for SQL Server and Spark
right next to the data.
• Data pools—either cache data from an external source or store an incoming stream of append-only data. The
data is partitioned and distributed across all the SQL Server instances in the pool.
• Master pools—can be either singleton SQL Servers or SQL Servers deployed as multiple instances in an
Always On availability group for high availability and read scale out. This SQL Server instance is where read-
write OLTP or dimensional data is stored in a big data cluster.
• Spark pools—enable Spark compute separate from HDFS for better resource management and optimize the
type of hardware the two pools require.
A high-level big data cluster architecture on SQL Server 2019
Easy data integration
Through the SQL Server Big Data Clusters architecture, Microsoft has created a way for you to integrate, manage,
and analyze all your data using the preferred skills and tools of your data engineers, data analysts, and savvy
business users. The value of the big data greatly increases when it is combined with the high-value data stored in
Microsoft SQL Server 2019 21
SQL Server through reports, dashboards, and applications. SQL Server 2019 Big Data Clusters makes all your data
available in one all one integrated system and accessible through big data tools or SQL Server tools.
Scenarios enabled by SQL Server Big Data Clusters
SQL Server Big Data Clusters offer flexibility in how you interact with your big data. They are especially useful in
four key scenarios.
Data virtualization
In a big data cluster, you can combine data from external data sources without moving or replicating it by
leveraging the enhancements to SQL Server 2019 PolyBase. These enhancements enable the big data cluster to
act as a data hub for integrating structured and unstructured data from across the entire data estate—SQL Server,
Oracle, Teradata, MongoDB, HDFS, and more–using familiar programming frameworks and data analysis tools.
Scale-out data marts
In a big data cluster, data from external data sources can be partitioned and cached across all the SQL Server
instances in a data pool, creating a “scale-out data mart”. The scale-out data mart can combine data from multiple
external data sources and tables, making it easy to integrate and cache combined data sets from multiple external
sources.
Microsoft SQL Server 2019 22
A shared data lake
For persistence in a big data cluster, SQL Server instances in the storage pool can read from and write data to
parquet and CSV files in HDFS. Applications and analytics that query the data through the SQL Server master
instance and Spark jobs can enable different analytics scenarios, using tools, languages, and skills of choice. Both
SQL Server and Spark can read and write these files, creating a shared data lake accessible by many different types
of users, tools, and systems. Using HDFS tiering, you can cache data from the source to big data clusters to
expand existing data lakes.
Integrated AI and Machine Learning
Now you can enable AI and machine learning tasks on the data stored in HDFS storage pools and the data pools
using Spark and/or built-in AI tools in SQL Server like R, Python, Scala, or Java. Big Data Clusters provide all the
tools and systems to ingest, store, and prepare data for analysis as well as to train the machine learning models,
store the models, and operationalize them.
• Ingest: Data can be ingested using Spark Streaming by inserting data directly to HDFS through the HDFS API
or by inserting data into SQL Server through standard T-SQL insert queries.
Microsoft SQL Server 2019 23
• Store: The data can be stored in files in HDFS, partitioned and stored in data pools, or stored in the SQL
Server master instance in tables, graph, or JSON/XML.
• Prep and train: Either T-SQL or Spark can be used to prepare data by running batch jobs to transform the
data, aggregate it, or perform other data wrangling tasks. Data scientists can choose to use either SQL Server
Machine Learning Services in the master instance to run R, Python, or Java model training scripts or to use
Spark. In either case, the full library of open-source machine learning libraries, such as TensorFlow or Caffe,
can be used to train models.
• Model and serve: Once the models are trained, they can be operationalized in the SQL Server master
instance using real-time, native scoring via the PREDICT function in a stored procedure; or you can use batch
scoring over the data in HDFS with Spark. Alternatively, using tools provided with the big data cluster, data
engineers can easily wrap the model in a REST API and provision the API + model as a container on the big
data cluster as a scoring microservice for easy integration into any application.
This entire pipeline takes place in the context of a big data cluster. The data never leaves the security and
compliance boundary to go to an external analytics server or a data scientist’s laptop. The full power of the
hardware underlying the big data cluster is available to process the data, and the compute resources can be
elastically scaled up and down as needed.
For more information, read SQL Server Big Data Clusters documentation.
Enterprise Information Management
SQL Server Integration Services
SQL Server Integration Services is a platform for building enterprise-level data integration and data
transformations solutions. Use Integration Services to solve complex business problems by copying or
downloading files, loading data warehouses, cleansing and mining data, and managing SQL Server objects and
data.
Integration Services can extract and transform data from a wide variety of sources such as XML data files, flat files,
and relational data sources, and then load the data into one or more destinations. Integration Services includes a
rich set of built-in tasks and transformations, graphical tools for building packages, and the Integration Services
Catalog database, where you store, run, and manage packages. You can use the graphical Integration Services
tools to create solutions without writing a single line of code. You can also program the extensive Integration
Services object model to create packages programmatically and code custom tasks and other package objects.
For more information about the capabilities and features of SSIS, see SQL Server Integration Services. Note that
the SSIS engine runs on Windows and Linux.
Master Data Services
The Master Data Services allows you to manage a master set of all your data. Using Master Data Services, you can
organize data into models, creating rules for updating the data including permissions for who on your team
performs those updates. The master data set can be shared using an Excel add-in and the Master Data Manager
(MDM) web application. In SQL Server 2019, all former dependences on Silverlight components have been
replaced with HTML controls. Additionally, Master Data Services now supports databases running on Azure SQL
Database Managed Instance.
Microsoft SQL Server 2019 24
Business Intelligence
Analysis Services
SQL Server Analysis Services (SSAS) provides modern data connectivity and transformation capabilities with
support for Power BI data sources as well as some advanced BI modeling capabilities, such as data mashup
transformations, drill-throughs, and ragged hierarchies. SSAS can be configured to work with in-memory tabular
models, or traditional multidimensional OLAP cubes.
SSAS provides several approaches for creating a business intelligence semantic model, including: Tabular,
Multidimensional (OLAP cubes), and Power Pivot for SharePoint. Tabular and multidimensional models use
imported data from external sources.
Customers using tabular models in SQL Server Analysis Services can leverage Azure Analysis Services for cloud-
scale modelling and querying.
In SQL Server 2019, we made improvements for customers leveraging tabular models to improve management
and speed up their time to insights.
Governance setting for Power BI cache refreshes
The Power BI service caches dashboard tile data and report data for initial load of Live Connect report, which
causes an excessive number of cache queries to be submitted to SSAS and may overload the server in extreme
cases. With this release, the ClientCacheRefreshPolicy property allows you to override this behavior at the server
level. To learn more, see General Properties.
Dynamic formatting of measures using calculation groups
This feature allows you to conditionally change format strings for measures with calculation groups. For example,
with currency conversion, a measure can be displayed using different foreign currency formats.
Online attach
Attach a tabular model as an online operation. Online attach can be used for synchronization of read-only replicas
in on-premises query scale-out environments.
Other new capabilities include:
• MDX query support for tabular models with calculation groups enables faster queries where calculation
groups are used.
• Many-to-many relationships in tabular models that allow multiple many-to-many relationships to be stored
and managed in a single model.
• Property settings for resource governance that allow you to choose how much resource you would like
your analytical model to use.
• Query interleaving for enabling efficient and faster query management by dynamically assigning priority to
queries ensuring that all queries run in the most optimal time frame.
For more information about the capabilities of Analysis Services, see Analysis Services. Note that Analysis Services
is currently a Windows-only feature.
Microsoft SQL Server 2019 25
Reporting Services
SQL Server Reporting Services (SSRS) provides a modern, on-premises solution for creating, deploying, and
managing reports in your organization. Since SQL Server 2016, SSRS has been bringing modern design to
enterprise reporting. You can create mobile reports optimized for smartphones and tablets to bring the power of
data to more users in the organization, in addition to creating modern paginated reports. The native Power BI
apps for mobile devices for Windows 10, iOS, Android, and HTML5 allow easy access to mobile reports and
dashboards both in online and offline mode.
This server-based reporting platform includes a complete set of tools for creating, managing, and delivering
reports and APIs that enable developers to integrate or extend data and report processing in custom applications.
SSRS supports both traditional paginated reports, in addition to mobile reports and dashboards.
For more information about the capabilities of Reporting Services, see Reporting Services. Note that Reporting
Services is currently a Windows-only feature.
Power BI Report Server
Power BI is a collection of services and features that enable collaborative and self-service data analysis, sharing,
and visualization. Power BI can connect to a mix of on-premises and online data sources, which can be
automatically refreshed on a fixed schedule.
With Power BI Desktop, users can self-serve to design reports, dashboards, and data models that connect to a
broad range of data sources. For more information about the capabilities of Power BI Desktop, see the Power BI
Desktop product page.
With the Power BI service, users can store and access Power BI reports from the Microsoft public cloud, using a
browser or mobile devices. The Power BI Premium service allows users to share and collaborate on Power BI
reports. For more information about the capabilities of Power BI, see the Power BI product page.
Power BI Report Server builds on SQL Server 2017 Reporting Services to allow reports designed in Power BI
Desktop to be deployed on an on-premises server rather than from the Power BI cloud service. Power BI Report
Server allows organizations with data protection policies that would otherwise bar them from using the public
cloud Power BI service to reap the benefits of Power BI reports.
A subscription to Power BI Premium service or SQL Server Enterprise Agreement with Software Assurance includes
licensing for Power BI Report Server, allowing organizations to start building on-premises Power BI reports that
they can later migrate to the cloud.
For more information about the capabilities of Power BI Report Server, see the Power BI Report Server product
page. Note that Power BI Report Server is currently a Windows-only feature.
Microsoft SQL Server 2019 26
Management and Developer Tools for SQL Server
You need great tools to manage your databases. While your databases can be running in the cloud, on the edge,
on-premises, on Windows, or on Linux, your tool doesn’t need to run on the same platform as the database.
Microsoft has made several tools available for DBAs, data engineers, data scientists, and developers. This section
mentions some of the available tools for working with your SQL databases.
Azure Data Studio
Azure Data Studio provides a centralized way to administer queries across all your SQL Server instances running
on any platform—including instances on Azure SQL Data Warehouse and Azure SQL Database. Its integrated tools
let you customize the workflows you use most often in your environment to save you time with query editing.
Several big improvements have been made to Azure Data Studio to make life easier for developers, one of which
is the addition of Notebooks. When used in conjunction with the SQL Server Big Data Clusters, Notebooks can
interact with a big data cluster using the Scale-out Data Management extension to improve their manageability.
Azure Data Studio is based on the same platform as Visual Studio Code and includes Git integration and
extensibility out of the box; several Microsoft, 3rd party, and community extensions are already available. It runs on
Windows, Linux, and macOS.
Find out more about Azure Data Studio on http://aka.ms/azuredatastudio.
SQL Server Management Studio
Starting with version SQL Server Management Studio (SSMS) 18.3, SSMS provides support for SQL Server 2019.
SSMS is a free Windows-only desktop application for administering and querying SQL Server instances running on
any platform, Azure SQL Data Warehouse, and Azure SQL Database instances. Since 2016, SQL Server
Management Studio (SSMS) has had its own release cycle independent of SQL Server, allowing for more frequent
updates. While SSMS runs only on Windows, you can use it to manage instances of SQL Server on Linux too.
Find out more about SQL Server Management Studio on Microsoft Docs.
Visual Studio Code
Visual Studio Code is a lightweight but powerful source code editor which runs on your desktop and is available
for Windows, macOS and Linux. It comes with built-in support for JavaScript, TypeScript and Node.js and has a
rich ecosystem of extensions for other languages (such as C++, C#, Java, Python, PHP, Go) and runtimes (such as
.NET and Unity).
The mssql extension for Visual Studio Code allows you to develop applications for SQL databases. It improves the
developer productivity by making it easier than ever to connect to a SQL database, query, and view the results.
Find out more about Visual Studio Code at https://code.visualstudio.com/ and how to work use the mssql Visual
Studio Code extension on Microsoft Docs.
CLI/shell tools
Several command-line/shell tools are available for interacting with SQL Server, including:
• sqlcmd—used to execute Transact-SQL commands from the command-line, either dynamically or from a file.
Microsoft SQL Server 2019 27
• bcp—used to bulk-import and bulk-export data to SQL Server instances.
• sqlpackage—used to script databases from and deploy databases to dacpac packages to simplify database
deployment and migration.
Cross-platform versions of sqlcmd and bcp are already available. A cross-platform release of sqlpackage is
currently in preview.
mssql-cli is a new, cross-platform, command-line/shell tool designed to be a more user-friendly shell
environment for interacting with SQL Server; mssql-cli improves on sqlcmd by adding modern features such as
syntax highlighting, auto-completion, and adaptive result-set formatting. mssql-cli is built on the open-source
dbcli project and is dependent on the Python language and the Python package management tool pip.
To read more about SQL query and management tools for SQL Server, please read the Tools documentation page.
Migrating and Modernizing to SQL Server 2019
You can take advantage of all the great new features and enhancements in SQL Server 2019 through your choice
of migration path, using free migration tools, and compatibility certification we offer to help you get up and
running quickly.
Choose the right migration path for you
Your options for migrating to SQL Server 2019 include
on-premises deployment, SQL Server 2019 on Azure VMs,
and Azure SQL Database Managed Instance. If you’re
running Oracle, SAP Sybase, IBM DB2, and/or MySQL, you
can also migrate to SQL Server 2019 with the help of a
free automation tool covered in the following section.
Take advantage of free migration tools
We offer free tools to help ease your migration that include guidance and best practices from Microsoft
engineers. These tools include:
• Data Migration Assistant (DMA)—allows you to upgrade to a modern data platform by detecting
compatibility issues that can impact database functionality on your new version of SQL Server or Azure SQL
Database. It recommends performance and reliability improvements for your target environment. It will also
move your database schema and data, and uncontained objects from your source server to your target server.
• Database Experimentation Assistant (DEA)—an experimentation solution for SQL Server upgrades. It helps
evaluate a targeted version of SQL for a given workload by capturing and replaying production database
workloads through automated setup.
• Query Tuning Assistant in SQL Server Management Studio v18—a new tool that will guide users through
the recommended workflow to keep performance stability during upgrades to newer SQL Server versions.
• SQL Server Migration Assistant (SSMA) automates migrations from Oracle, SAP Sybase, IBM DB2, and
MySQL. Learn more about SSMA in the online documentation.
Get the best of SQL Server 2019 and the cloud
• SQL Server 2019 on Azure VMs (IaaS)
• Azure SQL Database Managed Instance (PaaS)
Microsoft SQL Server 2019 28
Compatibility Certification
Compatibility Certification makes it possible to upgrade and modernize a SQL Server database on-premises, in the
cloud, and on the edge, eliminating application incompatibility risks. Because the same Database Engine powers
both SQL Server and Azure SQL Database (including Managed Instance), a user database can be moved
seamlessly between the on-premises SQL Server and Azure SQL Database.
Using Compatibility Certification is a valuable approach to database modernization. By certifying based on
compatibility level, developers set the technical requirements for an application to be supported on SQL Server
and Azure SQL Database, but decouple the application lifecycle from the database platform lifecycle. This allows
companies to keep the SQL Server Database Engine upgraded as needed by lifecycle policies, as well as leveraging
new scalability and performance enhancements that are not code dependent, and connecting applications
maintain their functional status through upgrades.
As long as the application does not need to leverage enhancements that are only available in a higher database
compatibility levels, it is a valid approach to upgrade the SQL Server Database Engine and maintain the previous
database compatibility level, with no need to recertify an application
Read more about Compatibility Certification
Conclusion
The endless streams of data we create and collect in our world today present exciting opportunities for
businesses—and significant challenges. Long gone are the two-dimensional, relational-on-premises ways of using
data. You need flexible, open, powerful options for working with data that remove the tedious work from your
daily obligations to help you deliver more value to your business.
We designed SQL Server 2019 to be a unified data platform for combining all your data sources and types
through new scenarios made possible by data virtualization and SQL Server Big Data Clusters with HDFS and
Apache Spark. SQL Server 2019 empowers your in new ways through intelligence over any data and all your data.
SQL Server 2019 gives you’re the choice of development language, platform, and deployment location on-
premises, hybrid, in the cloud and on the edge. With SQL Server 2019 you will get the industry leading
performance with intelligent database, enterprise level security, and AI built in.
With the release of SQL Server 2019 on Linux, you’ll also see greater feature parity with the Windows version, like
support for Machine Learning Services. This will continue to grow as we develop even more ways of bringing the
capabilities of SQL Server database across platforms.
Additional resources
For more information about SQL Server 2019, see: aka.ms/ss19
For more information about Azure Data Studio, see: aka.ms/azuredatastudio
For SQL Server documentation, see: https://docs.microsoft.com/en-us/sql/sql-server/?view=sql-server-ver15
For SQL Server and Azure SQL Labs and Workshops, see: http://aka.ms/sqlworkshops
© 2019 Microsoft Corporation. All rights reserved.