Workshop: Kubernetes - From Bare Metal to SQL Server Big Data Clusters

A Microsoft Course from the SQL Server team

04 - SQL Server Big Data Clusters Architecture

In this workshop you have covered creating and maintaining a Kubernetes Cluster. The end of this Module contains several helpful references you can use in these exercises and in production.

This module covers running Microsoft's Big Data Clusters on a Kubernetes Cluster.

4.1 SQL Server Big Data Clusters Capabilities

SQL Server (starting with version 2019) provides three ways to work with large sets of data:

Data Virtualization: Query multiple sources of data technologies using the PolyBase SQL Server feature (data left at source)
Storage Pools: Create sets of disparate data sources that can be queried from Distributed Data sets (data ingested into sharded databases using PolyBase)
SQL Server Big Data Clusters: Create, manage and control clusters of SQL Server Instances that co-exist in a Kubernetes cluster with Apache Spark and other technologies to access and process large sets of data (Data left in place, ingested through PolyBase, and into/through HDFS)

Each of these functions are available separately based on the requirements of your solution. You'll cover each of these components in the sections that follow, and learn more about how each is used within the BDC.

4.1 Data Virtualization

In both a Stand-Alone Instance configuration and within the BDC (BDC) configuration, you can use a series of Connectors to query data using the PolyBase feature. PolyBase enables your SQL Server instance to process Transact-SQL queries that read data from external data sources. Starting in SQL Server 2019, you can access external data in Hadoop, Azure Blob Storage and also external data in SQL Server, Oracle, Teradata, and MongoDB. PolyBase pushes as much of the query predicate as possible to the source system, which optimizes the query.

To leverage PolyBase, you first define the external table using a specific set of statements, then configure the connection and security, and then use standard Transact-SQL statements work with the data as if it were an standard SQL Server table. The components used in this configuration are as Follows:

Components used in Data Virtualization

The PolyBase Feature in SQL Server 2019	Enables your SQL Server instance to process Transact-SQL queries that read data from external data sources.
Java Runtime Environment (JRE)	Creates and runs HDFS Java jobs at the external tables

Activity: Review PolyBase Solution

In this section you will review a solution tutorial similar to one you will perform later. You'll see how to create a reference to an HDFS file store and query it within SQL Server as if it were a standard internal table.

Open this reference and locate numbers 4-5 of the steps in the tutorial. This explains the two steps required to create and query an External table. *Only review this information; you will perform these steps in another Module*.

4.2 BDC Components

The Big Data capabilities for SQL Server can be used in a stand-alone Instance by leveraging the Data Virtualization feature described above. To perform scale-out Big Data, SQL Server implements a big data cluster by leveraging Kubernetes with several other components.

The BDC can be deployed to three environments:

Locally for Testing (using minikube or another such environment)
In a Cloud Service (Such as the Azure Kubernetes Service - AKS)
On premises (using KubeADM, KubeSpray and others)

These architectures are not mutually exclusive - you can install some components on-premises, and others as a service. Your connections can interconnect across these environments. You'll explore more about deploying a BDC in the 03 Planning, Installation and Configuration module.

Control Plane: Kubernetes Components

There are multiple components within Kubernetes that work with the BDC. These components are used to manage and monitor the solution:

Kubernetes Master Node	A dedicated Node within the Kubernetes Cluster that Manages and Controls the Kubernetes Cluster. In addition to the Master Node, controlling services installed on each Node include kublet, kube-proxy and the Docker runtime engine.
Apache Knox	The Apache Knox Gateway is an Application Gateway for interacting with the REST APIs and UIs of the cluster. The Knox Gateway provides a single access point for all REST interactions in BDC's.
Apache Livy	Apache Livy is used to submit Jobs to Apache Spark.
Apache HIVE	A distributed storage database used for Spark meta-data.
Grafana	Visualization and metrics dashboard system.
Kibana	Kibana is an open source log visualization and search plugin.

Control Plane: Controller Service

The Controller in the BDC is a service that is deployed with the azdata utility. It bridges the interactions with SQL Server, Kubernetes, Spark and HDFS.

The controller service provides the following core functionality:

Manage cluster lifecycle: cluster bootstrap & delete, update configurations
Manage master SQL Server instances
Manage compute, data, and storage pools
Expose monitoring tools to observe the state of the cluster
Expose troubleshooting tools to detect and repair unexpected issues
Manage cluster security: ensure secure cluster endpoints, manage users and roles, configure credentials for intra-cluster communication
Manage the workflow of upgrades so that they are implemented safely
Manage high availability and DR for stateful services in the cluster

