By: Rajendra Gupta | Comments (6) | Related: > 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.
To install SQL Server 2019 and click on 'Download now' from this page.
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.
Microsoft released a document displaying the top 10 reasons to choose SQL Server 2019.
Early Adoption Program for SQL Server 2019
There is an Early Adoption program for SQL Server 2019. You can join the SQL Server 2019 early adoption program by signing up at this link Sign up Early Adoption 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 Adoption 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 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.
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.
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
- Stay tuned for more features of SQL Server 2019.
- Sign up for the SQL Server 2019 Early Adoption program.
- Download and explore SQL Server 2019 on the environment of your choice.
- Learn more about SQL Server 2019 features.
- Explore SQL Server 2019 tips.
About the author
Rajendra Gupta is a Consultant DBA with 14+ years of extensive experience in database administration including large critical OLAP, OLTP, Reporting and SharePoint databases.This author pledges the content of this article is based on professional experience and not AI generated.
View all my tips
