New Features of SQL Server 2019

It’s that time again to talk about the New Features of SQL Server 2019. Years ago, there was no need to keep so up-to-date with the New Features of SQL Server due to the release dates of each version (I’m talking about you SQL Server 2000 and SQL Server 2005) between each version. Now, with a much increased release cycle it is critical for any SQL Server DBA to stay in touch with these New Features. Of course, you don’t have to know about all the details of the new features, you just have to know what is there. As the Merovingian would say in the Matrix Reloaded, who has time?

Microsoft SQL Server 2019 Logo

Matrix and SQL Sequels

Well, that time has now come (as well as a new Matrix sequel, yay) and SQL Server was released towards the latter part of last year on the 4th November 2019.

You may think that SQL Server 2019 is an unnecessary release. I believe Microsoft are showing their dominance of the RDBMS market and not resting on their laurels but forging ahead with new and innovative features.

The New Features list is long, so I’m going to add all the new features here from the Microsoft post – What’s new in SQL Server 2019 (15.x)

New Features Path

My plan, and there must always be a path, Neo, is to go through the most relevant items and have a play and document my findings here, so if there’s a hyperlink then I’ve looked at it, click it, have a read, let me know.

It will also form a great reference list of any new Sequallity SQL Server 2019 training modules (SSRS, DBA, TSQL, Performance Tuning) I have to build going forward to include the up-to-date features of SQL Server 2019.

There’s so much to do…with these New Features

There’s no need to feel inundated, I don’t. I am excited by the challenge these new features bring and I am looking forward to the days when a client says, we need this and I go ah-ha, there’s a great new feature in SQL Server 2019 that does that. To be fair, been there already only a month after SQL Server 2019’s release date and the winner was…Optimize For Sequential Key.

However, I tuned the database so well, we didn’t need to implement this new feature, which is a shame but it meant the client could stay on their 2016 version if they wanted, they didn’t mind going to SQL Server 2019. If performance degrades again, we’ve got this in my bag if needed.

It’s always good to have a Plan B.

Big Data Clusters

Scalable big data solution
Data virtualization with PolyBase

Intelligent Query Processing

Row mode memory grant feedback
Batch mode on rowstore
Scalar UDF Inlining
Table variable deferred compilation
Approximate query processing with APPROX_COUNT_DISTINCT

In-Memory Database

Hybrid buffer pool
Memory-optimized TempDB metadata
In-Memory OLTP support for Database Snapshots

Intelligent performance

OPTIMIZE_FOR_SEQUENTIAL_KEY
Forcing fast forward and static cursors
Resource governance
Reduced recompilations for workloads
Indirect checkpoint scalability
Concurrent PFS updates
Scheduler worker migration

Monitoring

WAIT_ON_SYNC_STATISTICS_REFRESH
Custom capture policy for Query Store
LIGHTWEIGHT_QUERY_PROFILING
sys.dm_exec_requests column command
sys.dm_exec_query_plan_stats
LAST_QUERY_PLAN_STATS
query_post_execution_plan_profile
sys.dm_db_page_info(database_id, file_id, page_id, mode)

Graph

Edge constraint cascade delete actions
New graph function – SHORTEST_PATH
Partition tables and indexes
Use derived table or view aliases in graph match query

Unicode support

Support for UTF-8 character encoding

Language extensions

New Java language SDK
Java language SDK is open source
Support for Java data types
New default Java Runtime
SQL Server Language Extensions
Register external languages

Spatial

New spatial reference identifiers (SRIDs)

Error messages

Verbose truncation warnings

Security

Always Encrypted with secure enclaves
Certificate management in SQL Server Configuration Manager
Data Discovery & Classification

Availability Groups

Up to five synchronous replicas
Secondary-to-primary replica connection redirection
HADR Benefits

Recovery

Accelerated database recovery

Resumable operations

Online clustered columnstore index build and rebuild
Resumable online rowstore index build
Suspend and resume initial scan for Transparent Data Encryption (TDE)

Linux

Replication support
Support for the Microsoft Distributed Transaction Coordinator (MSDTC)
OpenLDAP support for third-party AD providers
Machine Learning Services on Linux
TempDB improvements
PolyBase on Linux
Change Data Capture (CDC) support

Containers

Microsoft Container Registry
Non-root containers
Red Hat certified container images
PolyBase and Machine Learning support

Setup Options

New memory setup options
New parallelism setup options

SQL Server Machine Learning Services

Partition-based modeling
Windows Server Failover Cluster

SQL Server Analysis Services

Calculation groups in tabular models
Query interleaving
Many-to-many relationships in tabular models
Property settings for resource governance
Governance setting for Power BI cache refreshes
Online attach

SQL Server Integration Services

Flexible file task
Flexible file source and destination

SQL Server Master Data Services

Support for Azure SQL Database managed instance databases
New HTML controls

SQL Server Reporting Services

Azure SQL Managed Instances
Power BI Premium datasets
enhanced accessibility
Azure Active Directory Application Proxy
Transparent Database Encryption
Microsoft Report Builder Updated

Leave a Reply