# SQL Server on Azure VM

> Source: Courseiva IT Certification Glossary — https://courseiva.com/glossary/sql-server-on-azure-vm

## Quick definition

SQL Server on Azure VM means you take a virtual machine (a computer running in the cloud) and install Microsoft SQL Server on it, just like you would on a physical server in your office. This lets you use familiar SQL Server tools and features without buying or maintaining physical hardware. Azure takes care of the server, networking, and storage, while you manage the SQL Server software and its settings.

## Simple meaning

Imagine you own a small bakery and need a special oven to bake your signature cakes. Buying that oven costs a lot of money, takes up space, and requires maintenance. One day, a friend offers you a deal: they have a huge kitchen with many ovens, and you can rent one of their ovens for as long as you need. You can set the temperature, use your own recipes, and bake whenever you want. When you are done, you stop paying and walk away. That is essentially what SQL Server on Azure VM does for your database needs.

Instead of buying and maintaining your own physical server computer to run SQL Server, you rent a virtual machine from Microsoft Azure. This virtual machine acts like a real computer with its own operating system (usually Windows Server), memory, and storage. You then install SQL Server on that virtual machine, just as you would on a server in your data center. You have full control to configure SQL Server settings, install updates, manage security, and run your databases.

The key advantage is flexibility and cost savings. You do not have to pay for hardware upfront, worry about hardware failures, or plan for future capacity. You can choose the size of the virtual machine based on your current needs, and if your business grows, you can upgrade to a larger VM with more CPU cores, memory, or faster disks. Azure also offers pre-configured images from the Azure Marketplace that already have SQL Server installed, so you can start using it within minutes.

However, with great control comes great responsibility. Unlike fully managed database services like Azure SQL Database, where Azure handles backups, patching, and high availability automatically, with SQL Server on Azure VM, you are responsible for managing the SQL Server software. You need to apply security patches, configure backups, set up high availability, and monitor performance. Azure provides tools like Automated Patching and Automated Backup to help, but you still control the schedule and settings.

Think of it this way: if you choose SQL Server on Azure VM, you are like a chef who brings their own recipes and cooks in a rented kitchen. You have total freedom, but you must clean up, maintain your tools, and ensure everything runs smoothly. The alternative, like Azure SQL Database, is like eating at a restaurant where the chef prepares everything for you, but you have less say in the menu and ingredients.

## Technical definition

SQL Server on Azure VM is an Infrastructure as a Service (IaaS) offering from Microsoft Azure that allows you to run a full instance of Microsoft SQL Server on a virtual machine hosted in Azure's cloud infrastructure. The service is built on top of Azure Virtual Machines, which use Hyper-V virtualization technology to provide isolated, scalable compute resources. Each VM runs on a physical host server managed by Azure, with the hypervisor ensuring resource isolation between tenants.

The architecture consists of several layers. At the base is the Azure physical infrastructure, including data centers, networking, and storage systems. On top of that runs the Azure hypervisor, which manages virtual machines. Each VM has its own operating system, typically a supported Windows Server edition (such as Windows Server 2016, 2019, 2022, or newer). The SQL Server instance is installed on top of this OS, either manually or via a pre-configured Azure Marketplace image that includes SQL Server licensing.

Networking in an Azure VM uses Virtual Networks (VNets) and subnets. The VM is assigned a private IP address within the VNet, and optionally a public IP address for internet access. Network Security Groups (NSGs) control inbound and outbound traffic using rules that filter by source IP, destination IP, port, and protocol. For SQL Server, port 1433 (default) must be opened in the NSG and in the Windows Firewall to allow client connections. You can also use Azure Private Link for more secure connections without exposing a public endpoint.

Storage is a critical component. Azure VMs use managed disks, which are block-level storage volumes managed by Azure. You can choose from Ultra Disk, Premium SSD, Standard SSD, or Standard HDD based on performance and cost requirements. For SQL Server workloads, Premium SSD or Ultra Disk are recommended because they provide low latency and high IOPS (input/output operations per second). The tempdb database, which is used for temporary objects and sorting operations, should be placed on a separate disk with high throughput. Data files and log files should also be separated onto different disks to avoid contention.

High availability and disaster recovery options include Always On Availability Groups, which can be configured within a single Azure region across multiple VMs, or across regions using Azure Site Recovery. You can also use SQL Server Failover Cluster Instances (FCIs) with Azure shared disks, but this requires additional configuration. Azure also offers automated backup to blob storage using Managed Backup, and automated patching can be enabled to apply SQL Server and Windows updates during a maintenance window.

Licensing is an important consideration. SQL Server on Azure VM is billed based on the VM size, storage, and SQL Server licensing. You can either use pay-as-you-go licensing, where the SQL Server license is included in the VM hourly rate, or use Azure Hybrid Benefit, which allows you to bring your own SQL Server license with Software Assurance to reduce costs. The Azure Hybrid Benefit can save up to 55% on SQL Server licensing costs.

Performance optimization involves selecting the right VM series. The M-series, E-series, and D-series VMs are commonly used for SQL Server workloads because they offer high memory-to-core ratios and support for premium storage. You should also consider using accelerated networking for lower latency, and enable Write Accelerator for disks hosting transaction log files. Monitoring is done through Azure Monitor, SQL Server DMVs (Dynamic Management Views), and tools like Azure SQL Insights.

Security measures include encryption at rest using Azure Storage Service Encryption (SSE) or SQL Server Transparent Data Encryption (TDE), encryption in transit using TLS, and SQL Server authentication or Azure Active Directory integration. Azure Defender for SQL provides advanced threat detection and vulnerability assessments for SQL Server VMs.

SQL Server on Azure VM delivers the full SQL Server engine with all its features, including SQL Server Integration Services (SSIS), Reporting Services (SSRS), and Analysis Services (SSAS). It is ideal for workloads that need full administrative control, existing applications that require specific SQL Server configurations, or when you need to migrate on-premises SQL Server databases with minimal changes.

## Real-life example

Think of moving your home into a luxury apartment building. Before, you lived in a house that you owned. You were responsible for everything: fixing the roof, painting the walls, mowing the lawn, repairing the plumbing, and updating the electrical wiring. When something broke, you had to call a contractor, buy the parts, and often do some of the work yourself. It was time-consuming and expensive.

Now you decide to move into a high-end apartment building. You rent a unit on the 10th floor. The building owner takes care of the roof, the exterior, the elevators, the hallways, and the security. If a pipe bursts in the wall, you call the building manager, and they send someone to fix it. The building also provides reliable electricity and internet connections directly to each unit. You do not have to worry about the building's foundation or the main water line.

However, inside your apartment, you are in charge. You decide where to put your furniture, what color to paint the walls (with permission), which appliances to buy, and how to arrange your living space. You are responsible for cleaning your apartment, changing light bulbs, and maintaining your own belongings. If you want a bigger apartment because your family grows, you can move to a larger unit in the same building without having to build an addition onto a house.

In this analogy, the apartment building is Microsoft Azure's cloud infrastructure. The building management handles the structural aspects: physical security, power, cooling, networking, and hardware maintenance. Your apartment unit is the virtual machine. You choose its size (number of rooms/cores), its location (floor/region), and what goes inside it. Installing SQL Server is like bringing in your own furniture and appliances. You decide exactly which version of SQL Server, which features to enable, and how to configure the database settings.

