# Deploying and Configuring Azure SQL Database

> Chapter 2 of the Courseiva DP-300 curriculum — https://courseiva.com/learn/dp-300/deploy-azure-sql-database

**Official objective:** 1.2 — Implement and manage Azure SQL Database

## Introduction

How do you get a SQL Server database up and running in the cloud without needing a PhD in networking or system administration? That’s the exact problem this chapter solves—and it’s a core part of what the DP-300 exam tests. Whether you’re building a new app or moving an old database off a creaky server in a cupboard, understanding how to deploy and configure Azure SQL Database is the first practical skill you need to pass the exam and work as a database administrator.

## The Apartment Rental Analogy

Ever tried to move into a new apartment? You’ve got a few big choices to make: do you rent a fully furnished place where you just show up with your toothbrush, or do you get an empty shell and install everything yourself—the lights, the plumbing, the kitchen cabinets, down to the floorboards?

Deploying and configuring Azure SQL Database is exactly like choosing how you want to move into that apartment, but with data instead of furniture. If you pick a ‘managed instance’—that’s your fully furnished flat. Azure has already set up the operating system, patched the security holes, configured the backups, and even installed the fire alarm. You just walk in, unlock the door (through your database connection string), and start putting your data on the shelves.

On the other hand, you could pick a ‘virtual machine’ running SQL Server—that’s the empty shell. You have to install the operating system yourself, configure the network, apply security updates, manage disk space, and handle backups. You have total control, but you also have to be the plumber, electrician, and landlord.

Azure SQL Database sits right in the middle: it’s like a serviced apartment. The building management (Azure) handles the building maintenance, security, and cleaning, but you still get to choose your floor plan (performance tier), set your own rules (firewall rules), and decide who gets a key (user permissions). You don’t have to fix the boiler, but you still have to lock your door.

## Core explanation

Azure SQL Database is a fully managed relational database service offered by Microsoft in their cloud platform, Azure. ‘Relational’ means data is stored in tables with rows and columns, like a spreadsheet, but with strict rules about how data relates to each other. ‘Fully managed’ means Microsoft takes care of the underlying hardware, the operating system, the SQL Server engine, security patches, backups, and basic high availability. You, as the database administrator, only worry about the data itself: its schema (the structure of tables), its security (who can see what), and its performance (making queries fast).

When you deploy an Azure SQL Database, you’re not installing any software. Instead, you create a logical server — that’s a virtual container that groups databases together. Think of the logical server as the apartment building. Inside that building, you can have multiple databases (the individual apartments). The logical server has its own name (like ‘myserver.database.windows.net’), a master database that holds login information, and firewall rules that control which internet addresses can connect to it.

The actual deployment happens through the Azure portal (a website), Azure CLI (command-line tools), or PowerShell scripts. For the DP-300 exam, you need to know how to do it through the portal and understand the options you choose during creation. Here is the typical flow:

First, you select a subscription. A subscription is your billing agreement with Azure — it’s like a payment method. Then you create a resource group. A resource group is a logical folder that holds all related Azure resources (like your database, server, and any associated storage or networking pieces). It helps with managing, monitoring, and billing everything together.

Next, you choose a name for your logical server. This name must be globally unique across all of Azure — no two servers in the world can have the same name. Then you set an admin login and password for the server. This is a SQL authentication account that has full control over every database on that server. You also have the option to use Microsoft Entra ID authentication instead, which lets you use corporate email addresses and passwords without storing another username/password pair.

Now the big decision: pick the database type. Azure SQL Database offers several deployment options, but the two primary ones are:

- Single Database: One database living on its own resources. It’s isolated, which means its performance doesn’t affect any other database. Great for a single app.
- Elastic Pool: A shared pool of resources (CPU, memory, IO) that multiple databases draw from. Perfect when you have many small databases that don’t all get busy at the same time — they can share the cost and performance burst capacity.

