Study guide

15.2 Always On Failover Cluster Instances and Log Shipping

DP-300: Administering Microsoft Azure SQL Solutions Exam

Track your progress as you read

Finished sections get a check mark.

1 Introduction & Exam Overview

1.1 Current DP-300 Exam Facts 1.2 Skills Outline and Domain Weights 1.3 Study Strategy and Official Resources

2 Planning and Deploying Azure SQL Solutions

2.1 Choosing and Deploying Azure SQL Database Offerings 2.2 Automated Deployment, Azure Arc, and SQL Database in Microsoft Fabric 2.3 Table Partitioning and Sharding Strategy 2.4 Hybrid SQL Server and IaaS Patching

3 Configuring Resources for Scale and Performance

3.1 Configuring Azure SQL Database for Scale and Performance 3.2 Configuring Azure SQL Managed Instance for Scale and Performance 3.3 Configuring SQL Server on Azure VMs for Scale and Performance 3.4 Configuring Table Partitioning and Data Compression

4 Planning and Implementing a Migration Strategy

4.1 Evaluating Migration Requirements and Strategies 4.2 Implementing Online, Offline, and Azure Migrations 4.3 Migrating Between Azure SQL Services and Troubleshooting

5 Configuring Database Authentication and Authorization

5.1 Microsoft Entra ID Authentication for Azure SQL 5.2 Authentication for SQL on Azure VMs and Managed Instance; Troubleshooting 5.3 Security Principals and Entra-Backed Users 5.4 Permissions, Least Privilege, and T-SQL Management

6 Implementing Security for Data at Rest and in Transit

6.1 Transparent Data Encryption and Object-Level Encryption 6.2 Always Encrypted and VBS Enclaves 6.3 Firewall Rules, Private Links, and Service Endpoints

7 Implementing Compliance Controls for Sensitive Data

7.1 Data Classification and Server/Database Auditing 7.2 Change Data Tracking and Dynamic Data Masking 7.3 Row-Level Security and Ledger in Azure SQL

8 Monitoring Resource Activity and Performance

8.1 Operational Baselines and Performance Metric Sources 8.2 Interpreting Metrics and Configuring Activity Monitoring 8.3 Monitoring with Database Watcher (Preview) 8.4 Monitoring with Extended Events

9 Monitoring and Optimizing Query Performance

9.1 Configuring and Monitoring with Query Store 9.2 Session Blocking and Dynamic Management Views 9.3 Index Changes and Execution Plan Review 9.4 Query Construct Modifications and Intelligent Insights

10 Configuring Database Solutions for Optimal Performance

10.1 Index, Statistics, and Integrity Maintenance 10.2 Automatic Tuning and Database-Scoped Configuration 10.3 Server Settings and Resource Governor 10.4 Compute/Storage Scaling and Intelligent Query Processing

11 Creating and Managing SQL Server Agent Jobs

11.1 Schedules for Regular Maintenance Jobs 11.2 Job Alerts, Notifications, and Agent Troubleshooting 11.3 Automating Deployment with ARM and Bicep Templates

12 Automating Deployments and Database Tasks in Azure

12.1 Azure PowerShell, Azure CLI, and Deployment Monitoring 12.2 Elastic Jobs for Cross-Database Automation 12.3 Azure Automation Runbooks, Alerts, and Troubleshooting Automated Tasks

13 Planning an HA/DR Strategy and Backup and Restore

13.1 Recommending HA/DR Strategy from RPO and RTO 13.2 Hybrid and Azure-Specific HA/DR Solutions and Testing 13.3 Backup and Restore Strategy with Native Tools 13.4 Point-in-Time Restore and Recovery Operations

14 Configuring HA/DR: Long-Term Retention, Geo-Replication, and Failover Groups

14.1 Long-Term Retention, T-SQL Backup/Restore, and Cloud Storage 14.2 Active Geo-Replication 14.3 Failover Groups

15 Advanced HA/DR: Availability Groups, FCIs, Log Shipping, and Troubleshooting

15.1 Always On Availability Groups on SQL Managed Instance and Azure VMs 15.2 Always On Failover Cluster Instances and Log Shipping 15.3 Monitoring and Troubleshooting HA/DR Solutions

15.2 Always On Failover Cluster Instances and Log Shipping

Key Takeaways Explain This Topic AI

An FCI protects the SQL Server instance by sharing one instance across cluster nodes, giving instance-level HA; an AG protects databases, and the two can be combined with an AG layered on an FCI

On Azure VMs, FCI shared storage can be Azure Shared Disk, Storage Spaces Direct (S2D), a Premium File Share, or an SMB file share; each has different node-count, latency, and resize constraints

Cloud witness is the recommended quorum mode on Azure because it lives in Azure storage and does not require a separate VM or file share; quorum prevents split-brain when a partition loses contact with peers

Log shipping backs up the primary's transaction log on a schedule, copies the files to a secondary, and restores them with NORECOVERY or STANDBY; RPO equals the backup interval and RTO equals restore time for the pending logs

Log shipping is a cheap, asynchronous, manual-failover DR option that complements AGs and fits budget-constrained or read-only reporting scenarios

Last updated: August 2026

FCI Architecture and Where It Fits

