At the heart of the modern data platform
In the last decade SQL Server has made its way from a solution for small and medium RDBMS to a powerful data platform for companies and business critical applications, highly reliable and failure-safe. Every new release of SQL Server looks more and more like an integrated data management centre. SQL Server considers all the modern requirements for processing various data formats and different data sources, which makes it natural to choose it as a platform for all data integration, management and analysis.
Requirements for a modern data processing platform
Recent years have seen more and more data generated and processed. Forms and concepts of data in the modern world have become more diverse. Some databases are relational and generated by traditional transaction instruments. Normally, the data is well structured, and its value and meaning are easily evaluated and understood. But a large amount of the data is still raw. These data is collected from various devices (the Internet of Things), such as sensors, cameras and others. These data are most probably valuable but its value is hard to extract.
The role of a modern data platform is to receive these heterogeneous data, integrate and process it, and derive the information valuable for business. Besides, a modern platform should be able to:
- Process data in the existing local environments and in cloud. This hybrid state will remain for an indeterminate amount of time.
- Transfer the existing data processing tools to a cloud platform without significant changes.
- Develop modern cloud apps from scratch, involving all cloud instruments.
- Analyze data successfully both in a local environment and the cloud platform.
Azure SQL is responsible for the cloud part of data processing, and SQL Server 2019 is in charge for the local component of Microsoft platform for data storage and processing.
* Linux and Docker support was first implemented in SQL Server 2017
-
SQL Server makes it easy to deploy, transfer and integrate big data
Kubernetes-based solution for big data processing, built in SQL Server, allows to deploy the big data cluster easily and work with it. The Kubernetes package provides the deployment of HDFS storage, a relation module SQL Server, and Spark Analytics in the form of containers.
SQL Server 2019 include Spark and HDFS, which allows to read and record data directly in HDFS, using SQL Server or Spark. Kubernetes architecture provides flexible scaling of compute capacity and storages on request.
-
Structured and unstructured data integration
Today's amount of information makes it irrational and unprofitable to convert all the available data to relation tables in order to store them in DBMS. 2 years ago Microsoft introduced PolyBase - the new technology, which enables SQL Server to process Transact-SQL requests that can access the data from Hadoop and combine the data from SQL Server and Hadoop. In SQL Server an external table or an external data source provides connection with Hadoop, virtualizing external data sources and making it unnecessary to import them to a relation base and access these data later on request.
Therefore, the data can be accumulated in their natural format, which doesn’t have to be relational. They can be represented in the form of a virtual table. Virtualization allows to integrate data of various formats, from different sources and storages without the replication and transfer, creating a unified virtual data matrix.
-
High productivity
Microsoft has been proving the high level of SQL Server productivity for a few years by transaction tests and data storage tests. Version 2019 has shown excellent results in the following tests:
- OLTP productivity
- DW productivity for 1 TB, 10 TB and 30 TB
- OLTP price/performance ratio
- DW price/performance ratio for 1 TB, 10 TB and 30 TB
-
Persistent Memory support (РМЕМ).
Persistent Memory (PMEM) is a fast memory able to store data after power shutdown. It allows to process in-memory data without using transmission channels. It makes the query processing 30% faster for intense I/O workloads.
Any SQL Server file saved on a PMM device can be reached directly with the use of memcpy efficient procedures, without accessing the operation system storage.
-
Hybrid transactional/analytical processing (НТАР).
HTAP model allows to perform operational transactions and analytics simultaneously for the same data in the same memory, applying the in-memory approach at the same time.
-
Intelligent processing of requests
Parallel processing and improved scaling of frequent requests provided by the mechanisms of intelligent processing of requests increase the productivity dramatically. The delayed compilation of table variables accelerates the processing of requests by 50%.
Apps and tools for analytics work with all the relational and big data via an SQL Server master instance T-SQL.
-
Safety and compliance with the requirements
Always Encrypted technology is designed to protect confidential data and enhanced with secure enclaves. In-place encryption method allows to perform cryptographic operations with confidential data without transferring them outside the database.
Cryptographic operations include column encryption and can be performed with Transact-SQL. They do not require transferring the data from the database. Secure enclaves support all the full-function calculations, including ranges comparison, which significantly expands the opportunities of their use.
Always Encrypted technology with secure enclaves is available in Windows Server 2019.
-
Azure Data Studio
Azure Data Studio (the former SQL Operations Studio) is a simple cross-platform graphic development and a code editor. It allows to create requests for relational and non-relational databases and supports various operational systems and data sources. Azure Data Studio allows to connect to SQL Server locally and in the cloud, in Windows, MacOS and Linux.
-
Selection of OS and containers
SQL Server 2019 is flexible in terms of platforms, programming languages and means of delivery.
- Supports Red Hat Enterprise Linux, SUSE Linux Enterprise Server, Ubuntu and Windows
- Docker containers for Linux and Windows. Installing with the support for Linux tools: Yum lnstall, Apt-Get and Zypper
- The same abstraction level as the SQL Server on Linux
- Alliws to use R, Python and Java when working with T-SQL. Now the language extension for Java is available in SQL Server.
-
Intelligent data analysis
SQL Server gradually integrates with other analytics platforms, Spark in particular, which is now included in the SQL Server package.
Spark is a popular tool for machine learning and advanced analytics, which has an efficient in-memory machine. It is integrated with SQL, which is highly efficient for visual analytics.
The opportunities of data analytics and visualization. The correct analysis and the efficient presentation of results directly affects the efficiency of the data analysis and the opportunity to make management decisions on its basis.