After that, you must choose a compute tier. The two main tiers are ‘Serverless’ and ‘Provisioned’. Serverless automatically pauses the database when it’s not in use and charges only for storage, then wakes up instantly when a connection arrives. Provisioned keeps the compute always running, so you pay for the capacity even when idle — it’s better for consistent workloads.

You also pick a service tier. Currently, the main tiers are:
- General Purpose: The budget-friendly option. It uses remote storage (like a network drive) and is suitable for most production workloads where you can tolerate a few extra milliseconds of latency.
- Business Critical: The high-performance tier. It uses local fast storage (SSD directly attached to the compute node) and has multiple replicas for resilience. Recommended for mission-critical apps that need extremely low latency and automatic failover.
- Hyperscale: The new tier for very large databases (up to 100 TB). It separates compute and storage completely so you can scale them independently. Great for growing datasets.

Finally, you configure networking. By default, Azure SQL Database blocks all public connections. You must create a firewall rule that allows specific IP addresses (like your office’s public IP) to connect. Alternatively, you can enable ‘Allow Azure services and resources to access this server’ — that’s useful when an Azure app needs to talk to the database.

Once deployed, you can connect to the database using tools like SQL Server Management Studio (SSMS), Azure Data Studio, or even a simple browser-based query editor in the portal. The connection string — a string of text that contains the server name, database name, and login details — is all you need to start querying your data.

Configuration continues after deployment. You can set up automatic backups (they’re on by default with a 7-day retention), configure geo-replication to copy your database to another Azure region for disaster recovery, enable threat detection, and much more. The key point: Azure handles the infrastructure so you can focus on the data.

## Real-world context

Let’s say you work for a small e-commerce company called ‘Books2Go’ that sells second-hand books online. Currently, their database runs on a dusty old server under the office stairs. The server is ten years old, the disk is almost full, and the company is growing — they need to scale, but buying a new physical server costs thousands and takes weeks to set up. The boss says, ‘Just put it in the cloud.’ You, as the junior database administrator, need to make it happen.

You start by logging into the Azure portal. You create a resource group called ‘books2go-prod-rg’ (the naming convention helps you organise resources later). Inside that group, you create a logical server called ‘books2go-db-server’. You set a strong admin password and note it down in a secure password manager — you never hardcode it in scripts.

You choose a single database because Books2Go only has one major database for all orders, customers, and inventory. You select the General Purpose tier because the workload is moderate — they don’t need the ultra-fast local SSDs of Business Critical, but they do need consistent performance. You pick the Serverless compute tier because traffic is lower at night and weekends — why pay for idle compute?

During the network configuration, you add a firewall rule allowing your office’s public IP address. You also enable the ‘Allow Azure services’ checkbox because the web app that runs the online store is also hosted in Azure (on an App Service), and it needs to reach the database.

After the database deploys, you need to move the existing data. You use the ‘Export data-tier application’ wizard in SQL Server Management Studio to create a .bacpac file from the old server — that’s like a zip file of the entire database schema and data. Then you upload that .bacpac to an Azure storage blob container (think of it as a cloud disk) and use the ‘Import’ feature in the portal to restore it into your new Azure SQL Database.

Once the data is in, you test a few queries from SSMS. You configure a daily backup schedule — although Azure does automatic backups, you set up a long-term retention policy to keep backups for one year for compliance reasons. You also enable Advanced Data Security (a threat detection tool) to get alerts if someone tries SQL injection attacks.

Finally, you write a small PowerShell script that calculates the current database size and sends a weekly email to the team. You also set up geo-replication to the West US region, so if the primary East US region goes down, the West US copy can take over with a single DNS change. The whole deployment — from creating the server to having a fully functioning, secure, replicated database — takes under an hour, compared to the two weeks it would have taken to order and set up a physical server. The company saves money, and you look like a rockstar.

## Exam focus

