Azure SQL Database Complete Guide: Architecture, Deployment, Pricing, Security, Backup, Performance, and High Availability

A comprehensive Azure SQL tutorial for developers, cloud engineers, and Azure administrators, including practical examples, Azure Portal demonstrations, SQL scripts, real-world scenarios, and AZ-104/AZ-305 interview questions.

This article covers the concepts in your attached material, expands on important architectural and security considerations, and includes additional guidance for deploying ASP.NET Core applications with Azure SQL Database.

The article is organized for publication on your portfolio website, jntech.in, with SEO metadata provided after the interview questions.

1. Introduction to Azure SQL Database

Most modern applications need a database to store information such as users, products, orders, payments, and transactions.

Traditionally, organizations installed SQL Server on physical servers or virtual machines and maintained the entire database infrastructure themselves.

That required database administrators and infrastructure engineers to manage:

• Operating system installation and updates

• SQL Server installation, configuration, and patching

• Database backups and recovery

• High availability and disaster recovery

• Storage capacity and performance

• Security configuration and access control

• Database server monitoring

• SQL Server licensing

Microsoft Azure simplifies many of these responsibilities through Azure SQL Database, a fully managed relational database service.

What is Azure SQL Database?

Azure SQL Database is a Platform as a Service (PaaS) database offering based on the Microsoft SQL Server database engine.

Microsoft manages the underlying infrastructure, while developers and database administrators focus on application data, database design, queries, security, and performance.

Architecture

                 End Users
                     |
                     v
              Web Application
             (ASP.NET Core API)
                     |
                     v
              Azure SQL Database
                     |
         +-----------+-----------+
         |           |           |
         v           v           v
     Database     Automated    Built-in
      Engine       Backups     Availability
         |           |           |
         +-----------+-----------+
                     |
                     v
           Microsoft Managed
             Infrastructure

For example, consider an ASP.NET Core e-commerce application hosted on Azure App Service.

The application needs to store customers, products, orders, and payments.

Instead of creating a Windows VM and installing SQL Server, you can create an Azure SQL Database and connect your application directly to it.

Microsoft manages the operating system, database engine patching, infrastructure availability, and automated backups.

You remain responsible for database objects, permissions, application queries, and protecting your data.

2. Why Managed Databases Exist

Traditional SQL Server Hosting

Suppose a company hosts SQL Server on a Windows Server VM.

             Application
                  |
                  v
             SQL Server
                  |
                  v
          Windows Server VM
                  |
                  v
           Azure Infrastructure

The company must manage SQL Server, the operating system, database maintenance, backups, and the high-availability configuration.

Azure SQL Database Hosting

             Application
                  |
                  v
           Azure SQL Database
                  |
                  v
        Microsoft Managed Platform
                  |
                  v
           Azure Infrastructure

Microsoft manages the database platform, reducing operational overhead.

Microsoft Responsibilities vs Customer Responsibilities

Important: Azure SQL Database is a managed service, but it does not eliminate the need for database administration. Query optimization, indexing, security, access control, and data governance remain customer responsibilities.

3. Azure SQL Database vs SQL Server on Azure VM

The primary distinction is the cloud service model.

When should you choose Azure SQL Database?

Choose Azure SQL Database when:

• Developing a new ASP.NET Core application

• Building APIs and microservices

• Hosting cloud-native applications

• Reducing infrastructure maintenance

• Requiring automatic backups and built-in availability

Choose SQL Server on Azure VM when:

• You require administrator access to the operating system.

• Your application depends on a specific SQL Server version.

• You must install third-party software on the database server.

• You require SQL Server features unavailable in Azure SQL Database.

Part 1 — Azure SQL Deployment Options

4. Understanding the Azure SQL Family

Azure offers four major SQL deployment approaches:

                    Azure SQL
                        |
       +----------------+----------------+
       |                |                |
       v                v                v
 SQL Databases     Managed Instance   SQL Virtual
       |                                Machines
   +---+---+
   |       |
   v       v
 Single  Elastic
Database  Pool

Each deployment model solves a different architectural problem.

5. Azure SQL Single Database

A Single Database is an independently managed database with its own compute and storage allocation.

For example:

            Azure App Service
                   |
                   v
            Logical SQL Server
                   |
                   v
              Database
              (AppDB)
                   |
            +------+------+
            |             |
            v             v
         Tables        Procedures

Each single database can be scaled, restored, and monitored independently.

Advantages

• Simple deployment and management

• Independent performance allocation

• Built-in backups and high availability

• Serverless compute support

• Suitable for most new applications

Real-world example

You develop a personal portfolio using ASP.NET Core Web API and Angular.

Your application contains tables such as:

PortfolioDB
    |
    +-- Articles
    +-- Categories
    +-- Tags
    +-- ArticleTags
    +-- Users
    +-- RefreshTokens

A Single Database is generally the appropriate starting point.

6. Azure SQL Elastic Pool

An Elastic Pool allows multiple Azure SQL databases to share a common pool of compute resources.

The problem

Imagine a SaaS application with 100 customers.

Each customer has a separate database.

If every database has dedicated compute capacity, much of that capacity may remain unused.

Without Elastic Pool

Customer A ---> Database A ---> Compute A
Customer B ---> Database B ---> Compute B
Customer C ---> Database C ---> Compute C
Customer D ---> Database D ---> Compute D

Each database is provisioned independently.

With Elastic Pool

              Elastic Pool
         Shared Compute Resources
                    |
       +------------+------------+
       |            |            |
       v            v            v
   Database A   Database B   Database C
       |            |            |
   Customer A   Customer B   Customer C

When Database A requires more resources, it can use available capacity from the pool, subject to configured limits.

Advantages

• Shared compute resources

• Better utilization for variable workloads

• Centralized pool-level cost management

• Individual database resource limits

• Suitable for multi-tenant SaaS applications

When should you use Elastic Pool?

Elastic Pools are particularly useful when databases have different peak usage periods.

For example:

• Customer A experiences high traffic in the morning.

• Customer B experiences high traffic in the afternoon.

• Customer C experiences high traffic in the evening.

All three can share the same pool capacity.

However, if every database experiences peak traffic simultaneously, the pool must have sufficient capacity for that combined workload.

Key point: Elastic Pools are valuable because of differences in database usage patterns, not simply because an application has many databases.

7. Azure SQL Managed Instance

Azure SQL Managed Instance is a fully managed SQL Server instance offering extensive compatibility with traditional SQL Server.

It is primarily designed for organizations migrating existing SQL Server workloads to Azure.

Architecture

             Corporate Network
                     |
                 VPN / ExpressRoute
                     |
                     v
               Azure Virtual Network
                     |
                     v
              Dedicated SQL Subnet
                     |
                     v
             SQL Managed Instance
                     |
          +----------+----------+
          |          |          |
          v          v          v
       Database A Database B Database C
                     |
              SQL Server Agent

Important features

