Microsoft SQL Server 2019 – High availability and advanced analytics for all your data

SQL Server 2019 – advanced security and compliance mechanisms, industry-leading performance, high availability and advanced analytics for all your data, and now support for working with large data.

The heart of the modern data processing platform
Over the past 10 years, SQL Server has evolved from a solution for small and medium DBMS to a powerful enterprise level data platform designed for critical business applications for reliability and fault tolerance.

With each new release, SQL Server is increasingly entitled to be called a single control center for all data. SQL Server takes into account all modern requirements for working with data of different formats and from different sources and becomes a natural choice for building a platform for integration, management and analysis of any data.

Requirements for a modern data processing platform
In recent years, more and more data have been generated and processed and the diversity of their form and meaning has increased. Some data is still in a relational format and is generated by traditional transaction tools. Usually such data is structured, its meaning and value are well understood and known.

But a huge amount of data is much more raw. This is data from sensors and sensors (the same Internet of things), video cameras, recording devices. This data is likely to have value, but it is still much harder to extract.

The benefits of SQL Server 2019:

• Analysis of any data. SQL Server is the space for data integration. With the powerful features of SQL Server and Spark, you can transform and analyze both structured and unstructured data.
• Choice of language and platform. Create modern applications with innovative features, using the platform and language of your choice. Windows, Linux and containers are now supported.
• Industry-leading performance. Take advantage of superior scalability, performance and availability for mission-critical intelligent applications, storage and data lakes.
• Advanced security features. Protect your data during storage and use. SQL Server is a DBMS that has been recognized as the least vulnerable for over 8 years by the National Institute of Standards and Technology (NIST) vulnerability tests.
• Make faster and more informed decisions. With Power BI Reporting Server, you can create professional interactive reports, as well as use the reporting capabilities of SQL Server Reporting Services.
• Big Data Clusters. With SQL Server 2019, managing a large data environment is much easier. This product contains the main elements of data lake – distributed file system Hadoop (HDFS), Spark and analysis tools. All of them are closely integrated with SQL Server and supported by Microsoft. You can easily deploy Linux containers in the Kubernetes cluster.
• Data virtualization. In SQL Server 2016 you could receive data from Hadoop in a structured format without leaving SQL Server, thanks to PolyBase and T-SQL queries – you didn’t even need to copy or move the data. In the new release we developed the idea of data virtualization and added new data sources including Oracle, Teradata, MongoDB and other SQL Servers.

Reasons to upgrade to Microsoft SQL Server 2019

SQL Server simplifies the deployment, transfer and integration of large data

• The large data processing solution based on Kubernetes embedded in SQL Server makes it easy to deploy and work with a large data cluster. Kubernetes provides the deployment of HDFS storage, SQL Server relational module and Spark’s container-based analytics tools in one convenient package.
• SQL Server 2019 includes Spark and HDFS, which allow you to read and write directly to HDFS using SQL Server or Spark. The Kubernetes architecture provides flexible scalability of computing power and storage on demand.

Integration of structured and unstructured data

Today’s data volumes make it unreasonable and unprofitable to convert all available data into relational tables for storage in the DBMS. Just 2 years ago, Microsoft introduced PolyBase technology that allows an SQL Server instance to process Transact-SQL queries that access Hadoop data and merge data from Hadoop and SQL Server.

In SQL Server, an external table or external data source provides a connection to the Hadoop, virtualizing external data sources without the need to directly import them into a relational database, and then allows you to access these data with queries.

Thus, the data is accumulated in its natural format, not necessarily relational, but can be presented as a virtual table. The virtualization allows integrating data of different formats, from heterogeneous sources and storage locations without their replication and moving, creating a single virtual data matrix.

High performance

It’s not the first year Microsoft confirms high performance of SQL Server with transaction and storage performance tests. Version 2019 has excellent results in the following tests:

• OLTP performance;
• DW performance for 1TB, 10TB and 30TB;
• price/performance ratio of OLTP;
• DW price/performance ratio for 1TB, 10TB and 30TB.

Continuous Memory Support (RMEM)

Persistent Memory (PMEM) is a fast memory that has the ability to store data after a power failure. It allows in-memory data processing, eliminating the need to transmit data over transmission channels and speeds up query processing by 30% for intensive I/O workloads.

Any SQL Server file placed on a PMM device is now available directly, bypassing the operating system storage stack using efficient memcpy operations.

Hybrid Transaction/Analysis Processing (HTAP)

The HTAP model allows to simultaneously perform operational transactions and analytics on the same data in the same memory, also implementing the approach in memory.

Intelligent query processing

Parallelization of queries and improved scaling of frequent queries thanks to intelligent query processing mechanisms make performance much better. Delayed compilation of tabular variables speeds up query processing by more than 50%.

Analytics applications and tools work with all relational and large data through the SQL Server master instance using T-SQL.

Safety and compliance

Protect confidential data with Always Encrypted technology with protected enclaves. On-site encryption allows you to crypt sensitive data without moving it outside the database.

Cryptographic operations include encryption of columns, and these operations can now be performed using Transact-SQL, they do not require moving data from the database. Within protected enclaves, all full-featured calculations, including range comparisons and comparisons, are supported, greatly expanding their application capabilities.

Always Encrypted technology with secure enclaves is available in Windows Server 2019.

Azure Data Studio

Azure Data Studio (formerly SQL Operations Studio) is a simplified cross-platform graphical management tool and code editor. It allows you to create queries to relational and non-relational databases supporting different operating systems and data sources. Azure Data Studio allows you to connect to SQL Server locally and in the cloud, on Windows, macOS and Linux.

OS and container selection

SQL Server 2019 is flexible in terms of platform, programming language and delivery system selection.

• Support for Red Hat Enterprise Linux, SUSE Linux Enterprise Server, Ubuntu and Windows.
• Docker containers for Linux and Windows. Installation with built-in support for Linux tools: Yum lnstall, Apt-Get and Zypper.
• Same level of abstraction from SQL Server to Linux.
• Ability to use R, Python and Java when working with T-SQL. Now the Java language extension is available to run Java code in SQL Server.

Intelligent data analysis

The development of SQL Server has followed the path of integration with other analytical platforms, in particular Spark, which is now included in the delivery of SQL Server.

Spark is a very popular tool for machine learning, for advanced analytics, has an effective in memory machine. And all this is integrated with SQL, which is very effective for visualization of analytics.

Proper analysis and effective presentation of results directly affects the efficiency of data analysis and the ability to make management decisions based on them.

What’s New in SQL Server 2019