Introduction

On November 4th, 2019, during the Ignite conference at Orlando, Microsoft released the General Availability of its flagship product Microsoft SQL Server 2019. SQL 2019 provides various enhancements to its core database engine and offers integration with Big data (Apache Spark, Data Lake), Machine learning, Linux/container compatibility with Kubernetes.

Microsoft is very excited about this new release as it takes a big leap in the integration of the database engine with other exciting features and technologies.

We can download the General Availability release from the link by clicking on Try now:

SQL 2019 release history

In the following table, we can get information about SQL 2019 releases, build versions and released dates:

Let us explore in brief new features of SQL Server 2019.

Database engine enhancements

SQL Server 2019 supports various database engine enhancements over previous versions.

Compatibility

SQL 2019 has compatibility with SQL Server on Azure VMs, Azure SQL Database Azure, SQL Database Edge. Azure SQL Database Edge is a preview version of SQL running on edge devices supporting ARM processors.

Intelligent Query Processing

It extends the SQL Server 2017 Intelligent Query Processing (IQP) feature with the new database compatibility level 150.

Batch mode on row store

It provides batch mode execution for data analytics. It does not use columnstore indexes. We can use this feature where the column store index creates overhead for the data workload.

Row Mode Memory grants feedback

SQL Server takes feedback from query executions and uses it for granting optimizes memory allocations in subsequent queries. You can refer to the article Row Mode Memory Grant Feedback in SQL Server 2019 for more details.

Table variable deferred compilation

It uses the actual cardinality in a subsequent execution of a table variable as feedback from the first query compilation.

Scalar UDF inlining

It improves the performance of a scalar user-defined function with transformation in a relational expression. You can refer to Improvements of Scalar User-defined function performance in SQL Server 2019 for it.

Approximate query processing

It provides a new function such as Approx_Count_Distinct for providing an approximate number of row counts for improving overall performance. You can refer to article The new SQL Server 2019 function Approx_Count_Distinct.

Lightweight query profiling

SQL Server 2017 introduced lightweight query profiling for collecting query runtime statistics with a minimal resource overhead. It requires enabling a trace flag 7412. SQL Server 2019 enables lightweight query profiling for all sessions by default. Read more about it in Lightweight performance profiling in SQL Server 2019 article.

Silent data Truncation

In previous versions of SQL Server, it truncates the data if it exceeds the column length. It is a tedious and frustrating task for SQL Server developers because SQL Server does not give information about data that caused this issue. SQL Server 2019 provides useful information for troubleshooting purposes. Refer article SQL truncate enhancement: Silent Data truncation in SQL Server 2019 for this information.

In-memory improvements

SQL Server provides many enhancements for in-memory features. It provides performance gains for memory-optimized tables, natively compiled stored procedures, and in-memory clustered columnstore indexes.

Memory-optimized TempDB

SQL Server 2019 provides memory-optimized TempDB metadata in removing bottlenecks for TempDB heavy workloads. You can refer to Memory-Optimized TempDB Metadata in SQL Server 2019 article for more details.

Hybrid buffer pool

SQL Server 2019 supports a hybrid buffer pool to access data pages stored on persistent memory (PMEM) devices. It eliminates the process of copying the data page in the buffer pool from the disk. SQL Server supports PMEM devices both on Windows and on Linux OS.

Accelerated Database Recovery

Before SQL Server, database recovery or rollback takes a long time for a significant transaction or SQL Service restart. It is a painful situation for DBAs for massive production databases. SQL Server provides instant database recovery in such cases using new feature Accelerated database recovery. Refer to article, Accelerated Database Recovery; Instant Rollback and Database Recovery for more details.

Columnstore Index Enhancements

SQL Server provides columnstore index enhancements for:

• sp_estimate_data_compression_savings for data compression estimates the storage savings. Refer to article Columnstore Index Enhancements – data compression, estimates and savings
• It also provides Columnstore index statistics update in clone databases. Refer to Columnstore Index Enhancements – Index stats update in clone databases for more details
• Online and offline index rebuilds of the columnstore index. Refer to Columnstore Index Enhancements – online and offline (re)builds for more details

