Complete Azure SQL Guide

Table of Contents
1. Introduction to Azure SQL
2. Azure SQL Service Types

3. Getting Started
4. Database Design and Management

5. Security
6. Performance and Optimization

7. Backup and Recovery

8. Monitoring and Troubleshooting

9. Advanced Features

10. Migration Strategies

11. Cost Management
12. Best Practices

1. Introduction to Azure SQL

Azure SQL is Microsoft's cloud-based relational database service built on the SQL Server engine. It
provides a fully managed database service with built-in intelligence, security, and scalability features.

Key Benefits
Fully Managed: No need to manage infrastructure, patches, or updates
High Availability: 99.99% uptime SLA with automatic failover

Scalability: Dynamic scaling based on workload demands

Security: Advanced threat protection and compliance certifications
Intelligence: Built-in AI for performance optimization

Cost-Effective: Pay-as-you-use pricing model

When to Use Azure SQL

Migrating on-premises SQL Server databases to the cloud

Building new cloud-native applications

Need for high availability and disaster recovery
Variable or unpredictable workloads

Requirement for advanced security features

2. Azure SQL Service Types

2.1 Azure SQL Database (Single Database)

A fully managed single database service ideal for modern applications.

Features:

Serverless and provisioned compute options

Up to 100TB storage

Built-in high availability

Automatic backups

Intelligent performance insights

Use Cases:

Modern cloud applications

SaaS applications

Development and testing environments

2.2 Azure SQL Database (Elastic Pool)

A cost-effective solution for managing multiple databases with varying usage patterns.

Features:

Shared resources across multiple databases

Automatic scaling

Cost optimization for unpredictable workloads

Simplified management

Use Cases:

Multi-tenant SaaS applications

Multiple databases with unpredictable usage

Cost optimization scenarios

2.3 Azure SQL Managed Instance

A fully managed SQL Server instance providing near 100% compatibility with on-premises SQL Server.

Features:

Native virtual network support

 Cross-database queries
SQL Server Agent

Common language runtime (CLR)

Service Broker

Database Mail

Use Cases:

Lift-and-shift migrations

Applications requiring SQL Server features

Hybrid scenarios

Complex multi-database applications

2.4 SQL Server on Azure VMs

Full control over SQL Server instance running on Azure virtual machines.

Features:

Complete control over SQL Server configuration

Support for all SQL Server features

Custom maintenance schedules

Hybrid licensing benefits

Use Cases:

Applications requiring specific SQL Server configurations

Legacy applications with dependencies

Regulatory requirements for specific configurations

3. Getting Started

3.1 Prerequisites
Azure subscription

Azure CLI or PowerShell (optional)

SQL Server Management Studio (SSMS) or Azure Data Studio

Basic understanding of SQL Server

3.2 Creating Your First Azure SQL Database

Using Azure Portal:

 1. Sign in to Azure Portal
2. Click "Create a resource"

3. Search for "SQL Database"

4. Select "SQL Database" and click "Create"

5. Configure the following:

Subscription: Select your subscription

Resource Group: Create new or select existing

Database Name: Enter a unique name

Server: Create new or select existing

Compute + Storage: Choose service tier

Backup Storage: Select redundancy option

Using Azure CLI:

bash

# Create resource group

az group create --name myResourceGroup --location eastus

# Create SQL server

az sql server create \
--name myserver \
--resource-group myResourceGroup \
--location eastus \
--admin-user myadmin \
--admin-password myPassword123!

# Create SQL database

az sql db create \
--resource-group myResourceGroup \
--server myserver \
--name mydatabase \
--service-objective Basic

3.3 Connecting to Azure SQL Database

Connection String Format:

 Server=tcp:myserver.database.windows.net,1433;
Initial Catalog=mydatabase;
Persist Security Info=False;
User ID=myusername;
Password=mypassword;
MultipleActiveResultSets=False;
Encrypt=True;
TrustServerCertificate=False;
Connection Timeout=30;

Using SSMS:

1. Open SQL Server Management Studio

2. In Connect to Server dialog:

Server type: Database Engine

Server name: myserver.database.windows.net

Authentication: SQL Server Authentication

Login: your username