• SQL Server Agent

• Cross-database queries

• Cross-database transactions

• SQL CLR support

• Database Mail

• Linked servers, subject to supported configurations

• Service Broker

• Native VNet integration

• Managed backups and patching

Example

An organization has an on-premises SQL Server application using:

• Multiple databases

• SQL Server Agent jobs

• Cross-database stored procedures

• Database Mail

Migrating directly to Azure SQL Database might require application changes.

Azure SQL Managed Instance is a strong migration candidate because it supports many traditional instance-level features.

However, compatibility is not absolute. Assess unsupported features and configuration differences before migration.

Cost considerations

Managed Instance is generally more expensive than a small Single Database and requires careful planning for compute, storage, networking, and availability.

Provisioning can also take considerably longer than creating a Single Database.

For learning purposes, explore its configuration in the Azure Portal without deploying an instance unnecessarily.

8. SQL Server on Azure Virtual Machines

SQL Server on an Azure VM provides complete control over the SQL Server environment.

              Azure Virtual Machine
                        |
                 Windows / Linux
                        |
                    SQL Server
                        |
             +----------+----------+
             |          |          |
             v          v          v
          Database   SQL Agent    SSRS

You can install additional software, configure SQL Server features, and manage the operating system.

The SQL Server IaaS Agent extension can assist with management features such as automated backup and patching for supported configurations.

However, the organization remains responsible for the VM and SQL Server lifecycle.

Deployment selection guide

Part 2 — Azure SQL Purchasing Models and Service Tiers

9. DTU vs vCore Purchasing Models

Azure SQL Database offers two principal purchasing models.

DTU model

DTU stands for Database Transaction Unit.

A DTU represents a blended measure of:

• CPU

• Memory

• Data I/O

• Transaction log I/O

Instead of selecting individual hardware resources, you select a performance level.

The traditional DTU tiers are:

For example, Standard S0 provides 10 DTUs.

The DTU model simplifies sizing but gives less visibility into individual compute resources.

vCore model

vCore stands for virtual core.

The vCore model exposes compute capacity more directly.

You can select:

• Number of vCores

• Hardware configuration

• Compute tier

• Storage capacity

• Service tier

The amount of memory is determined by the selected hardware and service configuration; it is not generally an independently adjustable setting.

DTU vs vCore comparison

Recommendation: For a new application, start by evaluating the vCore model because it offers clearer resource sizing and more purchasing options.

10. Azure SQL vCore Service Tiers

Azure SQL Database supports three major vCore service tiers:

1. General Purpose

2. Business Critical

3. Hyperscale

General Purpose

General Purpose separates database compute from storage.

             General Purpose
                    |
             Compute Layer
                    |
                    v
             Remote Storage
                    |
              Data + Logs

Suitable for:

• Business applications

• ASP.NET Core APIs

• Portfolio websites

• Development and testing

• General transactional workloads

Business Critical

Business Critical uses local SSD storage and a high-availability replica architecture.

              Business Critical
                     |
        +------------+------------+
        |            |            |
        v            v            v
      Primary     Replica 1    Replica 2
        |            |            |
      Local        Local        Local
       SSD          SSD          SSD

Business Critical is optimized for low-latency, high-I/O workloads.

It can also provide read scale-out through a readable secondary replica, subject to the service configuration.

Typical applications include:

• Financial transaction systems

• High-throughput OLTP applications

• Latency-sensitive enterprise systems

Hyperscale

Hyperscale separates compute, transaction logging, and distributed data storage.

                  Hyperscale
                      |
                Compute Node
                      |
              Transaction Log
                 Service
                      |
           +----------+----------+
           |          |          |
           v          v          v
       Page Server Page Server Page Server
           |          |          |
           +----------+----------+
                      |
                Azure Storage

Advantages include:

• Storage scaling to very large database sizes

• Rapid backup and restore architecture

• Independent compute and storage scaling

• Optional read replicas

• Suitable for large and growing databases

Hyperscale supports database sizes up to approximately 100 TB, subject to current service limits.

Its snapshot-based architecture makes many restore operations much faster than traditional size-dependent backup restores, although actual recovery time varies.

Service tier comparison

11. Azure SQL Serverless Compute

Serverless is a compute tier that automatically adjusts database compute resources according to workload demand.

It is especially useful for databases that remain idle for long periods.

Provisioned compute

          Provisioned Database
                   |
          Compute Always Allocated
                   |
           24-Hour Billing
                   |
       Active or Idle: Compute Cost

Serverless compute

          Serverless Database
                   |
             Workload Active?
                   |
             +-----+-----+
             |           |
            Yes          No
             |           |
             v           v
         Auto-scale   Idle Period
         Compute          |
             |            v
             |        Auto-pause
             |            |
             v            v
        Compute Cost   No Compute Cost

How Serverless works

You configure:

• Minimum vCores

• Maximum vCores

• Auto-pause delay, where supported

• Maximum storage

Azure automatically adjusts compute resources according to demand.

When an eligible database remains idle for the configured period, it can pause.

While paused, compute billing stops, but applicable storage charges remain.

The next connection triggers automatic resume.

Important limitations

• Resume introduces connection latency.

• Applications should implement retry handling.

• Some configurations or background activities prevent auto-pausing.

• Not every service tier or configuration supports auto-pause.

• Serverless compute does not mean that storage is free.

When should you use Serverless?

Good candidates include development databases, internal tools, test environments, learning projects, and applications with unpredictable or intermittent demand.

For continuously active production applications, provisioned compute may be more predictable and cost-effective.

Part 3 — Azure SQL Free Offer and Database Creation

12. Understanding the Azure SQL Database Free Offer

The attached training material describes an Azure SQL Database free offer with the following monthly allowances:

The free offer is distinct from the Azure free-account 12-month SQL Database benefit.

When configuring an eligible database, Azure may offer an option to pause the database when its free compute allowance is exhausted rather than continue with paid consumption.

Cost warning: Free-tier eligibility, regional availability, offer limits, and portal options can change. Confirm the offer displayed in your subscription before deploying. Additional databases, restored databases, private networking resources, monitoring storage, and optional security services may generate charges.

13. Hands-On: Create an Azure SQL Database

Step 1 — Create a resource group

Open the Azure Portal:

https://portal.azure.com

Search for Resource groups, select Create, and enter:

Click Review + create, then Create.

Step 2 — Open Azure SQL

Search for Azure SQL and select Create.

Choose:

• SQL databases

• Single database

Step 3 — Configure database basics

Step 4 — Create the logical SQL server

Enter:

The resulting endpoint resembles:

sql-lwm-yourname.database.windows.net

The logical server name must be globally unique.

Step 5 — Configure compute and storage

For an eligible free-offer configuration, select the free offer in the portal and confirm the settings it applies.

A representative serverless configuration is:

Do not assume manually selecting these values automatically enrolls the database in the free offer. Verify that the offer has been applied.