An Always On failover cluster instance (FCI) is a SQL Server instance shared between two or more nodes of a Windows Server Failover Cluster (WSFC) . Unlike an AG, which protects one or more databases , an FCI protects the whole instance : server-level objects (logins, SQL Agent jobs, linked servers, endpoints) move with the instance on failover. The mechanism is shared storage : every node reads and writes the same database files on a shared volume, and only the node that owns the instance mounts the volume at a time. When the owning node fails, the WSFC moves the instance (the SQL Server service, the shared disks, the network name, and the IP address) to another node, which mounts the storage and starts the service. An FCI does not maintain a live secondary replica - the surviving node starts cold from the shared storage, so there is a startup delay and no read scale-out.

FCI vs AG at a Glance

  • Dimension FCI AG
  • Granularity Instance-level Database-level
  • Storage Shared storage between nodes Each replica has its own copy of the database

Server objects (logins, jobs) Fail over with the instance Stay on whichever replica they were created on; must be synchronized manually

Read scale-out No readable secondary Readable secondaries supported

Automatic failover Yes (WSFC owns the resource) Yes (only synchronous-commit replicas with automatic failover mode)

Data loss on failover None (shared storage is the source of truth) None for synchronous commit; possible for asynchronous commit

Typical use case Instance-level HA, shared-storage apps, legacy migration HA plus DR across regions, read scale-out

An exam scenario that requires logins, SQL Agent jobs, and linked servers to follow the failover is pointing to an FCI, because an AG leaves server-level objects behind on whichever replica created them unless you synchronize them separately. The two technologies are complementary: you can run an AG on top of an FCI, so the instance is protected by the FCI and the databases are protected and replicated by the AG.

FCI Storage Options on Azure VMs

Azure VMs have no shared SAS storage, so the FCI's shared volume is implemented one of four ways. The choice is a frequent DP-300 scenario:

Storage option How it works Key constraints

Azure Shared Disk A premium or ultra managed disk attached as shared SCSI to multiple VMs Limited to one availability set; up to two nodes for premium, more for ultra; lowest latency

Storage Spaces Direct (S2D) Each node contributes local disks; S2D pools and mirrors them across the cluster into a CSV Supports multi-node and multi-zone clusters; higher cost (each node needs disks); recommended for production SQL FCIs

Premium File Share An Azure Files premium share mounted as SMB on every node Simplest; no storage management; higher latency; limited throughput caps; good for smaller workloads

SMB file share A standard Azure Files share or third-party SMB share Lowest performance; mainly dev/test or small instances

The exam-tested guidance: S2D is the recommended production storage for SQL Server FCIs on Azure VMs because it supports multi-node clusters and delivers low-latency, locally attached storage. Azure Shared Disk is the simplest path but is constrained to a single availability set and a small node count. Premium File Share is the easy-mode option for small or dev workloads that do not need the lowest latency.

Quorum Modes and Cloud Witness

Quorum determines whether a WSFC partition can keep running after a node loss. Without quorum, a partitioned cluster could start two primaries (a split-brain scenario), so the cluster shuts down if it loses quorum. Quorum modes:

Node majority - each node gets one vote; works for odd numbers of nodes.

Node and disk witness - a shared disk gets an extra vote; common on-premises with shared storage.

Node and file share witness - a file share on a separate server gets the extra vote.

Cloud witness - a blob in an Azure storage account gets the extra vote.

On Azure, cloud witness is the recommended quorum mode : it lives in Azure storage, requires no separate VM or on-premises file share, and works for even- and odd-node clusters. A scenario where an Azure FCI loses quorum after one node fails in a two-node cluster is missing a witness; the fix is to add a cloud witness, not to add a third VM.

Log Shipping Architecture

Log shipping is the simplest DR mechanism still tested on DP-300. Four jobs per primary/secondary pair:

Primary backup job runs a transaction log backup on the primary at a configured interval and writes the file to a backup share.

Copy job runs on the secondary, pulls the backup files from the share to a local folder.

Restore job runs on the secondary, restores the log backups WITH NORECOVERY (the database stays in a restoring state and continues to accept more logs) or WITH STANDBY (the database is readable between restores, at the cost of an undo file).

Monitor job (optional) watches latency against thresholds and raises alerts.

RPO equals the backup interval : a 15-minute backup job gives a 15-minute worst-case data loss window. RTO equals the time to restore the final pending logs and bring the database online - usually minutes, but it depends on how many log backups accumulated and the restore throughput. Log shipping is asynchronous by design : the primary never waits on the secondary, so it adds no latency to OLTP. That is the tradeoff vs an AG: simpler, cheaper, async, manual failover, no automatic redirect, no read scale-out unless STANDBY is used.

Failover Procedure

Because log shipping does not have an automatic failover mechanism, the procedure is manual:

Stop the primary backup job and copy the final log backup across (or take one last tail-log backup if the primary is still reachable).

Restore the final log backups WITH RECOVERY to bring the secondary online.

Update application connection strings to point at the secondary.

If you are failing back, reinitialize log shipping in the reverse direction (the old primary becomes the new secondary).

A common trap: if you restore the secondary WITH RECOVERY too early, it comes online and will refuse further log restores - you have to reinitialize from a full backup. Plan the cutover, restore the tail logs first, and only then run WITH RECOVERY.