Overview of SQL Server 2019 Features

By:   |   Comments (6)   |   Related: > SQL Server 2019


Microsoft recently announced the preview of SQL Server 2019 on September 24 at the Ignite 2018 Conference. Really! Yes, Microsoft is moving very fast with feature enhancements, improving security, performance, enhancing the capabilities of machine learning, data integration, etc. In this tip, we will see an overview of some of the exciting new features of SQL Server 2019.


As per the Microsoft SQL Server blog, "SQL Server 2019 creates a unified data platform with Apache Spark and Hadoop Distributed File System (HDFS) packaged together with SQL Server as a single, integrated solution".

To install SQL Server 2019, go to the Downloads page and it shows important features you'll love about SQL Server 2019.

SQL Server 2019 important features

To install SQL Server 2019 and click on 'Download now' from this page.

introducing sql server 2019

We can select the desired Operation system version to install SQL Server 2019. We will cover more on this in later tips. We can now build SQL Server on Windows, Linux, Docker, and Big Data Analytics containers with kubernetes images.

Install SQL Server 2019

Microsoft released a document displaying the top 10 reasons to choose SQL Server 2019.

Top 10 reasons to chose SQL Server 2019

Early Adoption Program for SQL Server 2019

There is an Early Adoption program for SQL Server 2019.  You can join the SQL Server 2019 early adoption program by signing up at this link Sign up Early Adoption Program. This program is especially for customers who can test the product thoroughly and provide recommendations and feedback to Microsoft to improve the product.

Once enrolled in the Early Adoption program, you get the following benefits:

  • We can test the latest features of SQL Server 2019 and get production support from Microsoft to fix any issues.
  • We can test and provide feedback to the Microsoft database engineering team for any feature enhancements and bugs.

Let's briefly explore some of the important features of the SQL Server 2019 preview.

Data Virtualization in SQL Server 2019

SQL Server 2019 provides a data virtualization solution which is an improvement to the ETL process. Data virtualization allows integrating data from different sources such as MongoDB, Oracle, DB2, Cosmos, and Hadoop Distributed File System (HDFS) without moving data around.

  • Eliminate the need of data movement: In the organization, we have a variety of the database servers which might include relational databases such as SQL Server, Oracle, MySQL, DB2, etc. and non-relational databases such as Mongo DB, Big data, etc. Normally in traditional business intelligence systems, we copy data and load the data into a reporting platform with extract-transform-load (ETL) processes. ETL processes have their own pros and cons which includes:
    • ETL process requires development. It could be a complex solution that might require more effort to maintain and support it.
    • ETL processes are relatively slow and might take a long time depending upon the data flow and logic in place.
    • We move data from different sources to SQL Server, transform the data, and introduce the necessary logic. If we are working with a large amount of data, it also requires more storage.
    • If we are dealing with production data or PII related data, it must be secure throughout the process.

The below image shows data virtualization options in SQL Server 2019.

data sources

Data virtualization integrates data from different sources and does not need replicating or moving the data. As shown above, a single virtual data layer delivers unified data services that can support multiple applications. In SQL Server 2019, we define this virtual data layer as Data hub or Data Lake. We can process data through this layer. This layer also works as a data controller between multiple data sources.

SQL Server 2019 allows us to integrate data from structured and unstructured data sources. We can now process diverse big data and relational data sources using Transact-SQL from SQL Server using PolyBase.

We can see below PolyBase support external databases.

(Image courtesy: Microsoft blogs)

Big Data Clusters in SQL Server 2019

SQL Server 2019 Big Data Clusters adds enhancements to PolyBase to improve the data virtualization experience between SQL Server and other database engines. It provides bi-directional integration with big data Hadoop and Apache Spark systems. We can easily manage the big data using the built-in tools with SQL Server 2019.

It offers three major pieces of functionality:

  • Data virtualization - Combine data from many sources without data movement. It improves security, performance to process data.
  • Managed SQL Server, Spark, and Data Lake - Store high volume data in a data lake and access it easily using either SQL or Spark. It is very easy to manage using management services, admin portal, and integrated security.
  • Complete AI platform - Easily feed integrated data and prepare the solution. This solution works to operate all models in one system.
Big Data Clusters SQL Server 2019
(Image courtesy: Microsoft blogs)

In SQL Server 2019, we can combine big data with the analytical database or traditional database system. This provides data scientists to access big data with simple T-SQL queries. Users can also use the Power BI to work with the data presented.

Additional New Features in SQL Server 2019

Here are some other features enhancements.

Support to Persistent Memory (PMEM) Devices

SQL Server 2019 provides support to Persistent Memory (PMEM) devices. SQL Server directly accesses the device, bypassing the storage stack of the operating system for the files placed on the PMEM device.

Columnstore Index Enhancements

SQL Server 2019 also provides enhancements to columnstore index features such as columnstore index maintenance, better metadata memory management, a low-memory load path for columnstore tables, and improved performance for bulk loading to columnstore indexes.

Resumable Online Index Creation

SQL Server 2019 also provides support for resumable online index creation similar to resumable online index rebuilds in SQL Server 2017.  Check out this tip about resumable online index creation.

Up to Five Synchronous Replica Pairs for Availability Groups