Step 6 — Configure networking

For a temporary learning environment:

• Select Public endpoint.

• Add your current client IP address.

• Keep public access limited to selected networks.

• Set the minimum TLS version to a supported secure value.

The source tutorial enables Allow Azure services and resources to access this server for convenience. That setting grants a broad Azure-origin network exception and is better avoided unless specifically required.

Step 7 — Load sample data

Under Additional settings, select Sample data if that option is available for your selected configuration.

Azure can load the AdventureWorksLT sample schema.

It includes tables such as:

SalesLT
   |
   +-- Customer
   +-- Product
   +-- ProductCategory
   +-- SalesOrderHeader
   +-- SalesOrderDetail

Step 8 — Deploy

Select Review + create, inspect the estimated cost and configuration, then Create.

After deployment, open the database Overview page and verify its status, server name, and compute tier.

Part 4 — Azure SQL Logical Server and Networking

14. What Is a Logical SQL Server?

A logical SQL server is an administrative container for Azure SQL databases.

It is not a virtual machine or a physical server you can access.

You cannot RDP or SSH into an Azure SQL logical server.

It provides:

• A DNS endpoint

• Server-level firewall configuration

• SQL administrator settings

• Microsoft Entra administrator configuration

• Database management boundaries

• Regional placement

Architecture

           Azure SQL Logical Server
          sql-demo.database.windows.net
                       |
            +----------+----------+
            |          |          |
            v          v          v
         SalesDB     HRDB      InventoryDB
            |          |          |
         General    Business   Serverless
         Purpose    Critical

Databases on the same logical server can use different compute tiers.

The logical server itself does not represent a separately billed VM. Billing is associated with the database resources and optional services.

15. Azure SQL Firewall Rules

Azure SQL supports server-level and database-level firewall rules.

Server-level firewall rules

These apply to the databases under a logical server.

For example:

Allowed IP: 203.0.113.10

A connection originating from that IP can reach the public SQL endpoint, provided authentication and authorization also succeed.

Database-level firewall rules

These can be configured through T-SQL for individual databases.

Example:

EXEC sp_set_database_firewall_rule
    @name = N'OfficeNetwork',
    @start_ip_address = '203.0.113.10',
    @end_ip_address = '203.0.113.20';

Database-level firewall rules provide more granular network restrictions for specific databases.

Allow Azure services

The setting Allow Azure services and resources to access this server adds a special firewall exception represented by 0.0.0.0.

This is broader than allowing only your own Azure subscription.

Resources belonging to other Azure customers may also be able to reach the public endpoint, although valid authentication is still required.

For production systems, use more restrictive network access wherever possible.

16. Azure SQL Private Endpoint

Azure Private Endpoint allows clients to access Azure SQL through a private IP address inside an Azure Virtual Network.

Architecture

                Internet Users
                       |
                       v
                Azure App Service
                       |
                VNet Integration
                       |
                       v
                Azure Virtual Network
                       |
                       v
                 Private Endpoint
                  (Private IP)
                       |
                       v
                Azure SQL Database

           Public Network Access: Disabled

How it works

1. Create a VNet and appropriate subnets.

2. Configure application VNet connectivity.

3. Create a Private Endpoint for the SQL logical server.

4. Configure private DNS resolution.

5. Disable public network access after verifying private connectivity.

Azure commonly uses the private DNS zone:

privatelink.database.windows.net

Your application can continue using the standard SQL server hostname.

DNS resolves that hostname to the private endpoint address from appropriately configured networks.

Important: A Private Endpoint alone does not automatically disable public network access. Disable public access separately when the requirement is private-only connectivity.

Part 5 — Connecting to Azure SQL Database

17. Azure SQL Connection Methods

Azure SQL Database supports standard SQL Server client protocols and tools.

Common options include:

• Azure Portal Query Editor

• SQL Server Management Studio (SSMS)

• Visual Studio Code with the MSSQL extension

• ADO.NET

• Entity Framework Core

• JDBC and ODBC clients

The SQL Server TDS endpoint commonly uses TCP port 1433.

18. Connect Using Azure Portal Query Editor

Open your Azure SQL Database.

Navigate to Query editor and authenticate using either SQL authentication or Microsoft Entra authentication, depending on the configured access.

After connecting, you can execute T-SQL.

Example 1 — Retrieve products

SELECT TOP 10 *
FROM SalesLT.Product;

Example 2 — Customer order summary

SELECT
    c.CompanyName,
    COUNT(soh.SalesOrderID) AS OrderCount,
    SUM(soh.TotalDue) AS TotalSpent
FROM SalesLT.Customer c
INNER JOIN SalesLT.SalesOrderHeader soh
    ON c.CustomerID = soh.CustomerID
GROUP BY c.CompanyName
ORDER BY TotalSpent DESC;

This query demonstrates joins, aggregation, grouping, and sorting.

19. Create and Manage Your Own Table

Create table

CREATE TABLE dbo.Students
(
    StudentID INT IDENTITY(1,1) PRIMARY KEY,
    FullName NVARCHAR(100) NOT NULL,
    Email NVARCHAR(100),
    PhoneNumber NVARCHAR(20),
    EnrolledOn DATE DEFAULT GETDATE()
);

Insert data

INSERT INTO dbo.Students
    (FullName, Email, PhoneNumber)
VALUES
    ('Anita Sharma', 'anita@example.com', '+91-9876543210'),
    ('Ravi Kumar', 'ravi@example.com', '+91-9876543211'),
    ('Sofia Almeida', 'sofia@example.com', '+91-9876543212');

Read data

SELECT *
FROM dbo.Students;

Update data

UPDATE dbo.Students
SET Email = 'anita.sharma@example.com'
WHERE StudentID = 1;

Delete data

DELETE FROM dbo.Students
WHERE StudentID = 2;

These commands work much like SQL Server running on a local machine.

20. Connecting ASP.NET Core to Azure SQL

For an ASP.NET Core application using Entity Framework Core, install the SQL Server provider:

dotnet add package Microsoft.EntityFrameworkCore.SqlServer

Register your database context:

builder.Services.AddDbContext<ApplicationDbContext>(options =>
    options.UseSqlServer(
        builder.Configuration.GetConnectionString("DefaultConnection")));

A traditional SQL-authentication connection string looks like:

Server=tcp:sql-demo.database.windows.net,1433;
Initial Catalog=PortfolioDB;
User ID=appuser;
Password=<secret>;
Encrypt=True;
TrustServerCertificate=False;
Connection Timeout=30;

Do not commit real credentials to Git or embed them directly in source code.

For production Azure-hosted applications, prefer Microsoft Entra managed identity authentication and use Azure Key Vault when secrets are unavoidable.

Part 6 — Azure SQL Backup and Restore

21. Automatic Backups in Azure SQL Database

One major advantage of Azure SQL Database is that backups are automatically managed by Microsoft.

You do not need to install backup software or schedule traditional SQL Server backup jobs.