The DP-300 exam devotes significant attention to deploying and configuring Azure SQL Database under objective 1.2. You should expect multiple-choice questions, case studies, and possibly ‘yes/no’ or ‘drag-and-drop’ scenarios that require you to choose the correct deployment option or configuration setting.

Here are the exact high-frequency topics they test:

- Differences between Single Database, Elastic Pool, and Managed Instance. They love to give a scenario with many small databases that have infrequent usage patterns — the correct answer is Elastic Pool because it shares resources and reduces cost.
- Service tier selection: General Purpose vs Business Critical vs Hyperscale. Traps include giving a low-latency requirement (like ‘sub-millisecond response needed for financial trading’) — the answer is Business Critical. Hyperscale is for databases exceeding 4 TB.
- Compute tier: Serverless vs Provisioned. Look for clues in the scenario like ‘unpredictable usage’ or ‘long periods of inactivity’ — Serverless is the answer. If the scenario mentions ‘consistent heavy load’ or ‘no tolerance for cold-start latency’, pick Provisioned.
- Firewall rules: They will test that by default, Azure SQL Database denies all external connections. You must explicitly create a server-level firewall rule to allow specific IPs. They may also test the difference between server-level and database-level firewall rules — server-level rules are set via the portal, database-level rules are set via T-SQL.
- Authentication methods: They test SQL authentication vs Microsoft Entra ID authentication. A common trap asks how to allow users to log in using their corporate Office 365 credentials — the answer is to use Microsoft Entra ID authentication and grant them a contained database user.
- Backup and restore: You need to know that point-in-time restore (PITR) is available for any point within the retention period (7-35 days by default). They will ask what to do if a user accidentally drops a table — answer is to restore the database to a point just before the deletion using the portal or T-SQL.
- Geo-replication: They may ask how to ensure business continuity if an entire Azure region goes down — the answer is to configure active geo-replication or failover groups.
- Elastic pools: They love to ask about ‘DTU vs vCore’ pricing models. But since the new portal defaults to vCore, be prepared for both. DTU is simpler but less flexible; vCore allows more granular control.

Common traps include thinking that ‘fully managed’ means you don’t have to configure anything — you do have to configure firewall rules, authentication, and backup policies. Another trap is confusing Elastic Pool with a single database that can scale — an Elastic Pool is a container for multiple databases sharing resources, not a single database that auto-scales.

Memorise the key limitations: maximum database size in General Purpose is 4 TB (for vCore), 1 TB for DTU; Business Critical is also 4 TB; Hyperscale goes up to 100 TB. Know the default backup retention is 7 days but can be increased to 35 days. Also, you cannot change from a Single Database to an Elastic Pool without moving the database into the pool — they are different deployment models.

## Step by step

1. **Create a resource group** — A resource group is a logical container that holds all the Azure resources for your project. You name it (e.g., 'myapp-rg') and choose a location (Azure region). This helps with organisation, billing, and permissions.
2. **Provision the logical server** — You create the logical SQL Server that will host your database. You give it a globally unique name, choose an admin login and password, and decide if you want to use Microsoft Entra ID authentication. The server itself has no storage — it’s just a management endpoint.
3. **Select deployment option: single database or elastic pool** — Decide if you need one isolated database (single) or a shared resource pool for multiple databases (elastic pool). For a single app, pick single database. For a SaaS app with many tenants, pick elastic pool.
4. **Configure compute + storage (service tier and compute tier)** — Choose General Purpose, Business Critical, or Hyperscale based on performance and latency needs. Then pick Serverless (auto-pauses) or Provisioned (always on). Set the initial storage size (up to the tier’s maximum).
5. **Set networking and security** — Add one or more firewall rules to allow specific client IP addresses to connect. Optionally enable ‘Allow Azure services’ if other Azure services need access. You can also configure private endpoints (VNet integration) for extra security.
6. **Review and create** — Review all your settings, then click Create. Azure will deploy the logical server and the database, which typically takes a few minutes. Once complete, you get a connection string you can use in applications.