We can now configure up to five synchronous replicas in an Availability Groups AG (one primary and up to four secondary replicas) with automatic failover between these replicas.

Enable High Availability Configurations for SQL Server Running in Containers

With SQL Server 2019, we can configure Always on Availability Groups using Kubernetes as an orchestration layer.

Better Scale-out with Automatic Redirection of Connections Based on read/write Intent

In SQL Server 2019, client applications can connect to any of the replicas of the Availability Group. The connection redirects to the primary replica as per the AG configuration and connection string.

SQL Data Discovery and Classification

We have explored this feature in SSMS 17.5. In SQL Server 2019, SQL Data discovery and classification is integrated into the SQL Server engine with new metadata. This enables us to ensure GDPR and other compliance needs for our databases.

Always Encrypted with Secure Enclaves

SQL Server 2019 introduces the secure enclave technology. A secure enclave extends client applications, data trust to the server side. It secures the data from the malware and privileged users.

Certificate Management Functionality in SQL Server Configuration Manager

In SQL Server 2019, certificate management is integrated into the SQL Server Configuration Manager. We can view, validate the certificates being used in SQL Server instance. We can view and validate certificates installed in a SQL Server instance. This also provides detail about certification expiration dates. This feature helps to manage certificates in a better way. We can also deploy certificates for AG instances starting from the primary replica.

UTF-8 Support

SQL Server 2019 provides support for UTF-8 character encoding. We can now create a char or varchar column to store UTF-8 data. This feature improves data compatibility and performance improvements.

Vulnerability Assessment

We can use vulnerability assessment to track compliance of SQL Server instances and Azure SQL Database instances with recognized security best practices. We can implement using the reports shared by this tool. This provides easy to implement security compliance such as GDPR.

Enhancement in SQL Graph

In SQL Server 2017 we have explored the graph databases, some of the tips for reference are:

  • Graph processing with SQL Server
  • SQL Server 2017 Graph Database Example
  • SQL Server 2019 provides enhancements to include match support with T-SQL MERGE and edge constraints. We can now limit the type of nodes a given edge type can connect. This helps users to enforce restrictions on the edge table. It also maintains data integrity in their graph database.
  • SQL Server 2019 also support MATCH predicates to specify graph relationships in a single statement, instead of separate DML statements. This makes easy to query the graph database.
Next Steps

sql server categories

sql server webinars

subscribe to mssqltips

sql server tutorials

sql server white papers

next tip

About the author
MSSQLTips author Rajendra Gupta Rajendra Gupta is a Consultant DBA with 14+ years of extensive experience in database administration including large critical OLAP, OLTP, Reporting and SharePoint databases.

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips

Comments For This Article

Thursday, November 26, 2020 - 9:40:39 PM - Rajendra Back To Top (87855)
Go through the below Microsoft docs article for information about additional data files.


Friday, August 7, 2020 - 12:02:29 PM - Balaji Back To Top (86251)
I see there are 4 new system data files in the directory, but not in SSMS. What are these files all about.
model_msdbdata mdf file
model_msdblog log
model_replicatedmaster log
model_replicatedmaster mdf file.

Monday, December 2, 2019 - 9:39:23 AM - rajendrA Back To Top (83251)

Please check Python configuration and PATH in the environment variables

Wednesday, November 27, 2019 - 10:16:43 AM - Abhi Back To Top (83208)

Facing below issue with Python and R queries after SQL 2019 and configuration

any help please?

Msg 39012, Level 16, State 14, Line 1

Unable to communicate with the runtime for 'Python' script for request id: 9848A19B-085C-4329-B3F2-72E5CB3CA53C. Please check the requirements of 'Python' runtime.

STDERR message(s) from external script: 

Traceback (most recent call last):

  File "<string>", line 3, in <module>

  File "E:\Program Files\Microsoft SQL Server\MSSQL15.MSSQLSERVER\PYTHON_SERVICES\lib\site-packages\revoscalepy\computecontext\RxInSqlServer.py", line 657, in rx_sql_satellite_pool_call

    rx_sql_session_start(sessionDirectory = sessionDirectory, sessionId = sessionId, waitTime = waitTime)

  File "E:\Program Files\Microsoft SQL Server\MSSQL15.MSSQLSERVER\PYTHON_SERVICES\lib\site-packages\revoscalepy\computecontext\RxInSqlServer.py", line 640, in rx_sql_session_start

    rx_native_call("SqlSessionStart", params)

  File "E:\Program Files\Microsoft SQL Server\MSSQL15.MSSQLSERVER\PYTHON_SERVICES\lib\site-packages\revoscalepy\RxSerializable.py", line 375, in rx_native_call

    ret = px_call(functionname, params)

RuntimeError: Write error: expected 8 got 0

STDOUT message(s) from external script: 

Options function failed. Please see the console output for more information.

SqlSessionStart function failed. Please see the console output for more information.

Thursday, August 1, 2019 - 2:39:26 AM - Dave Boltie Back To Top (81925)

Useful article to read when considering moving to SQL Server 2019, thank you

Monday, April 15, 2019 - 11:41:52 PM - Hafizur Rahman Back To Top (79568)

Hi Rajendra,

Great post! Is there any new feature for SSRS?


get free sql tips
agree to terms