SQL Server 2019 includes welcome new features, particularly updates to its indexing and high availability capabilities and added SQL Server on Linux functionality.

Like every version before it, Microsoft SQL Server 2019 is jam-packed with new features and capabilities — and not just the support for big data clusters that the company highlighted when it released the first public preview of the database update in September.

Beyond the ability to combine SQL Server with Spark and other big data technologies, let’s look at some of the added features that I believe will be the most useful to users of the database management system (DBMS).

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 introduced 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 new features and capabilities found in Microsoft SQL Server 2019 are geared toward users who want to run SQL Server on Linux. Doing so first became possible in SQL Server 2017, but the Linux version of that release 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 for 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 new 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 memory devices to operate in “enlightened” mode to improve performance. That can include database, transaction log and In-Memory OLTP checkpoint files. When these types of files are placed on persistent memory, which is also known as storage class memory, they can be directly accessed through memcpy operations, which can be performed without utilizing the OS’ storage stack.

This enables file I/O operations to be done far more efficiently, without incurring the usual storage latency. Support for persistent memory 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 new features or if you try out the preview.