What's new in SQL Server 2019

Database Engine Enhancements on Windows

Enhancements in the SQL Server 2019 database engine are not limited to PolyBase and Big Data Clusters; many components of the database engine have new features and capabilities with this release. The following features are added or enhanced for SQL Server 2019 CTP 2.0.

Database Engine
  • UTF-8 support 
  • Resumable online index create allows index create to resume after interruption 
  • Clustered columnstore online index build and rebuild 
  • Always Encrypted with secure enclaves 
  • Intelligent query processing 
  • Java language programmability extension 
  • SQL Graph features 
  • Database scoped configuration setting for online and resumable DDL operations 
  • Enhancement in Always On Availability Groups 
  • Data discovery and classification - natively built into SQL Server 
  • Expanded support for persistent memory devices 
  • Support for columnstore statistics in DBCC CLONEDATABASE 
  • New options added to sp_estimate_data_compression_savings 
  • SQL Server Machine Learning Services failover clusters 
  • Lightweight query profiling infrastructure enabled by default 
  • New PolyBase connectors 
  • New sys.dm_db_page_info system function returns page information 
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).

AlwaysOn Availability Groups

SQL Server 2019 adds support for even more high availability scenarios and platforms, including:
Enable high availability configurations for SQL Server running in containers - SQL Server 2019 enables customers to configure highly-available systems with AlwaysOn Availability Groups using Kubernetes as an orchestration layer.
Up to five synchronous replica pairs – SQL Server 2019 increases the limit for synchronous replica pairs from three (in SQL Server 2017) to five. Users can now configure up to five synchronous replicas (1 Primary and up to 4 secondary replicas) with automatic failover between these replicas.
Better scale-out with automatic redirection of connections based on read/write intent - Configuring an AlwaysOn Availability can be challenging for a number of reasons, including:
  • In SQL Server 2017, an administrator must configure the Availability Group listener (and the corresponding cluster resource) to direct SQL Server traffic to the primary replica to ensure that clients are transparently reconnected to the active primary node upon failover; however, there are cluster technologies that support SQL Server Availability Groups that do not offer a listener-like capability.
  • In a multi-subnet configuration such as Azure or multi-subnet floating IP address in an availability group using Pacemaker, configurations become complex, prone to errors and difficult to troubleshoot due to multiple components involved.
  • When the availability group is configured for read scale-out or DR and cluster type is NONE, there is no straightforward mechanism to ensure transparent reconnection upon manual failover.
To address these challenges, SQL Server 2019 adds a new feature for Availability Groups: secondary to primary replica connection redirection. With this feature, client applications can connect to any of the replicas of the Availability Group and the connection will be redirected to the primary replica, according to the Availability Group configuration and the connection intent (read only or read/write) specified in the connection string

Data discovery and classification

SQL Data Discovery and Classification allows to classify columns in the database that contain sensitive information. Columns can be classified by the type of information it contains. For example names, addresses, social security numbers etc and by the level of sensitive data in the column including levels such as public, general and confidential. We can easily generate reports from the classification that are applied to meet statutory and regulatory requirements, such as EU GDPR. SSMS also includes the SQL Data Discovery and Classification wizard, which will try to identify columns in the database that contain sensitive information. SQL Data Discovery and Classification uses the underlying mechanism of SQL Server Extended Properties, and so is backwards-compatible with SQL Server 2008 and later

Always Encrypted with Secure Enclaves

Introduced in SQL Server 2016, Always Encrypted is an encryption technology that protects the confidentiality of sensitive data from malware and high-privileged, but unauthorized users of SQL Server, including DBAs, machine admins and cloud admins. Sensitive data is never visible in plain-text to those users. 
The secure enclave technology, introduced in SQL Server 2019, addresses the limitations of Always Encrypted by allowing computations on plain-text data within a secure enclave inside the SQL Server process. A secure enclave is a protected region of memory within the SQL Server process, and it acts as a trusted execution environment for processing sensitive data inside the SQL Server Engine. A secure enclave extends client applications’ trust boundary to the server side. While it is contained by the SQL Server environment, the secure enclave is not accessible to SQL Server, the operating system, or to the database or system administrators.

Clustered columnstore online index build and rebuild

Creating clustered columnstore indexes (CCI) was an offline process in the previous versions of SQL Server - requiring all changes stop while the CCI is created. 
With SQL Server 2019 preview and Azure SQL Database we can create or re-create clustered columnstore index online. Workload will not be blocked and all changes made on the underlying data are transparently added into the target columnstore table.

Expanded support for persistent memory devices

Microsoft is improving persistent memory support for this release. It’s doing so with a newly optimized I/O path that’s meant to interact with persistent memory storage. When an SQL Server file is placed on a persistent memory device, it allows SQL Server to access the device directly, bypassing the operating system’s storage stack entirely. Basically, this improves performance and low latency I/O without actually changing your database design.

Intelligent query processing