Password: your password

4. Database Design and Management

4.1 Service Tiers and Performance Levels

DTU-Based Model:

Basic: 5 DTUs, 2GB storage

Standard: 10-3000 DTUs, up to 1TB storage

Premium: 125-4000 DTUs, up to 1TB storage

vCore-Based Model:

General Purpose: Balanced compute and storage

Business Critical: High performance, low latency

Hyperscale: Highly scalable storage (up to 100TB)

4.2 Storage Options

Standard Storage: Cost-effective, suitable for most workloads

Premium Storage: High-performance SSD storage

Ultra Storage: Highest performance for mission-critical workloads

4.3 Creating Tables and Indexes

sql

-- Create a sample table

CREATE TABLE Customers (
CustomerID int IDENTITY(1,1) PRIMARY KEY,
FirstName nvarchar(50) NOT NULL,
LastName nvarchar(50) NOT NULL,
Email nvarchar(100) UNIQUE,
CreatedDate datetime2 DEFAULT GETUTCDATE(),
IsActive bit DEFAULT 1
);

-- Create index for better performance

CREATE INDEX IX_Customers_Email ON Customers(Email);
CREATE INDEX IX_Customers_LastName ON Customers(LastName);

4.4 Data Types and Constraints

sql

-- Common data types in Azure SQL

CREATE TABLE Products (
ProductID int IDENTITY(1,1) PRIMARY KEY,
ProductName nvarchar(255) NOT NULL,
Price decimal(10,2) CHECK (Price > 0),
Description nvarchar(max),
CreatedDate datetime2 DEFAULT GETUTCDATE(),
ModifiedDate datetime2,
IsActive bit DEFAULT 1,
CategoryID int,

-- Foreign key constraint

CONSTRAINT FK_Products_Category
FOREIGN KEY (CategoryID) REFERENCES Categories(CategoryID)
);

5. Security

5.1 Authentication Methods

SQL Authentication:

Traditional username/password authentication

Managed at the database level

 Suitable for applications and legacy systems

Azure Active Directory Authentication:

Integrated authentication with Azure AD

Supports multi-factor authentication

Centralized identity management

Supports managed identities

5.2 Firewall Rules

Server-level Firewall Rules:

sql

-- Allow access from specific IP range

EXECUTE sp_set_firewall_rule
@name = N'AllowOfficeNetwork',
@start_ip_address = '192.168.1.1',
@end_ip_address = '192.168.1.255';

Database-level Firewall Rules:

sql

-- Allow access to specific database

EXECUTE sp_set_database_firewall_rule
@name = N'AllowApplicationServer',
@start_ip_address = '10.0.0.1',
@end_ip_address = '10.0.0.1';

5.3 Transparent Data Encryption (TDE)

sql

-- Enable TDE (enabled by default)

ALTER DATABASE mydatabase SET ENCRYPTION ON;

-- Check encryption status

SELECT
name,
is_encrypted,
encryption_state
FROM sys.databases
WHERE name = 'mydatabase';
5.4 Always Encrypted

sql

-- Create column master key

CREATE COLUMN MASTER KEY MyCMK
WITH (
KEY_STORE_PROVIDER_NAME = 'AZURE_KEY_VAULT',
KEY_PATH = 'https://myvault.vault.azure.net/keys/mykey/version'
);

-- Create column encryption key

CREATE COLUMN ENCRYPTION KEY MyCEK
WITH VALUES (
COLUMN_MASTER_KEY = MyCMK,
ALGORITHM = 'RSA_OAEP',
ENCRYPTED_VALUE = 0x...
);

-- Create table with encrypted columns

CREATE TABLE Employees (
EmployeeID int IDENTITY(1,1) PRIMARY KEY,
FirstName nvarchar(50),
LastName nvarchar(50),
SSN char(11) ENCRYPTED WITH (
COLUMN_ENCRYPTION_KEY = MyCEK,
ENCRYPTION_TYPE = DETERMINISTIC,
ALGORITHM = 'AEAD_AES_256_CBC_HMAC_SHA_256'
),
Salary decimal(10,2) ENCRYPTED WITH (
COLUMN_ENCRYPTION_KEY = MyCEK,
ENCRYPTION_TYPE = RANDOMIZED,
ALGORITHM = 'AEAD_AES_256_CBC_HMAC_SHA_256'
)
);