High Availability Solutions

SQL Server 2019 provides the following enhancements for improving disaster recovery and high availability solutions.

Five synchronous replica pairs

SQL 2019 supports one primary and four secondary replicas in synchronous mode. It also allows automatic failover between these replicas.

Secondary to primary replica connection redirection

SQL 2019 supports connection redirection from secondary to a primary replica without SQL listener as well.

Enhanced database-level health detection for AG groups

SQL 2019 improves database health detection for the Availability group databases. It initiates a failover in case of errors in health detection.

High availability solution with remote storage on Kubernetes

SQL Server 2019 allows configuring AG groups using Kubernetes as an orchestration layer. It also provides database health detection for errors and failover if needed.

Big Data Clusters

SQL 2019 supports the configuration of scalable clusters, Spark and HDFS containers. We can use T-SQL to process big data. It enables us to analyze a high volume of big data. It also supports SQL Server on Linux, Hadoop, and Kubernetes.

Enhanced PolyBase

SQL Server 2019 provides feature enhancements to PolyBase and integrates with various data sources such as Oracle, MongoDB, HDFS, ODBC based data sources. You can refer the following articles for more details:

• Enhanced PolyBase SQL 2019 – Installation and basic overview
• Enhanced PolyBase SQL 2019 – External tables for Oracle DB
• Enhanced PolyBase SQL 2019 – MongoDB and external table
• Enhanced PolyBase SQL 2019 – External tables using t-SQL
• Enhanced PolyBase SQL 2019 – External tables SQL Server, Catalog view and Pushdown

SQL Server Security and compliance

SQL Server has the lowest number of security vulnerabilities for 2010-2019 as per the National Institute of Standards and Technology (NIST).

SQL 2019 provides the following enhancements for security and compliance.

Certificate Management

SQL Server 2019 is integrated with the certificate management and provides benefits such as:

• View and validate SSL certificates
• Check certificate expiration dates
• Deploy certificate for Always on Availability groups
• Deploy certificate for failover cluster instance

Read more about it using Certificate Management in SQL Server 2019.

SQL Vulnerability Assessment

We can use this tool for assessment, track and remediate database vulnerability issues in SQL Server. It is available in SSMS 17.4 or later versions. Read more about What’s new in SSMS 17.5; Data Discovery and Classification and more.

SQL Data Discovery and Classification

We can classify database columns and provide labels such as public, general, and confidential. It is helpful for regulatory requirements such as GDPR. Read more about SQL data classification – Add sensitivity classification in SQL Server 2019

Always Encrypted with Secure Enclaves

SQL Server 2019 introduces Secure Enclaves in Always Encrypted technology. It protects the sensitivity of data while initial data encryption using Always Encrypted feature of SQL Server. We can also do data computation or mathematical operations, pattern matching, range comparisons as well on secure data.

SQL Server on Linux

SQL Server 2017 and 2019 works on cross-platform operation systems such as Windows and Linux. It provides the following enhancements in SQL 2019 Linux version:

• SQL Server on Linux can acts in the transactional, merge, and snapshot replication. We can use it publisher, distributor or subscriber role
• SQL Server 2019 Linux supports Change Data Capture (CDC) feature as well for insert, update and delete activity recording
• It also supports distributed transactions using Microsoft distributed transaction coordinator (MSDTC)
• SQL 2019 Linux supports machine learning languages such as Python, R Scripts
• It also supports PolyBase for configuration of external data sources for Oracle, MongoDB, Teradata and ODBC based data source
• SQL 2019 also supports TempDB configuration in Linux instance similar to a Windows-based instance

You can follow these articles for more details about SQL 2019 on Linux:

• SQL Server 2019 on Linux with Ubuntu and Azure Data Studio
• SQL Server 2019 on Linux with a Docker container on Ubuntu
• SQL Server 2019 on Linux with Ubuntu
• SQL Server 2019 installation on Ubuntu without a Docker Container

