Benefits of moving from SQL Server platform 2016 to 2019

Many companies are still using the 2016 version of SQL Server and Analysis services since the official support lifespan is up to 2025. This article will help the Data Analytics and Business Intelligence department managers to decide if it is the right time to upgrade to the 2019 platform now.

We will take a look at some of the features relevant for a data and analytics platform when moving from 2016 to 2019 (including those from 2017). And specifically focusing on the benefits for Data Science, Governance, Analytics, and Business Intelligence. We would not focus on the benefits of the IT department and database administrators except for features that improve query and data update performance.

Data Analytics:

Calculation groups in Tabular models. According to some experts, it's the most important feature ever implemented in DAX. It would spare development time spent on time intelligence calculations. Now Tabular models would not require redundant measures created for every combination of base measure and calculation type needed e. g. events by month-to-date (MTD), quarter-to-date (QTD), year-to-date (YTD), year-to-date for the previous year (PY).

Object-level security in Tabular models. Version 2016 implemented row-level security. In version 2019, access to specific users or groups also could be set for columns and tables. In our days of GDPR and other data restraining policies, the ability to provide users the data which has passed through granular control is highly demanded.

The introduction of many-to-many relationships for two non-unique tables helps to avoid unnecessary “snowflake” models.

New small DAX features are added with every new version of SSAS. Each of them might replace many lines of coding. We are particularly fond of the dynamic format string, detailed model drill through in Excel, and introduction of IN operator for DAX.

Data Science & Exploration:

Support for Machine Learning, R, and Python has for SQL Server provides more flexibility for data scientists.

PolyBase feature was introduced in SQL Server 2016 and allowed reading data from external data sources like Hadoop and Azure using SQL queries. SQL Server 2019 added connections to Oracle, Teradata, MongoDB, SQL Server, and ODBC.

Master Data Services:

HTML controls replace all former Silverlight components. Silverlight will be supported until 2021. HTML works faster, and users do not need extra plugins installation.

It is now possible to move MDS to Azure managed instance in Cloud.

Performance:

Query interleaving with short query bias allows concurrent queries to share CPU resources, so fast queries are not blocked by the slow ones.

Intelligent Query Processing is a behind-the-scene set of enhancements that affect the behavior of the Query Optimizer that generates execution plans for queries. According to some observations, this could give up to a 10x performance boost for certain queries.

Automatic tuning, introduced in SQL Server 2017,  is a database feature that provides insight into potential query performance problems, recommend solutions, and automatically fix identified problems.

Data Governance:

A new feature of SQL Data Discovery and Classification is natively built-in SQL Server 2019 and allows marking of columns in a database that contains sensitive information.

This feature is designed to help with GDPR compliance and traceability for sensitive information. The classification metadata is stored on SQL object level and is not necessarily designed for a data platform or data warehouse where data flows through ETL processes and are moved to SSAS and or Power BI.

Since the scope of compliance is bigger than one database or database object, we find the classification a bit limited in a data warehouse / BI environment. We think that data Tagging in the latest Xpert BI release is a more flexible and manageable solution. The Xpert BI tagging (classification) includes also propagation, i.e. the column lineage enables a tag to ‘follow’ the column from source to destination, and enables searching and other flexible metadata reports.

Administration:

Accelerated Database Recovery (ADR) - speeds up the time SQL server needs to perform database recoveries, transaction rollbacks, and failovers.

Always Encrypted With Secure Enclaves allows transparent column encryption without giving administrators access to the decryption keys.

With the support lifespan until 2025 we still have years to go with SQL Server 2016. The announced support lifespan for SQL server 2019 is the year 2030.

Is now the right time to make the move?

It will be the right time to move to 2019 if your analytics delivery time is more than desired, and developers complain about the lack of DAX functionality. It is the way to go if the information security is concerned with the uncontrollable degree of sensitive details exposed in the reports.  And it might be a remedy if your server environment suffers from performance issues.

One thing to remember when you've upgraded SQL and SSAS server to 2019, to enable the new features you need to update the compatibility level for your SQL databases and analysis models.