New features in SQL Server 2019 GA

SQL Server 2019 has now been released as GA and offers many great new features. Let's take a look at the new features in SQL Server 2019 GA and see the new capabilities contained in this release.

Author
Recent Posts

Brandon Lee

Brandon Lee has been in the IT industry 15+ years and focuses on networking and virtualization. He contributes to the community through various blog posts and technical documentation primarily at Virtualizationhowto.com.

SQL Server 2019 version and build number

The following are the version and build numbers for SQL Server 2019:

Version number X
RTM build number for SQL Server2019: 0.2000.5

New features in SQL Server 2019 GA

There are many great new features with SQL Server 2019. It brings a number of modern technologies and integrates them with SQL Server, including big data, machine learning, and cloud technologies. In this overview of SQL Server 2019 GA new features, we will focus on the following new features:

SQL Server 2019 Big Data Clusters
Data virtualization
AI and ML
UTF-8 encoding support
Secure enclaves
Always On availability groups

Let's take a look at these features and see how they are implemented in SQL Server 2019.

SQL Server 2019 Big Data Clusters

Microsoft is making a very wise, strategic move, given the need for today's businesses to process, query, and make intelligent decisions based on big data environments. Today's businesses are dealing with more data than ever before, with information that must be queried efficiently and effectively.

There are many great use cases today that require large amounts of data. This includes machine learning, business intelligence, and data mining as examples. All of these require access to and take advantage of large datasets.

With SQL Server 2019 Big Data Clusters, Microsoft has created a way to interact intelligently with the big data needs of today's organizations. SQL Server 2019 Big Data Clusters are built on exciting technologies, including:

Kubernetes
Apache Spark
HDFS – Hadoop Distributed File System
PolyBase

SQL Server 2019 Big Data Cluster architecture (Image courtesy of Microsoft)

Much of what Microsoft has been able to introduce with Big Data Clusters in SQL Server 2019 was made possible by the introduction of support for SQL Server on Linux back in SQL Server 2017. This opened the door to many of the Big Data technologies that are built upon Linux, including Kubernetes.

There are a few things to take note of if you are wondering why SQL Server 2019 Big Data Clusters are a big deal:

Much quicker time to value for SQL Server Big Data
- Easy to deploy on top of K8s
- Creating a Big Data Cluster is much easier deployed into a containerized environment
Integrated security layer
- With SQL Server 2019 Big Data Clusters, there is an integrated security layer that helps to secure SQL Server, Spark, and HDFS
Data virtualization negates the need to perform an extract, transform, and load (ETL) operation by creating a data abstraction layer so that external database resources can be queried directly without the need for the ETL

Data virtualization is one of the core technologies that takes SQL Server 2019 to the next level. What is data virtualization and how is it implemented in SQL Server 2019?

Data virtualization

One of the key new features of SQL Server 2019 is data virtualization, one of the technologies that makes the SQL Server 2019 Big Data Cluster possible. What is it, exactly? First, let's take a look at the traditional approach that most people are familiar with.

The traditional systems approach for interacting with different data sources was to extract the data, transform it, sanitize it, and then insert it into a data warehouse.

Data virtualization is a key component of SQL Server 2019 that is an alternative to the traditional extract, transform, and load methodology of previous SQL Server releases. SQL Server data virtualization creates an abstracted virtual data layer that allows querying data from a number of different sources within a single interface, without knowing where the data actually resides.

SQL Server 2019 Big Data Clusters make use of data virtualization to enable integrating both structured and unstructured data across the entire data landscape. These features allow SQL Server 2019 to query both relational and non-relational data without movement or replication.

AI and ML in SQL Server 2019

We've heard a lot about artificial intelligence and machine learning lately. Both of these computer-assisted technologies can provide tremendous value, especially when you are working with a large amount of data such as is typically found in Big Data environments.

Moving forward with your Big Data environment, you want to be able to leverage both AI and ML in working with your Big Data and other environments. Due to the mass of data that exists, humans simply can no longer manually examine all the data there is and make decisions about it.

This is where the power of AI and ML come into play. The premise of machine learning is that you feed a computer system a large amount of data. It then uses a mathematical algorithm or series of algorithms to compute the outcome or organize the data.

