Microsoft SQL Server 2019 : Data Platform in a Box

Posted in: Microsoft SQL Server, Technical Track

I’m writing this blog post the day after Microsoft has unveiled SQL Server 2019 at their flagship Ignite 2018 conference. Officially named SQL Server 2019, it comes hot off the heels of the SQL 2017 release. This should be no surprise to anyone though, we know that the cloud has fuelled faster release cycles that “spill” into the on-premises product.

I have heard some people complain that this rate of release is “too fast” for them, but I personally see only positives with it. Microsoft still supports older versions (even extended support for SQL 2008!) and the compatibility level has been extended back to almost five versions now. If you don’t want or need to or can upgrade, no one is forcing you. However, if you do want to get the most value out of your Software Assurance then you get a new version, roughly 18 months apart.

Another popular argument is that there can’t really be that much new stuff when the release cycles are so close to each other. Well, turns out that one can be easily disproved as we can see from the release notes and information coming out regarding SQL 2019. Now, if you want the full exhaustive list, you can find it here. Following are the items that I’m most surprised or excited about, let’s take a look!

Note: credit for the images goes to Microsoft’s own documentation.

Big Data Clusters

This one is a very interesting development in terms of Microsoft’s strategy about SQL Server and big data. SQL will now come bundled with HDFS and Spark all in one integrated package. For people that have been intimidated by the big data stack, this can be an easy way to get up and running, with a product that you know and support you are familiar with.

Your people that are familiar with T-SQL can use Polybase and External Tables to query the HDFS data with SQL queries and then your data engineers or data scientists can work with Spark if that is what they prefer. You can also use SQL Server as the “port-of-entry” to your entire data state due to expanded capabilities of Polybase supporting now Oracle, Teradata, MongoDB and more. This is not the same as a linked server; Polybase will allow MPP style parallel reading and processing of data through these SQL Server clusters.

AlwaysEncrypted with Secure Enclaves

Microsoft introduced AlwaysEncrypted in Azure SQL DB and SQL 2016 as a way to keep encrypted data completely at the control of the client, not even possible to be decrypted by a SQL administrator. This approach had a couple of downsides: non-optimal performance as the data had to be encrypted/decrypted on the client side and the lack of rich query computation against the encrypted values.

To get around these issues and keep the promise of AlwaysEncrypted, Microsoft is introducing support for secure enclaves. Secure enclaves are protected areas of memory that perform trusted execution and do not allow visibility into their data to any other process, not even to the rest of the SQL Server code. Enclaves are implemented at the hardware level (like Intel SGX) or through virtualization extensions in what is called in Windows as Virtualization-based Security. In the particular case of SQL Server it is currently implemented as the latter.

With this in-place, the client is allowed to send their key to the SQL Server process, perform compute operations on the data inside the enclave and get results back, all without exposing this data to any other process running on the server. Cryptographic operations can then take advantage of the beefier server specs as well as rich computations can happen inside the enclave so that encrypted columns don’t have to suffer from poor querying capabilities.

Expanded Support for Persistent Memory Devices

This is a good one for the performance aficionados that want their database to have the fastest performance possible based on the available hardware in the industry. If you are not familiar with Persistent Memory devices, also called Storage-class memory, they are non-volatile memory devices that provide more density than RAM and faster speeds than SSDs. For more information you can check Intel’s video below:

With this release, SQL Server will support placing data, log and In-memory checkpoint files on these devices, providing file access at nanosecond latencies. It also includes a new mode of access for the files where it bypasses the OS storage stack and directly addresses the device’s storage through memory instructions for even faster results.

Intelligent Query Processing Improvements

Microsoft continues to invest on making database tuning an automated process that requires less human intervention. They started this Intelligent Query processing effort targeting mostly batch mode operations and functions but they are really expanding the scope on this release.

If you are a fan of database internals and query performance the improvements here will probably make you very excited. Batch mode is enabled now for rowstore tables (no need for dummy columnstore indexes anymore) and memory grant feedback is also enabled for queries that are not running in batch mode. Table variables will now also have deferred compilation so the optimizer will have an accurate cardinality number to use for optimization instead of the hard-coded estimates that have made table-variable plans unstable on previous releases.

Data Discovery and Classification

The regulatory environment for dealing with data is getting tighter and more complicated every day. We just saw the go-live of GDPR and the big impact it is still causing throughout the industry. To help clients assess and keep tabs of all their sensitive data, this new feature will allow for automated discovery of PII in your database as well as tagging and auditing access specific to this data.

You can then run canned reports against this information or write your own reports or tools that use the data classification metadata. I can see this capability making auditing and compliance a lot easier and automated.

Final Thoughts

As I mentioned before, these are just my picks but the entire list is bigger. Keep in mind this is right now a CTP release so we might see a couple of more things enabled by the time RTM rolls in. Depending on your requirements and your current situation, upgrading to SQL 2019 can be a very compelling scenario. I invite everyone to download the CTP and start playing around with the bits when you have a chance. I will be doing that and blogging in the future about specific features in detail as part of my SQL On The Edge series. Cheers!

email

Interested in working with Warner? Schedule a tech call.

About the Author

Microsoft Data Platform MVP and SQL Server MCM, Warner has been recognized by his colleagues for his ability to remain calm and collected under pressure. His transparency and candor enable him to develop meaningful relationships with his clients, where he welcomes the opportunity to be challenged. Originally from Costa Rica, Warner is fluent in English and Spanish and, when he isn’t working, can be found watching movies, playing video games, and hosting board game nights at Pythian.

No comments

Leave a Reply

Your email address will not be published. Required fields are marked *