You have two ways of working with the Controller service: the azdata utility, and the Azure Data Studio tool. All communication to the controller service is conducted via a REST API over HTTPS. A self-signed certificate will be automatically generated for you at bootstrap time. Authentication to the controller service endpoint is based on username and password.

These components are used in the Controller of the BDC:

SQL Server 2019 Controller Service

The controller hosts the core logic for deploying and managing a big data cluster.

BDC: SQL Server Master Instance

The SQL Server Master Instance is an installation of SQL Server 2019 in a Pod on a Node in the Kubernetes cluster. You access it the same way as any SQL Server Instance, and use it for high-value, OLTP, OLAP or other types of workloads. It has Machine Learning Services already configured, so you have the full range of R, Python, and Java to work with on the data in the Cluster environment.

The Master Instance stores meta-data which is outside the scope of the meta-data HIVE is storing. It also contains the PolyBase definition tables, and in addition to the standard SQL Server system databases, the SQL master instance also contains the following:

A metadata database that holds HDFS-table metadata
A data plane shard map
Details of external tables that provide access to the cluster data plane.
PolyBase external data sources and external tables defined in user databases.

These components are used in the SQL Server Master Instance:

SQL Server 2019 Master Instance	The SQL Server master instance provides an externally accessible TDS endpoint for the cluster.
SQL Server extensibility framework	Used for executing Machine Learning and other functionality in Java, R and Python code in SQL Server.

BDC: Compute Pool

The Compute Pool holds one or more SQL Server Pods used for distributed processing under the direction of the SQL Server Master Instance. It makes the calls out to the PolyBase connectors for a distributed Compute layer of the BDC.

These components are used in the Compute Pool of the BDC:

SQL Server 2019

Provides scale-out computational resources for a big data cluster.

BDC: App Pool

The App Pool is a set of Pods within a Node that hold multiple types of end-points into the system. SQL Server Integration Services lives in the App Pool, and other Job systems are possible. You could instantiate a long-running job (such as IoT streaming) or Machine Learning (ML) endpoints used for scoring a prediction or returning a classification.

These components are used in the Compute Pool of the BDC:

Kubernetes Node

Provides computational resources for Operationalizing a big data cluster.

BDC: Data Pool

The Data Pool in a BDC consists of one or more SQL Server data pool instances. SQL data pool instances provide persistent SQL Server storage for the cluster. A data pool is used to ingest data from SQL queries or Spark jobs, or other locations. To provide better performance across large data sets, data in a data pool is distributed into shards across the member SQL data pool instances.

These components are used in the Data Pool of the BDC:

Data Pool	Provides scaled data storage a big data cluster.
Polybase Feature	Used for working with external tables.

BDC: Storage Pool

The storage pool consists of storage nodes comprised of SQL Server on Linux, Spark, and HDFS. All the storage nodes in a SQL big data cluster are members of an HDFS cluster. You can use these as a "Data Lake" construct to work with large sets of data stored on disparate data sources.

Inside the Storage Pool, the Storage nodes are responsible for data ingestion through Spark, data storage in HDFS (Parquet format). HDFS also provides data persistency, as HDFS data is spread across all the storage nodes in the SQL big data cluster. The Storage Nodes also provide data access through HDFS and SQL Server endpoints.

These components are used in the Storage Pool of the BDC:

HDFS	The distributed data storage system used by SQL Server and Apache Spark.
Apache Spark	Apache Spark is an analytics engine for processing large-scale data. It can be used with data stored in HDFS, and has connectors to work with data in SQL Server as well.

Activity: Review Data Pool Solution

In this section you will review the solution tutorial similar to the one you will perform in a future step. You'll see how to load data into the Data Pool.

Open this reference and review the steps in the tutorial. This explains the two steps required to create and load an External table in the Data Pool. You'll perform these steps in the Operationalization Module later. *Only review this information at this time. You will perform these steps in another Module.

For Further Study

Official Documentation for this section
Update on 2019 Blog
Introduction Webinar on Big Data
Big Data Whitesheet
Infographic on Big Data
Books Online on this topic
Session on Big Data at at Ignite

Next Steps

Next, Continue to Module 5 - Data Science.

Provide feedback

Saved searches

Use saved searches to filter your results more quickly

Uh oh!