## Comparisons

### Single Database vs Elastic Pool

**Single Database:**
- Isolated resources — no sharing with other databases.
- Best for a single application with predictable load.
- Maximum size per database is 4 TB (General Purpose vCore).

**Elastic Pool:**
- Multiple databases share a common pool of resources.
- Best for SaaS apps with many tenants that have variable usage.
- Each database in the pool can grow to a max of 1 TB (but the pool total is limited).

### General Purpose Tier vs Business Critical Tier

**General Purpose Tier:**
- Uses remote storage (blob storage) for data files.
- Provides up to 99.99% availability SLA.
- Suitable for most production workloads with moderate latency tolerance.

**Business Critical Tier:**
- Uses local SSD storage attached to the compute node.
- Provides up to 99.995% availability SLA with multiple replicas.
- Suitable for mission-critical apps needing sub-millisecond latency.

### Serverless Compute vs Provisioned Compute

**Serverless Compute:**
- Auto-pauses after a period of inactivity (configurable, default 1 hour).
- Billed only for compute when active, plus storage always.
- Better for unpredictable or intermittent workloads.

**Provisioned Compute:**
- Compute runs continuously, even when idle.
- Billed per hour for the provisioned capacity.
- Better for consistent or high-volume workloads where cold-start latency is unacceptable.

### SQL Authentication vs Microsoft Entra ID Authentication

**SQL Authentication:**
- Uses a local username and password stored in the master database.
- No integration with corporate identity systems.
- Simple to set up but requires managing another set of credentials.

**Microsoft Entra ID Authentication:**
- Uses corporate identities synced from Microsoft Entra ID (Office 365).
- Supports multi-factor authentication and conditional access.
- Eliminates the need to store passwords in connection strings — uses token-based auth.

## Diagram

_Flowchart showing the step-by-step process to deploy an Azure SQL Database from the Azure portal._

```mermaid
graph LR
    A[Azure Portal] --> B[Create Resource Group]
    B --> C[Provision Logical Server]
    C --> D[Choose Single Database or Elastic Pool]
    D --> E[Select Service Tier: General Purpose / Business Critical / Hyperscale]
    E --> F[Set Compute Tier: Serverless or Provisioned]
    F --> G[Configure Firewall Rules]
    G --> H[Set Authentication: SQL or Microsoft Entra ID]
    H --> I[Review + Create]
    I --> J[Database is Ready with Connection String]
```

## Common misconceptions

- **Misconception:** Azure SQL Database is the same as SQL Server on an Azure Virtual Machine. **Reality:** Azure SQL Database is a PaaS (Platform as a Service) offering — Microsoft manages the OS, SQL engine, and infrastructure. SQL Server on an Azure VM is IaaS (Infrastructure as a Service) — you manage everything, including patching and backups. (Both run SQL Server, so beginners assume the management responsibilities are the same. The exam tests this distinction heavily.)
- **Misconception:** Once you deploy an Azure SQL Database, you don’t need to worry about security at all. **Reality:** Azure secures the underlying platform, but you must configure firewall rules, authentication (SQL or Microsoft Entra ID), data encryption, and user permissions. You are still responsible for securing the data layer. (The term 'fully managed' misleads people into thinking the provider handles all security, when in fact the shared responsibility model applies.)
- **Misconception:** You can directly connect to an Azure SQL Database using its local server name (like 'localhost'). **Reality:** You must connect using the fully qualified domain name (FQDN) of the logical server, e.g., 'myserver.database.windows.net'. It runs in Azure's network, not on your local machine. (Beginners are used to connecting to local SQL Server instances by name, so they assume the cloud works the same way.)
- **Misconception:** Serverless compute tier means you pay nothing at all when the database is paused. **Reality:** While you don’t pay for compute during auto-pause, you still pay for the storage that your data occupies. There is no 'free' tier. (The word 'serverless' suggests zero cost, but storage costs are separate and always apply.)
- **Misconception:** You can change a General Purpose database to Business Critical seamlessly without any downtime. **Reality:** Changing the service tier triggers an online operation that typically causes a brief failover (a few seconds of downtime) as the database moves to new hardware. It’s not instantaneous and zero-downtime. (Beginners assume scaling in the cloud is magic, but even Azure needs a moment to reconfigure resources.)

## Key takeaways

- Azure SQL Database is a fully managed PaaS service where Microsoft handles the OS, patching, backups, and hardware — you manage the data schema, security, and performance tuning.
- Every deployment starts with a logical server that acts as a container for one or more databases; the server name must be globally unique within Azure.
- Choose a Single Database for isolated workloads and an Elastic Pool when you have many small databases with fluctuating usage that can share resources.
- The three primary service tiers are General Purpose (cost-effective, remote storage), Business Critical (low latency, local SSD), and Hyperscale (up to 100 TB, separate compute and storage).
- By default, Azure SQL Database blocks all incoming connections — you must create at least one firewall rule to allow specific IP addresses to connect.
- To use corporate credentials for login, configure Microsoft Entra ID authentication and create contained database users mapped to Microsoft Entra identities.

## FAQ

**Can I use my local SQL Server Management Studio to connect to Azure SQL Database?**

Yes, you can use SSMS to connect. You just need to enter the fully qualified server name (e.g., myserver.database.windows.net) and your login credentials, and ensure your client IP is allowed by a firewall rule.

**What is the difference between Azure SQL Database and SQL Server on an Azure VM?**

Azure SQL Database is a managed PaaS service — Microsoft handles the OS, patching, and backups. SQL Server on a VM is IaaS — you manage everything, including the OS and SQL Server installation. PaaS is easier but gives you less control.

**How do I back up my Azure SQL Database?**

Azure SQL Database automatically performs full, differential, and transaction log backups. By default, backups are retained for 7 days, but you can configure long-term retention policies to keep them up to 10 years.

**What happens if I accidentally drop a table?**

You can perform a point-in-time restore to recover the database to a point just before the drop. This is done through the Azure portal or via T-SQL, and works for any point within the configured retention period.

**Do I need to patch the operating system or SQL Server for Azure SQL Database?**

No, Microsoft handles all patching and maintenance for the underlying platform. You are only responsible for your data and schema changes.

**Can I change the service tier after deployment?**

Yes, you can change the service tier (e.g., from General Purpose to Business Critical) at any time through the portal or CLI. The operation is online but may cause a brief failover (seconds of downtime).

**What is an elastic pool and when should I use it?**

An elastic pool is a collection of databases that share a set of resources (eDTUs or vCores). You should use it when you have many small databases with variable usage, as it helps reduce cost by pooling resources.

## Check your understanding

1. **When deploying an Azure SQL Database, what must you create first to act as the logical container for your databases?**

   Answer: You must first create a logical server, which provides the endpoint (FQDN) and master database for managing logins and firewall rules.

2. **A company has 50 small databases for different clients, each used only a few times per day. Which deployment option is most cost-effective?**

   Answer: An Elastic Pool, because it allows the databases to share a pool of resources, reducing cost compared to provisioning a single database for each client.

3. **What is the default backup retention period for Azure SQL Database?**

   Answer: The default retention period is 7 days, but you can configure it up to 35 days for point-in-time restore, and up to 10 years with long-term retention policies.

4. **How do you allow a specific office IP address to connect to an Azure SQL Database?**

   Answer: You create a server-level firewall rule in the Azure portal that specifies the start and end IP range for the office’s public IP address.

5. **You need to restore an Azure SQL Database to a point just before a user accidentally deleted a table 2 hours ago. What feature do you use?**

   Answer: You use the point-in-time restore feature, which can restore the database to any second within the configured retention period (default 7 days).

---

Interactive version with quiz and diagrams: https://courseiva.com/learn/dp-300/deploy-azure-sql-database