Azure SQL uses full, differential, and transaction log backups to support recovery.

Backup architecture

                Azure SQL Database
                        |
              Automatic Backups
                        |
         +--------------+--------------+
         |              |              |
         v              v              v
    Full Backup    Differential    Transaction Log
                       Backup          Backup
         |              |              |
         +--------------+--------------+
                        |
                        v
                 Backup Storage
                        |
                        v
                Restore Database

Backup frequency

These are typical platform backup schedules; exact timing is managed by Azure and can vary.

22. Point-in-Time Restore (PITR)

Point-in-Time Restore allows you to recover a database to a selected point within the configured retention period.

For example, suppose an administrator accidentally deletes a table at 2:30 PM.

You may restore the database to a point before the deletion, provided that restore point is available.

  10:00 AM        2:25 PM        2:30 PM
      |               |              |
      v               v              v
  Database OK     Restore Point   Table Deleted
                      |
                      v
               Restored Database
               (New Database)

Retention

For eligible standard Azure SQL Database configurations:

• Default short-term retention is commonly 7 days.

• Retention can generally be configured between 1 and 35 days.

• Restore creates a new database.

The free offer has additional restrictions. In particular, its auto-pause-on-free-limit option restricts PITR retention to seven days.

Important distinction

A PITR operation does not overwrite the original database.

For example:

Original Database:
db-lwm-demo

Restored Database:
db-lwm-demo-restored

Both databases can exist simultaneously.

23. Hands-On: Restore an Accidentally Deleted Table

Use the Students table created earlier.

Step 1 — Delete the table

DROP TABLE dbo.Students;

Verify:

SELECT *
FROM dbo.Students;

The query fails because the table no longer exists.

Step 2 — Open Restore

In the Azure Portal:

1. Open the database.

2. Select Restore.

3. Choose Point-in-time restore.

4. Select a valid restore point before the deletion.

5. Enter a new database name.

6. Review the configuration and create the restored database.

Step 3 — Verify restored data

Connect to the restored database.

SELECT *
FROM dbo.Students;

If the selected restore point contains the table, the records should be available.

Billing warning: A restored database is a separate resource and may incur charges. The Azure SQL free offer does not automatically cover a restored database. Delete test restores when they are no longer needed.

24. Long-Term Retention (LTR)

Some organizations must retain database backups for several years.

For example:

• Banking and financial records

• Government systems

• Audit requirements

• Regulatory compliance

• Historical business records

Azure SQL Long-Term Retention allows eligible databases to retain selected backups for up to 10 years.

Policies can retain weekly, monthly, and yearly backups.

Example

                Azure SQL Database
                        |
                  LTR Policy
                        |
          +-------------+-------------+
          |             |             |
          v             v             v
        Weekly        Monthly       Yearly
        Backups       Backups       Backups
          |             |             |
          +-------------+-------------+
                        |
                        v
                 Long-Term Storage

LTR is designed for retention and recovery, not direct querying of historical backups.

Backup storage beyond included allowances may incur additional charges.

25. Geo-Restore

Geo-restore helps recover a database when an entire Azure region becomes unavailable.

It relies on geo-redundant backup storage.

             Primary Azure Region
                     |
                     v
              Azure SQL Database
                     |
                     v
              Geo-Redundant Backup
                     |
            Asynchronous Replication
                     |
                     v
              Secondary Azure Region
                     |
                     v
               Restore Database

Geo-restore has a higher potential recovery time and data-loss window than live geo-replication.

It is a disaster recovery mechanism, not a continuously running secondary database.

Understanding RPO and RTO

RPO — Recovery Point Objective

The maximum acceptable amount of data loss, measured in time.

RTO — Recovery Time Objective

The maximum acceptable time required to restore service.

Example:

Database Failure: 3:00 PM

Last Recoverable Data: 2:55 PM
RPO = 5 minutes

Database Available Again: 3:20 PM
RTO = 20 minutes

These are illustrative values, not guaranteed Azure recovery times.

Backup and recovery comparison

Part 7 — Azure SQL Database Security

26. Azure SQL Security Architecture

Azure SQL Database provides multiple security controls.

                 Application / User
                         |
                         v
                  Authentication
                 (SQL / Entra ID)
                         |
                         v
                  Network Security
              (Firewall / Private Link)
                         |
                         v
                    TLS Encryption
                         |
                         v
                  Azure SQL Database
                         |
          +--------------+--------------+
          |              |              |
          v              v              v
         TDE         Permissions      Auditing
          |              |              |
          v              v              v
     Encryption      Access Rules     Audit Logs
      at Rest

A secure database requires several layers of protection rather than relying on a single feature.

27. Transparent Data Encryption (TDE)

Transparent Data Encryption protects database files at rest.

It encrypts database files, transaction logs, and backups.

TDE is enabled by default for newly created Azure SQL databases.

How TDE works

             Application
                  |
                  v
             SQL Queries
                  |
                  v
           Azure SQL Engine
                  |
                  v
          Encryption / Decryption
                  |
                  v
             Encrypted Data
                at Rest

TDE protects stored data but does not prevent authorized users from querying plaintext values.

Encryption key options

Microsoft-managed keys: Azure manages the encryption keys.

Customer-managed keys: Your organization manages encryption keys using Azure Key Vault or a supported key management configuration.

Customer-managed keys provide additional control but also introduce operational responsibilities, including key availability, rotation, permissions, and recovery planning.

28. SQL Authentication vs Microsoft Entra Authentication

Azure SQL Database supports SQL authentication and Microsoft Entra authentication.

SQL authentication

Users connect with a username and password.

Username: appuser
Password: ********

Advantages:

• Familiar SQL Server authentication model

• Supported by many applications and tools

Limitations:

• Password management required

• Password rotation needed

• Credentials can be exposed if stored insecurely

Microsoft Entra authentication

Microsoft Entra ID provides identity-based authentication.

                 Microsoft Entra ID
                         |
                Identity Verification
                         |
                         v
                  Azure SQL Database
                         |
                   Database User
                         |
                   Database Roles

Benefits include centralized identity management, group-based access, and integration with identity security policies.

Example: Create an Entra database user

CREATE USER [developer@contoso.com]
FROM EXTERNAL PROVIDER;

ALTER ROLE db_datareader
ADD MEMBER [developer@contoso.com];

This creates a contained database user associated with an Entra identity and grants read access.

The required identity must exist and the executing administrator must have the appropriate permissions.

29. Managed Identity for Azure SQL

Managed identity allows an Azure service to authenticate without storing a database password.

For example, an ASP.NET Core Web API hosted on Azure App Service can connect to Azure SQL using its managed identity.

Architecture

               ASP.NET Core Web API
                 Azure App Service
                         |
                         v
                   Managed Identity
                         |
                         v
                  Microsoft Entra ID
                         |
                    Access Token
                         |
                         v
                  Azure SQL Database

