Learn more about SQL Server tools

mssqltips logo
 

Tutorials          DBA          Dev          BI          Career          Categories          Webcasts          Whitepapers          Today's Tip          Join

Tutorials      DBA      Dev      BI      Categories      Webcasts

DBA    Dev    BI    Categories

 

Overview of SQL Server 2019 Features


By:   |   Last Updated: 2018-10-30   |   Comments   |   Related Tips: More > SQL Server 2019

Problem

Microsoft recently announced the preview of SQL Server 2019 on September 24 at the Ignite 2018 Conference. Really! Yes, Microsoft is moving very fast with feature enhancements, improving security, performance, enhancing the capabilities of machine learning, data integration, etc. In this tip, we will see an overview of some of the exciting new features of SQL Server 2019.

Solution

As per the Microsoft SQL Server blog, "SQL Server 2019 creates a unified data platform with Apache Spark and Hadoop Distributed File System (HDFS) packaged together with SQL Server as a single, integrated solution".

To install SQL Server 2019, go to the Downloads page and it shows important features you'll love about SQL Server 2019.

SQL Server 2019 important features

To install SQL Server 2019 and click on 'Download now' from this page.

introducing sql server 2019

We can select the desired Operation system version to install SQL Server 2019. We will cover more on this in later tips. We can now build SQL Server on Windows, Linux, Docker, and Big Data Analytics containers with kubernetes images.

Install SQL Server 2019

Microsoft released a document displaying the top 10 reasons to choose SQL Server 2019.

Top 10 reasons to chose SQL Server 2019

Early Adaption Program for SQL Server 2019

There is an Early Adaption program for SQL Server 2019.  You can join the SQL Server 2019 early adoption program by signing up at this link Sign up Early Adaption Program. This program is especially for customers who can test the product thoroughly and provide recommendations and feedback to Microsoft to improve the product.

Once enrolled in the Early Adaption program, you get the following benefits:

  • We can test the latest features of SQL Server 2019 and get production support from Microsoft to fix any issues.
  • We can test and provide feedback to the Microsoft database engineering team for any feature enhancements and bugs.

Let's briefly explore some of the important features of the SQL Server 2019 preview.

Data Virtualization in SQL Server 2019

SQL Server 2019 provides a data virtualization solution which is an improvement to the ETL process. Data virtualization allows integrating data from different sources such as MongoDB, Oracle, DB2, Cosmos, and Hadoop Distributed File System (HDFS) without moving data around.

  • Eliminate the need of data movement: In the organization, we have a variety of the database servers which might include relational databases such as SQL Server, Oracle, MySQL, DB2, etc. and non-relational databases such as Mongo DB, Big data, etc. Normally in traditional business intelligence systems, we copy data and load the data into a reporting platform with extract-transform-load (ETL) processes. ETL processes have their own pros and cons which includes:
    • ETL process requires development. It could be a complex solution that might require more effort to maintain and support it.
    • ETL processes are relatively slow and might take a long time depending upon the data flow and logic in place.
    • We move data from different sources to SQL Server, transform the data, and introduce the necessary logic. If we are working with a large amount of data, it also requires more storage.
    • If we are dealing with production data or PII related data, it must be secure throughout the process.

The below image shows data virtualization options in SQL Server 2019.

data sources

Data virtualization integrates data from different sources and does not need replicating or moving the data. As shown above, a single virtual data layer delivers unified data services that can support multiple applications. In SQL Server 2019, we define this virtual data layer as Data hub or Data Lake. We can process data through this layer. This layer also works as a data controller between multiple data sources.

SQL Server 2019 allows us to integrate data from structured and unstructured data sources. We can now process diverse big data and relational data sources using Transact-SQL from SQL Server using PolyBase.

We can see below PolyBase support external databases.

polybase
(Image courtesy: Microsoft blogs)

Big Data Clusters in SQL Server 2019

SQL Server 2019 Big Data Clusters adds enhancements to PolyBase to improve the data virtualization experience between SQL Server and other database engines. It provides bi-directional integration with big data Hadoop and Apache Spark systems. We can easily manage the big data using the built-in tools with SQL Server 2019.