5.5 Row-Level Security (RLS)

sql
 -- Create security function
CREATE FUNCTION dbo.fn_securitypredicate(@UserID int)
RETURNS TABLE
WITH SCHEMABINDING
AS
RETURN SELECT 1 AS fn_securitypredicate_result
WHERE @UserID = USER_ID();

-- Create security policy

CREATE SECURITY POLICY dbo.UserDataSecurityPolicy
ADD FILTER PREDICATE dbo.fn_securitypredicate(UserID) ON dbo.UserData
WITH (STATE = ON);

6. Performance and Optimization

6.1 Query Performance Insights

Azure SQL Database provides built-in intelligence to identify and resolve performance issues.

Key Features:

Query performance recommendations

Automatic index management

Query execution statistics

Wait statistics analysis

6.2 Indexing Strategies

sql

-- Create clustered index

CREATE CLUSTERED INDEX IX_Orders_OrderDate
ON Orders(OrderDate);

-- Create covering index

CREATE INDEX IX_Orders_CustomerID_Status
ON Orders(CustomerID, Status)
INCLUDE (OrderDate, TotalAmount);

-- Create filtered index

CREATE INDEX IX_Orders_ActiveOrders
ON Orders(OrderDate)
WHERE Status = 'Active';
6.3 Query Optimization Techniques

sql

-- Use appropriate WHERE clauses

SELECT * FROM Customers
WHERE CustomerID = 1001; -- Use indexed columns

-- Avoid SELECT *
SELECT CustomerID, FirstName, LastName
FROM Customers
WHERE IsActive = 1;

-- Use EXISTS instead of IN for better performance

SELECT * FROM Customers c
WHERE EXISTS (
SELECT 1 FROM Orders o
WHERE o.CustomerID = c.CustomerID
);

-- Use appropriate JOINs

SELECT c.FirstName, c.LastName, COUNT(o.OrderID) as OrderCount
FROM Customers c
LEFT JOIN Orders o ON c.CustomerID = o.CustomerID
GROUP BY c.CustomerID, c.FirstName, c.LastName;

6.4 Automatic Tuning

sql

-- Enable automatic tuning

ALTER DATABASE mydatabase
SET AUTOMATIC_TUNING (FORCE_LAST_GOOD_PLAN = ON);

ALTER DATABASE mydatabase

SET AUTOMATIC_TUNING (CREATE_INDEX = ON);

ALTER DATABASE mydatabase

SET AUTOMATIC_TUNING (DROP_INDEX = ON);

6.5 Memory-Optimized Tables (In-Memory OLTP)

sql
 -- Create memory-optimized filegroup
ALTER DATABASE mydatabase
ADD FILEGROUP memory_optimized_filegroup
CONTAINS MEMORY_OPTIMIZED_DATA;

ALTER DATABASE mydatabase

ADD FILE (
name = 'memory_optimized_file',
filename = 'memory_optimized_file'
) TO FILEGROUP memory_optimized_filegroup;

-- Create memory-optimized table

CREATE TABLE dbo.FastTable (
ID int IDENTITY(1,1) PRIMARY KEY NONCLUSTERED,
Name nvarchar(50) NOT NULL,
Value decimal(10,2),
CreatedDate datetime2 DEFAULT GETUTCDATE(),

INDEX IX_FastTable_Name HASH (Name) WITH (BUCKET_COUNT = 1024)

) WITH (MEMORY_OPTIMIZED = ON, DURABILITY = SCHEMA_AND_DATA);

7. Backup and Recovery

7.1 Automated Backups

Azure SQL Database automatically creates backups:

Full backups: Weekly

Differential backups: Every 12-24 hours

Transaction log backups: Every 5-10 minutes

7.2 Point-in-Time Restore

sql

-- Restore database to specific point in time using Azure CLI

az sql db restore \
--dest-name myrestoreddb \
--resource-group myResourceGroup \
--server myserver \
--source-database mydatabase \
--time "2024-01-15T10:30:00"

7.3 Long-Term Retention (LTR)

 powershell