The building's security system and common areas are like Azure's built-in network security, firewalls, and load balancers. The building manager offering optional services like package delivery or trash pickup is similar to Azure's optional automated backups, patching, and monitoring. You can take advantage of these services to make your life easier, but you can also choose to handle those tasks yourself.

If you had chosen a fully managed database service like Azure SQL Database, that would be like moving into a fully furnished hotel suite with room service. The hotel takes care of everything: cleaning, cooking, laundry, and maintenance. You just use the space and pay the bill. But you cannot rearrange the furniture or change the wall color. For many people, that trade-off is worth it. But for others, especially those with specific needs or existing SQL Server configurations, the apartment rental model of SQL Server on Azure VM is the perfect balance of control and convenience.

## Why it matters

SQL Server on Azure VM matters because it provides a bridge between traditional on-premises database management and modern cloud services. Many organizations have years of investment in SQL Server applications, stored procedures, integrations, and custom configurations. Migrating these to a fully managed cloud database often requires significant rework, testing, and application changes. SQL Server on Azure VM allows you to lift and shift your existing SQL Server workloads to the cloud with minimal modifications, preserving your investment while gaining cloud benefits like elastic scaling, pay-as-you-go pricing, and reduced hardware management.

For IT professionals, this means you can continue using the tools and skills you already have, such as SQL Server Management Studio (SSMS), T-SQL, and PowerShell. You do not need to learn a completely new database platform or rewrite your applications. This reduces migration risk and speeds up time-to-cloud. You also gain the ability to quickly provision new environments for development, testing, and disaster recovery without waiting weeks for hardware procurement.

From a cost perspective, SQL Server on Azure VM can be more cost-effective than maintaining physical servers, especially when you consider electricity, cooling, physical space, and IT staff time for hardware maintenance. The Azure Hybrid Benefit allows you to reuse existing SQL Server licenses, further reducing costs. You can also scale up or down based on demand, avoiding over-provisioning.

However, it also means you retain operational responsibility. You must manage backups, apply patches, monitor performance, and handle failover scenarios. This is a double-edged sword: you have control, but you must be diligent. Understanding this service is essential for roles like database administrators (DBAs), solution architects, and cloud engineers, especially those working toward Azure certifications like AZ-104 and Azure Fundamentals, as well as other cloud certifications that test IaaS concepts.

## Why it matters in exams

The term SQL Server on Azure VM appears in several cloud certification exams, though its relevance varies by exam. For Microsoft Azure exams, it is a core topic. The AZ-104 (Microsoft Azure Administrator) exam covers Azure Virtual Machines extensively, including deploying SQL Server on VMs, configuring network security groups for database access, managing availability sets and zones, and implementing backup strategies. You might be asked about choosing between IaaS (SQL on VMs) and PaaS (Azure SQL Database) for a given scenario. The Azure Fundamentals (AZ-900) exam also touches on this concept at a high level, focusing on the difference between IaaS and PaaS, and the trade-offs of control versus management.

The DP-900 (Microsoft Azure Data Fundamentals) exam includes SQL Server on Azure VM as one of the data workload options. You should understand that it falls under IaaS, that you have full control over the SQL Server instance, and that it is best for lift-and-shift migrations. Questions may ask you to recommend the appropriate Azure data service based on requirements like administrative control, compatibility, or need to run third-party software alongside SQL Server.

For AWS exams like AWS Cloud Practitioner, Developer Associate, and Solutions Architect Associate, SQL Server on Azure VM is not directly tested, but understanding IaaS concepts is valuable cross-platform knowledge. AWS has its own equivalent service (Amazon RDS for SQL Server and EC2 with SQL Server). The exam may test your understanding of when to use IaaS versus PaaS, which is a universal cloud concept. So while you won't see Azure-specific services on AWS exams, the underlying principles of virtualization, scalability, and responsibility sharing are directly transferable.

Google Cloud exams (Google ACE, Google Cloud Digital Leader) similarly do not focus on Azure services, but they test similar IaaS concepts using Google Compute Engine. Knowing the SQL Server on Azure VM model helps you understand the general pattern of running database software on virtual machines in any cloud.

In exam questions, you might be presented with a scenario: a company wants to migrate their on-premises SQL Server database to Azure with minimal changes, requires full control over SQL Server configuration, and needs to use SQL Server Agent jobs. The correct answer would be SQL Server on Azure VM. Alternatively, if the scenario emphasizes built-in high availability, automatic backups, and reduced administrative overhead, the correct choice might be Azure SQL Database or Azure SQL Managed Instance.

Be prepared for questions that ask about licensing options (Azure Hybrid Benefit), high availability configurations (Always On Availability Groups versus failover cluster instances), and storage recommendations (Premium SSD for production workloads). Understanding these details will help you differentiate between SQL Server on Azure VM and other Azure data services.

## How it appears in exam questions

Exam questions about SQL Server on Azure VM typically fall into several categories. The most common type is a scenario-based question where you must recommend the best Azure data service for a given business requirement. For example: A company has an on-premises SQL Server 2019 database with custom CLR assemblies and SQL Server Agent jobs. They want to migrate to Azure with minimal changes. Which Azure service should they use? The correct answer is SQL Server on Azure VM because it provides full control over the SQL Server instance, supports custom assemblies and SQL Agent jobs without limitation, and allows a lift-and-shift migration.

Another question pattern involves comparing IaaS and PaaS. You might be asked: Which Azure service requires the most administrative effort for patching the operating system and SQL Server? The answer is SQL Server on Azure VM because you are responsible for both OS and SQL Server patching, whereas Azure SQL Database handles patching automatically. You might also see questions about licensing: A company has existing SQL Server Enterprise Edition licenses with Software Assurance. Which option can reduce their costs when migrating to Azure? The answer is Azure Hybrid Benefit applied to SQL Server on Azure VM.

Configuration questions are also common. For instance: You need to ensure that clients can connect to a SQL Server on Azure VM over the internet. What two things must be configured? You need to open port 1433 (or your custom port) in the Network Security Group (NSG) and also create a firewall rule in Windows Firewall on the VM. Another configuration question: How can you improve disk performance for a SQL Server on Azure VM that handles heavy transactional workloads? The correct approach is to use Premium SSD or Ultra Disk managed disks and separate data, log, and tempdb files onto different disks.

High availability questions often appear. For example: You need to provide high availability for a SQL Server database on Azure VM with automatic failover. Which feature should you implement? The answer is SQL Server Always On Availability Groups, which can be configured across multiple Azure VMs in an availability set or availability zone. You might also be asked about disaster recovery options: To replicate a SQL Server database to a different Azure region for disaster recovery, you could use Azure Site Recovery or SQL Server log shipping.

Performance tuning questions are less common but possible. For instance: You notice slow query performance on a SQL Server on Azure VM. What should you check first? Possible answers include checking the VM size (is it undersized?), disk latency (use Storage Spaces Direct or premium disks), and query execution plans. Monitoring and diagnostics questions might ask about Azure Monitor, SQL Server DMVs, or query store.

