The security architecture of SQL Server is designed to allow developers and database administrators to create secure database applications and counter threats. In Microsoft tradition, each version of SQL Server has been revised and upgraded from previous versions with the debut of new features and functionality. With SQL 2019 security features include:

• The SQL Vulnerability Assessment allows you to find, track and resolve potential database vulnerabilities


• SQL Server Reporting Services allows you to build, execute and manage mobile and paginated reports with robust visualization


• SQL Server 2019 security features such as AlwaysEncrypted with secure enclaves now allow DBAs to encrypt a segment of memory without exposing the unencrypted values to the remaining processes or the administrators. This allows your organization to build several layers of protection with security features built into each edition.

Curious about the pricing of SQL? Learn more in our article on SQL server cost.

In large-scale environments that process large-volume data, workloads on TempDB can be a cause of strain for DBAs. Waiting for resources to free up so the process can complete and allow them to access necessary files and data can become tiresome. You can now improve the performance by moving the tempdb’s system tables into memory-optimized tables, removing the workload from your disks. In the new version, this is very simple to enable and makes TempDB far more scalable.

Hybrid buffer pool is a new feature of the SQL Server Database Engine where database pages sit on database files and are placed on a persistent memory (PMEM) device which will be directly accessed when required.

You will be able to get instant or near-instant results to your query, avoiding dreaded bottlenecks.

Not sure which one is right for you? Book your free consultation today

Microsoft SQL Server now performs database recovery in a whole new way. Microsoft has completely redeveloped how recovery works and has significantly decreased the time involved in completing a recovery in the event of availability group failover, a transaction rollback, or an instance restart. Struggling to complete on time due to active long-running transactions have long frustrated users.

Managing SQL transaction log growth before it balloons can be difficult, however with the new SQL Server 2019, the log truncation process is much more aggressive in helping you ensure that it won’t get out of control and take up all your disk space. By enabling the Persisted Version Store you are now able to track changes, permitting the rolling back of long-running transactions instantaneously. The best part is that it doesn’t matter how long the transaction has been running or the volume of computations. This is one of the built-in featured services within Microsoft SQL Server 2019 and is included in the license.

Learn how to Install SQL Lite

The Query Optimizer is the component within SQL Server that creates the execution protocols for queries, including batch mode on rowstore, dynamic memory grants for rowstore tables, and table variable deferred compilation, among other queries. Many of the new enhancements affect the behavior of the Query Optimizer. This will improve the quality of the protocols for all related queries.

Many other improvements have been made to the engine including the Table Variable Deferred Compilation. Rather than estimate during optimization, TVD results in the use of actual cardinality, and the first time it’s compiled.

In Batch Mode on Rowstore you can use batch mode execution, without having to use columnstore indexes.

Ever required a quick row count estimate of unique values (excluding nulls)? Approximate Query Processing using the new APPROX_COUNT_DISTINCT function will provide you results incredibly fast.

With so many new query features, you will notice a marked improvement in your queries and far better overall performance.

Learn How to install SQL.

To improve the data virtualization experience between SQL Server and other database engines, SQL Server 2019's Big Data Clusters added enhancements to PolyBase. By providing bi-directional integration with big data Hadoop and Apache Spark systems, you can now easily manage volumes of data with many of the built-in tools that SQL Server 2019 offers.

The upgrade brought about primary features of functionality:

• Managed SQL Server, Spark, and Data Lake - When you use a data lake to store large volumes of data, you can now access it using either SQL or Spark.
• Complete AI platform - You can run Python and R scripts on the master instance of SQL Server Big Data Clusters with Machine Learning Services .
• Data virtualization - Improves security and data processing performance. Combine data from many sources without data movement.

Azure is Microsoft's SQL Database that comes as a fully managed platform as a service (PaaS) database engine. It is able to take on most of the database management functions that you may require, such as backups, patching, upgrading, and monitoring without user involvement.

The Azure Database will always run on the latest stable version of the SQL Server database engine and patched OS with 99.99% availability. Because Azure is a PaaS, its capabilities are built into Azure enabling you to focus on the domain-specific database administration and optimization activities that are vital for your business to function.

Azure SQL Server 2019 Features:

• Create a high availability and high-performance data storage layer for the applications and solutions in Azure.
• Use advanced query processing features, such as high-performance in-memory technologies and intelligent query processing .
• Microsoft will handle all patching and updating of the SQL and operating system code. You don't have to manage the underlying infrastructure.
• Azure Data Studio provides a modern editor experience with IntelliSense, source control integration, code snippets, and an integrated terminal. It's engineered specifically for the data platform, including built-in charting of query result sets and customizable dashboards

PolyBase is a data virtualization feature for SQL Server, it enables you to integrate data from a variety of sources such as Cosmos, MongoDB, DB2, Oracle, and Hadoop Distributed File System (HDFS) without having to create a transactional replication or having to move data around.

The PolyBase feature allows the data to remain in its original file location and format, permitting you to virtualize the external data through the SQL Server instance. Now it can be queried in place like any other table in SQL Server.

Previous versions used the ETL process (extract-transform-load), so Microsoft made a big improvement in SQL Server 2019, providing this data virtualization solution. To use the data virtualization, you will need to use PolyBase connectors.

Query Store custom capture policies provide you with insight on query plan choice, simplifying performance troubleshooting, and enable you to find performance differences caused by query plan changes.

Previously when you had values somewhere that did not fit under new data types, you would have received a truncation error. Then it's a hit-and-miss analysis until you find the egregious offender. With SQL Server 2019 you will receive Verbose truncation warnings, providing you with all of the data truncation issues, saving you time and money. The best part, it's the new default.

Resumable index build permits you to stop an index rebuild operation while it is still in progress. Even better, you can keep the work that has been done so far, and resume the process again at any time in the future. All you need to do is change your Resumable option.

Previously, getting an actual query plan meant that you needed either an XEvents trace or a Profiler trace, not an easy step for such a critical function. SQL Server 2019 offers a Dynamic Management Function called sys.dm_exec_query_plan_stats and by enabling the lightweight query profiling feature, it will track the last ACTUAL execution plan for your query. Simply enable the setting for Last actual execution plan DMF .