In November 2019, after over a year of beta previews, Microsoft published their official release for SQL Server 2019. Building off advancements from SQL Server 2017, this new update introduces new functionality and modern improvements that continue to make SQL Server the top contender among the list data solutions.
Intelligent Database
SQL Server 2019 made huge improvements to memory allocation, scalability, and performance. The Query Optimizer has been improved to include dynamic memory grants for rowstore tables. This generally improves queries across the board and requires no change from the developers. New memory improvements in areas like in-memory online transaction processing (OLTP) allows for better scalability depending on present workloads. This feature also introduces In-Memory Database that reduces bottlenecking from intense workloads. Now, system tables that are used to manage tempdb metadata can be moved into memory-optimized tables without having to refactor.
High Availability
Unless you’re on vacation on a beach or relaxing with a good book, downtime is never a good time. SQL Server 2019 introduces some new key improvements to ensure things stay up and running. This new update, first, allows for up to 5 synchronous replicas, an update from 2017’s maximum of 3. Including more synchronous replicas helps keep downtime as low as possible in the event of a failure. SQL Server 2019 introduces Accelerated Database Recovery (ADR) for enhanced recovery in the event of a rollback, failover, or restart.
Mission-Critical Security
With each update to SQL Server, Microsoft has implemented up to date security enhancements to protect the database and help prevent attacks. With the 2019 release, Always Encrypted has been expanded with secure enclaves. This gives the SQL Server the ability to securely encrypt a part of memory without the unencrypted values of a column becoming exposed to the rest of the system. This implementation provides high end security without performance taking a hit.
Developer Experience
Vague truncation errors are never fun to deal with, especially when it is extremely difficult to determine exactly where the error took place in your code. This is like looking for a missing semicolon in your Java code and the compiler doesn’t tell you which line of code, or even the file name, that is causing the syntax error. SQL Server 2019 implements a beautiful new system to enhance the developer experience that details the table and column names along with the truncated value when an error occurs.
Data Virtualization
Data is beautiful, but it becomes very ugly and slows data transfer to a crawl when trying to query across different providers. SQL Server 2019 provides a new service called PolyBase which allows users to process Transact-SQL (T-SQL) queries that fetch data from an external source like, MongoDB and Oracle. Setting up your SQL Server to implement these external tables requires work, but once configured, your SQL Server becomes the main hub that can connect to a large selection of databases and access their data quickly.
