15.2 Always On Failover Cluster Instances and Log Shipping

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

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:

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:

1. Primary backup job runs a transaction log backup on the primary at a configured interval and writes the file to a backup share.
2. Copy job runs on the secondary, pulls the backup files from the share to a local folder.
3. 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).
4. 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:

1. 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).
2. Restore the final log backups WITH RECOVERY to bring the secondary online.
3. Update application connection strings to point at the secondary.
4. 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.

Configuring Log Shipping: Jobs, Schedules, Thresholds

Log shipping is configured through SSMS (Database Properties > Transaction Log Shipping) or T-SQL. The configuration surface:

• Backup job schedule (the interval that sets RPO), retention on the primary share, and compression on the log backups.
• Copy job schedule (should be more frequent than backup so files do not pile up), the destination folder on the secondary, and retention.
• Restore job schedule, the restore mode (NORECOVERY or STANDBY), the disconnect policy (whether to disconnect users before restore), and the standby file path.
• Monitor thresholds: an alert fires when the latency between primary and secondary exceeds the configured minutes. You can also configure a secondary server as a monitor server for centralized alerting.

The latency that the monitor reports is the elapsed time since the last log backup the secondary restored. If the threshold is 60 minutes and the last restored backup is 75 minutes old, the alert fires. Latency rises when any of the three jobs is delayed: the backup job by a long-running transaction that prevents log truncation, the copy job by network or storage throughput, or the restore job by an under-provisioned secondary. The troubleshooting order is therefore: check the primary for open transactions blocking log truncation, then check the copy job history for transfer errors, then check the restore job history for restore errors.

Log Shipping as a DR Complement

Log shipping pairs well with AGs and FCIs because it is cheap and low-touch:

• AG + log shipping to a third region: the AG handles synchronous HA within one region; log shipping adds an asynchronous off-region copy at lower cost than a second AG replica.
• FCI + log shipping: the FCI handles instance-level HA; log shipping handles cross-region DR with manual failover.
• Reporting: a STANDBY secondary is readable for reporting, though stale by the backup interval.

The exam contrast is usually about cost and automation. Log shipping is chosen when simplicity, budget, or async distance tolerance matters more than automatic failover and read scale-out; AGs are chosen when you need automatic failover, zero data loss, or readable secondaries with read-only routing.

Monitoring Log Shipping

Two tables in msdb carry the monitoring state:

• log_shipping_monitor_primary - per-primary row with last backup time, last backup file, latency threshold, and last alert.
• log_shipping_monitor_secondary - per-secondary row with last copied file, last restored file, last restored latency, and thresholds.

Job history in SQL Server Agent tells you whether each backup, copy, and restore job succeeded, how long it ran, and the error message if it failed. The standard health check is a query against these tables joined to msdb.dbo.sysjobhistory, plus an alert rule that fires when the latency column exceeds the threshold. A scenario describing a log-shipped secondary that has not advanced in hours is best diagnosed by reading log_shipping_monitor_secondary and the restore job history: if the last restored file is hours old but the copy job shows recent successful copies, the restore job is the bottleneck (often a stuck restore or full disk on the secondary); if the copy job has failed recently, look at the network or storage path between the primary's backup share and the secondary.