It offers three major pieces of functionality:

  • Data virtualization - Combine data from many sources without data movement. It improves security, performance to process data.
  • Managed SQL Server, Spark, and Data Lake - Store high volume data in a data lake and access it easily using either SQL or Spark. It is very easy to manage using management services, admin portal, and integrated security.
  • Complete AI platform - Easily feed integrated data and prepare the solution. This solution works to operate all models in one system.
Big Data Clusters SQL Server 2019
(Image courtesy: Microsoft blogs)

In SQL Server 2019, we can combine big data with the analytical database or traditional database system. This provides data scientists to access big data with simple T-SQL queries. Users can also use the Power BI to work with the data presented.

Additional New Features in SQL Server 2019

Here are some other features enhancements.

Support to Persistent Memory (PMEM) Devices

SQL Server 2019 provides support to Persistent Memory (PMEM) devices. SQL Server directly accesses the device, bypassing the storage stack of the operating system for the files placed on the PMEM device.

Columnstore Index Enhancements

SQL Server 2019 also provides enhancements to columnstore index features such as columnstore index maintenance, better metadata memory management, a low-memory load path for columnstore tables, and improved performance for bulk loading to columnstore indexes.

Resumable Online Index Creation

SQL Server 2019 also provides support for resumable online index creation similar to resumable online index rebuilds in SQL Server 2017.  Check out this tip about resumable online index creation.

Up to Five Synchronous Replica Pairs for Availability Groups

We can now configure up to five synchronous replicas in an Availability Groups AG (one primary and up to four secondary replicas) with automatic failover between these replicas.

Enable High Availability Configurations for SQL Server Running in Containers

With SQL Server 2019, we can configure Always on Availability Groups using Kubernetes as an orchestration layer.

Better Scale-out with Automatic Redirection of Connections Based on read/write Intent

In SQL Server 2019, client applications can connect to any of the replicas of the Availability Group. The connection redirects to the primary replica as per the AG configuration and connection string.

SQL Data Discovery and Classification

We have explored this feature in SSMS 17.5. In SQL Server 2019, SQL Data discovery and classification is integrated into the SQL Server engine with new metadata. This enables us to ensure GDPR and other compliance needs for our databases.

Always Encrypted with Secure Enclaves

SQL Server 2019 introduces the secure enclave technology. A secure enclave extends client applications, data trust to the server side. It secures the data from the malware and privileged users.

Certificate Management Functionality in SQL Server Configuration Manager

In SQL Server 2019, certificate management is integrated into the SQL Server Configuration Manager. We can view, validate the certificates being used in SQL Server instance. We can view and validate certificates installed in a SQL Server instance. This also provides detail about certification expiration dates. This feature helps to manage certificates in a better way. We can also deploy certificates for AG instances starting from the primary replica.

UTF-8 Support

SQL Server 2019 provides support for UTF-8 character encoding. We can now create a char or varchar column to store UTF-8 data. This feature improves data compatibility and performance improvements.

Vulnerability Assessment

We can use vulnerability assessment to track compliance of SQL Server instances and Azure SQL Database instances with recognized security best practices. We can implement using the reports shared by this tool. This provides easy to implement security compliance such as GDPR.

Enhancement in SQL Graph

In SQL Server 2017 we have explored the graph databases, some of the tips for reference are:

  • Graph processing with SQL Server
  • SQL Server 2017 Graph Database Example
  • SQL Server 2019 provides enhancements to include match support with T-SQL MERGE and edge constraints. We can now limit the type of nodes a given edge type can connect. This helps users to enforce restrictions on the edge table. It also maintains data integrity in their graph database.
  • SQL Server 2019 also support MATCH predicates to specify graph relationships in a single statement, instead of separate DML statements. This makes easy to query the graph database.
Next Steps


Last Updated: 2018-10-30


next webcast button


next tip button



About the author
MSSQLTips author Rajendra Gupta Rajendra is a Consultant DBA with 9+ years of extensive experience in database administration including large critical OLAP, OLTP, Reporting and SharePoint databases.

View all my tips
Related Resources




Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name    *Email    Email me updates 


Signup for our newsletter
 I agree by submitting my data to receive communications, account updates and/or special offers about SQL Server from MSSQLTips and/or its Sponsors. I have read the privacy statement and understand I may unsubscribe at any time.



    



Learn more about SQL Server tools