Graph database enhancements

SQL Server supports graph databases for a complicated relationship, and hierarchical data.SQL 2019 enhances its capabilities using edge constraints, MATCH predicates, SHORTEST_PATH and derived tables support in the graph match query. Read more about in using Graph Database features in SQL Server 2019.

Enhanced License benefit

SQL Server 2019 customer with software assurance program gets the following benefits:

• It provides one free passive SQL Server license for high availability or DR
• It also provides a free passive asynchronous SQL Server license for DR purpose on a separate OS as well
• We can also use free asynchronous replica in a disaster recovery environment

Refer to article New high availability and disaster recovery benefits for SQL Server for more details.

Installation changes

We see changes in SQL Server installation with every release. Microsoft’s focus is to make the installation more straightforward, easy to use with maximum user-friendly configurations. In previous versions, we did TempDB configurations during the installation of SQL Server.

In the article, SQL Server 2019 overview and installation, we installed SQL Server vNext CTP 2.0. SQL 2019 is in General Availability phase now, and we should look at the installation changes. You can follow the article for installation; however, I will highlight the differences in the setup.

Feature Selection

You can see below SQL Server vNext CTP 2.0 feature selection screen:

In the General Availability release, we can see a new language option Java in machine learning services:

If we select Java language, we get JRE 11.0.3 installation option in the installation process:

Database Engine Configurations

In the previous setup, we get the following options in the database engine configurations:

• Server configuration
• Data directories
• TempDB
• FILESTREAM

In the General Availability release setup, we get two new options in the database engine configuration.

• MaxDOP
• Memory

Click on MaxDOP, and it gives you the configuration for the max degree of parallelism. It detects the logical CPU on OS and displays the recommendation for the MAXDOP configuration. You can modify it as per your requirement, but you should be careful while changing the recommended value.

You can also click on the hyperlink specified in the page for reference purpose:

Next, click on the Memory tab. On this page, you can configure the minimum and maximum memory configurations for SQL Server. Usually, DBA sets the min and max memory after the installation. Sometimes if you forget to set the memory configuration, you face high memory consumptions by SQL Server processes, and it might cause issues for you in terms of high memory alerts. SQL Server 2019 provides flexibility to do memory configurations during installation. It is also beneficial for a junior DBA’s as well because you can use the recommended value provided by the SQL installer:

You can choose either recommended or default memory configuration. If you use the recommended configuration, you need to accept the recommended memory configuration using the checkbox.

Conclusion

SQL Server 2019 provides many exciting features. It integrates SQL Server relational database with big data, data virtualization, analytical capabilities with enhanced performance and troubleshooting support. You should explore it in the test environment and be familiar with the solutions provided. You can also follow SQLShack for being updated with SQL Server 2019 features.

• Author
• Recent Posts

Rajendra Gupta

Hi! I am Rajendra Gupta, Database Specialist and Architect, helping organizations implement Microsoft SQL Server, Azure, Couchbase, AWS solutions fast and efficiently, fix related issues, and Performance Tuning with over 14 years of experience.

I am the author of the book "DP-300 Administering Relational Database on Microsoft Azure". I published more than 650 technical articles on MSSQLTips, SQLShack, Quest, CodingSight, and SeveralNines.

I am the creator of one of the biggest free online collections of articles on a single topic, with his 50-part series on SQL Server Always On Availability Groups.

Based on my contribution to the SQL Server community, I have been recognized as the prestigious Best Author of the Year continuously in 2019, 2020, and 2021 (2nd Rank) at SQLShack and the MSSQLTIPS champions award in 2020.

Personal Blog: https://www.dbblogger.com
I am always interested in new challenges so if you need consulting help, reach me at rajendra.gupta16@gmail.com

View all posts by Rajendra Gupta

Latest posts by Rajendra Gupta (see all)

• Understanding PostgreSQL SUBSTRING function - September 21, 2024
• How to install PostgreSQL on Ubuntu - July 13, 2023
• How to use the CROSSTAB function in PostgreSQL - February 17, 2023