Finally, migration questions appear: Which tool can you use to assess and migrate on-premises SQL Server databases to SQL Server on Azure VM? The answer is the Azure Database Migration Service (DMS), which supports both online and offline migrations with minimal downtime.

## Example scenario

Contoso Ltd is a medium-sized retail company that has been running its inventory management system on SQL Server 2016 on a physical server in their main office for the past five years. The server is now out of warranty, and the hard drives are showing signs of failure. The company's IT manager, Sarah, wants to move the system to the cloud to avoid purchasing new hardware and to improve reliability.

The inventory application is critical for daily operations. It uses SQL Server stored procedures, linked servers to connect to a legacy accounting system, and SQL Server Agent jobs that run nightly to update inventory levels. The application also uses a custom CLR assembly for a specialized pricing calculation. Sarah needs the new solution to support all these features with minimal changes to the application code.

After evaluating Azure options, Sarah decides to use SQL Server on Azure VM. She provisions a Standard_D8s_v3 virtual machine with Windows Server 2022, 8 vCPUs, and 32 GB of RAM. She selects a pre-configured Azure Marketplace image that includes SQL Server 2016 Enterprise. She attaches two Premium SSD managed disks: one for data files (500 GB) and one for transaction logs (250 GB). She configures a third disk for tempdb on a separate volume to improve performance.

Sarah configures the VM's network security group to allow inbound traffic on port 1433 only from the company's on-premises IP range and the head office VPN. She also enables Azure Backup for automated daily backups of the SQL Server databases to Azure Blob Storage. She then uses the Azure Database Migration Service (DMS) to migrate the inventory database from the on-premises server to the VM with minimal downtime.

After migration, Sarah tests the application thoroughly. The custom CLR assembly works without modification, the SQL Server Agent jobs run on schedule, and the linked server connection to the accounting system is reconfigured using a VPN connection. The company now has a more reliable database solution with the ability to scale up the VM if needed, and they no longer worry about hardware failures.

## SQL Server on Azure VM: Overview, Architecture, and Key Use Cases

SQL Server on Azure Virtual Machines (also known as SQL Server on Azure IaaS) is a foundational Azure data service that allows you to run a fully installed and licensed SQL Server instance inside a Windows or Linux virtual machine in the Azure cloud. This is part of the Azure Infrastructure-as-a-Service (IaaS) offering, meaning you have full administrative control over the operating system, SQL Server configuration, and all settings. Unlike Platform-as-a-Service (PaaS) options such as Azure SQL Database or Azure SQL Managed Instance, SQL Server on Azure VM does not abstract away the underlying OS or SQL Server binaries. This gives you the flexibility to use any SQL Server version (2008, 2012, 2014, 2016, 2017, 2019, 2022) and any edition (Express, Web, Standard, Enterprise) with full compatibility.

A primary use case for SQL Server on Azure VM is lift-and-shift migration. Organizations that already run on-premises SQL Server workloads often choose this service to rapidly move to the cloud without rewriting applications or refactoring databases. You can migrate using backup/restore, Data Migration Assistant (DMA), Azure Database Migration Service, or even detach/attach databases. This path is highly popular for legacy applications that require proprietary SQL Server features like SQL Server Integration Services (SSIS), SQL Server Reporting Services (SSRS), SQL Server Analysis Services (SSAS), or distributed transactions (DTC). Because you have full OS access, you can also install custom agents, third-party monitoring tools, and maintain exact security configurations.

Another strong use case is for hybrid scenarios and disaster recovery. You can configure an Always On availability group between an on-premises SQL Server and a replica running in an Azure VM. Azure Site Recovery can also be used for DR. SQL Server on Azure VM integrates tightly with Azure services like Azure Backup (for automated backups to Azure Recovery Services vault), Azure Site Recovery, Azure Monitor, and Log Analytics. For exam-level understanding, remember that SQL Server on Azure VM is ideal when you need full control, compatibility assurance, and the ability to run any version of SQL Server alongside custom applications. It is also a cost-effective way to use existing SQL Server licenses with Software Assurance via Azure Hybrid Benefit, allowing you to pay only for the compute and storage resources of the VM.

Key architectural aspects include the use of premium SSD or ultra disks for high performance, automatic patching and automated backup features (optional), and integration with Azure Key Vault for Transparent Data Encryption (TDE). The VM size choice is critical: memory-optimized series (E-series, M-series) are preferred for large SQL workloads. The storage architecture can include tempdb on a separate disk, data and log file separation, and read caching. For high availability, you can deploy SQL Server in a Windows Server Failover Cluster (WSFC) with shared disks using Azure Shared Disks or Stretch Clusters. Always On availability groups (AGs) are also supported but require a load balancer or a distributed network name (DNN) for listener connectivity.

Exam relevance: For AZ-104 (Azure Administrator), expect questions about configuring automated backups, patching schedules, and choosing VM sizes for SQL workloads. For DP-900, you should contrast IaaS vs PaaS. For Azure Fundamentals, know that SQL Server on Azure VM is a dedicated, customizable option. For AWS exams, note that this is the equivalent of Amazon RDS for SQL Server (which is PaaS) or SQL Server on EC2 (IaaS). Understanding the differences between IaaS and PaaS for SQL Server is a common exam trap, SQL Server on Azure VM gives you full OS control but requires you to manage backups, patches, and high availability manually or with optional automation. Google Cloud exams compare this to Cloud SQL (PaaS) or SQL Server on Compute Engine (IaaS). Master the concept that SQL Server on Azure VM is the correct choice when you need to run custom SQL Server features like SSIS or require specific collation settings that cannot be changed in PaaS offerings.

## Cost Optimization Strategies for SQL Server on Azure VM

Cost management for SQL Server on Azure VM is a critical skill for both Azure administrators and architects, and it appears frequently in exams like AZ-104, AZ-900, and DP-900. The cost of a SQL Server VM is composed of three main components: compute (the virtual machine itself), storage (disks and IOPS), and licensing (SQL Server license). Understanding each element's pricing model and optimization techniques can drastically reduce total ownership costs.

First, the compute cost is driven by the VM size and the operating system. Azure offers various VM series, but for SQL Server, memory-optimized VMs like Edsv4, Eadsv5, M-series, and Mv2-series are recommended because SQL Server performance is heavily tied to memory. You can reduce compute costs by selecting reserved instances (1-year or 3-year commitments) instead of pay-as-you-go. Reserved instances can provide up to 72% savings compared to on-demand pricing for consistent workloads. Azure Hybrid Benefit is available for SQL Server and Windows Server. If you have existing SQL Server licenses with Software Assurance, you can bring your own license (BYOL) to Azure and pay only for the base VM compute cost (without the SQL Server premium). This is often the single biggest cost-saving measure. For Azure exams, remember that Azure Hybrid Benefit applies per vCPU, you need to have at least 4 core licenses for each VM vCPU (in Standard edition) or apply per core for Enterprise.

Second, storage cost optimization involves selecting the right disk types and sizes. Premium SSD v2 is often more cost-effective than Premium SSD for high-throughput workloads, and Ultra Disk offers sub-millisecond latency but at a premium. You should configure separate disks for data, logs, and tempdb to avoid contention and align with best practices. Using Azure Disk Storage reserved capacity can also reduce costs. For development and test environments, you can use Standard HDD or Standard SSD but expect lower IOPS. Another cost-saving technique is to use Azure Spot VMs for non-production SQL workloads (like staging or testing) that can tolerate interruptions, but never for production because Spot VMs can be evicted with short notice. Consider using Azure Disk Encryption or server-side encryption at no extra cost (using platform-managed keys).