The new Intelligent Query Processing suite is meant to rectify some of the common query performance problems by taking some automatic corrective approaches during run-time. It leverages a feedback loop based on statistics collected from past executions. Microsoft has already started leveraging some of these features in Azure SQL Database and plans to continue building on this area for SQL Server 2019.

UTF-8 Support

SQL Server 2019 includes full support for the widely used UTF-8 character encoding as an import or export encoding, or as database-level or column-level collation for text data. 
UTF-8 is allowed in the CHAR and VARCHAR datatypes, and is enabled when creating or changing an object’s collation,to a collation with the "UTF8" suffix,such as LATIN1_GENERAL_100_CI_AS_SC to LATIN1_GENERAL_100_CI_AS_SC_UTF8. UTF-8 is only available to windows collations that support supplementary characters, as introduced in SQL Server 2012. Note that NCHAR and NVARCHAR allow UTF-16 encoding only, and remain unchanged.
Significant storage savings can also be achieved, depending on the character set in use. For example, changing an existing column data type from NCHAR(10) using UTF-16 to CHAR(10) using an UTF-8 enabled collation, translates into nearly 50% reduction in storage requirements. This is because NCHAR(10) requires 22 bytes for storage, whereas CHAR(10) requires 12 bytes for the same Unicode string.

Database Engine Enhancements on Linux

The following features are added or enhanced for SQL Server 2019 preview CTP 2.

SQL Server on Linux
  • Replication support
  • Support for the Microsoft Distributed Transaction Coordinator (MSDTC)
  • Always On Availability Group on Docker containers with Kubernetes
  • OpenLDAP support for third-party AD providers
  • Machine Learning on Linux
  • New container registry
  • New RHEL-based container images
  • Memory pressure notification

Replication support

SQL Server 2019 preview supports SQL Server Replication on Linux. A Linux virtual machine with SQL Agent can be a publisher, distributor, or subscriber. Create the following types of publications
  • Transactional
  • Snapshot
  • Merge
Configure replication SQL Server Management Studio or use replication stored procedures

Support for the Microsoft Distributed Transaction Coordinator (MSDTC)

SQL Server 2019 on Linux supports the Microsoft Distributed Transaction Coordinator (MSDTC). SQL Server on Linux instances can now initiate and participate in distributed transactions. This is achieved with a Linux version of the Microsoft Distributed Transaction Coordinator (MSDTC) that runs within the SQL Server process to help SQL Server participate in distributed transactions. With access to MSDTC, SQL Server on Linux can participate in distributed transactions with other third-party transaction coordinators, or if you have specific processing needs there is support for you to develop your own.

Active Directory Integration

Integration between SQL Server on Linux and Active Directory is much improved. SQL Server on Linux instances can be configured use Active Directory for authentication of users, for replication, and for distributed queries. SQL Server on Linux instances can now participate in Availability Groups that are authenticated by Active Directory, in addition to the support for certificate-based authentication included in SQL Server 2017 on Linux. As an alternative to Active Directory, SQL Server on Linux instances can now use OpenLDAP as a directory provider, making it easier to manage domain-joined Linux servers.

Always On Availability Group on Docker containers with Kubernetes

Kubernetes can orchestrate containers running SQL Server instances to provide a highly available set of databases with SQL Server AlwaysOn Availability Groups. A Kubernetes operator deploys a StatefulSet including a container with mssql-server container and a health monitor.

OpenLDAP support for third-party AD providers

SQL Server 2019 preview on Linux supports OpenLDAP, which allows third-party providers to join Active Directory.

Machine Learning on Linux

There are many advantages to bringing the machine learning compute to the data instead of moving data out to compute. These advantages include the elimination of data movement, ease of deployment, improved security and better scale and performance. These advantages also make SQL Server a powerful end to end machine learning platform. Enhancements to Machine Learning in SQL Server 2019 CTP 2.0 include:
  • Machine Learning on Linux: SQL Server 2019 Machine Learning Services (In-Database) is now supported on Linux. 
  • Input data partitioning: without changing your R or Python scripts, you can process data at table partition level. This allows you to train a model for each table partition and parallelize model training per partition. 
  • Failover cluster support: You can install SQL Server 2019 Machine Learning Services (In-Database) on a Windows failover cluster to meet your requirements for redundancy and uptime in the event your primary server fails over. 
  • Java language extension: In addition to R and Python runtimes, SQL Server 2019 adds a Java language extension. This will allow you to call a pre-compiled Java program and securely execute Java code on SQL Server. This reduces the need to move data and improves application performance by bringing your workloads closer to your data. You specify the Java runtime you want to use, by installing the JDK distribution and Java version of your choice.


All container images for SQL Server 2019 preview as well as SQL Server 2017 (14.x) are now located in the Microsoft Container Registry. Microsoft Container Registry is the official container registry for the distribution of Microsoft product containers. In addition, certified RHEL-based images are now published.
  • Microsoft Container Registry: mcr.microsoft.com/mssql/server:vNext-CTP2.0 
  • Certified RHEL-based container images: mcr.microsoft.com/mssql/rhel/server:vNext-CTP2.0

No comments:

Post a Comment