# Configure long-term retention policy

Set-AzSqlDatabaseBackupLongTermRetentionPolicy \
-ResourceGroupName "myResourceGroup" \
-ServerName "myserver" \
-DatabaseName "mydatabase" \
-WeeklyRetention "P4W" \
-MonthlyRetention "P12M" \
-YearlyRetention "P7Y" \
-WeekOfYear 1

7.4 Geo-Restore

sql

-- Create database from geo-redundant backup

az sql db restore \
--dest-name myrestoreddb \
--resource-group myResourceGroup \
--server myserver \
--source-database "/subscriptions/subscription-id/resourceGroups/source-rg/providers/Microsoft.Sql/servers/source-serv
--time "2024-01-15T10:30:00"

 

7.5 Active Geo-Replication

sql

-- Create secondary database for geo-replication

az sql db replica create \
--name mydatabase \
--resource-group myResourceGroup \
--server myserver \
--partner-resource-group mySecondaryRG \
--partner-server mysecondaryserver

8. Monitoring and Troubleshooting

8.1 Azure Monitor Integration

Metrics: CPU, DTU, storage, connections
Logs: Query execution, deadlocks, timeouts
Alerts: Proactive monitoring and notifications
8.2 Dynamic Management Views (DMVs)

sql

-- Check current connections

SELECT
session_id,
login_name,
host_name,
program_name,
status,
cpu_time,
memory_usage
FROM sys.dm_exec_sessions
WHERE is_user_process = 1;

-- Identify expensive queries

SELECT TOP 10
qs.sql_handle,
qs.execution_count,
qs.total_worker_time / qs.execution_count AS avg_cpu_time,
qs.total_elapsed_time / qs.execution_count AS avg_elapsed_time,
SUBSTRING(qt.text, (qs.statement_start_offset/2)+1,
((CASE qs.statement_end_offset
WHEN -1 THEN DATALENGTH(qt.text)
ELSE qs.statement_end_offset
END - qs.statement_start_offset)/2)+1) AS statement_text
FROM sys.dm_exec_query_stats qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) qt
ORDER BY avg_cpu_time DESC;

-- Check index usage

SELECT
OBJECT_NAME(i.object_id) AS table_name,
i.name AS index_name,
s.user_seeks,
s.user_scans,
s.user_lookups,
s.user_updates
FROM sys.indexes i
LEFT JOIN sys.dm_db_index_usage_stats s
ON i.object_id = s.object_id AND i.index_id = s.index_id
WHERE OBJECTPROPERTY(i.object_id, 'IsUserTable') = 1
ORDER BY s.user_seeks + s.user_scans + s.user_lookups DESC;

8.3 Query Store

 sql

-- Enable Query Store

ALTER DATABASE mydatabase
SET QUERY_STORE = ON (
OPERATION_MODE = READ_WRITE,
CLEANUP_POLICY = (STALE_QUERY_THRESHOLD_DAYS = 30),
DATA_FLUSH_INTERVAL_SECONDS = 900,
INTERVAL_LENGTH_MINUTES = 60,
MAX_STORAGE_SIZE_MB = 1000,
QUERY_CAPTURE_MODE = AUTO,
SIZE_BASED_CLEANUP_MODE = AUTO
);

-- Query Store analysis

SELECT
q.query_id,
qt.query_sql_text,
rs.avg_duration,
rs.avg_cpu_time,
rs.avg_logical_io_reads,
rs.count_executions
FROM sys.query_store_query q
JOIN sys.query_store_query_text qt ON q.query_text_id = qt.query_text_id
JOIN sys.query_store_runtime_stats rs ON q.query_id = rs.query_id
WHERE rs.last_execution_time > DATEADD(day, -7, GETUTCDATE())
ORDER BY rs.avg_duration DESC;

9. Advanced Features

9.1 Elastic Queries (Cross-Database Queries)

sql
 -- Create external data source
CREATE EXTERNAL DATA SOURCE RemoteDB
WITH (
TYPE = RDBMS,
LOCATION = 'myserver.database.windows.net',
DATABASE_NAME = 'RemoteDatabase',
CREDENTIAL = MyCredential
);

