Azure SQL Stretch Database & SQL Data Warehouse

Azure SQL Stretch Database allows organizations to migrate their cold data transparently and securely to the Microsoft Azure Cloud. It efficiently categorizes data into two types:

• Hot Data: Frequently accessed data that remains on-premises.
• Cold Data: Infrequently accessed data that is migrated to the Azure SQL Stretch Database.

How It Works

For instance, in a sales order table, open and in-progress sales orders can be classified as hot data, while closed sales orders can be treated as cold data. The cold data is automatically migrated to the Azure SQL Stretch Database without requiring any changes to the existing application. Applications can continue using the same queries to fetch data, and the SQL Server will automatically direct queries to the appropriate data location.

Advantages of SQL Stretch Database

• Cost-Effective Storage: Provides a low-cost solution for storing cold data by leveraging Azure’s cheaper storage options compared to on-premises solutions.
• Seamless Integration: No changes are needed in existing queries or applications; the data’s location is transparent to the application.
• Reduced On-Premises Maintenance: Minimizes the on-premises storage requirements and simplifies backup processes, allowing faster completion of backups within maintenance windows.
• Enhanced Security: Maintains data security during migration with encryption in transit. It also supports row-level security and other advanced SQL Server security features to safeguard data.

Azure SQL Data Warehouse

SQL Data Warehouse (now referred to as Azure Synapse Analytics) is a cloud-based, scalable database service designed for processing large volumes of both relational and non-relational data. It operates on a massively parallel processing (MPP) architecture.

How It Works

In this architecture, requests are managed by a control node, which optimizes the query and delegates tasks to compute nodes that perform work in parallel. SQL Data Warehouse utilizes premium, locally redundant storage linked to these compute nodes for efficient query execution.

Components of SQL Data Warehouse

• Data Warehouse Units (DWUs): Resources allocated to SQL Data Warehouse are measured in DWUs, which quantify the underlying resources such as CPU, memory, and IOPS. DWUs provide a performance measure based on three key metrics:
  • Scan/Aggregation: Measures the performance of standard data warehousing queries that involve scanning large datasets and performing complex aggregations. This is an I/O and CPU-intensive operation.
  • Load: Assesses the system’s capacity to ingest data, focusing on the network and CPU efficiency.
  • Create Table As Select (CTAS): Evaluates the ability to copy tables by reading data from storage, distributing it across the nodes, and writing it back. This operation is also CPU, I/O, and network-intensive.