Azure SQL Database
Index
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.
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).
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.