Microsoft SQL Server 2019: Complete Overview

Whether you are exploring SQL Server for a personal project or a business application, the range of possibilities can be overwhelming. In this guide, we cover the most important features of SQL Server 2019 and explain why it remains a popular choice for database professionals.

Microsoft SQL Server 2019 is a relational database management system (RDBMS) that supports a wide range of transaction processing, business intelligence and analytics applications in enterprise IT environments.

The release of SQL Server 2019 brought many new and exciting features. The database engine, security, database recovery and several other areas have all been significantly improved.

Are you tired of TempDB data bottlenecks? Fed up with long database recovery times caused by lengthy transactions? Frustrated by low-performance queries due to scalar UDFs? Waiting ages for a unique count to complete on a huge table with a billion rows? SQL Server 2019 may have the solutions you have been looking for.

Key Features of SQL Server 2019

SQL Server 2019 is designed to solve the challenges of the modern data professional. Here are its headline capabilities:

• Store enterprise data in a data lake and offer query capabilities through both SQL and Spark.
• Reduce the need for extract, transform and load (ETL) applications by minimising data movement.
• Integrate and secure machine learning applications with scalable performance.
• Achieve performance improvements with minimal changes to applications and queries.
• Improve confidential processing of data through hardware enclaves.
• Increase uptime and availability through features such as ADR (Accelerated Database Recovery).
• Extend the power of the T-SQL language in a secure and robust way.
• Run applications and deploy databases on multiple operating systems and platforms.
• Reduce upgrade risk and adopt new SQL features at your own pace, thanks to built-in database compatibility layers.

Most Useful Features in SQL Server 2019

1. Accelerated Database Recovery (ADR)

Accessing the database can take a considerable amount of time and may not always complete on schedule due to long-running active transactions. Managing the transaction log to prevent overflow is also a well-known challenge.
With SQL Server 2019, the log truncation process is far more aggressive, preventing it from growing out of control and consuming all your storage space. When enabled, ADR uses a persistent version store for change tracking that allows instant rollback of long-running transactions, regardless of how long they have been running or how large they are.

2. Replication on Linux

SQL Server on Linux now supports replication, including Snapshot, Transactional and Merge types. The instance can serve any replication role: Publisher, Distributor or Subscriber. You can even mix and match operating systems — for example, Publisher and Distributor can run on Windows or Linux, and Subscribers can run on a combination of both.

3. Memory-Optimised TempDB Metadata

Tired of the bottlenecks caused by high TempDB workloads? You can now move TempDB system tables to memory-optimised tables to relieve your disks. It is straightforward to activate and makes TempDB significantly more scalable.

4. Intelligent Query Processing Improvements

Numerous performance improvements have been made to the engine itself. Scalar UDF Inlining automatically converts your scalar UDFs into something more efficient and set-based. Batch mode on Rowstore allows batch-mode execution without requiring columnstore indexes. Deferred compilation of table variables uses actual cardinality instead of an estimate during optimisation. The new APPROX_COUNT_DISTINCT function provides a quick estimate of distinct values in tables with billions of rows, without the overhead of COUNT(DISTINCT).

5. Always Encrypted with Secure Enclaves

A secure enclave is a protected area on the server where unencrypted data can be processed, giving you more control over your encrypted data. The engine now includes built-in classification and a new T-SQL interface that helps you categorise your data to meet compliance standards. You can also use audits to track who has accessed your sensitive data.

Summary

These are just some of the exciting features available in SQL Server 2019. There are also improvements to Availability Groups, Query Store, Azure Data Studio and more. For a deeper understanding of what SQL Server is and why you need it, take a look at our dedicated guide.

The memory-optimised TempDB metadata feature helps with TempDB bottlenecks. ADR dramatically reduces database recovery times by eliminating the need to wait for long-running transactions, and it controls the uncontrolled growth of the transaction log.

Want to use scalar UDFs without worrying about the performance impact? Need a quick estimate of unique values in a massive table? Need more control over your encrypted data? Always Encrypted with Secure Enclaves could be the solution you are looking for.

If you are deciding between editions, our comparison between SQL Server Standard and Enterprise breaks down the key differences.

Ready to get started? Browse our SQL Server licences and find the right edition for your needs.

If you have any questions, please do not hesitate to contact us.

Best regards,
The Licendi Team