In this article, we will be discussing about what’s new in SQL Server 2019, in terms of new features and enhancements.
In September 2018, during the MS Ignite Conference, Microsoft announced the Community Technology Preview of SQL Server 2019 (CTP 2.0). This is an exciting new version with a plethora of new features, as well as with enhancements to existing features. Read this article, in order to learn what’s new in SQL Server 2019 (currently CTP 2.0).
Installing SQL Server 2019
Prior to talk about the new features in SQL Server 2019, let’s talk a bit about how you can install it. I have prepared a short presentation, which illustrates a simple installation of SQL Server 2019 CTP 2.0 on my local machine.
Note that this is a simple installation, performed for demo purposes. Therefore, since I have installed everything on local C drive (never do that on Production environments), I have not taken into consideration any performance or scalability considerations.
You can check out the relevant presentation below.
What’s New in SQL Server 2019
In this article, we will talk for some of these features along with providing some screenshots. We will structure our discussion based on three areas of SQL Server 2019:
- High Availability
- SQL Server on Linux
- Big Data
- Other Enhancements
For the full list of features, please visit Microsoft’s official documentation.
Learn What’s New in SQL Server 2019 – Enroll to our Online Course!
Check our online course titled “SQL Server 2019: What’s New (New and Enhanced Features)”
(special limited-time discount included in link).
Learn about the exciting new features and enhancements in SQL Server 2019 Database. Many live demonstrations included!
Intelligent query processing
Intelligent query processing in SQL Server, is a family of features that improve the performance of existing workloads with minimal implementation effort. Currently, up to the CTP 2.0 release of SQL Server 2019, the following features are supported:
- Adaptive Query Processing
- Table variable deferred compilation
- Approximate query processing
- Batch Mode on Rowstore
Learn more about Intelligent query processing.
Clustered columnstore online index build and rebuild
In SQL Server 2019, you can build and rebuild clustered columnstore indexes. This is very useful, especially in Data Warehouse environments, where columnstore indexes are heavily used.
Resumable online index create
With this feature, an index create operation, can be resumed after interruption.
New options added in order to support colunstore indexes.
Always Encrypted with secure enclaves
This is a new security feature that provides additional functionality to the the existing “Always Encrypted” feature. This feature, addresses previous limitations, by allowing computations on plaintext data inside a secure enclave on the server side.
Data discovery and classification
Data discovery and classification is natively built into SQL Server, and allows you to classify columns in your database that contain sensitive information. This is extremely useful for Compliance scenarios that have to do with data security.
Strengthen your SQL Server Administration Skills – Enroll to our Online Course!
Check our online course on Udemy titled “Essential SQL Server Administration Tips”
(special limited-time discount included in link).
Via the course, you will learn essential hands-on SQL Server Administration tips on SQL Server maintenance, security, performance, integration, error handling and more. Many live demonstrations and downloadable resources included!
Certificate management in SQL Server Configuration Manager
You can now import directly into SQL Server and manage SSL/TLS certificates.
Here’s a screenshot below:
Briefly, in SQL Server Configuration Manager, with regards to SSL/TLS certificate management, you can now perform the following actions:
- View and validate certificates installed in a SQL Server instance.
- Report on certificates close to expiration.
- Deploy certificates across machines participating in Always On Availability Groups (from the node holding the primary replica).
- Deploy certificates across machines participating in a failover cluster instance (from the active node).
High Availability Enhancements
Enhanced database level health detection for Availability Group databases
The above functionality has been enhanced in order to detect additional error conditions and -if required- to initiate a failover or restart of the involved availability group resource.
Up to five synchronous replica pairs
SQL Server 2019 supports up to 5 synchronous replica pairs (1 primary- 4 secondary). In SQL Server 2017 the limit was set to 3 synchronous replica pairs.
Secondary replica connection redirection in Always On Availability Groups
The secondary replica connection redirection, allows redirecting connections to be redirected to the primary replica, regardless of the target server specified in the connection string. This redirection can be achieved without the use of a listener, and it is suitable for such cases.
UTF-8 support in SQL Server 2019, is another amazing feature. The basic idea is that when you use a collation ending in _UTF8, along with the datatypes CHAR and VARCHAR, you can store, import and export data in unicode, without needed to use the NCHAR and NVARCHAR data types. This allows for significant storage savings.
An example of using UTF-8
See the below example, where I have created a UTF-8 enabled database and table, inserted and then successfully retrieved records with Greek characters, by using VARCHAR columns instead of NVARCHAR.
Java language programmability extension
By using this extension (currently in preview), you can execute Java code in SQL Server. This feature is related to the Machine Learning Services (In-Database) feature. It gets installed when you installed the Machine Learning Services feature.
SQL Server on Linux
There are many new features regarding SQL Server on Linux. Among other, SQL Server 2019 on Linux now supports:
- SQL Server replication.
- Microsoft Distributed Transaction Coordinator service (MSDTC).
- Machine-Learning Services (In-Database)
- Improved integration between SQL Server on Linux and Active directory, via the OpenLDAP support for third-party AD providers.
- …and much more
Many new features and enhancements are also introduced, regarding Big Data.
Other significant new features and enhancements in SQL Server 2019 (CTP 2.0), include: SQL Graph features, new PolyBase connectors, SQL Server Machine Learning Services failover clusters, and much more.
Also, along with the release of SQL Server 2019, a new release of Azure Data Studio is out, as well as SQL Server Management Studio (SSMS) v18.0 (Preview).
Moreover, SQL Server 2019, introduces the “Transparent Data Encryption (TDE) Scan – Suspend and Resume” option. Learn more about it in the below video.
Watch a Preview of our Online Course on SQL Server 2019
In the below video, you can watch a short preview of my online course: “SQL Server 2019: What’s New (New and Enhanced Features)“.Enroll from $12.99
Watch also: How to Install SQL Server 2019 Express and the Latest Version of SQL Server Management Studio (SSMS)
Learn more about SQL Server 2019
You can learn more about SQL Server 2019 by accessing the below online resources:
- Enroll to our course on Udemy “SQL Server 2019: What’s New”
- MS Docs article on all the new features in SQL Server 2019
- SQL Server 2019 white paper
- SQL Server 2019 datasheet
- SQL Server 2019 Official Page
Featured Online Courses:
- Introduction to Azure SQL Database for Beginners
- SQL Server 2019: What’s New – New and Enhanced Features
- SQL Server Fundamentals – SQL Database for Beginners
- Essential SQL Server Administration Tips
- Boost SQL Server Database Performance with In-Memory OLTP
- Essential SQL Server Development Tips for SQL Developers
- Working with Python on Windows and SQL Server Databases
- Introduction to Computer Programming for Beginners
- .NET Programming for Beginners – Windows Forms with C#
- Introduction to SQL Server Machine Learning Services
- Entity Framework: Getting Started – Complete Beginners Guide
- How to Import and Export Data in SQL Server Databases
- Learn How to Install and Start Using SQL Server in 30 Mins
- A Guide on How to Start and Monetize a Successful Blog
- New Online Course – SQL Server 2019: What’s New
- How to Offline Install SQL Server on Linux
- Getting Started with SQL Server on Linux
- What’s New in SQL Server 2019
- … more articles on SQL Server 2019
Subscribe to our newsletter and stay up to date!
Check out our latest software releases!
Check out Artemakis’s eBooks!
Rate this article:
Reference: SQLNetHub.com (https://www.sqlnethub.com)
Artemakis Artemiou is a Senior Database and Software Architect, Certified Database, Cloud and AI professional, Author, a 9 Times Microsoft Data Platform MVP (2009-2018). He has over 20 years of experience in the IT industry in various roles. Artemakis is the founder of SQLNetHub. Artemakis is the creator of the well-known software tools Snippets Generator and DBA Security Advisor. Also, he is the author of many eBooks on SQL Server. Moreover, Artemakis teaches on Udemy, you can check his courses here.