Step 1 — Enable managed identity

In Azure App Service, open Identity and enable the system-assigned managed identity.

Step 2 — Configure database permissions

Connect to Azure SQL using an appropriately privileged Microsoft Entra administrator.

CREATE USER [my-portfolio-api]
FROM EXTERNAL PROVIDER;

ALTER ROLE db_datareader
ADD MEMBER [my-portfolio-api];

ALTER ROLE db_datawriter
ADD MEMBER [my-portfolio-api];

The database principal must correspond to the intended managed identity.

Step 3 — Configure the connection string

A managed-identity connection string can use:

Server=tcp:sql-demo.database.windows.net,1433;
Database=PortfolioDB;
Authentication=Active Directory Managed Identity;
Encrypt=True;
TrustServerCertificate=False;

Use a recent version of Microsoft.Data.SqlClient with the appropriate Microsoft Entra authentication dependencies.

Step 4 — Configure EF Core

builder.Services.AddDbContext<ApplicationDbContext>(options =>
    options.UseSqlServer(
        builder.Configuration.GetConnectionString("DefaultConnection")));

No SQL password is required in the connection string.

Best practice: Grant only the permissions your application requires. Avoid granting db_owner to application identities.

30. Dynamic Data Masking (DDM)

Dynamic Data Masking limits the exposure of sensitive values in query results for users without permission to see unmasked data.

For example, a customer-support user may need to identify a customer without viewing the complete email address or phone number.

Without masking

FullName       Email                  PhoneNumber
Anita Sharma   anita@example.com      +91-9876543210

With masking

FullName       Email                  PhoneNumber
Anita Sharma   aXXXX@XXXX.com         +91-XXXXX210

The exact masked output depends on the masking function.

The original values remain unchanged in the database.

Add masking to an email column

ALTER TABLE dbo.Students
ALTER COLUMN Email
ADD MASKED WITH (FUNCTION = 'email()');

Add masking to a phone column

ALTER TABLE dbo.Students
ALTER COLUMN PhoneNumber
ADD MASKED WITH (
    FUNCTION = 'partial(4,"XXXXX",3)'
);

Create a limited database user

CREATE USER supportagent
WITH PASSWORD = 'Replace-With-A-Strong-Password';

ALTER ROLE db_datareader
ADD MEMBER supportagent;

When the user connects with suitable restricted permissions, masking is applied to the query results.

Privileged users and users granted UNMASK can view the original values.

Limitations of Dynamic Data Masking

DDM is not a complete security boundary.

It does not encrypt data and is not a replacement for database permissions.

For stronger protection, evaluate:

• Always Encrypted

• Row-Level Security

• Column-level permissions

• Application authorization

• Data minimization

31. Azure SQL Auditing

Azure SQL Auditing records database activity for investigation, compliance, and security monitoring.

It can capture events such as:

• Database authentication activity

• Failed access attempts

• Query and statement activity, according to the audit policy

• Permission changes

• Schema changes

Audit destinations

Architecture

               Azure SQL Database
                       |
                  SQL Auditing
                       |
          +------------+------------+
          |            |            |
          v            v            v
     Log Analytics   Storage     Event Hubs
          |            |            |
          v            v            v
      Monitoring    Retention      SIEM

Auditing itself and the chosen destinations may have separate cost implications. Log ingestion, retention, and storage should be included in cost estimates.

32. Microsoft Defender for SQL

Microsoft Defender for SQL provides additional database security capabilities.

These can include:

• Vulnerability assessment

• Suspicious activity detection

• Potential SQL injection detection

• Anomalous login monitoring

• Security recommendations

Defender for SQL is a paid security offering, with pricing depending on the applicable plan and resource configuration.

It complements, rather than replaces, application security and database access controls.

Part 8 — Azure SQL Performance and Scaling

33. Scaling Azure SQL Database

Azure SQL Database allows compute and storage configurations to be adjusted as workload requirements change.

Vertical scaling

Vertical scaling increases or decreases the compute resources allocated to a database.

           Small Database
             1 vCore
                |
                v
           Medium Database
             2 vCores
                |
                v
           Large Database
             4 vCores

This can help when an application experiences increasing CPU, memory, or I/O demand.

Example

Suppose an ASP.NET Core API initially receives 100 requests per day.

After business growth, it receives 50,000 requests per day.

You may need to increase database capacity.

However, database scaling should follow workload measurement. Inefficient queries or missing indexes should be investigated before purchasing more compute.

Important consideration

Changing compute tiers can involve a brief connection interruption.

Applications should use connection resiliency and appropriate retry logic.

34. Query Performance Insight

Query Performance Insight helps identify expensive queries.

It can provide information about:

• Queries consuming high CPU

• Long-running queries

• Query execution frequency

• Historical query performance

Example

Suppose this query performs poorly:

SELECT *
FROM dbo.Articles
WHERE Title LIKE '%Azure%';

A leading wildcard generally prevents a normal B-tree index from being used for an efficient seek on Title.

Depending on the search requirements, alternatives may include full-text search, more selective filtering, or a redesigned search strategy.

Query Performance Insight and Query Store can help identify whether the query is contributing significantly to resource consumption.

35. Automatic Tuning

Azure SQL Database can recommend and, when configured, automatically apply selected performance improvements.

Main tuning capabilities

Example: Query plan regression

              SQL Query
                  |
                  v
          Query Optimizer
                  |
         +--------+--------+
         |                 |
         v                 v
      Good Plan         Bad Plan
       100 ms           5000 ms
         |                 |
         +--------+--------+
                  |
                  v
          Automatic Tuning
                  |
                  v
          Force Last Good Plan

Automatic tuning can evaluate changes and reverse certain changes that do not improve performance.

Availability and behavior differ by service tier and tuning option.

Best practice

For production workloads, review tuning recommendations, test their impact, and monitor changes. Automatic index management should not be treated as a replacement for database performance engineering.

36. Additional Performance Concepts

Indexing

Indexes improve data retrieval for appropriate query patterns.

CREATE INDEX IX_Articles_CategoryId
ON dbo.Articles(CategoryId);

Query Store

Query Store records query plans and runtime statistics, helping investigate changes in query performance.

Connection pooling

Connection pooling reuses database connections instead of repeatedly creating new physical connections.

This is especially useful for web applications receiving many concurrent requests.

Retry handling

Transient database errors can occur during:

• Scaling

• Failover

• Temporary connectivity disruptions

• Serverless resume

EF Core provides retry capabilities for SQL Server.

builder.Services.AddDbContext<ApplicationDbContext>(options =>
    options.UseSqlServer(
        builder.Configuration.GetConnectionString("DefaultConnection"),
        sqlOptions =>
        {
            sqlOptions.EnableRetryOnFailure(
                maxRetryCount: 5,
                maxRetryDelay: TimeSpan.FromSeconds(30),
                errorNumbersToAdd: null);
        }));

