Serverless Architecture and Concepts. What is it?
Published Jan 22 2021 04:34 PM 12.2K Views
Microsoft

I needed to go through this subject this week so I thought that would be a good opportunity to share SQL Serverless Architecture concepts.

 

1) What is the difference between SQLOD and the former SQLDW?

formerDW_SQLOD.png

 

SQL Serverless or SQL On-demand

  • Grow or shrink compute power, within a dedicated SQL pool, without moving data.
  • Pause compute capacity while leaving data intact, so you only pay for storage.
  • Resume compute capacity during operational hours.

SQL DW you can actually pause and resume. You insert, update and delete. You have storage with your data which SQLDW is held responsible.

 

What is SQLOD?

SQLOD is a query service over the data in your data lake. You do not need to pause or resume. It is a service per comsumption or per demand. The service is resilient to failure and elastic.

Note: Serverless SQL pool has no local storage, only metadata objects are stored in databases. Basically is for reading, only.

 

What it means resilient to failures and elastic?

It means it auto-scale the node's resources if required by the engine while querying your file and it means also if there is any failure in any node it recovers without any user intervention.

 

 

What is supported on Serverless?

Supported T-SQL:

  • Full SELECT surface area is supported, including a majority of SQL functions
  • CETAS - CREATE EXTERNAL TABLE AS SELECT
  • DDL statements related to views and security only

How does it work?

  • DQP or Distributed Query Processing.
  • Compute Node

 

DQP is responsible per optimize and orchestrate distributed execution of user queries by splitting them into smaller queries that will be executed on Compute nodes.

The Compute Nodes will execute the tasks creates by the DQP. The tasks are pretty much the query logic break in chunks of data to be processed. Those chunks of data are the files organized in data cells.  How many data cells and tasks will be executed depends on the plan optimization.

 

Plan optimization also depends on the stats. The more serverless the SQL pool knows about your data, the faster it can execute queries against it and in the end, the plan chosen is based on cost, lowest cost.  Note: Automatic creation of statistics is turned on for Parquet files. For CSV files, you need to create statistics manually.

 

 

 

Ref: Synapse SQL architecture - Azure Synapse Analytics | Microsoft Docs

Serverless SQL pool - Azure Synapse Analytics | Microsoft Docs

https://www.microsoft.com/en-us/research/publication/polaris-the-distributed-sql-engine-in-azure-syn...

Create and update statistics using Azure Synapse SQL resources - Azure Synapse Analytics | Microsoft...

 

That is it!

Liliam Leme

UK Engineer.

 

Co-Authors
Version history
Last update:
‎Sep 15 2021 12:12 PM
Updated by: