SQL Server 2019: What is it?

If you want to get involved with SQL Server for your hobby or your business, you will quickly realise that there are many possibilities. In this blog, we will inform you about the most important features of SQL Server 2019.

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.

With the release of SQL Server 2019 comes many new and exciting features. Overall, the database engine, security, database recovery and several other areas have been significantly improved.

Are you tired of TempDB data bottlenecks? Are you fed up with long database recovery times because you are waiting for that super long transaction? Are you tired of low performance queries due to scalar UDFs? Tired of waiting for that unique count to complete on that huge table with a billion rows? SQL Server 2019 may have the solutions you've been looking for!

SQL Server 2019 is designed to solve the challenges of the modern data professional, including new features:

• Store enterprise data in a data lake and offer the query capabilities of SQL and Spark in general.
• Reduce the need for extract, transform and load (ETL) applications by reducing data movement.
• Integrate and secure machine learning applications with scalable performance
• Reduce changes to applications and queries to achieve performance improvements
• Improving confidential processing of data through hardware enclaves
• Increased uptime and availability of applications and databases through features such as ADR (Advanced Database Recovery)
• Extending the power of the T-SQL language in a secure and robust way
• Run applications and deploy databases on multiple operating systems and platforms with compatibility
• Reduce the risk of upgrades and take advantage of new SQL features when you're ready, thanks to built-in database compatibility layers.

Below we list the features we find most useful in SQL Server 2019:

1. accelerated database access (ADR)

Accessing the database can take a lot of time and may not always be completed on time due to long running active transactions. Managing the transaction log so that it does not overflow is also known to cause a lot of headaches.
With SQL Server 2019, the log truncation process is much more aggressive to prevent it from getting out of control and taking up all your storage space. When enabled, ADR uses a strong version store for change tracking that allows for instant rollback of long-running transactions. It does not matter how long the transaction has been running or how large it is.

2. replication on Linux

SQL Server on Linux now supports replication, including Snapshot, Transactional and even Merge. The instance can support any of the replication roles: Publisher, Distributor, Subscriber. You can even mix and match the operating systems involved. For example, Publisher and Distributor can run on Windows or Linux, and Subscriptions can also run on a mix of Windows and Linux instances.

3. memory-optimised TempDB metadata

Tired of the bottlenecks caused by high TempDB workloads? Now there is finally help! You can now move TempDB system tables to memory-optimised tables to relieve your hard disks. It is very easy to activate and makes TempDB more scalable.

4. improvements in intelligent query processing (IDQ).

Numerous performance improvements have been made to the engine itself. Among the most interesting is Scalar UDF Inlining, which automatically converts your scalar UDFs into something more efficient and quantity-based. Batch mode in Rowstore allows you to run in batch mode, and you no longer need to use columnstore indexes. Deferred compilation of table variables allows you to use actual cardinality instead of an estimate during optimisation and initial compilation. Approximate query processing via the new APPROX_COUNT_DISTINCT function is for when you need a quick estimate of the number of rows of distinct values (without zeros) in a table of billions and count(distinct) is too slow.

5. always encrypted with Secure Enclaves

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

Summary of the findings on SQL Server 2019

These are just some of the exciting new features available in SQL Server 2019. There are also improvements to Availability Groups, Query Store, Azure Data Studio and, pssst, here it comes.... they've finally done it. Finally, the unhelpful error message "String or binary data would be truncated" has been changed to a message that includes not only the table and column names, but also the value that was truncated!

Using the memory-optimised TempDB metadata feature will help you with TempDB bottlenecks. ADR helps you significantly reduce your database retrieval times by eliminating the need to wait for a single lengthy transaction, and it helps you control the uncontrolled growth of the transaction log.
Want to use scalar UDFs but afraid of the performance impact? Need a quick estimate of the number of unique data in that huge table with a billion rows? Need more control over your encrypted data? Always Encrypted with Secure Enclaves could be the solution you are looking for.

Contact us at

If you have any questions, please do not hesitate to contact us.
Also, here is our YouTube channel if you would like to see more of our original licence content.
Greetings,
your Licendi team.