Retry logic is especially important for cloud applications, but non-idempotent operations and explicit transactions require careful handling to avoid duplicate writes.

Part 9 — Azure SQL High Availability and Disaster Recovery

37. Built-In High Availability

Azure SQL Database includes high availability mechanisms designed to recover from infrastructure failures.

The architecture depends on the selected service tier.

General Purpose

General Purpose separates compute and storage.

When the compute node fails, Azure can provision replacement compute and reconnect it to the underlying storage.

Business Critical

Business Critical uses multiple synchronized replicas to support rapid failover.

                 Business Critical
                         |
               +---------+---------+
               |                   |
               v                   v
           Primary             Secondary
               |                   |
               +---------+---------+
                         |
                  Synchronous
                   Replication

The exact number and roles of replicas depend on the configuration.

38. Zone Redundancy

Availability Zones are physically separate groups of datacenters within an Azure region.

Zone redundancy distributes eligible database infrastructure across zones.

                  Azure Region
                       |
          +------------+------------+
          |            |            |
          v            v            v
        Zone 1       Zone 2       Zone 3
          |            |            |
          v            v            v
      SQL Replica  SQL Replica  SQL Replica
          |            |            |
          +------------+------------+
                       |
                High Availability

Zone redundancy protects against certain datacenter-level failures within a region.

It does not provide full regional disaster recovery.

39. Active Geo-Replication

Active geo-replication creates readable secondary databases in other Azure regions.

Replication is asynchronous.

Architecture

              Primary Region
                    |
                    v
              Primary Database
                    |
           Async Replication
                    |
          +---------+---------+
          |                   |
          v                   v
    Secondary Region A  Secondary Region B
          |                   |
          v                   v
    Readable Secondary  Readable Secondary

Benefits include:

• Regional disaster recovery

• Readable secondary databases

• Geographic read distribution

• Manual failover capability

Azure SQL Database supports up to four geo-secondary databases in supported configurations.

Important

Because replication is asynchronous, some recently committed transactions may not yet exist on a secondary when a forced failover occurs.

Actual data loss depends on replication lag.

40. Azure SQL Failover Groups

Failover groups coordinate database failover between regions and provide stable listener endpoints.

Architecture

               ASP.NET Core Application
                         |
                         v
                Failover Group Listener
                         |
              +----------+----------+
              |                     |
              v                     v
        Primary Region        Secondary Region
              |                     |
              v                     v
        Primary Database      Geo-Secondary
              |                     |
              +-----Replication-----+

Listener endpoint

An example read-write listener is:

my-failover-group.database.windows.net

Your application uses this endpoint rather than hardcoding the primary server endpoint.

After a failover, the listener directs connections to the current primary.

Applications must still handle DNS propagation and reconnect after failover.

Geo-replication vs Failover Group

Interview point: If the requirement mentions automatic regional failover and avoiding manual connection-string changes, a failover group is a strong answer.

Part 10 — Azure Database for PostgreSQL and MySQL

41. Azure Database for PostgreSQL Flexible Server

Azure Database for PostgreSQL Flexible Server is a managed relational database service based on PostgreSQL.

Microsoft manages the underlying database infrastructure, patching, backups, and supported availability features.

Important features

• Burstable, General Purpose, and Memory Optimized compute tiers

• Automatic backups and point-in-time restore

• High availability options

• Read replicas

• Public and private networking options

• Stop and start functionality

• Support for PostgreSQL extensions

Common PostgreSQL extensions

When to choose PostgreSQL

PostgreSQL is suitable for open-source applications, geospatial systems, AI applications requiring vector search, and organizations already using PostgreSQL.

Hands-on: Create a PostgreSQL Flexible Server

In the Azure Portal:

1. Search for Azure Database for PostgreSQL flexible servers.

2. Select Create.

3. Choose a resource group and region.

4. Select a supported PostgreSQL version.

5. Choose a Burstable compute tier for a small learning workload.

6. Configure authentication and networking.

7. Review pricing and deploy.

Example connection:

psql "host=my-postgres.postgres.database.azure.com port=5432 dbname=postgres user=myadmin sslmode=require"

For a learning environment, stop the server when it is not needed. Storage and other applicable charges may continue.

42. Azure Database for MySQL Flexible Server

Azure Database for MySQL Flexible Server provides managed MySQL hosting.

Features include:

• Automatic backups

• Point-in-time restore

• High availability

• Read replicas

• Burstable compute

• Stop/start functionality

• Monitoring and scaling

Common use cases

MySQL is frequently used with:

• WordPress

• Drupal

• Magento

• Laravel

• PHP-based applications

43. Azure SQL vs PostgreSQL vs MySQL

The most appropriate database engine generally depends on the application's existing dependencies, required features, operational needs, and team expertise.

44. Relational Databases vs NoSQL

Relational databases organize data into tables with defined relationships.

For example:

              Customers
                  |
               CustomerId
                  |
                  v
                Orders
                  |
                OrderId
                  |
                  v
              OrderItems
                  |
               ProductId
                  |
                  v
               Products

Relational databases are particularly well suited to applications requiring structured data, referential integrity, and transactional consistency.

NoSQL databases provide different data models and scaling characteristics.

For example, Azure Cosmos DB can support globally distributed applications with flexible document models.

However, NoSQL is not automatically faster or more scalable for every workload. Choose it based on specific requirements rather than general assumptions.

Part 11 — Additional Important Azure SQL Concepts

The following topics expand beyond the original training material and are especially relevant to real-world Azure SQL implementations.

45. Migrating SQL Server to Azure SQL Database

Organizations frequently migrate existing on-premises SQL Server databases to Azure.

Migration architecture

            On-Premises SQL Server
                      |
                      v
              Compatibility Assessment
                      |
                      v
                Migration Planning
                      |
                      v
                Data Migration
                      |
                      v
                Azure SQL Database
                      |
                      v
              Application Validation
                      |
                      v
                Production Cutover

Migration considerations

Evaluate:

• SQL Server version and compatibility

• Unsupported features

• SQL Agent dependencies

• Cross-database queries

• Stored procedures and functions

• Authentication

• Database size

• Downtime requirements

• Application connection strings

Microsoft provides migration assessment and migration tooling for supported SQL Server scenarios.

46. Database Compatibility Level

Azure SQL Database supports database compatibility levels that influence query processing behavior and available T-SQL functionality.

Check the current level:

SELECT
    name,
    compatibility_level
FROM sys.databases
WHERE name = DB_NAME();

Compatibility levels are particularly important during migrations because changing the level can affect query execution plans.

Test application behavior and query performance before changing it in production.

47. Azure SQL Monitoring

Monitoring helps identify resource bottlenecks, performance regressions, and unusual behavior.

Important metrics include:

Monitoring architecture

              Azure SQL Database
                      |
                      v
                 Azure Monitor
                      |
          +-----------+-----------+
          |           |           |
          v           v           v
        Metrics      Logs       Alerts
          |           |           |
          +-----------+-----------+
                      |
                      v
              Operations Team

