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 (CTP 2.0)
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 SlideShare 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 SlideShare 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 STP 2.0:
- High Availability
- SQL Server on Linux
- Big Data
- Other Enhancements
For the full list of features, please visit Microsoft’s official documentation.
Learn all about SQL Server 2019 Now!
Check my course “SQL Server 2019: What’s New” on Udemy and learn about all major features and enhancements in SQL Server 2019! See you inside the course!
– Artemakis Artemiou, Senior SQL Server & Software Architect, Former Data Platform MVP
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.
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).
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 CTP 2.0, a new release of Azure Data Studio is out, as well as SQL Server Management Studio (SSMS) v18.0 (Preview).
Learn more about SQL Server 2019
You can learn more about SQL Server 2019 by accessing the below online resources:
- Enroll to my course on Udemy “SQL Server 2019: What’s New” (special discount included in link)
- MS Docs article on all the new features in SQL Server 2019
- SQL Server 2019 white paper
- SQL Server 2019 datasheet
- Download SQL Server 2019 CTP 2.0
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)