In September 2018 Microsoft announced the first preview of the next release of SQL Server – SQL Server 2019. For busy database administrators, this is a double edge sword – excitement of all those new features in the latest release and how they can save time and make your SQL Server estate operate more efficiently, but the knowledge that you are now another major release behind and where will you find the time to upgrade or migrate?
Let’s start by exploring my top 5 new or improved features in SQL Server 2019, and try and quantify how they can help your organisation do more or work more efficiently.
- Data Virtualisation. SQL Server 2019 provides new connectors to external data allowing you to integrate MongoDB, Oracle, DB2, Cosmos, Teradata & HDFS without moving data around. It is incredibly useful when creating data workflows to be able to bring information into one place without actually moving or copying the data. The fact that Microsoft now support more connectors should drive usage of this feature.
- Persistent Memory (PMEM) support. This feature is enhanced in SQL Server 2019. PMEM allows block level storage on persistent memory devices that writes at the speed of ram, giving large performance benefit, especially where you are suffering with latency issues. Any SQL Server file that is placed on a persistent memory device can now operate in enlightened mode. SQL Server directly accesses the device, bypassing the storage stack of the operating system using efficient memcpy operations. This mode improves performance because it allows low latency input/output against such devices.
- Always Encrypted. SQL Server 2019 Secure Enclaves for Always Encrypted have been updated to allow range scans and like operators, not just equality operator. They expand upon previous Always Encrypted functionality by enabling of computations on plaintext data, inside a secure enclave on the server side. I like this as it allows Always Encrypted to be used in more use cases.
- AlwaysOn Availability Groups. Another updated feature in SQL Server 2019. AlwaysOn now supports up to five synchronous replicas (up from 3) with automatic failover between them all. SQL Server 2019 also supports secondary to primary replica connection redirection – allowing client application connections to be directed to the primary replica regardless of the target server specified in the connection string. This capability allows connection redirection without an AlwaysOn listener.
- Big Data Clusters. SQL Server 2019’s big new feature. SQL Server 2019 has support for Kubernetes for containerised multi instance deployment composed of SQL Server, Spark and HDFS Data Lake. It uses storage and compute pools but build scalable data architecture supporting big data storage, advanced analytics and machine learning with Spark. This opens up SQL Server to be your database engine for more use cases, operating directly on data you couldn’t do before without ETL type operations.
Finally, there are new client tools that support this new version that you should get familiar with. Checkout SQL Server Management Studio version 18 and Azure Data Studio (replaces SQL Operations Studio).
It can feel sometimes that you are spending all your time keeping things running, upgrading or migrating, but you never have time to innovate and unlock the benefits of what these new features deliver. Has your organisation taken advantage of new technologies in previous SQL Server versions such as Column Store Indexes, In Memory OLTP or AlwaysOn? I see a lot of organisations that haven’t, often not for the want of doing it, it’s just not having the time to spend implementing, testing and amending processes.
If you want to work smarter with SQL Server, come and talk to risual about SQL Server Modernisation and Data Transformation, and how to unlock the benefits of these new releases. Make the software work for you.