Third, licensing cost is an area where exam questions often test your knowledge. SQL Server on Azure VM can be provisioned with a license included (pay-as-you-go for SQL Server license) or with Azure Hybrid Benefit (BYOL). The license-included option adds a per-hour surcharge that varies by edition (Standard vs Enterprise) and number of vCPUs. Azure Hybrid Benefit requires you to have Software Assurance or qualifying subscription licenses. Important: You cannot use Azure Hybrid Benefit for both SQL Server and Windows Server on the same VM unless you have separate licenses. Also, Azure Hybrid Benefit is available for both Standard and Enterprise editions, and you can apply it to multiple VMs as long as you have enough licenses. For exam scenarios, if a company has existing SQL Server licenses with Software Assurance, they should always use Azure Hybrid Benefit to save money. Another cost factor is that SQL Server on Azure VM is licensed per core (not per user), and you must license all physical cores (or vCPUs) in the VM. There is no cost benefit from using hyper-threading, licensing is based on the number of vCPUs presented to the OS. So a 4 vCPU VM requires at least 4 core licenses (if Standard edition) or 4 core licenses (if Enterprise edition, which covers up to 4 vCPUs per license package, but you still need to license all vCPUs).

Finally, additional cost optimization includes using auto-shutdown for development VMs, selecting low-cost regions for DR replicas, and managing backup costs. Automated backups to Azure Recovery Services vault incur storage costs, you can adjust retention periods to save. For high availability, consider using a 2-node cluster with an active-passive configuration rather than active-active (which requires extra licensing for the passive node? Actually, passive secondaries in an availability group are free if they only handle read-only traffic or no traffic, but failover cluster instances require both nodes to be licensed unless the passive node is used for HA only, exam nuance: a passive secondary in an Always On availability group that is not serving read traffic does not require a SQL Server license. This is a common exam trap: you must know that passive nodes for disaster recovery (in a different region) are also license-free if they are not used for reporting. Cost optimization is a core topic in AZ-104 and Azure Solutions Architect exams, so understand these trade-offs.

## High Availability and Disaster Recovery for SQL Server on Azure VM

High availability (HA) and disaster recovery (DR) are essential for production SQL Server workloads running on Azure VMs. The Azure platform itself provides VM-level SLAs (99.95% to 99.99% with availability sets or availability zones), but SQL Server-level protection requires additional configuration. There are two primary HA architectures for SQL Server on Azure VM: Always On availability groups (AGs) and Failover Cluster Instances (FCIs). Each has different use cases, licensing implications, and exam nuances.

Always On availability groups operate at the database level. You create a primary replica and up to 9 secondary replicas (in SQL Server 2022) that can be synchronous or asynchronous. Synchronous commit ensures no data loss but requires low latency between replicas; it is best within a single region. Asynchronous commit is suitable for DR across regions. The AG requires a Windows Server Failover Cluster (WSFC) in the background, but the cluster itself does not need shared storage, each replica has its own copy of the database. For client connectivity, you need a listener (a virtual network name and IP) that routes connections to the primary replica. In Azure, you can use an internal load balancer (ILB) with HA ports or a Distributed Network Name (DNN) for the listener. DNN is the recommended approach for SQL Server 2019 and later because it avoids the complexity of load balancer rules and probe ports. For exam purposes, remember that DNN requires SQL Server 2019 CU8+ and Windows Server 2016+. DNN also works with read-only routing for secondary replicas.

Failover Cluster Instances (FCIs) operate at the instance level, the entire SQL Server instance fails over, including all databases. FCIs require shared storage, which in Azure can be implemented using Azure Shared Disks (for 2-node clusters), Storage Spaces Direct (S2D, for multi-node clusters), or even third-party clustering solutions. FCIs provide redundancy for the SQL Server service itself but not necessarily for storage; shared disks are a single point of failure unless you use premium SSD with zone-redundant storage (ZRS) or Azure Disk Redundancy. FCIs are often simpler for applications that do not support AGs (like older apps with single database dependency) and have a single listener for the instance. Licensing: For an FCI, both nodes must be licensed if both are actively used, but the passive node (only used for HA) does not require a license if it does not serve any read traffic or do any work. This is a common exam topic, a passive secondary in an AG or FCI does not need a SQL Server license.

For disaster recovery, you can deploy a secondary replica in a different Azure region using asynchronous commit. This can be part of an AG or you can use Azure Site Recovery (ASR) to replicate the entire VM. ASR provides crash-consistent replication but may have data loss of a few seconds. For zero data loss DR, a multi-region AG with synchronous commit is not feasible due to latency, so you use asynchronous commit with manual failover. Azure also provides geo-redundant backup (GRS) for automated backups, which can restore in a secondary region.

Key exam concepts for AZ-104, DP-900, and Google Cloud exams: know that SQL Server on Azure VM HA is user-managed; Azure does not provide automatic failover for SQL Server instances unless you configure it. For cost, always note that a passive HA replica does not incur SQL Server licensing fees. For availability, you can achieve up to 99.99% uptime with properly configured AGs across availability zones. Some exam questions test the difference between an FCI (single instance, shared storage) and an AG (multiple instances, separate storage). For Azure Solutions Architect (AZ-305), you might be asked to design a solution that meets an RPO of 5 seconds and RTO of 1 minute, that is achievable with a synchronous AG within the same region across availability zones. For RPO of 0 and RTO of minutes, you may need an AG with automatic failover and a load balancer.

Troubleshooting HA: common issues include listener connectivity problems (often due to incorrect load balancer rules, missing probe ports, or firewall), split-brain scenarios (misconfigured quorum), and failover not triggering due to health detection timeouts. For exams, you should understand that the cluster quorum in Azure often uses a file share witness (in Azure Files or a small VM) or a cloud witness (Azure Storage blob). Cloud witness is preferred because it doesn't require an extra VM. Also, for cross-region AGs, note that the listener must be accessible via a public IP or VPN, private IPs only route within a VNet unless you use global VNet peering.

high availability and disaster recovery for SQL Server on Azure VM require careful planning, proper storage configuration, and correct licensing to avoid surprises. For exam success, memorize the differences between AGs and FCIs, understand DNN vs ILB, and know that passive replicas are license-free. These concepts differentiate SQL Server on Azure VM from PaaS offerings like Azure SQL Database, where HA is built-in and transparent.

## Security and Backup for SQL Server on Azure VM

Security and backup management are critical responsibilities for any SQL Server on Azure VM deployment. Because this is an IaaS model, the burden of securing the operating system, SQL Server instance, and network falls on the administrator, with some assistance from Azure platform features. Understanding these security mechanisms and backup strategies is a frequent topic in DP-900, AZ-104, and Google Cloud Digital Leader exams.

First, network security. The VM hosting SQL Server should be placed in a subnet that uses Network Security Groups (NSGs) to restrict inbound traffic to only necessary ports. By default, SQL Server uses TCP port 1433 for the default instance and a dynamic port for named instances. You should never expose port 1433 to the internet unless absolutely necessary, and even then, use Azure Firewall, Application Gateway with WAF, or a VPN connection. For secure access, use Azure Private Link to expose the SQL Server VM privately within your VNet. For client connections from on-premises, use a Site-to-Site VPN or Azure ExpressRoute. Enable Azure DDoS Protection for the VNet to reduce attack surface.

Second, identity and access management. Use Azure Active Directory (Azure AD) authentication for SQL Server if you are running SQL Server 2012 SP2 or later (for Windows VMs) or SQL Server 2017 (for Linux VMs). Azure AD authentication allows you to manage logins centrally and supports conditional access policies. However, for SQL Server on Azure VM, Azure AD authentication is more complex than in PaaS options, you must configure the VM to join an Azure AD domain or use Azure AD Domain Services (Azure AD DS). Alternatively, you can use Windows Authentication by joining the VM to an on-premises Active Directory domain (via VPN/ExpressRoute) or an Azure AD DS managed domain. For exam scenarios, choose Azure AD DS for simplified identity management if the organization already uses Azure AD. Also, implement SQL Server security best practices: use least privilege principles, avoid using the 'sa' account, enable SQL Server and Windows Authentication mode only if needed, and regularly audit logins.

Third, data protection at rest and in transit. For encryption at rest, you can use Transparent Data Encryption (TDE) with Azure Key Vault integration. TDE encrypts the database files automatically. You can also configure Azure Disk Encryption (via BitLocker for Windows or DM-Crypt for Linux) for full disk encryption. Azure Storage Service Encryption (SSE) is enabled by default for managed disks, encrypting at the storage platform level. For exam understanding, know that SSE is enough for most compliance needs, but TDE is needed for database-specific encryption. For encryption in transit, enforce TLS 1.2 or higher and use Azure Firewall or NSG to block older protocols. You can also require encrypted connections in SQL Server Configuration Manager.

Now, backup and disaster recovery. SQL Server on Azure VM offers two main backup methods: manual backups using SQL Server native tools (like T-SQL BACKUP DATABASE) and automated backups via Azure Backup (which uses the Azure Backup extension for SQL Server VM). Azure Backup is recommended because it offloads backup management, provides centralized monitoring, supports log backups, and stores backups in Azure Recovery Services vault with geo-redundancy. The Azure Backup service automatically registers the SQL Server VM, discovers databases, and takes full, differential, and transaction log backups based on a policy. For exam note: Azure Backup for SQL Server on Azure VM supports backup files to disk (managed disks) and to the vault, but the recommended path is vault-based backup. You must ensure the VM has the SQL IaaS Agent extension installed (which is automatic for marketplace images).

For point-in-time recovery, Azure Backup uses log backup chains. You can restore to any point within the retention period. You can use long-term retention for regulatory compliance. For RPO and RTO calculations, log backups can be set to every 5-15 minutes (minimum is 5 minutes). For performance, place the backup temporary file location on a separate disk with high IOPS. For exam-like scenarios, if a company needs RPO of < 5 minutes and RTO of 1 hour, Azure Backup with frequent log backups is a valid solution, but for ultra-low RPO, you might pair it with an Always On availability group synchronous secondary.

Troubleshooting backup failures: common issues include insufficient permissions for the SQL IaaS Agent extension, storage account misconfiguration, firewall blocking the vault communication, or large database sizes causing timeouts. Ensure the SQL Server service account (NT Service\SQLSERVERAGENT or custom) has the necessary permissions on the temp directory. For exam questions, know that the backup extension communicates with the recovery services vault via HTTPS (port 443) and requires outbound connectivity. If the VM uses a proxy, you must configure proxy settings.

Finally, security monitoring. Use Azure Security Center (or Microsoft Defender for Cloud) to enable the SQL Server on VM plan for vulnerability assessments and threat detection. This is a key differentiator for security posture in exams. Also, enable SQL Server auditing (to a blob storage or event log) and integrate with Azure Sentinel for advanced threat hunting. Remember, in the IaaS model, you are responsible for OS-level patching and SQL Server cumulative updates. Azure can automate patching via the SQL IaaS Agent extension (Automatic Patching feature), but you still need to plan maintenance windows. For exam design questions, if security and compliance are top priorities, combine Azure Hybrid Benefit with Azure Backup and Azure Security Center to get the most value while staying compliant.

## Common mistakes

- **Mistake:** Thinking SQL Server on Azure VM is a fully managed service like Azure SQL Database.
  - Why it is wrong: This is incorrect because with SQL Server on Azure VM, you are responsible for managing the operating system, SQL Server software, backups, patching, and high availability. Azure only manages the underlying hardware and hypervisor.
  - Fix: Remember that SQL Server on Azure VM is IaaS (Infrastructure as a Service), giving you control but also responsibility. For less management overhead, consider Azure SQL Database (PaaS) or Azure SQL Managed Instance.
- **Mistake:** Choosing SQL Server on Azure VM when the primary requirement is automatic scaling and built-in high availability.
  - Why it is wrong: SQL Server on Azure VM does not provide automatic scaling or built-in high availability. You must manually configure Always On Availability Groups or failover cluster instances. For automatic scaling and high availability, Azure SQL Database is a better fit.
  - Fix: Align your choice with the required level of management. If automatic scaling and built-in HA are critical, choose a PaaS service like Azure SQL Database. If you need full control and are willing to manage HA yourself, SQL Server on Azure VM is suitable.
- **Mistake:** Using Standard HDD or Standard SSD disks for SQL Server production workloads.
  - Why it is wrong: Standard HDD and Standard SSD disks have higher latency and lower IOPS, which can severely degrade SQL Server performance, especially for transaction log writes and high-volume queries. Production SQL Server workloads require Premium SSD or Ultra Disk managed disks.
  - Fix: Always choose Premium SSD or Ultra Disk for production SQL Server on Azure VM workloads. Reserve Standard SSD for development or test environments where performance is not critical.
- **Mistake:** Forgetting to configure both the Network Security Group (NSG) and the Windows Firewall to allow SQL Server traffic.
  - Why it is wrong: The NSG controls inbound traffic at the network level, but the Windows Firewall on the VM also needs to allow traffic on port 1433. If only one is configured, clients cannot connect. This is a common source of connectivity issues.
  - Fix: After opening port 1433 in the NSG, also log into the Windows VM and create an inbound rule in Windows Firewall for port 1433 (or your custom SQL Server port). Use the SQL Server Configuration Manager to verify the instance is listening on the correct port.
- **Mistake:** Assuming that SQL Server on Azure VM supports all SQL Server features exactly like on-premises without any limitations.
  - Why it is wrong: While SQL Server on Azure VM is nearly identical to an on-premises installation, there are some limitations. For example, distributed availability groups across regions may have higher latency, and some features like stretch database or SQL Server data files in Azure do not apply. Also, licensing costs may differ.
  - Fix: Review the official Microsoft documentation for SQL Server on Azure VM feature limitations. In most cases, features work as expected, but always verify for your specific use case, especially for high availability and replication scenarios.
- **Mistake:** Neglecting to use Azure Hybrid Benefit for existing SQL Server licenses.
  - Why it is wrong: Organizations that already own SQL Server licenses with Software Assurance can significantly reduce costs by applying Azure Hybrid Benefit. Without it, you pay for SQL Server licensing at the VM's pay-as-you-go rate, which is often more expensive.
  - Fix: Always assess whether your organization has eligible SQL Server licenses. During VM provisioning, choose the option to use Azure Hybrid Benefit. If deploying after creation, you can modify the VM's configuration to enable it.

## Exam trap

{"trap":"The exam question presents a scenario where a company needs to migrate a SQL Server database to Azure with minimal changes, and they also want built-in high availability and automatic backups. Some learners select SQL Server on Azure VM because it supports minimal application changes, but they forget that built-in HA and auto backups are not inherent to this service.","why_learners_choose_it":"Learners see the requirement for minimal changes and think of SQL Server on Azure VM as the obvious lift-and-shift option. They overlook the additional requirement for built-in high availability and automatic backups, which are features of PaaS services, not IaaS.","how_to_avoid_it":"Read every requirement in the scenario carefully. If the scenario mentions built-in high availability, automatic failover, or automatic backups, that points toward Azure SQL Database or Azure SQL Managed Instance, not SQL Server on Azure VM. Remember that with IaaS, you must configure these features manually. When in doubt, note how much administrative overhead the company wants to take on."}

## Commonly confused with

- **SQL Server on Azure VM vs Azure SQL Database:** Azure SQL Database is a fully managed Platform as a Service (PaaS) offering where Azure handles all management tasks including backups, patching, and high availability. You only manage the data and schema. In contrast, SQL Server on Azure VM is IaaS where you manage the entire SQL Server instance and OS. (Example: Azure SQL Database is like a hotel with room service; SQL Server on Azure VM is like a rented apartment where you cook your own meals.)
- **SQL Server on Azure VM vs Azure SQL Managed Instance:** Azure SQL Managed Instance is a PaaS service that provides near 100% compatibility with on-premises SQL Server, including features like SQL Server Agent and linked servers, but with automated backups and patching. It is easier to migrate to than Azure SQL Database but offers less control than SQL Server on Azure VM. (Example: Azure SQL Managed Instance is like a fully furnished apartment with a cleaning service; you still have more control than a hotel room but less than a rented unfurnished apartment (SQL Server on Azure VM).)
- **SQL Server on Azure VM vs Amazon RDS for SQL Server:** Amazon RDS for SQL Server is a PaaS service on AWS that automates database administration tasks like backups, patching, and failover. SQL Server on Azure VM is equivalent to running SQL Server on an Amazon EC2 instance, which gives you full control over the OS and SQL Server but requires you to manage all administrative tasks. (Example: If you use Amazon RDS, AWS handles the database maintenance like a landlord providing utilities. If you use EC2 with SQL Server, you are the plumber and electrician for your rented house.)
- **SQL Server on Azure VM vs SQL Server on Google Compute Engine:** SQL Server on Google Compute Engine is the equivalent service on Google Cloud, where you run SQL Server on a GCE VM. The concept is identical to SQL Server on Azure VM: you have full control over the VM and SQL Server, but you are responsible for management. The difference is the underlying cloud provider and specific VM types/features. (Example: Both are like renting a workshop space from different landlords (Azure vs Google), the workshop setup is similar, but the amenities and lease terms differ.)
- **SQL Server on Azure VM vs Azure Synapse Analytics:** Azure Synapse Analytics (formerly SQL Data Warehouse) is a distributed analytics service designed for large-scale data warehousing and big data analytics. It uses a massively parallel processing (MPP) architecture and is optimized for analytical queries, not OLTP. SQL Server on Azure VM is a general-purpose relational database engine suitable for both OLTP and smaller analytics workloads. (Example: Azure Synapse is like a giant warehouse for storing and analyzing massive amounts of inventory data, while SQL Server on Azure VM is like your daily sales database at the checkout counter.)

## Step-by-step breakdown

1. **Plan VM Size and Configuration** — Choose the appropriate VM series and size based on your workload requirements. Consider CPU cores, memory, and network bandwidth. For SQL Server, memory is often the most critical resource. Common series include D-series (general purpose), E-series (memory optimized), and M-series (high memory). Also decide whether to use a pre-configured SQL Server image from Azure Marketplace or deploy a blank Windows Server VM and install SQL Server manually.
2. **Provision the Virtual Machine** — Using the Azure portal, CLI, or PowerShell, create a new virtual machine. Select the desired region, availability options (availability set, availability zone, or none), and authentication method (password or SSH key for Linux). For Windows VMs, Azure Bastion is recommended for secure RDP access. Choose the OS image, if using SQL Server, select an image with the edition you need (Express, Standard, Enterprise).
3. **Configure Storage Disks** — Add managed disks for data, logs, and tempdb. Use Premium SSD or Ultra Disk for production workloads. Attach at least two additional disks beyond the OS disk: one for SQL Server data files and one for transaction logs. Optionally, add a third disk for tempdb. Use caching settings: ReadOnly for data disks (if using Premium), None for log disks. Configure storage in SQL Server to use these separate drives.
4. **Configure Networking and Security** — Create or select a Virtual Network (VNet) and subnet. Configure Network Security Group (NSG) rules to allow inbound traffic on SQL Server port (default 1433) only from trusted sources. Also configure Windows Firewall on the VM to allow the same port. For production, use Azure Private Link or VPN to avoid exposing the VM to the internet. Enable accelerated networking for lower latency.
5. **Install and Configure SQL Server** — If using a pre-configured image, SQL Server is already installed. Otherwise, install SQL Server from media. After installation, use SQL Server Configuration Manager to set the instance to listen on a specific port (usually 1433) or use a dynamic port. Configure SQL Server authentication mode (Windows and SQL Server authentication) and create SQL Server logins. Apply the latest cumulative updates.
6. **Set Up Automated Backups** — Enable Azure Backup for SQL Server VMs, which uses the Azure Backup service to take application-consistent backups. Alternatively, configure SQL Server Managed Backup to Azure Blob Storage using Transact-SQL or PowerShell. Define retention periods and schedule. Verify that backups are completing successfully by checking backup history in SSMS or Azure Backup reports.
7. **Configure High Availability (if needed)** — For high availability, decide between Always On Availability Groups (AG) or Failover Cluster Instances (FCI). For AGs, create two or more VMs in an availability set, add a Windows Server Failover Cluster, configure the AG listener, and point applications to the listener. For FCI with Azure shared disks, use Azure Shared Disks or SQL Server FCIs on Azure VMs with iSCSI targets. Test failover to ensure it works.
8. **Monitor and Optimize Performance** — Use Azure Monitor to track VM metrics like CPU, memory, disk IOPS, and throughput. Enable SQL Server Query Store to analyze query performance and identify regressions. Use SQL Server DMVs to check for blocking, deadlocks, and index usage. Adjust VM size if metrics indicate bottlenecks. Consider using Azure SQL Insights for deeper database-specific monitoring.

## Practical mini-lesson

In the real world, deploying SQL Server on Azure VM involves several critical decisions that can affect cost, performance, and operational efficiency. One of the first decisions is selecting the right VM size. For example, a workload that requires 64 GB of memory and 16 cores might be well-served by an E16-4s_v3 VM (which offers 16 cores, 128 GB memory) with memory scaling disabled, or a D16s_v3 (16 cores, 64 GB memory). However, you should also consider the Azure Premium Disk limitations: each VM size has a maximum number of data disks and maximum uncached disk throughput. The E16-4s_v3 can support up to 32 data disks with a maximum uncached throughput of 8,000 MB/s, while a D16s_v3 supports 16 data disks with 1,024 MB/s. For a heavy I/O workload, the E-series is often more suitable.

Another common mistake is failing to properly size the tempdb database. tempdb is used for sorting, temporary tables, and version store. In an Azure VM, tempdb should be placed on a dedicated disk, preferably using Premium SSD. It is also recommended to use multiple tempdb data files (as many as the number of CPU cores, but not exceeding 8 files) to reduce allocation contention. You can move the tempdb files after SQL Server installation using ALTER DATABASE commands.

Backup strategies are another area where professionals need to plan carefully. Azure Backup for SQL Server VMs is a simple solution that integrates with the Azure Backup vault and automatically triggers VSS snapshots. However, it has a limit of 100 databases per VM for backup. If you have more databases, you need to consider alternative methods like SQL Server Managed Backup to Azure Blob Storage, which can scale to any number of databases but requires more manual configuration. For large databases (over 1 TB), consider file-snapshot backups using Azure Snapshot for SQL Server, which provides near-instant backups regardless of database size.

What can go wrong? Connectivity issues are the most common problem. After deployment, you might find that clients cannot connect even though port 1433 is open in the NSG. The culprit is often the Windows Firewall on the VM, which blocks inbound SQL Server traffic by default. Always verify both layers of firewall. Another issue is performance degradation due to disk caching settings. By default, the OS disk has Read/Write cache enabled, but for SQL Server data disks, you should set the host caching to ReadOnly if using Premium SSD. For log disks, caching must be None to prevent data loss in case of a crash.

Licensing can also be tricky. If you use the Azure Hybrid Benefit, you must have Software Assurance or equivalent subscriptions. You can enable or disable the Azure Hybrid Benefit at any time, but it affects billing from that hour onward. If you forget to enable it at deployment time, you can modify the VM's configuration later under the 'Configuration' blade, but you will be charged the pay-as-you-go rate for the period before it was enabled.

Finally, consider the cost of leaving development VMs running 24/7. A cost-saving practice is to shut down non-production VMs during off-hours using Azure Automation. You can also use Azure Reservations or Savings Plans for committed usage discounts. Always monitor your costs using Azure Cost Management and set budgets and alerts.

## Commands

```
az vm create --resource-group myRG --name sqlvm --image MicrosoftSQLServer:sql2019-enterprise:sql2019-ent-ubuntu2004:latest --admin-username azureuser --generate-ssh-keys
```
Creates a new Azure Linux VM with SQL Server 2019 Enterprise Edition from the Azure Marketplace. This is the infrastructure base for SQL Server on Azure VM.

*Exam note: Exams test that SQL Server images are available in Azure Marketplace for both Windows and Linux. Know the naming convention: publisher:offer:sku:version. For SQL Server, the publisher is MicrosoftSQLServer.*

```
az sql vm show --name sqlvm --resource-group myRG --query 'sqlManagement'
```
Shows the SQL Server management mode of an existing SQL Server VM. The output indicates whether the SQL IaaS Agent extension is in 'Full' or 'Lightweight' or 'NoAgent' mode.

*Exam note: The SQL IaaS Agent extension is required for automated backups, patching, and Azure portal management. Exams test that 'Full' mode provides all features but requires a restart of SQL Server during installation.*

```
az sql vm create --resource-group myRG --name sqlvm --license-type PAYG --sql-mgmt-type Full
```
Registers an existing SQL Server VM with the SQL IaaS Agent extension in 'Full' mode and sets licensing to pay-as-you-go. This enables Azure portal manageability and additional features.

*Exam note: Exam questions often require choosing the correct license-type (PAYG or AHUB) when registering a VM. 'AHUB' stands for Azure Hybrid Benefit.*

```
az disk create --resource-group myRG --name datadisk1 --size-gb 256 --sku Premium_LRS --max-shares 2
```
Creates a Premium SSD managed disk with 'max-shares' set to 2, allowing it to be attached to two VMs simultaneously (needed for failover cluster instances with shared disks).

*Exam note: For SQL Server on Azure VM high availability using FCI, disks must have the max-shares property set. This is a key exam detail for shared storage in Azure.*

```
Set-SqlAvailabilityGroup -Path SQLSERVER:\SQL\MyVM\Default -AvailabilityGroup MyAG -FailoverMode Automatic -HealthCheckTimeout 30000
```
PowerShell command to configure an Always On availability group with automatic failover and a health check timeout of 30 seconds. Used within SQL Server Management Studio or PowerShell module.

*Exam note: Automatic failover requires a synchronous commit replica and a valid listener. Exam questions test understanding of failover modes: automatic vs manual, and health check timeout values.*

```
New-AzResourceGroupDeployment -ResourceGroupName myRG -TemplateFile azuredeploy.json -TemplateParameterFile azuredeploy.parameters.json
```
Deploys a SQL Server on Azure VM using an ARM template. This is the declarative infrastructure-as-code method for repeatable deployments.

*Exam note: ARM templates are the recommended way to deploy SQL Server VMs in enterprise environments. Exams often ask about using templates for consistent deployments. Also relevant for Bicep.*

```
BACKUP DATABASE AdventureWorks TO DISK = 'F:\SQLBackup\AdventureWorks.bak' WITH FORMAT, COMPRESSION, CHECKSUM;
```
Transact-SQL command to perform a native SQL Server backup to a disk location on the VM (F: drive). The WITH FORMAT, COMPRESSION, CHECKSUM options ensure overwrite, reduce size, and verify integrity.

*Exam note: Native backup is a fundamental skill. Exams test knowledge of backup device types, compression, and checksum. Also, know that Azure Backup uses log sequence numbers (LSN) for point-in-time restore.*

```
az backup protection enable-for-sqlvm --resource-group myRG --vault-name myVault --vm-name sqlvm --policy-name myPolicy
```
Enables Azure Backup for SQL Server on Azure VM, using a backup policy defined in the Recovery Services vault. This automates full, differential, and log backups.

*Exam note: Azure Backup is the preferred solution for SQL Server on Azure VM. Exams test that Azure Backup can restore to a point in time, and that it requires the SQL IaaS Agent extension to be installed.*

```
sqlcmd -S tcp:myserver.database.windows.net -U myuser -P mypass -Q "SELECT @@VERSION"
```
Connects to a SQL Server using sqlcmd with explicit TCP protocol. Although more common for Azure SQL Database, this is also used for SQL Server on Azure VM if the VM has a public IP or DNS name.

*Exam note: Exams check that you know the connection string format for SQL Server on IaaS: tcp:myserver.westus.cloudapp.azure.com (or private IP). Also, the default port 1433 can be changed.*

```
Add-SqlAvailabilityDatabase -Path "SQLSERVER:\SQL\PrimaryServer\Default\AvailabilityGroups\MyAG" -Database "AdventureWorks"
```
PowerShell command to add a database to an existing Always On availability group on the primary replica. The database must be in full recovery model and have a full backup.

*Exam note: Exam scenarios test prerequisites for adding a database to an AG: full recovery model, valid full backup, and consistent log chain. Also, the database must be on the primary replica.*

## Troubleshooting clues

- **Cannot connect to SQL Server on Azure VM from client** — symptom: Client receives a timeout error or connection refused when trying to connect to the SQL Server instance via SSMS or application.. This usually happens because the firewall on the VM (Windows Firewall or iptables) is blocking port 1433 (or the configured port). The Azure Network Security Group (NSG) may not have an inbound rule for port 1433. The SQL Server service might also be stopped or not listening on TCP/IP. (Exam clue: Exam questions present a scenario where a client cannot connect after deploying a SQL Server VM. The answer often points to checking the NSG inbound rules and enabling SQL Server TCP/IP in SQL Server Configuration Manager.)
- **SQL Server IaaS Agent extension not responding or status 'NotReady'** — symptom: In Azure portal, the SQL Server VM's management blade shows 'NotReady' or the extension fails to install. Automated backup and patching features are unavailable.. The SQL IaaS Agent extension needs outbound internet access to Azure services (specifically to the Recovery Services vault and Azure Resource Manager). Common causes include a restrictive NSG, a proxy server without configuration, or the VM not having the required .NET Framework. The extension also requires that the SQL Server service is running and the SQL Server Browser service is enabled for named instances. (Exam clue: Exams test that the 'Full' management mode requires internet connectivity. Traps include scenarios where the VM is in a 'No Internet' subnet or behind a proxy. The fix is to configure the proxy or allow outbound HTTPS (443).)
- **Always On availability group listener unavailable after failover** — symptom: After an automatic or manual failover, clients cannot connect to the availability group listener. The listener IP is unreachable or the name does not resolve.. This often occurs because the load balancer (if using ILB) does not have the correct health probe or the probe port is not open on the new primary replica. Alternatively, if using DNN, the DNN is not registered on all replicas or the SQL Server version/update does not support DNN. Also, the listener resource may not have moved to the new primary in the cluster. (Exam clue: Exams present a scenario where after failover, the listener is down. The correct answer can be: verify the load balancer's health probe is targeting the correct port on the new primary, or consider switching to Distributed Network Name (DNN) for simplicity.)
- **Automated backup (Azure Backup) fails with error 'VMBundleSnapshotFailed'** — symptom: Azure Backup jobs for SQL Server VM fail repeatedly. The error message in the Recovery Services vault indicates a snapshot failure.. This occurs when the Azure Backup extension cannot take a VSS (Volume Shadow Copy) snapshot of the SQL Server VM. Common causes: large database size causing timeout, insufficient disk space on the system drive, or the SQL Writer service is disabled or not running. For Linux, it may be related to the fsfreeze utility. (Exam clue: Exam questions might list a snapshot failure scenario. The key diagnostic steps: check that the SQL Writer service is running (Windows), ensure at least 10-20% free space on the temp directory drive, and check the Backup extension logs. The fix often involves restarting the SQL Writer service or increasing the retention interval.)
- **SQL Server VM running out of memory (OOM) under load** — symptom: SQL Server performance degrades, queries time out, or the SQL Server process crashes with an out-of-memory exception. The VM shows very high memory usage.. This happens when SQL Server is configured to use too much memory (max server memory set too high) or the VM size does not provide enough RAM for the workload. Other applications on the same VM may compete for memory. The VM might also have over-committed memory due to memory ballooning in some hypervisors (though not typical in Azure). (Exam clue: Exam scenarios test the 'max server memory' configuration for SQL Server on Azure VM. The recommended practice is to reserve 1-2 GB for the OS and set max server memory appropriately. For VM sizing, the 'E' series (memory optimized) is preferred for SQL Server.)
- **Patching fails with 'DiskFull' or insufficient space during SQL Server update** — symptom: When applying a SQL Server cumulative update or security patch via Windows Update or manual installation, the update fails with a disk space error.. SQL Server patches often require free space on the system drive (C:) for temporary files, backup of current binaries, and installation logs. On Azure VMs, the OS disk is typically 127 GB (or larger) but may run out of space if tempdb or log files consume too much space. Also, the temporary directory (TEMP) might be on a small drive. (Exam clue: This scenario is used in exams to test understanding of disk sizing. The solution is to ensure the OS disk has at least 20 GB free, or create a separate temporary disk (D: drive on Windows VMs) for the patch installer. The D drive is temporary and should not be used for databases.)
- **Read-scale queries to secondary replica fail with 'The target database is not in an availability group'** — symptom: Read-only routing to a secondary replica in an availability group is not working; queries that specify ApplicationIntent=ReadOnly get an error or are redirected to the primary.. This typically occurs because the read-only routing list is not configured correctly in the availability group, or the listener does not support read-only routing. Also, the secondary replica must be configured to allow read-only connections and have the ALLOW_CONNECTIONS = READ_ONLY property set. The DNS resolution for the listener may not be round-robin to secondaries. (Exam clue: Exam questions about read-only routing test your knowledge of the AG listener's routing list. The correct configuration includes setting the 'ReadOnlyRoutingUrl' on each replica and adding them to the 'ReadOnlyRoutingList' property. Also, the application must use ApplicationIntent=ReadOnly in the connection string.)
- **Unable to enable TDE with Azure Key Vault integration** — symptom: When trying to configure Transparent Data Encryption (TDE) for a SQL Server database using Azure Key Vault, the operation fails with 'Cannot open key vault' or access denied.. This occurs because the SQL Server service account does not have permissions to access the Azure Key Vault. You need to grant the service principal (the VM's managed identity or an Azure AD application) the 'Get', 'List', 'WrapKey', and 'UnwrapKey' permissions on the key vault. Also, the VM must have the Managed Identity enabled (system-assigned or user-assigned). For SQL Server on Azure VM, you must also install the SQL Server Connector for Azure Key Vault. (Exam clue: Exams test the steps: enable Managed Identity on the VM, grant the identity necessary Key Vault permissions (using Access Policies), and install the SQL Server Connector. A common trap is forgetting the 'UnwrapKey' permission, which is required for decryption operations.)
- **Failover cluster instance (FCI) disks not attaching after node reboot** — symptom: After a planned or unplanned reboot of one node in a two-node FCI, the shared disks (Azure Shared Disks) do not appear in Disk Management or fail to mount.. Azure Shared Disks have specific requirements: they must use the 'max-shares' property set to the number of nodes (e.g., 2), and the disks must be attached to both VMs. After a reboot, the disk may need to be re-scanned or the cluster service may not have started yet. The disks must be in the same availability zone (if using zone-redundancy) or same region. Incorrect SCSI reservation issues may also occur. (Exam clue: This issue tests your understanding of Azure Shared Disks for FCI. The solution is to ensure the cluster service starts automatically, and after reboot, manually bring the disk resource online via Failover Cluster Manager. Exam questions often test the difference between shared disks and shared-nothing architectures (like AGs).)

## Memory tip

Think 'IaaS = I am a Sysadmin', with SQL Server on Azure VM, you are responsible for the full stack, just like your own server room.

---

Practice questions and the full interactive page: https://courseiva.com/glossary/sql-server-on-azure-vm