SQL Server on Azure Virtual Machines (Azure VMs) – Complete Guide for DP-900
Introduction
SQL Server on Azure Virtual Machines (VMs) is one of the core deployment options for relational data on Azure. It is categorized as an Infrastructure-as-a-Service (IaaS) offering, giving you full control over the SQL Server instance and the underlying operating system. Understanding this topic is essential for the Microsoft DP-900: Azure Data Fundamentals exam, as questions frequently test your ability to distinguish between IaaS and PaaS relational database options on Azure.
Why Is SQL Server on Azure VMs Important?
SQL Server on Azure VMs is important for several reasons:
• Lift-and-Shift Migration: Organizations that run SQL Server on-premises can migrate their workloads to the cloud with minimal changes. This is often called a "lift-and-shift" strategy, where the existing SQL Server configuration, databases, and applications are moved to an Azure VM with little or no code modification.
• Full Control: Unlike PaaS options such as Azure SQL Database, SQL Server on Azure VMs gives you complete administrative control over the operating system, the SQL Server instance, patching schedules, and configuration. This is critical for applications that require specific OS-level configurations, custom software installations, or access to features not available in PaaS offerings.
• Feature Parity: SQL Server on Azure VMs provides 100% compatibility with on-premises SQL Server. Every feature of the SQL Server engine is available, including SQL Server Agent, cross-database queries, distributed transactions, SSIS, SSRS, SSAS, and more.
• Hybrid Scenarios: It supports hybrid cloud architectures where some workloads remain on-premises while others run in Azure.
What Is SQL Server on Azure VMs?
SQL Server on Azure VMs is a cloud deployment model where a full installation of SQL Server runs inside an Azure Virtual Machine. Microsoft provides pre-configured VM images in the Azure Marketplace with SQL Server already installed on Windows Server or Linux. You can also bring your own SQL Server license using the Azure Hybrid Benefit.
Key characteristics include:
• Deployment Model: Infrastructure-as-a-Service (IaaS)
• Operating System Access: Full access – you can RDP (Windows) or SSH (Linux) into the VM
• SQL Server Version: Supports SQL Server 2012, 2014, 2016, 2017, 2019, 2022 and others
• Licensing: Pay-as-you-go (SQL Server license included in VM cost) or Bring Your Own License (BYOL) via Azure Hybrid Benefit
• Management Responsibility: You are responsible for OS patching, SQL Server patching, backups, high availability configuration, and security
• Storage: You configure and manage Azure managed disks for data, log, and tempdb files
How Does SQL Server on Azure VMs Work?
1. Provisioning: You create an Azure VM from the Azure portal, CLI, or PowerShell. You can select a marketplace image that includes SQL Server pre-installed, or install SQL Server manually on a blank VM.
2. Configuration: After provisioning, you configure the VM size (CPU, RAM), storage (Premium SSDs are recommended for production workloads), networking (virtual network, network security groups, public/private IP), and SQL Server settings.
3. SQL IaaS Agent Extension: Microsoft provides the SQL Server IaaS Agent Extension, which enables features like automated patching, automated backups, Azure portal integration for SQL management, and licensing flexibility. This extension bridges some management gaps between IaaS and PaaS.
4. High Availability: You are responsible for configuring high availability. Options include:
- Always On Availability Groups (recommended)
- Failover Cluster Instances (FCI)
- Log Shipping
- Azure Site Recovery for disaster recovery
5. Backups: You can configure backups manually, use the automated backup feature through the SQL IaaS extension, or use Azure Backup for SQL Server in Azure VMs.
6. Security: You manage firewall rules, SQL authentication, encryption (TDE, Always Encrypted), and OS-level security. Azure also provides features like Microsoft Defender for SQL for threat detection.
7. Networking: The VM runs inside an Azure Virtual Network (VNet). You control inbound/outbound traffic using Network Security Groups (NSGs) and can connect to on-premises networks using VPN Gateway or ExpressRoute.
SQL Server on Azure VMs vs. Other Azure SQL Options
Understanding the differences is critical for the DP-900 exam:
• SQL Server on Azure VMs (IaaS): Full OS and SQL Server control. You manage everything. Best for lift-and-shift, apps needing OS access, or features not in PaaS.
• Azure SQL Database (PaaS): Fully managed single database or elastic pool. No OS access. Microsoft manages patching, backups, and HA. Best for modern cloud applications.
• Azure SQL Managed Instance (PaaS): Near 100% compatibility with on-premises SQL Server but fully managed. Supports features like SQL Agent, cross-database queries, and CLR. Best for migrations that need instance-scoped features without managing infrastructure.
When to Choose SQL Server on Azure VMs
Choose SQL Server on Azure VMs when:
• You need full control over the OS and SQL Server instance
• Your application requires OS-level access or third-party software installed alongside SQL Server
• You need specific SQL Server features not available in PaaS (e.g., SSIS, SSRS, SSAS on the same machine)
• You are performing a quick lift-and-shift migration with minimal changes
• You need a specific SQL Server version or edition that is not supported in PaaS
• You require full compatibility with on-premises SQL Server
• Your workload requires Windows or Linux OS customization
When NOT to Choose SQL Server on Azure VMs
Do not choose SQL Server on Azure VMs when:
• You want to minimize administrative overhead (choose Azure SQL Database or Managed Instance instead)
• You want automatic high availability without manual configuration
• You want built-in automatic scaling (PaaS options handle this more seamlessly)
• You want Microsoft to handle all patching and maintenance
Key Concepts for the DP-900 Exam
• SQL Server on Azure VMs = IaaS
• Azure SQL Database = PaaS
• Azure SQL Managed Instance = PaaS
• IaaS means you manage OS patching, SQL patching, backups, HA, and security
• PaaS means Microsoft manages most infrastructure concerns
• SQL Server on Azure VMs offers 100% feature compatibility with on-premises SQL Server
• Azure Hybrid Benefit lets you use existing SQL Server licenses on Azure VMs to save costs
Exam Tips: Answering Questions on SQL Server on Azure Virtual Machines
1. Identify the Deployment Model: If a question mentions full OS access, RDP/SSH into the server, or managing the operating system, the answer is almost always SQL Server on Azure VMs (IaaS).
2. Lift-and-Shift Keywords: If a scenario describes migrating an existing on-premises SQL Server to Azure with minimal changes or as-is, SQL Server on Azure VMs is the best answer.
3. Responsibility Questions: Remember that with IaaS, you are responsible for OS updates, SQL Server patches, backup configuration, and high availability setup. If the question asks who is responsible for patching in an Azure VM scenario, the answer is the customer.
4. Feature Requirements: If the question mentions needing SSIS, SSRS, or SSAS running on the same server, or cross-instance features, SQL Server on Azure VMs is likely the answer.
5. Do Not Confuse with Managed Instance: Azure SQL Managed Instance also provides high compatibility with on-premises SQL Server, but it is PaaS. If the question emphasizes no OS management but near-full compatibility, the answer is Managed Instance. If it emphasizes full OS control, the answer is Azure VM.
6. Cost and Licensing: Know that Azure Hybrid Benefit applies to SQL Server on Azure VMs. If a question asks about reducing costs by reusing existing SQL Server licenses, Azure Hybrid Benefit on VMs is the answer.
7. Look for "IaaS" in Answer Choices: The exam may use the terms IaaS, PaaS, and SaaS. SQL Server on Azure VMs = IaaS. Azure SQL Database and Managed Instance = PaaS.
8. High Availability: If a question asks about configuring Always On Availability Groups manually, this is an IaaS responsibility and points to SQL Server on Azure VMs.
9. Elimination Strategy: If a scenario requires something that PaaS cannot provide (e.g., installing custom software on the server, accessing the C:\ drive, running non-SQL services), eliminate PaaS options and select SQL Server on Azure VMs.
10. Remember the Shared Responsibility Model: In IaaS, Microsoft manages the physical infrastructure (data center, network, hardware). You manage the OS, SQL Server, data, and applications. This is a common exam topic.
Summary
SQL Server on Azure Virtual Machines provides a powerful IaaS option for running SQL Server workloads in the cloud with complete control and full feature parity with on-premises SQL Server. For the DP-900 exam, focus on understanding that it is an IaaS solution, when to use it versus PaaS alternatives, the shared responsibility model, and the key scenarios (lift-and-shift, OS access, full feature compatibility) that make it the right choice. Mastering these distinctions will help you confidently answer exam questions on this topic.