SQL Server 2019 includes all the AI and ML tools you will need to work with your data intelligently and predictively. These include the following:

The Machine Learning Services extensibility framework: This provides a combined solution comprising the SQL Server Launchpad service, which is the code executor, along with a stored procedure that provides a secure way for SQL and the specified language to interact.
PREDICT T-SQL command: This command generates a predicted value or scores based on a stored model.
The rx_Predict stored procedure: “Generates a predicted value for a given input consisting of a machine learning model stored in a binary format in a SQL Server database.”
Spark in SQL Server 2019 Big Data Clusters: Spark libraries are invoked to create, train, store, and score machine learning models by leveraging PySpark, SparkR, or SparkML.

UTF-8 encoding support

UTF-8 is by far the most common character encoding system in use, especially across web technologies. Up until SQL Server 2019, UTF-8 was not supported. However, SQL Server 2019 introduces support for UTF-8 as an import or export encoding, and at the DB and column level for string data. Why is this important?

As companies expand globally, they may develop requirements for a database that supports global multilingual applications. Support for UTF-8 encoding becomes extremely important. In addition, there are many performance benefits in the ASCII range when workloads perform very intense read/write I/O on UTF-8. Performance benefits greatly when using UTF-8 in this scenario and is also enhanced when datasets are not predominately ASCII.

Secure enclaves

Security is everything these days. Since business-critical and often sensitive data exists in databases, you must give due attention to the security of your database. Always Encrypted functionality was introduced in SQL Server 2016 and has been improved in SQL Server 2019 with secure enclaves. What are these?

According to Microsoft, “A secure enclave is a protected region of memory within the SQL Server process, and acts as a trusted execution environment for processing sensitive data inside the SQL Server engine. A secure enclave appears as a black box to the rest of the SQL Server and other processes on the hosting machine. There is no way to view any data or code inside the enclave from the outside, even with a debugger.” As you can imagine, this helps to protect Personally Identifiable Information (PII) data.

With Always Encrypted in SQL Server 2016 and 2017, the only operation you can perform on encrypted database columns is an equality comparison. Microsoft details the challenges with SQL 2016 and 2017 with Always On outside of comparative queries. “For anything else, your apps need to download the data to perform the computations outside of the database. Similarly, if you need to encrypt your data for the first time or re-encrypt it later (e.g. to rotate your keys), you need to use special tools that move the data and perform crypto operations on a different machine than your SQL Server computer.” These details provide challenges in using Always Encrypted.

SQL Server 2019 leverages Always Encrypted with secure enclaves. It does this by leveraging virtualization-based security (VBS). This allows the SQL Server to perform cryptographic operations on sensitive data and decrypt data for rich computations in plain text while still securing the data. During these operations, secure enclaves protect the data from the SQL Server itself, the operating system, or the database administrator.

Always On availability groups

Microsoft's Always On availability groups are a DR and HA solution that essentially create database mirrors. The availability group is created to provide the replicated environment for the user databases. This is a very mature solution introduced back in SQL Server 2012 and has been improved upon with each SQL Server release.

SQL Server 2019 continues to improve upon the functionality of availability groups by introducing the following new features:

The maximum number of synchronous replicas has been increased from 3 to 5
Client application connections can be redirected to the primary replica regardless of the target server specified in the connection
Availability Groups can now use Kubernetes as the orchestration engine

The improvements with the Always On availability groups and other DR features and functionality make this the most feature-rich SQL version from a disaster recovery/high-availability standpoint.

Conclusion

SQL Server 2019 is certainly the most fully featured and capable SQL Server version to date. Microsoft has introduced many great new features that embrace many of the new technologies available. With SQL Server 2019 Big Data, Microsoft leverages many open source technologies such as Kubernetes.

SQL Server 2019 will allow businesses to deal effectively with the massive amounts of data they possess and make intelligent decisions using the data by leveraging new machine-learning technologies built right into SQL Server 2019.

Subscribe to 4sysops newsletter!

With the features listed above as well as many other new features found in SQL Server 2019, many businesses will no doubt be looking at upgrading their SQL Server infrastructure to SQL Server 2019.