SQL Server 2019–CTP 2.0

At Microsoft Ignite in Orlando Florida, it was announced this morning the release of Microsoft SQL Server 2019 CTP 2.0 (preview into what is coming with the new release of SQL Server). To watch the release video to get an insight into what is happening have a look here. Now there is a lot being packed into this new release of the product and as like all previews, there will more than likely be some modifications or even some new inclusions once the product is released for general availability.

An overview of some of the key features that have been included into this preview release are:

• Big Data Clusters
• Enhancements / New features in the Database Engine
• Enhancements / New features running on Linux
• Improved Security
• Enhancements to the toolset (SSMS, Azure Data Studio)

While I have only outlined the major points above we will take a closer look at these six key areas in more detail allowing you to understand what they mean for you.

Big Data Clusters

A brand new scalable solution combining SQL Server 2019, SPARK and HDFS Docker containers running on Kubernetes. All of these components in 1 place allow you to access (read/write & process) your big data from T-SQL or Spark, giving you the ability to combine and analyse your high value relational data along with your high volume Big Data.

The massive benefit with this solution is the ability to query your external data sources without having to import the data, thus making it quicker for you to understand and make decisions on your data. With the large scale storage capabilities due to HDFS storage pools, and the ability to scale out horizontally allows for increased performance at your finger tips. With the out of the box AI and Machine Learning Capabilities this allows you to really dig into your data providing those much needed analytical insights.

You can manage and maintain your Big Data Cluster through SSMS or Azure Data Studio. To learn more about Big Data Clusters have a look at SQL Server 2019 Big Data Clusters.

Database Engine Enhancements

The enhancements and new features that have been included into this preview release do provide a compelling reason to consider adding SQL Server 2019 into your roadmap to upgrade and migrate to SQL Server 2019.

Your data is even more highly available with the improvements to Resumable Online Index Builds, being able to build Clustered Columnstore Indexes in an online fashion, increases to the number of synchronous availability replicas from 3 to 5 as well as the auto failover partners to match the 5 synchronous replicas as well as having support for Availability Groups across containers using Kubernetes.

Linux Enhancements

Since the support for SQL Server on Linux was introduced in SQL Server 2017, this feature set has been playing catch up with the rest of the product and it is catching up very quickly.

SQL Server 2019 takes this feature set along leaps and bounds with support for Replication, Microsoft Distributed Transaction Coordinator, Availability Groups running on Docker Containers, Open LDAP support for 3rd party AD providers, Machine Learning on Linux. Given I have not started looking at SQL Server on Linux yet as there is just so much happening I will let you read more about these feature improvements here.

Improved Security

This set of improvements is something that is never going to stop let alone slow down on improvements. But SQL Server 2019 gives us Always Encrypted (introduced in SQL Server 2016) with Secure Enclaves.

What does this mean for me I hear?

Well with the feature release in SQL Server 2016, the data and corresponding cryptographic keys cannot appear as plain text inside of the SQL Server Database Engine. Now this makes your data secure from being accessed when it should not but it also restricts the functionality of the Database Engine on your encrypted data. That means you have to undertake anything on the data at the client side where it is encrypted. A secure enclave is a protected region of memory within the SQL Server Database engine, that appears as a black box to the rest of Database Engine now allowing for data manipulation to be undertaken on the server side while keeping the data protected.

Toolset Enhancements

The two pronged attack continues with providing us tooling to manage and maintain our data. SQL OPs Studio is no longer, but instead it has had a name change and is now called Azure Data Studio. Azure Data Studio provides us the ability to support our environments on multiple platforms (Windows, Linux & MacOS) while Management Studio is the main player at the game with all of the functionality we are all comfortable with. You can download Azure Data Studio here, while you can get the latest updates for SSMS from here.