Configure alerts for meaningful thresholds rather than relying entirely on manual portal checks.

48. Azure SQL Cost Optimization

Azure SQL costs depend on compute, storage, service tier, backup retention, redundancy, and optional services.

Practical recommendations

• Start with a small database tier.

• Use Serverless for suitable intermittent workloads.

• Evaluate Elastic Pools for multiple databases with complementary usage patterns.

• Use Azure Hybrid Benefit when eligible.

• Consider reserved capacity for predictable provisioned workloads.

• Avoid unnecessary geo-secondary databases.

• Delete unused restored databases.

• Review backup retention requirements.

• Monitor compute and storage utilization.

• Configure budgets and cost alerts.

Important free-offer clarification

Microsoft's current documentation confirms that the Azure SQL Database free offer supports up to 10 free General Purpose databases per Azure subscription, each with its own monthly allowance of 100,000 vCore-seconds, 32 GB of data storage, and 32 GB of backup storage. This is an update to the attached tutorial, which describes only one free database.

Microsoft Learn

+1

There are also two distinct auto-pause concepts:

• Idle auto-pause: A serverless database pauses after an eligible idle period. For General Purpose, Microsoft's documented minimum is currently 15 minutes, with a 60-minute default.

• Free-limit auto-pause: A free-offer database can be configured to pause when its monthly allowance is exhausted, resuming at the beginning of the next calendar month.

These behaviors should not be confused.

Microsoft Learn

+1

49. Production Architecture: ASP.NET Core + Azure SQL

A practical production design can use the following components.

                    Internet
                       |
                       v
                Azure App Service
                 ASP.NET Core API
                       |
                Managed Identity
                       |
                       v
                 VNet Integration
                       |
                       v
                 Azure Virtual Network
                       |
                       v
                  Private Endpoint
                       |
                       v
                 Azure SQL Database
                       |
             +---------+---------+
             |         |         |
             v         v         v
           Backups   Auditing   Monitoring

Recommended security and operational configuration

For a .NET application, this architecture reduces credential exposure and infrastructure administration while retaining strong operational controls.

Part 12 — Azure SQL Hands-On Cleanup

50. Clean Up Azure Resources

After completing the demonstrations, review all created resources.

Typical resources include:

rg-jntech-demo
    |
    +-- Azure SQL Logical Server
    |
    +-- db-lwm-demo
    |
    +-- db-lwm-demo-restored
    |
    +-- PostgreSQL Flexible Server
    |
    +-- Monitoring Resources

Delete temporary restored databases and other unused paid resources.

If the entire resource group is no longer required, deleting it removes the resources contained within it.

Before deleting, confirm that no required database, backup configuration, or shared resource is included.

Finally, open Azure Cost Management and review the estimated charges.

Part 13 — Important Azure SQL Interview Questions and Answers

These questions focus on the concepts covered in this article and are suitable for Azure Administrator, Azure Architect, SQL Developer, and .NET Technical Lead interviews.

Group 1 — Azure SQL Fundamentals

Q1. What is Azure SQL Database?

Azure SQL Database is a fully managed relational database PaaS offering based on SQL Server. Microsoft manages infrastructure, patching, backups, and built-in availability.

Key point: PaaS removes infrastructure management, not database development responsibilities.

Q2. What is the difference between Azure SQL Database and SQL Server on Azure VM?

Azure SQL Database is PaaS without OS access. SQL Server on an Azure VM is IaaS, providing full OS and SQL Server instance control.

Key point: Choose a VM when you require OS-level control or unsupported instance features.

Q3. What are the four main Azure SQL deployment options?

Single Database, Elastic Pool, Managed Instance, and SQL Server on Azure VM.

Key point: Each option serves different workload and compatibility requirements.

Q4. What is an Azure SQL logical server?

A logical server is an administrative container that provides a DNS endpoint, firewall settings, and authentication configuration for Azure SQL databases.

Key point: It is not a VM and has no independently allocated database compute.

Q5. What is an Azure SQL Elastic Pool?

An Elastic Pool allows multiple databases to share provisioned compute resources.

Key point: Best suited to databases with variable, non-simultaneous workloads.

Group 2 — Pricing and Compute

Q6. What is the difference between DTU and vCore?

DTU combines CPU, memory, and I/O into a bundled performance measure. vCore exposes compute capacity more directly and supports additional purchasing options.

Key point: vCore is generally preferred for new workloads.

Q7. What are the three main vCore service tiers?

General Purpose, Business Critical, and Hyperscale.

Key point: They differ in storage architecture, performance, availability, and scaling capabilities.

Q8. What is Azure SQL Serverless?

Serverless automatically scales compute according to workload demand and can pause eligible databases during inactivity.

Key point: Useful for intermittent workloads.

Q9. Does Serverless mean the database is completely free?

No. Normal Serverless configurations charge for applicable compute and storage. Compute billing stops while the database is paused.

Key point: The separate free offer provides defined monthly allowances.

Q10. What is the Azure SQL Database free offer?

It provides a recurring monthly allowance of 100,000 vCore-seconds, 32 GB data storage, and 32 GB backup storage per eligible database.

Key point: The current offer supports up to 10 eligible databases per subscription.

Q11. When should you choose Business Critical?

Choose Business Critical for low-latency, high-I/O transactional workloads requiring high performance and rapid failover.

Q12. When should you choose Hyperscale?

Choose Hyperscale for very large databases requiring scalable distributed storage and rapid backup/restore capabilities.

Group 3 — Networking and Security

Q13. What is an Azure SQL firewall rule?

A firewall rule controls which client IP addresses or ranges can access the Azure SQL public endpoint.

Q14. What does Allow Azure services and resources to access this server mean?

It permits connection attempts from a broad range of Azure-hosted resources, including resources outside your subscription.

Key point: Authentication is still required, but network exposure is broader.

Q15. What is Azure SQL Private Endpoint?

A Private Endpoint provides private IP connectivity to Azure SQL through an Azure Virtual Network.

Key point: Combine it with disabled public network access for private-only connectivity.

Q16. What is Transparent Data Encryption?

TDE encrypts database files, transaction logs, and backups at rest.

Key point: Enabled by default on new Azure SQL databases.

Q17. What is Microsoft Entra authentication?

It enables Azure SQL users and applications to authenticate using identities managed through Microsoft Entra ID.

Q18. What is managed identity authentication?

Managed identity allows Azure services to authenticate to Azure SQL without storing database passwords.

Q19. What is Dynamic Data Masking?

DDM hides portions of sensitive column values in query results for users without unmasking privileges.

Key point: It is not encryption or a substitute for access control.

Q20. What is the difference between TDE and Always Encrypted?

TDE protects database files at rest. Always Encrypted protects selected sensitive columns using encryption and decryption performed by supported client drivers.

Q21. What is Azure SQL Auditing?

