Comparison to other SQL Server Versions
New features in SQL Server 2019
1. Deploy Big Data Clusters and Interact with your Big Data
2019 Server has introduced big data clusters. The essence of machine learning or
Artificial Intelligence (AI) on big data clusters has made data processing and
analytics much easier. Hence, big data clusters can deal with all the data sources
and analyze every type of data in a way that humans or traditional data processing
cannot.
You can run Python, Java, or R scripts from built-in Apache Spark—analytics engine
for big data and machine learning—to combine structured, unstructured, relational,
non-relational, high-volume big data, data stored in HDFS storage pools, and
analyze it.
2. Choose Language & Platform of your Choice
SQL server 2019 is flexible. It allows you to choose the language and runs on
Windows and Linux. Moreover, you can use Kubernetes to deploy and manage your
deployments.
You can also read: How to Fix SQL Database Error 7929?
3. Improved Performance and Scalability
The all-new SQL Server has improved Intelligent Database features in-memory
database capabilities such as intelligent query processing, persistent memory
support, and memory-optimized ‘tempdb’. This takes the performance of all your
database workloads to the next level. You can also migrate to Azure without
modifying your applications or database design, which defines its scalability.
4. More Secure
SQL Server 2019 provides mission-critical security to your data. It has built-in
features for data protection and monitoring. It gives security alerts on suspicious
activities, helps you to identify and fix the misconfigurations and security gaps. You
can also encrypt sensitive data and enable customized data access.
5. High Availability
The number of synchronous replicas in the new SQL server has increased to five
from three in SQL server 2017. Out of these five replicas, one is the primary replica
and the other four are secondary replicas. This assures high availability of the SQL
server. The new SQL server also has a feature called Accelerated Database
Recovery (ADR). This reduces the time to recover after a restart.
6. Power BI report Services Included
The inclusion of Power BI services with new 2019 SQL Server is noteworthy. This
enables users to explore visual data and access interactive Power BI reports on any
device. Interactive analysis of data lakes and visual data exploration help users to
discover patterns quickly and make better and faster decisions.
As per the above comparison, we found some extra
features that SQL server 2019 supports. I am going to
explain those features in detail.
Big Data Clusters and DataVirtualization with Polybase
Deploy and run applications in Big Data Clusters.
Store big data in HDFS managed by SQL Server.
Polybase module allows for fast and parallel T-SQL
queries. Polybase is getting expanded to support
Oracle, Teradata, MongoDB, and more.
Query data from multiple external data sources
through the cluster.
Easily merge and analyze high-value relational data
with large-volume big data.
Intelligent query processing and Intelligent Database
Intelligent Database features of SQL Server 2019
improve the performance and scalability of all
database workloads without any changes to your
application or database design.
Intelligent query processing means improvements on
the Query Optimizer that will improve the quality of
the plans for all applicable queries. This includes
dynamic memory grants for rowstore tables, table
variable deferred compilation, batch mode on
rowstore, and more.
Automatically transforms scalar UDFs into relational
expressions and embeds them in the calling SQL query.
Accelerated database recovery
This is a completely new feature for SQL Server to
perform database recovery in the time of a transaction
rollback, an instance restart, or an availability group
failover.
With accelerated database recovery (ADR), SQL server
2019 Reduce the time to recover after a restart or a
long-running transaction rollback.
Security
Always encrypted with secure enclaves
The term encryption is first implemented in SQL
server2016 for transparent column encryption. The
drawback of the first implementation is due to SQL
Server not being able to decrypt the data, the queries
on the SQL side couldn’t do any computations or
manipulation of the actual column values. SQL Server
can now safely encode a part of memory to perform
calculations on these encrypted columns without ever
exposing the unencrypted values to the rest of the
processes
Cloud Readiness
In the SQL Server 2019 version, a new feature for cloud
readiness is added. The previous version (i.e. 2016,
2017) was also able to backup and disaster recovery to
azure. But this new version of SQL Server supports free
asynchronous replication on Azure Virtual Machines
for disaster recovery.
SQL Server 2019 (15.x) increases the maximum
number of synchronous replicas to 5.
Management and programmability
Support for the java data type.
Simplifies the development of Java programs that can
be run from SQL Server.
Support UTF-8
A new Data Definition Language (DDL), CREATE
EXTERNAL LANGUAGE, registers external languages,
such as Java, in SQL Server.
BI and Analytics Support (Tabular Model)
The new version of the SQL server allows many-to-
many relationships between tables, where both
columns are non-unique.
Calculation groups can fundamentally reduce the
number of duplicate measures by grouping common
measure expressions as calculation items
