During Microsoft Ignite 2018, Microsoft announced the release of its highly anticipated SQL Server 2019. Responding to increasing demands for a modernized data platform capable of delivering real-time performance, Microsoft has added a host of new functionality based largely around improvements in big data, Linux and availability. Users now have a unified platform that offers a greater choice of operating systems, development languages and data types in the cloud or on-premises. Here we take a look at some of the newest features of SQL Server 2019.
Big Data Clusters & Virtualization
One of the most anticipated features for SQL Server 2019 is the introduction of Big Data Clusters. These enable the simultaneous deployment of multiple clusters of SQL Server, Spark and HDFS containers (running on Kubernetes).
- New data virtualization features remove the need to perform ETL (extract, transform, and load) to integrate data with SQL Server.
- Data lake: Increased efficiency in storing data from external sources through scalable HDFS storage.
- Regular database management tasks can now be handled with built-in snippets.
- Create and run multiple notebooks (Jupyter compatible)
- When used with K8 infrastructure, the whole group set up can be completed much more quickly
UTF-8 Support
With UTF-8 becoming one of the most used encoding systems, it made sense for SQL Server 2019 to upgrade its support for it. Using UTF-8 provides data security and reduces the storage space needed.
Resumable Online Index Create
This nifty little feature is the answer to a Database Managers prayers. It allows you to pause and resume an index creation. Database administrators no longer have to start from scratch.
- Ongoing indexing can be paused, allowing resources to be freed up for other bottlenecked tasks.
- Avoid producing too many logs due to index creation. Back up the log and then resume.
Intelligent Query Processing (IQP)
Building on the already robust features of SQL Server 2017, the IQP functionality of the latest release takes it to the next level.
- Count Distinct will return the approximate number of non-null values in a given group. This provides greater efficiency and reduced memory footprint.
- The new version now allows Batch mode on Row store without requiring column store indexes.
- Row Mode Memory Grant Feedback. Additional memory will be allocated if a row mode query requires it (and will be made available for further consecutive executions). If a query uses 50% or less of the allocated memory, the memory grant will be lowered in further executions.
Disaster Recovery and High Availability
Disaster recovery and business continuity for critical systems are other main features of SQL Server 2019. Again, building on the functionality of 2017, it has improved the availability of replicas during a disaster recovery situation.
- The maximum number of replicas has been increased from two to five. One is a primary and the remaining four are secondary.
- Secondary to primary connection redirection is now possible regardless of the original target specified in the connection string.
- Kubernetes can now be used in place of Windows cluster failure to configure Always On Availability Groups.
Linux
Microsoft has come a long way towards achieving parity between SQL Servers on Linux and those on Windows. This is especially apparent in the new improvements in Machine Learning on Linux.
- A Java language extension has been added alongside the existing R and Python.
- Local user accounts have been replaced by AppContainers under SQL Restricted User Group (SQLRUserGroup).
- A Launchpad service account now handles membership availability to the SQLRUserGroup. Multiple, local user accounts are no longer used.
- Big Data Clusters are now supported on Linux in Docker containers, Kubernetes, Hadoop, and Apache Spark.
- Enhanced support for distributed transactions and replication.
- Machine Learning and Advanced Analytics are now very well supported by Spark.
- Subscribers, publishers, and distributors can now participate in a snapshot, merge and transactional topologies.
- The latest version of Linux Microsoft Distributed Transaction Coordinator (MSDTCO) now allows distributed transactions.
- Improved integration with Active Directory provides functionality around user authentication, distributed queries, and replication.
- A new master container directory can now export existing catalogs in Azure Marketplace, Red Hat Container Hub and Docker Hub.
SQL Server 2019 Security
Given the sensitive nature of the information and transaction stores in databases, security is of paramount importance. Certificates are used to manage access to the SQL servers. SQL Server 2019 also features a Certificate management feature in the form of SQL Server Configuration Manager. This allows database managers to:
- Validate view all installed certificates.
- Have oversight of expiring certificates.
- Manage deployment of certificates on those machines included in Always On Availability Groups.
The above list is just some of the main features and improvements available in SQL Server 2019. Some continue to improve and expand on existing functionality found in the 2012 and 2017 releases. Massive improvements have been made in the areas of big data clusters, security, and Linux functionality. There are some minor differences in functionality between the Standard and Enterprise Editions of the Server that will need to be taken into account when choosing which best suits your needs.
Getting Started with SQL Server 2019
If you’re ready to take advantage of all that the SQL server 2019 has to offer, but feel you aren’t tech-savvy enough, then it may be time to speak with a professional.
Contact us today to learn about our various services and how we can help your business. In no time you’ll find ample space, speed, and cutting-edge technological tools working for you to make your business more productive and proactive. We look forward to speaking with you and getting you started.