Auditing records selected database events for compliance, investigation, and security monitoring.

Group 4 — Backup and Disaster Recovery

Q22. Does Azure SQL Database automatically take backups?

Yes. Azure manages automatic full, differential, and transaction log backups.

Q23. What is Point-in-Time Restore?

PITR restores a database to an available point within its backup retention window.

Key point: It creates a new database rather than overwriting the existing database.

Q24. What is the default PITR retention?

Seven days is the common default, with configurable retention up to 35 days for eligible standard configurations.

Q25. What is Long-Term Retention?

LTR retains selected database backups for extended compliance and recovery requirements, potentially up to 10 years.

Q26. What is geo-restore?

Geo-restore rebuilds a database in another region using geo-redundant backups.

Q27. What is active geo-replication?

It asynchronously replicates a primary database to readable secondary databases in other Azure regions.

Q28. What is the difference between active geo-replication and failover groups?

Active geo-replication provides secondary databases and manual failover. Failover groups add listener endpoints and support automatic failover policies.

Q29. What is RPO?

Recovery Point Objective is the maximum acceptable data-loss window.

Q30. What is RTO?

Recovery Time Objective is the maximum acceptable duration of service unavailability during recovery.

Q31. What is zone redundancy?

Zone redundancy distributes eligible database infrastructure across multiple Availability Zones within a region.

Key point: It protects against zone-level failures, not every regional disaster.

Group 5 — Performance and Monitoring

Q32. How do you scale Azure SQL Database?

Modify the database's compute configuration or service tier through the Azure Portal, APIs, CLI, or supported management tools.

Q33. What is Query Performance Insight?

It helps identify queries consuming significant database resources and investigate their performance trends.

Q34. What is automatic tuning?

Automatic tuning evaluates query performance and can apply supported improvements such as index changes or plan correction.

Q35. What is FORCE LAST GOOD PLAN?

It helps correct certain query plan regressions by forcing a previously successful execution plan.

Q36. What is Query Store?

Query Store captures query execution plans and runtime statistics to support performance analysis and troubleshooting.

Q37. Why is retry logic important in Azure SQL applications?

Transient connectivity failures can occur during failover, scaling, maintenance, or Serverless resume.

Key point: Applications should retry suitable transient operations safely.

Group 6 — Real-Time Scenario Questions

Q38. Your ASP.NET Core application needs a managed SQL database. Which Azure service would you select?

Azure SQL Single Database is a suitable default because it offers managed infrastructure, automated backups, and independent scaling.

Q39. You manage 100 customer databases with different peak usage times. What would you recommend?

Azure SQL Elastic Pool, because databases can share compute capacity.

Q40. Your application requires SQL Server Agent and cross-database queries. Which deployment option would you consider?

Azure SQL Managed Instance, subject to a compatibility assessment.

Q41. Your application requires full operating system access. Which option should you choose?

SQL Server on Azure Virtual Machines.

Q42. Your database is idle most of the day. How would you reduce costs?

Evaluate Azure SQL Serverless with auto-pause and confirm that the workload can tolerate resume latency.

Q43. An administrator accidentally deletes a production table. How would you recover it?

Perform a Point-in-Time Restore to a new database at a point before the deletion, validate the data, and carefully restore the required objects or redirect the application.

Q44. Your application must connect to Azure SQL without storing passwords. What would you use?

Microsoft Entra managed identity authentication.

Q45. Your SQL database must not be accessible from the public internet. How would you configure it?

Create a Private Endpoint, configure private DNS and client network connectivity, and disable public network access.

Q46. Your database must automatically fail over to another region without changing the application connection string. What would you use?

An Azure SQL failover group with its read-write listener endpoint.

Q47. Your database performance suddenly becomes slow, but application code has not changed. What would you investigate?

Check resource metrics, Query Store, Query Performance Insight, blocking, indexes, and query plan regressions.

Q48. Your organization must retain database backups for seven years. Which feature would you use?

Azure SQL Long-Term Retention with an appropriate retention policy.

Q49. Your support team must view customer records without seeing complete email addresses. What feature can help?

Dynamic Data Masking, combined with appropriate permissions.

Q50. Your Azure SQL database frequently reaches 100% CPU. Should you immediately increase vCores?

Not necessarily. First inspect expensive queries, indexing, execution plans, and workload patterns. Scale compute when metrics justify additional capacity.

Part 14 — AZ-104 and AZ-305 Exam Revision

Azure certification questions frequently describe requirements rather than explicitly naming the database feature.

Use these associations for quick revision.

These are useful indicators, but actual exam answers depend on the complete requirements, supported features, and constraints.

Part 15 — Conclusion

Azure SQL Database simplifies relational database hosting by removing much of the infrastructure management traditionally associated with SQL Server.

Its managed architecture provides automatic backups, built-in high availability, encryption, monitoring, and scalable compute options.

The Azure SQL family offers different solutions for different workloads:

• Single Database: New cloud applications

• Elastic Pool: Multiple databases sharing compute

• Managed Instance: SQL Server migration with instance-level requirements

• SQL Server on VM: Complete OS and instance control

• Serverless: Intermittent workloads

• Business Critical: Low-latency transactional systems

• Hyperscale: Very large databases

For ASP.NET Core applications, Azure SQL Database combined with managed identity, private networking, EF Core, and suitable backup and monitoring policies provides a practical managed relational database architecture.

The most important design principle is to select the database service based on application requirements, availability targets, security needs, and measured workload behavior—not simply on the largest available service tier.

Recommended Internal Links

For better topical relevance and navigation, link this article to related articles already published on your portfolio, where applicable:

• Azure Fundamentals — Complete Guide

• Azure Virtual Machines — Complete Guide

• Azure Networking — IP Addresses, CIDR, and Subnetting

• Azure Storage — Complete Guide

• Azure App Service — Complete Guide

Use descriptive anchor text and ensure the links point to the correct published URLs.

Official Microsoft Documentation

For ongoing technical verification and updates, use:

• Azure SQL Database Free Offer

• Azure SQL Serverless Compute

• Azure SQL Database Documentation

Publication recommendation: Use the complete guide as a pillar article on jntech.in. Keep the 50 interview questions at the end, as requested, and consider separate supporting articles on Azure SQL Managed Identity, Private Endpoints, and Backup/Restore that link back to this guide.

Tags: Azure SQL Database, Azure SQL, Azure SQL Serverless, Azure SQL Managed Instance, Azure SQL Elastic Pool, Azure SQL Pricing, Azure SQL Security, Azure SQL Backup, Azure SQL High Availability, Azure SQL Failover Groups, Azure SQL Private Endpoint, Microsoft Entra ID, Azure Managed Identity, Azure Database for PostgreSQL, Azure Database for MySQL, Azure SQL Interview Questions, AZ-104, AZ-305, ASP.NET Core, Entity Framework Core, Microsoft Azure, Cloud Computing