-- Create external table

CREATE EXTERNAL TABLE dbo.RemoteCustomers (
CustomerID int,
FirstName nvarchar(50),
LastName nvarchar(50)
)
WITH (
DATA_SOURCE = RemoteDB,
SCHEMA_NAME = 'dbo',
OBJECT_NAME = 'Customers'
);

-- Query across databases

SELECT
l.OrderID,
r.FirstName,
r.LastName
FROM dbo.Orders l
JOIN dbo.RemoteCustomers r ON l.CustomerID = r.CustomerID;

9.2 JSON Support

sql
 -- Store JSON data
CREATE TABLE ProductCatalog (
ProductID int PRIMARY KEY,
ProductData nvarchar(max) CHECK (ISJSON(ProductData) = 1)
);

INSERT INTO ProductCatalog VALUES

(1, '{"name": "Laptop", "price": 999.99, "specs": {"cpu": "Intel i7", "ram": "16GB"}}');

-- Query JSON data

SELECT
ProductID,
JSON_VALUE(ProductData, '$.name') AS ProductName,
JSON_VALUE(ProductData, '$.price') AS Price,
JSON_VALUE(ProductData, '$.specs.cpu') AS CPU
FROM ProductCatalog
WHERE JSON_VALUE(ProductData, '$.price') > 500;

-- Update JSON properties

UPDATE ProductCatalog
SET ProductData = JSON_MODIFY(ProductData, '$.price', 899.99)
WHERE ProductID = 1;

9.3 Temporal Tables

sql
 -- Create temporal table
CREATE TABLE Employees (
EmployeeID int IDENTITY(1,1) PRIMARY KEY,
FirstName nvarchar(50) NOT NULL,
LastName nvarchar(50) NOT NULL,
Salary decimal(10,2),

-- System versioning columns

ValidFrom datetime2 GENERATED ALWAYS AS ROW START HIDDEN,
ValidTo datetime2 GENERATED ALWAYS AS ROW END HIDDEN,
PERIOD FOR SYSTEM_TIME (ValidFrom, ValidTo)
)
WITH (SYSTEM_VERSIONING = ON (HISTORY_TABLE = dbo.EmployeesHistory));

-- Query historical data

SELECT * FROM Employees
FOR SYSTEM_TIME AS OF '2024-01-01T00:00:00'
WHERE EmployeeID = 1;

-- Query all changes for a record

SELECT * FROM Employees
FOR SYSTEM_TIME ALL
WHERE EmployeeID = 1
ORDER BY ValidFrom;

9.4 Graph Database Features

sql
 -- Create node tables
CREATE TABLE Person (
ID int PRIMARY KEY,
Name nvarchar(100)
) AS NODE;

CREATE TABLE Restaurant (

ID int PRIMARY KEY,
Name nvarchar(100),
City nvarchar(100)
) AS NODE;

-- Create edge table

CREATE TABLE Likes (
Rating int
) AS EDGE;

-- Insert data
INSERT INTO Person VALUES (1, 'John'), (2, 'Mary');
INSERT INTO Restaurant VALUES (1, 'Pizza Palace', 'Seattle'), (2, 'Burger House', 'Seattle');

-- Create relationships
INSERT INTO Likes VALUES ((SELECT $node_id FROM Person WHERE ID = 1),
(SELECT $node_id FROM Restaurant WHERE ID = 1), 5);

-- Query using MATCH

SELECT p.Name, r.Name, l.Rating
FROM Person p, Likes l, Restaurant r
WHERE MATCH(p-(l)->r) AND r.City = 'Seattle';

10. Migration Strategies

10.1 Data Migration Service (DMS)

Azure Database Migration Service provides a seamless migration experience from on-premises SQL
Server to Azure SQL.

Migration Process:

1. Assessment: Use Data Migration Assistant (DMA) to assess compatibility

2. Schema Migration: Migrate database schema using DMS
3. Data Migration: Migrate data with minimal downtime

4. Cutover: Switch applications to Azure SQL Database

10.2 Backup and Restore Migration

sql

-- Export on-premises database to BACPAC

