SQL Server 2019 New Features

Microsoft’s leading database is an essential tool, with in-cloud and on-premises versions providing incredible storage and analytic tools. It’s additionally turned into an essential application for data scientists, giving a structure to building and testing machine learning models. There is more in SQL Server, and the new release version can indicate where Microsoft thinks your data needs will go over the next few years.

Like all the previous versions, the Microsoft SQL Server 2019 latest version is also jam-packed with new features and capabilities. It will help organizations evaluate and test relational data with multiple integrations of various software. By creating a simplified view of data, users can have a comprehensive view of information.

The new version of SQL Server 2019 is available in Linux and Windows versions and support for containers and Kubernetes is added. Using Docker, including container support and the Linux version of SQL Server is an intriguing alternative, as it will enable you to build SQL Server with huge Kubernetes based analytic engines that work across Azure facilitated data lakes using Apache Spark.

Now let’s take a look at the new SQL Server 2019 Community Technical Preview (CTP 2.1) installation

Installation

The new preview provides three types of installation options – Basic, Custom and Download Media. Basic installation requires less disk space, as these are the files needed to run that basic install, while a custom install brings down the whole SQL Server 2019 installation media. Basic installation is sufficient for most basic developer tasks. I would recommend a custom installation for the full evaluation and Download Media for installing on more than one machine to evaluate the SQL Server’s cluster features.

Then select the installation type whatever you want from Basic, Custom, and Download Media. I want to go with Download Media, so I clicked it. Then the download process starts, and the following page is displayed.

Then select the language, the type of package you would like to download, and the download location. Then click Download. The download process will start and display the following media downloading progress.

Once the download is complete, it will show us the download successful message.

Once the download of suitable media is complete, it will take you through the process of checking prerequisites and setup changes you have to make. After making changes, the installation will be completed in less than 20 minutes.

Big Data Clusters

The latest version of SQL Server simplifies big data analytics for users. It combines Apache Spark and HDFS (Hadoop Distributed Filing System) and provides one integrated system. The new SQL server allows you to build “Big Data Clusters” using a blend of SQL Server and Apache Spark containers over Kubernetes utilizing SQL Server’s current PolyBase features. With the help of local Kubernetes, which is supported by public clouds, you’ll be able to deploy Big Data Clusters on AWS, on Azure, on GCP, and also on your own infrastructure.

Always On Availability Groups

For the first time, Always On Availability Groups were introduced in SQL Server 2012. Since then, Microsoft has  made some improvements to this feature in each new release. In the same way, in SQL Server 2019, has made improvements to the high availability and disaster recovery feature. In the new version, the Always On Availability Group can have 5 synchronous replicas (1 primary copy and 4 secondary ones) for failover purposes, whereas there were 3 limitations in previous SQL Server 2017.

One of the great things about this feature is that it enables numerous duplicates of a database to be reproduced on different servers. SQL Server 2019 can redirect connections for customer applications from a secondary replica to the primary one. This  means that a customer can be redirected to the primary replica without  using the accessibility group listener, which is a virtual system name used to interface customers to databases in replicas.

UTF-8 Support

This feature provides significant storage savings. The new version supports the widely used UTF-8 encoding as an export or import encoding, or column-level or as a database-level grouping for text data. Thus, Unicode string data will take up much less storage space than the previous UTF-16.

UTF-8 is permitted in VARCHAR and CHAR and  extends capabilities when creating or changing an object’s collation with UTF-8. And you can use the familiar CHAR data type rather than NCHAR, as CHAR requires only 10 bytes, whereas NCHAR requires 20 bytes for the same Unicode string data storage.

Resumable online index

The most exciting capabilities are related to indexing. Probably many database administrators face a terrible situation when an indexing operation goes wrong. Finally, SQL Server 2019 has come up with the new features to cope with these situations. When resumable online index is created, we can pause the indexing process and then resume from where we left off. We don’t need to start again from the very beginning.

New SQL Server 2019 supports the recovery of indexing failure. The process may fail due to many factors, such as after running out of disk space or after a database failure. You can resume the indexing process, once you have corrected the error that caused the index operation to crash without having to start over.

New SQL Server 2019 also reduces the amount of log space required when you create a large index, compared to the previous SQL Server 2017.

Additionally, SQL Server 2019 has a new feature for online conversion of conventional row storage tables to columnstore indexes. In the previous SQL Server 2017, such conversions could only be performed offline. But, with the latest version of SQL Server 2019 and Azure SQL Database, we can create or re-create Creating clustered columnstore indexes (CCI) online.

Intelligent Query Processing

The new Intelligent Query Processing suite is developed to fix some of the common query execution issues by adopting some automatic corrective strategies at runtime. It uses feedback data insights gathered from past executions. Microsoft has also started leveraging some of these features in Azure SQL DB and plans to keep expanding this region for SQL Server 2019. The image above shows the new and improved areas in the Intelligent Query Processing features.

Added features for SQL Server on Linux

Microsoft has added plenty of new features to its Linux edition. Perhaps the most exciting update is support for data replication which enables you to build distributed SQL databases effectively, especially those related to the Linux version of the Distributed Transaction Coordinator.

Another significant expansion of the Linux version is the ability to set up Always On Availability Groups in Docker containers arranged with Kubernetes. Additionally, for Linux, Microsoft makes certified container images available and places all of its windows and container images for SQL Server 2019 and SQL Server 2017 into the Microsoft Container Registry.

Another significant added feature – SQL Server 2019 on Linux supports OpenLDAP. It is an open source form of the Lightweight Directory Access Protocol. Although OpenLDAP can work autonomously of Microsoft’s Active Directory, the new support for OpenLDAP allows Linux-based SQL Server databases to join Active Directory.

In addition, another important part of SQL Server 2019, Microsoft has included integrated tools for building and testing machine-learning models on Linux. Thus, it enables SQL Server on Linux users to run machine learning applications written in Python and R languages.

Master Data Services (MDS)

Silverlight controls are replaced with HTML: Silverlight support for MDS portal is no longer needed. HTML controls will now perform the same function.

Security

The new version has come up with advanced security support. It encrypts the data using secure enclave technology. Certificate management is now integrated into the SQL Server Configuration Manager. A widely used SSL/TLS certificates are integrated to secure access to SQL Server instances.

Finally, in this article, I have mentioned some of the improved features in the new version of SQL Server 2019.  However, there are some significant improvements coming to the often used functional areas in SQL Server. You can find other areas that will be useful in your SQL Server environment. The preview version is now available for Windows, Linux, and Docker. All you need to do is download it and see what it can do for you.