Azure SQL Database -

Azure SQL Database

Introduction

SQL Server is a relational database management system (RDBMS) developed by Microsoft. It is used to store, retrieve, and manage structured data.

Azure offers three types of services to manage SQL Server workloads:

Managed Instance – A fully managed SQL Server environment that is almost identical to an on-premises SQL Server but offers automatic patching and updates.
Virtual Machine (VM) – Provides a virtual machine where you can install SQL Server manually. Here, you are responsible for updates, maintenance, and custom configurations.
Azure SQL Database – A fully managed, cloud-based relational database offering high performance, scalability, security, and easy integration with Azure Data Factory, Power BI, etc. It runs on SQL Server and allows users to store, retrieve, and manage data in a structured format.

DTU vs. vCore

DTU (Database Transaction Unit)

A blended metric combining CPU, RAM, and I/O into a single unit, making it easy to choose performance levels.

DTU-Based Pricing Tiers
Tier	Use Case	DTUs	Max Size	Features
Basic	Small databases with light workloads	5	2 GB	Basic performance
Standard	Moderate workloads	10 to 300	250 GB to 1 TB	Enhanced performance and storage
Premium	High-performance workloads with high I/O	125 to 4,000	4 TB	High performance, In-Memory OLTP, high availability

vCore (Virtual Core)

Provides more granular control over compute resources (CPU, memory, and cores).

vCore-Based Pricing Tiers
Tier	Use Case	vCores	Max Size	Features
General Purpose	Balanced performance for most workloads	2 to 80	16 TB	Standard performance, local SSD storage
Business Critical	High-performance with low latency	2 to 80	16 TB	In-Memory OLTP, high availability with three replicas
Hyperscale	Very large databases needing high scalability	2 to 80 (scalable)	Up to 100 TB	Highly scalable, separate compute and storage

Additional Features

Elastic Pool

Elastic Pool allows databases to share resources efficiently, instead of each database having separate compute resources.

PolyBase

PolyBase enables querying and retrieving data from external file systems like Azure Data Lake or Azure Blob Storage. Below is a step-by-step guide to setting up PolyBase in Azure SQL Database:

Create a Master Key: The master key is used to encrypt credentials in the database. It's required to establish secure connections to external data sources.


CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'YourStrongPassword';

Create a Database Scoped Credential: A database-scoped credential provides authentication information (like a Shared Access Signature, or SAS token) to access Azure Storage securely.


CREATE DATABASE SCOPED CREDENTIAL StorageCredential
WITH IDENTITY = 'SHARED ACCESS SIGNATURE', SECRET = 'YourSASToken';

Define the External Data Source: This defines the external data source, linking PolyBase to the Azure Blob Storage where the data resides. Replace placeholders with actual storage account details.


CREATE EXTERNAL DATA SOURCE ExternalDataSource
WITH (
    TYPE = HADOOP,
    LOCATION = 'wasbs://YourContainer@YourStorageAccount.blob.core.windows.net',
    CREDENTIAL = StorageCredential
);

Create the External File Format: The external file format specifies how the data is structured (e.g., delimited text, field separator). In this example, the file is a CSV with commas as delimiters.


CREATE EXTERNAL FILE FORMAT FileFormat
WITH (
    FORMAT_TYPE = DELIMITEDTEXT,
    FORMAT_OPTIONS (FIELD_TERMINATOR = ',', STRING_DELIMITER = '"')
);

Create the External Table: The external table is created to query data from the external source. You define its schema (columns and data types) and provide the file's location in Azure Blob Storage.


CREATE EXTERNAL TABLE ExternalTableName (
    Column1 DATATYPE,
    Column2 DATATYPE,
    ...
)
WITH (
    LOCATION = '/FolderPath/FileName.csv',
    DATA_SOURCE = ExternalDataSource,
    FILE_FORMAT = FileFormat
);

This entire setup enables PolyBase to query external data stored in Azure Blob Storage as if it were a SQL table. This is especially useful for analytics and big data scenarios.

SSMS (SQL Server Management Studio)

SQL Server Management Studio (SSMS) is a graphical user interface (GUI) tool developed by Microsoft for managing SQL Server databases. It provides a comprehensive environment for database administration, development, and performance monitoring.

Azure SQL Database

Index