SqlPackage.exe /Action:Export /TargetFile:"C:\mydatabase.bacpac"
/SourceServerName:"myserver" /SourceDatabaseName:"mydatabase"

-- Import BACPAC to Azure SQL Database

SqlPackage.exe /Action:Import /SourceFile:"C:\mydatabase.bacpac"
/TargetServerName:"myserver.database.windows.net"
/TargetDatabaseName:"mydatabase" /TargetUser:"myuser" /TargetPassword:"mypassword"

10.3 Transactional Replication

sql

-- Configure transactional replication for online migration

-- This requires SQL Server Agent and proper network connectivity

-- On source server (Publisher)

EXEC sp_addpublication
@publication = 'MyPublication',
@description = 'Migration Publication',
@sync_method = 'concurrent',
@retention = 0,
@allow_push = 'true',
@allow_pull = 'false';

-- Add articles to publication

EXEC sp_addarticle
@publication = 'MyPublication',
@article = 'Customers',
@source_object = 'Customers';

11. Cost Management

11.1 Pricing Models

DTU-Based Pricing:

Basic: $4.90/month for 5 DTUs

Standard: $15/month for 10 DTUs (scales up to $4,508/month for 3000 DTUs)

Premium: $465/month for 125 DTUs (scales up to $14,424/month for 4000 DTUs)
vCore-Based Pricing:

General Purpose: $0.5/vCore/hour (provisioned), $0.52/vCore/hour (serverless)

Business Critical: $1.0/vCore/hour

Hyperscale: $0.5/vCore/hour + storage costs

11.2 Cost Optimization Strategies

Use Serverless for intermittent workloads

Implement Elastic Pools for multiple databases

Right-size your service tier based on actual usage

Use Reserved Capacity for predictable workloads

Enable auto-pause for development databases

Monitor and optimize storage usage

11.3 Monitoring Costs

powershell

# Get cost information using PowerShell

Get-AzConsumptionUsageDetail -StartDate "2024-01-01" -EndDate "2024-01-31" |
Where-Object {$_.ResourceType -eq "Microsoft.Sql/servers/databases"} |
Select-Object ResourceName, Cost, UsageQuantity

12. Best Practices

12.1 Security Best Practices

Use Azure AD authentication whenever possible

Enable Advanced Threat Protection for production databases

Implement Row-Level Security for multi-tenant applications

Use Always Encrypted for sensitive data

Regular security audits and compliance checks

Principle of least privilege for user access

Use Virtual Network service endpoints or Private Link

12.2 Performance Best Practices

Monitor Query Performance Insights regularly

Enable Automatic Tuning for production databases

Use appropriate indexes and avoid over-indexing
 Implement connection pooling in applications
Use appropriate isolation levels for transactions

Partition large tables when necessary

Regular statistics updates and index maintenance

12.3 Availability Best Practices

Implement geo-replication for critical databases

Use Failover Groups for automatic failover

Regular backup testing and restoration procedures

Monitor database health proactively

Implement retry logic in applications

Use multiple availability zones when available

12.4 Development Best Practices

Use parameterized queries to prevent SQL injection
Implement proper error handling in applications

Use staging environments for testing

Version control for database schema changes

Automated testing for database operations

Document database design and procedures

12.5 Operational Best Practices

Regular patching and updates (handled automatically)

Capacity planning based on growth projections

Cost monitoring and optimization

Disaster recovery testing procedures

Change management processes

Documentation of configurations and procedures

Conclusion
Azure SQL Database provides a comprehensive, fully managed database service that combines the
familiarity of SQL Server with the benefits of cloud computing. By following the practices and techniques
outlined in this guide, you can effectively design, implement, and manage robust database solutions in
Azure.

Key takeaways:
 Choose the appropriate Azure SQL service type based on your requirements
Implement proper security measures from the beginning

Monitor and optimize performance continuously

Plan for high availability and disaster recovery

Manage costs through appropriate sizing and optimization

Follow established best practices for security, performance, and operations

For the most current information and updates, always refer to the official Microsoft Azure documentation
and stay informed about new features and capabilities as they are released.

This guide provides a comprehensive overview of Azure SQL Database. For detailed implementation
guidance and the latest features, consult the official Azure documentation and Microsoft learning resources.