SQL Server 2019 Review

SQL Server 2019 Review

Tác giả: [email protected]
09:10 16/02/2022

The Big Tech Microsoft has released SQL Server 2019 edition with unique features. SQL Server 2019 is considered a suitable tool for database experts and a prominent tool for users to keep up with advanced technology.

Microsoft built SQL Server 2019 on top of the intelligent query processing features implemented in earlier versions of SQL Server. Microsoft also extended SQL Server to the new components of the database engine with more remarkable edges.

1. Introduction to SQL Server 2019

SQL Server 2019 is a database management engine built on the concept of artificial intelligence to facilitate, improve database services, secure, and reduce problems encountered when developing applications and housing data.

SQL Server 2019 integrated with the Cloud that offers organizations the benefits of high security and data synchronization across multiple computers and other modern devices.

SQL Server 2019 creates a unified data platform that comes with Apache Spark and Hadoop Distributed File System (HDFS) to get smarter with all data.

2. What is distinctive about SQL Server 2019?

SQL Server 2019 delivers innovative and industry-leading performance, security and compliance, mission-critical availability. The product also provides advanced analytics to all data workloads and supports big data capabilities built-in, which saves users a lot of time when commencing a project.

2.1. Big Data Clusters and PolyBase

Microsoft implements Big Data Clusters into SQL Server 2019 to bring flexibility and scalability to enterprise data platforms.

SQL Server 2019 big data cluster is deployed as a container on Kubernetes, connecting the SQL Server database engine directly to Apache Spark. As a result, users can get insights from all data via query relational data to obtain an overview.

This feature set is considered an improvement for PolyBase (an optional component in SQL Server) as about connecting SQL Server 2019 with Oracle, Teradata, and MongoDB while providing connectivity to other data sources. Microsoft calls this “data virtualization”. Data virtualization with PolyBase lets

you use SQL Server 2019 as a data center, querying multiple sources. PolyBase is included in the SQL Server Standard Edition, making it accessible for many users.

Additionally, users can install PolyBase as a scale-out group to improve the performance and scalability of SQL Server 2019.

This image has an empty alt attribute; its file name is rTIh_5oKYqm5kFSZTXT8A2m1XpAYj_PGV_eo3RgewFanC2Dwv16YIekcB9ksT8xB_Y6VXHirdQtdjohQoTVo-cCbsgWDWiIAsQFCCQJzmMhUqc1f_JHlDQ_9S7pBnC-dH8azMNxI
Component diagram of a SQL Server big data cluster | Source: Microsoft

2.2. Intelligent query processing

The Query Store is an excellent feature introduced in SQL Server 2016. This feature gives users the ability to obtain information about runtime and execution plans for queries. Therefore, the database administrator can quickly identify the root of the performance issue.

In SQL Server 2019, Microsoft gives the default Query Store features extra improvements by adding automatic tuning for performance regression.

Evaluate the performance using Query Store | Source: Microsoft
Evaluate the performance using Query Store | Source: Microsoft

2.3. Smart monitoring

To limit the resource contention that Query Store causes on some systems, SQL Server 2019 introduces an option to customize data collection. In addition, SQL Server 2019 also updates some improvements around the collection of execution plans.

With these updates, users can capture actual execution plans, check the number of rows used by the query, compare them with the estimates given by the optimizer, and identify performance issues quickly.

2.4. Faster Database Recovery

Microsoft has improved the database recovery process of SQL Server 2019 by implementing the version list to allow transactions to be updated much faster after a server restoration or update, with sample recovery taking only 1 second.

Fast data recovery time down from 90 seconds to 1 second | Source: Microsoft
Fast data recovery time down from 90 seconds to 1 second | Source: Microsoft

2.5. Performance Optimization

SQL Server often crashes when the system tries inserting sequential records into a very high volume table. This issue occurs due to memory contention since only one thread can access one data page at a given time. The in-memory OLTP feature is set up to handle it. However, its weakness is that it is not 100%

compatible with all data types, so it is not available everywhere. In addition, SQL Server 2019 introduced two additional features: “Optimize_for_sequential_key” and TempDB. While “Optimize_for_sequential_key” reduces the impact of issues, increasing performance by up to 40%, TempDB eliminates bottlenecks at the data layer by leveraging the in-memory OLTP feature.

2.6. Other features

Besides the above outstanding features, SQL Server 2019 allows users to create indexes in the maintenance window or re-build after a server crash. Enhanced encryption also allows the database engine to operate on data encrypted in T-SQL unseen by administrators.

SQL Server 2019 versions are now available at FPT Smart Cloud at good offers:

– SQL Server 2019 – 1 User CAL

– SQL Server 2019 – 1 Device CAL

– SQL Server 2019 Enterprise Core – 2 Core License Pack

– SQL Server 2019 Standard Edition

– SQL Server 2019 Standard Core – 2 Core License Pack

As the Gold Partner and Business Partner for cloud computing solutions of Microsoft in Vietnam, FPT Smart Cloud is honored to accompany businesses in accessing advanced technology solutions to customers from Microsoft.

For further information about FPT Smart Cloud services, please contact us.

Fanpage: https://www.facebook.com/microsoft.fptsmartcloud/

Email: [email protected]

Hotline: 1900 638 399

FPT Smart Cloud – The leading consulting and solution provider on Cloud Computing and Artificial Intelligence in Vietnam.

SQL Server 2019 Review