Deploying and Configuring SQL Server on Azure Virtual Machines. This concept is the foundation of the 'lift and shift' strategy that many businesses use to move their databases to the cloud without rewriting a single line of code. For the DP-300 exam, you need to understand exactly when to choose a virtual machine instead of a managed service, and how to configure it properly once it is running.
Jump to a section
A simple way to picture Deploying and Configuring SQL Server on Azure Virtual Machines
12 months of planning went into your cousin's wedding reception at the Grand Ballroom. You booked the venue (the physical space), not the caterer, not the band, and not the photographer. The venue gave you an empty hall with tables, chairs, a kitchen, and a dance floor. It was your job to bring in everything else: the food, the music, the decorations, and the staff to run it all. You had total control over which caterer you hired, what menu you served, and how late the band played. But you also had to handle every detail yourself — arranging the tables, cleaning up after the event, and paying the electrician when a fuse blew.
Deploying SQL Server on an Azure Virtual Machine is exactly like booking that ballroom. Azure provides the virtual machine (the empty venue) with a certain amount of RAM, CPU, and storage (the tables, chairs, and kitchen). You then bring in SQL Server (the caterer) by installing it on that VM. You control every setting — memory limits, security, backups — just like you control the playlist and the menu. But the trade-off is that you are also responsible for patching the operating system, applying SQL Server updates, managing disk space, and handling failures. You get full power and flexibility, but you also do all the work. A Platform as a Service (PaaS) option like Azure SQL Database would be the equivalent of an all-inclusive package where the venue provides the caterer, the music, and the cleanup crew — but you have less say over the menu and the playlist.
When you deploy SQL Server on an Azure Virtual Machine (VM), you are renting a computer in Microsoft's data centre and installing the SQL Server database software on it yourself. This is different from using a fully managed database like Azure SQL Database where Microsoft handles all the maintenance.
An Azure Virtual Machine is a piece of Infrastructure as a Service (IaaS). Think of IaaS as the most basic cloud offering: you get raw computing power — CPU, memory, storage — and you build everything else on top. You choose the size of the VM (how many virtual CPUs, how much RAM), the operating system (usually Windows Server or a Linux distribution), and the storage configuration (how many disks, how fast they are). Once your VM is running, you connect to it via Remote Desktop Protocol (RDP) for Windows or SSH for Linux, just as if you were sitting at a physical server in your own server room.
To turn this plain VM into a database server, you install SQL Server. Microsoft offers pre-configured images in the Azure Marketplace that already have SQL Server installed, so you can skip the manual installation. These images come in different editions: Developer (free for development and testing), Express (free, limited to 10 GB), Standard, and Enterprise. The edition you choose determines what features you get — for example, Enterprise edition supports advanced features like Online Index Rebuilds and Always On Availability Groups.
Why would you choose this approach? There are three main reasons:
Compatibility: Some applications use features of SQL Server that are not available in the managed services. For example, SQL Server Agent jobs that use xp_cmdshell or custom CLR assemblies. If your application needs these, you need a VM.
Control: On a VM, you have full administrative access to the operating system. You can install third-party monitoring tools, change firewall rules, tweak Windows Registry settings, or even run other applications on the same server.
Performance: You can configure the VM with very high levels of CPU, memory, and especially IOPS (Input/Output Operations Per Second) for the storage, which can be critical for heavy transactional workloads.
But this power comes with responsibility. You must manage:
Patching: You are responsible for applying Windows and SQL Server security updates.
Backups: While Azure can automate backups, you must configure backup policies and ensure they are working.
High Availability: If you want your database to stay up if the VM or the physical host fails, you must set up features like Always On Availability Groups or Failover Cluster Instances yourself.
Cost: You pay for the VM compute hours, storage, and your SQL Server licence (unless you use the included licence model where Azure bills you per minute of SQL Server usage).
The configuration process after deployment is critical. You need to configure the storage correctly — typically separating data files, log files, and TempDB onto different disks to avoid contention. You should also configure the network security group (NSG) to limit which IP addresses can connect to the SQL Server port (default 1433). And you need to enable SQL Server Authentication or use Microsoft Entra ID for logins.
Many beginners think that deploying a SQL Server VM is just clicking a button — and it is, initially — but the real work starts after the deployment. The DP-300 exam tests your knowledge of these post-deployment configuration choices, especially around storage, security, and high availability.
Choose the right VM image and size
Begin by selecting a pre-configured SQL Server image from the Azure Marketplace. Pick the edition (Developer, Express, Standard, Enterprise) based on your needs. Then choose a VM size with enough vCPUs and RAM for your workload — for example, the D-series or E-series offer balanced CPU-to-memory ratios. This step is critical because you cannot easily change the VM size later without a reboot.
Configure Disks and Storage Tiers
Attach multiple Premium SSD disks to the VM. Place the OS on one disk, database data files on a second, transaction log files on a third, and TempDB on a fourth. Choose Premium SSD for most workloads or Ultra Disk if you need extreme IOPS. This separation prevents disk contention and is a favourite topic on the DP-300 exam.
Set Network Security and Connectivity
Configure a Network Security Group (NSG) to allow inbound traffic only on port 1433 from your corporate IP range. Do not allow all internet traffic. Also, configure a private endpoint or a site-to-site VPN for secure connectivity from on-premises. Optionally, enable Azure Bastion for secure RDP access without exposing port 3389.
Install and Configure SQL Server (if not pre-installed)
If you chose a plain Windows VM, install SQL Server manually using the installation media. If you used a pre-configured image, connect to SQL Server Management Studio (SSMS) and verify the instance settings. Configure memory limits for SQL Server so it does not consume all system memory, and set the max degree of parallelism (MAXDOP) appropriately.
Enable Automated Backups and Patching
In the Azure portal, locate your SQL Server VM resource and enable Automated Backup to store backups in Azure Blob Storage. Also enable Automated Patching to install Windows and SQL Server updates during a maintenance window. Without these, your VM will not be backed up or patched automatically — a common beginner mistake.
Restore or Migrate Your Database
Take a full backup of your existing database and restore it onto the new VM. You can use Azure Data Migration Service (DMS) for a live migration with minimal downtime, or simply copy a .bak file to the VM and restore it using SSMS. Verify the database integrity after restore by running DBCC CHECKDB.
Test and Configure High Availability (optional)
If your application requires high availability, set up a Windows Server Failover Cluster and configure a Failover Cluster Instance or an Always On Availability Group. This involves adding additional VMs, configuring a load balancer, and setting up a distributed network name. Test the failover manually to ensure it works.
Imagine you work at a medium-sized company called 'Northwind Traders' that has been running its inventory management system on a physical SQL Server in a local server closet for the last ten years. The server is getting old, the hardware warranty is expiring, and the IT team is tired of replacing failed hard drives at 3 AM. The company decides to migrate to Azure.
The first decision the IT manager makes is to move the database 'as is' — no code changes, no schema redesign. This is called 'lift and shift'. The best way to do this is to deploy SQL Server on an Azure Virtual Machine.
Step by step, an IT professional would do this:
First, they log into the Azure portal and search for 'SQL Server on Azure VM'. They choose a pre-configured image, say SQL Server 2022 Standard Edition on Windows Server 2022. They select a VM size like Standard_D8s_v3 (8 vCPUs, 32 GB RAM) because their on-premises server had 8 cores and 32 GB of memory.
They configure the disks: a 127 GB OS disk, a 512 GB premium SSD for the database data files, a 256 GB premium SSD for the transaction logs, and a 256 GB premium SSD for TempDB. They note that Microsoft recommends placing TempDB on its own disk for performance.
They set the network security group to allow inbound connections on port 1433 only from the company's office IP address range, and they enable encrypted connections by installing a certificate.
Once the VM is running, they remote into it using RDP. They open SQL Server Management Studio (SSMS) and connect to the local instance. They verify that SQL Server Agent is running (it is used for scheduled jobs like backups).
They take a full backup of the on-premises database, copy the .bak file to a blob storage container, and then download it to the VM. They restore the database using SSMS. This process can take hours for large databases.
Finally, they configure automated backups by enabling the 'Azure Backup for SQL Server' feature in the portal, set up a maintenance plan to rebuild indexes weekly, and test the connection from the company's inventory application.
In practice, IT professionals also use Azure Migrate tools to assess compatibility, Azure Site Recovery for disaster recovery, and Azure Monitor to track performance metrics like disk latency and CPU usage. They automate the deployment using ARM templates or Terraform so they can reproduce the environment consistently. The key skill tested in DP-300 is knowing which configuration options are available and what each option does.
The DP-300 exam tests your knowledge of deploying and configuring SQL Server on Azure VMs in several specific ways. The exam does not ask you to actually do it — it asks you to choose the correct approach based on a scenario.
Here are the exact topics you must master:
Storage configuration: The exam loves to ask about placing data files, log files, and TempDB on separate disks. They will present a scenario with a slow database and ask you how to improve performance. The correct answer is almost always 'move the log files to a separate disk with higher IOPS' or 'move TempDB to its own disk'. They also test the difference between Standard HDD, Standard SSD, and Premium SSD storage tiers.
High availability options: You must know the difference between Always On Availability Groups (AG), Failover Cluster Instances (FCI), and a simple VM with Azure Site Recovery. A common trap: they describe a scenario requiring automatic failover within the same Azure region and ask which option supports it. The correct answer is FCI with a cluster in a single region, not AG which is more suited for multiple regions.
Licensing: They test the difference between 'Pay-as-you-go' (includes SQL Server licence cost per minute) and 'Azure Hybrid Benefit' (bring your own SQL Server licence to save money). A typical question: 'A company has existing SQL Server licences with Software Assurance. What should you configure?' Answer: Enable Azure Hybrid Benefit on the VM.
Security: Questions about network security groups, Azure Bastion for secure RDP access (instead of exposing port 3389 to the internet), and using Microsoft Entra ID for authentication. A trap: they ask if you should enable port 1433 to the internet. The correct answer is no — use a VPN, Azure Bastion, or private endpoint.
Automated patching and backups: They test the 'Automated Patching' and 'Automated Backup' settings available in the Azure portal for SQL Server VMs. They ask what happens if you don't configure these — answer: no automatic patching or backups happen, and the VM remains vulnerable.
Migration: They ask which tools to use when migrating an on-premises SQL Server to an Azure VM. The correct answers include Azure Data Migration Service (DMS) with minimal downtime, or a simple backup and restore. They warn against using transactional replication for large initial loads.
Traps to watch for:
They will offer 'SQL Managed Instance' as a distractor when the scenario requires SQL Agent with xp_cmdshell. SQL Managed Instance does not support xp_cmdshell, so the correct answer is 'SQL Server on Azure VM'.
They will describe a scenario needing very high IOPS (like 100,000+ IOPS) and offer a managed instance or single database. Only a VM with ultra disks can hit those numbers.
They will ask about the 'best' storage for log files — many beginners choose the largest disk, but the exam wants you to choose the disk with the highest IOPS (Premium SSD or Ultra Disk), regardless of total size.
Memorise these definitions:
IOPS: Input/Output Operations Per Second — a measure of how many read/write operations a disk can handle per second.
LRS: Locally Redundant Storage — three copies of your data within a single data centre.
GRS: Geo-Redundant Storage — three copies in one region plus three in another region.
Ultra Disk: The highest-performance disk option for Azure VMs, capable of over 300,000 IOPS.
Knowing these specific details is what gets you the pass.
Deploying SQL Server on an Azure VM is an IaaS model that gives you full control but requires you to manage patching, backups, and security yourself.
Separate Azure Premium SSD disks for data files, transaction log files, and TempDB to avoid I/O contention and maximise performance.
Always use Azure Hybrid Benefit to save up to 55% on SQL Server licensing costs if you already own licences with Software Assurance.
Never expose port 1433 to the internet — use Azure Bastion, a VPN, or a private endpoint for secure administrative access.
When migrating an on-premises database to an Azure VM, use Azure Data Migration Service (DMS) for minimal downtime, or backup and restore for simpler offline migrations.
For high availability within a single Azure region, use a Failover Cluster Instance (FCI) with Azure shared disks; for multi-region disaster recovery, use Always On Availability Groups.
These come up on the exam all the time. Here's how to tell them apart.
SQL Server on Azure VM (IaaS)
You manage OS, patches, and backups yourself
Supports all SQL Server features including xp_cmdshell and SQL Agent with OS access
Can achieve very high IOPS with Ultra Disk (300,000+)
Azure SQL Database (PaaS)
Microsoft manages OS, patching, and backups automatically
Does not support xp_cmdshell or OS-level customisations
Maximum IOPS is limited by service tier (up to 100,000 for Hyperscale)
Always On Availability Groups (AG)
Replicates data to a secondary replica in a different Azure region or availability zone
Requires a load balancer and distributed network name for client connectivity
Built-in read-only routing allows secondary replicas to serve read traffic
Failover Cluster Instance (FCI)
Uses shared storage (Azure shared disks or Storage Spaces Direct) so all nodes access the same data
Failover is at the SQL Server instance level, not the database level
Best for high availability within a single Azure region with low latency
Pay-as-you-go Licensing
You pay per minute for both the VM compute and the SQL Server licence
No upfront cost or long-term commitment required
More expensive if you run the VM 24/7 for months or years
Azure Hybrid Benefit Licensing
You use your existing SQL Server licence with Software Assurance, paying only for compute and storage
Can save up to 55% compared to pay-as-you-go
Requires you to own eligible licences and activate the benefit in the portal
Premium SSD (P30, P40, etc.)
Provides up to 20,000 IOPS per disk (depending on size)
Cost is lower per GB compared to Ultra Disk
Good for most general-purpose database workloads
Ultra Disk
Provides up to 300,000 IOPS per disk with sub-millisecond latency
Significantly more expensive per GB
Used for extreme performance scenarios like high-frequency trading or very large OLTP databases
Mistake
Once I deploy a SQL Server VM in Azure, all my problems with patching and backups are solved automatically.
Correct
Azure automates nothing for a SQL Server VM unless you explicitly configure Automated Patching and Automated Backup features in the portal. By default, the VM is unpatched and has no backup schedule.
This mistake comes from confusing IaaS (Infrastructure as a Service) with PaaS (Platform as a Service). With a VM, you manage everything. People assume 'the cloud' magically handles maintenance, but it does not.
Mistake
I can put my database data files and transaction log files on the same disk to save money — it works fine on my laptop.
Correct
On a production system, data and log files have different I/O patterns. Logs are write-heavy and sequential; data files are read/write and random. Sharing a disk causes contention and reduces performance dramatically.
Many beginners have only used SQL Server on a single-drive development laptop where performance does not matter. They do not realise that production workloads are IOPS-sensitive and require separate disks for each workload type.
Mistake
Azure VMs with SQL Server are always cheaper than on-premises servers because you only pay for what you use.
Correct
Azure VMs can be more expensive than on-premises servers if you leave them running 24/7 without using Azure Hybrid Benefit, or if you choose a large VM size for a light workload. Cost depends on licensing, usage hours, and disk tier.
People hear 'pay-as-you-go' and assume it is always cheaper. They forget that on-premises hardware is paid for once, while a VM charges every minute. Without proper cost management, the bill can be shocking.
Mistake
SQL Server on an Azure VM supports the same high-availability features as on-premises, like Always On Availability Groups, without any extra configuration.
Correct
Always On Availability Groups on Azure VMs require additional components: a Windows Server Failover Cluster (WSFC), a load balancer (Azure Load Balancer or Internal Load Balancer), and a distributed network name (DNN) or a virtual IP address. It is not automatic.
Microsoft documentation lists the supported features, but beginners miss the 'prerequisites' section. They assume cloud = simpler, but high availability in Azure VMs often adds more complexity than on-premises.
Mistake
The default port 1433 is fine to leave open to the internet because Azure Firewall protects my VM.
Correct
Leaving port 1433 open to the internet (0.0.0.0/0) is a major security risk. Azure Network Security Groups (NSGs) act as a firewall, but if you misconfigure the rule, any attacker can attempt to brute-force your SQL Server login. Always restrict access to specific IP ranges or use Azure Bastion.
Beginners often think a cloud firewall is 'magical' and automatically blocks bad traffic. They do not realise that NSGs are just rules they must define correctly, and a misrule can expose the server to the entire internet.
Reveal each answer, then mark whether you got it right. Score 60%+ to unlock the next chapter.
SQL Server on an Azure VM is IaaS: you manage everything. Azure SQL Database is PaaS: Microsoft manages the OS, patching, and backups. Choose a VM when you need full control or compatibility with older features; choose Azure SQL Database when you want less management overhead.
You can enable Automated Backup in the Azure portal for your SQL Server VM, which backs up to Azure Blob Storage automatically. Alternatively, you can configure SQL Server Agent to run a backup job manually to a local disk or to URL storage.
Yes. If you have a SQL Server licence with Software Assurance, enable the Azure Hybrid Benefit on your VM when you deploy it. This reduces your cost because you are not paying the per-minute SQL Server licence fee, only the base compute and storage costs.
The most common causes are: placing all database files on a single disk (causing I/O contention), insufficient IOPS on the disk tier, or not enough memory allocated to SQL Server. Check that you have separate disks for data, logs, and TempDB, and that you are using Premium SSD or Ultra Disk.
Yes. Since you manage the operating system, you are responsible for security. Install an antivirus solution and configure the Windows Firewall. Azure does not automatically protect the OS of your VM from malware — that is your job.
Avoid opening RDP (port 3389) or SQL Server port (1433) to the public internet. Instead, use Azure Bastion for RDP access, and configure a VPN or private endpoint for SQL Server connections. This ensures traffic stays within the Azure network and is encrypted.
Yes, SQL Server Configuration Manager is a Windows tool used to manage SQL Server services, network protocols, and client aliases. On an Azure VM, you use it to enable TCP/IP (which is disabled by default) and set the port that SQL Server listens on.
You've finished Deploying and Configuring SQL Server on Azure Virtual Machines. Continue through the DP-300 study guide to build a complete picture of the exam.
Done with this chapter?