The most useful new features in Microsoft SQL Server 2019

Indexing improvements

The new capabilities that I'm the most excited about are related to indexing. As database administrators, we've probably all occasionally run into a situation in which an indexing operation goes horribly wrong. Thankfully, SQL Server 2019 finally gives us the tools to cope with these situations.

If, for example, an indexing operation ends up being resource-intensive to the point of disrupting normal database operations, the indexing process can be paused and then resumed during off-peak hours to free up badly needed system resources.

Another equally compelling improvement to the indexing engine is SQL Server 2019's new ability to recover from an indexing failure. The indexing process can fail as a result of any number of factors, such as running low on disk space or a database failover occurring. In the past, such failures meant that the indexing process had to be started all over again from square one. In SQL Server 2019, Microsoft has made it possible to resume the indexing process without having to start over.

In addition, SQL Server 2019 reduces the amount of log space that's required when you create a large index compared with previous versions of the DBMS.

Another welcome change that Microsoft has implemented in SQL Server 2019 is the online conversion of conventional rowstore tables into columnstore indexes. In previous versions of SQL Server, such conversions could only be performed offline. In SQL Server 2019, they can be done transparently in the background, while the database continues to run.

Updates to Always On availability groups

Always On Availability Groups aren't new to SQL Server 2019, but Microsoft has made some improvements to the high availability and disaster recovery feature. For starters, SQL Server 2019 allows an Always On availability group to contain up to five synchronous replicas for failover purposes, with one primary replica and as many as four secondary ones. That's up from a maximum of three replicas in SQL Server 2017.

Microsoft has also the ability to redirect connections for client applications from a secondary replica to the primary one. This means that a client can be redirected to the primary replica without the use of an availability group listener, which is a virtual network name used to connect clients to databases in replicas.

A listener automatically redirects user traffic to the primary replica when a failover occurs; the secondary-to-primary replica connection redirection feature in SQL Server 2019 can be used to do that in situations in which listeners aren't supported, no matter which target server is specified in the connection string.

Added features for SQL Server on Linux

Many of the features and capabilities found in Microsoft SQL Server 2019 are geared toward users who want to incorporate Linux systems into their SQL Server environments. Doing so became possible in SQL Server 2017, but the Linux version didn't include the full set of features. Neither does the one in SQL Server 2019, but Microsoft has added some of the missing pieces.

For starters, SQL Server on Linux now supports data replication. A database instance in a Linux virtual machine running SQL Server 2019 can act as a replication publisher, distributor or subscriber, so long as the SQL Server Agent job scheduler is installed.

Another major addition to the Linux version of the DBMS is the ability to set up Always On availability groups in Docker containers orchestrated with Kubernetes. Microsoft is also making certified container images available for Red Hat Enterprise Linux, and it's putting all of its Linux and Windows container images for both SQL Server 2017 and SQL Server 2019 on the Microsoft Container Registry, a container catalog it created in May.

In addition, Microsoft is making its Machine Learning Services (In-Database) software available in Linux environments, enabling SQL Server on Linux users to run machine learning applications written in R and Python. The added support for the machine learning package includes the use of the sp_execute_external_script stored procedure, a Transact-SQL command for executing scripts against a database.

One of the biggest -- yet least talked about -- improvements to SQL Server on Linux is SQL Server 2019's support for OpenLDAP. For those who might not be familiar with OpenLDAP, it is an open source version of the Lightweight Directory Access Protocol. Although OpenLDAP can function independently of Microsoft's Active Directory, the support for OpenLDAP indirectly opens the door to allowing Linux-based SQL Server databases to access Active Directory.

SQL Server 2019 enables file enlightenment

The Linux version of SQL Server 2019 also adds support for setting files that are stored on persistent devices to operate in "enlightened" mode to improve performance. That can include database, transaction log and In- OLTP checkpoint files. When these types of files are placed on persistent , which is also known as storage class , they can be directly accessed through memcpy operations, which can be performed without utilizing the storage stack in the OS.

This enables file I/O operations to be done far more efficiently, without incurring the usual storage latency. Support for persistent devices was added in SQL Server 2016 for Windows, but the file enlightenment capability is available only in the Linux version of the SQL Server 2019 preview.

The items discussed in this article are just a small sampling of what Microsoft SQL Server 2019 has to offer. You may well find others that will be useful in your SQL Server environment in the full list of features or if you try out the preview.