Designing SQL Server high availability (HA) is less about picking a single feature and more about engineering a complete continuity plan: resilient compute and storage, predictable failover behavior, data protection that meets business targets, and operational discipline that keeps the design healthy over time. SQL Server includes multiple HA and disaster recovery (DR) capabilities, but they optimize for different failure modes. A design that excels at server failure may still be vulnerable to storage corruption, regional outages, or operator error.
This article walks through the main strategies used for SQL Server high availability and business continuity. It starts with the core availability metrics you must define (RPO/RTO and SLA/SLO) and then maps them to SQL Server technologies: Always On availability groups (AGs), failover cluster instances (FCIs), log shipping, backup/restore patterns, and common hybrid approaches. Along the way, it calls out operational details—quorum, DNS and connection routing, seeding and synchronization, backup strategy alignment, and failover validation—that often determine whether HA works under real pressure.
Define the business continuity targets before choosing an HA feature
SQL Server HA discussions frequently jump straight to “use Always On,” but that skips the only part that truly constrains the design: what the business expects when something breaks. Two numbers guide almost every decision.
Recovery Point Objective (RPO) is the maximum acceptable data loss measured in time. An RPO of 0 means you cannot lose committed transactions. Recovery Time Objective (RTO) is the maximum acceptable downtime until service is restored. These targets are often different per database or workload, even within the same instance.
Once you have RPO/RTO, translate them into service-level objectives (SLOs) that the engineering team can measure: acceptable failover time, maximum tolerated replica lag, backup frequency, and the cadence of DR testing. It is also important to classify failure domains. A design that handles “single VM lost” may not handle “storage array corruption,” “entire site offline,” or “accidental data deletion.”
A practical way to anchor the design is to list failure domains from smallest to largest—SQL Server service crash, OS crash, host failure, storage failure, network partition, site failure, region failure—and decide which ones must be automatically mitigated, which ones can be manual, and which ones can be restored from backups.
Understand the main SQL Server HA building blocks
SQL Server high availability is delivered through a mix of instance-level and database-level technologies. Knowing where state lives—instance metadata, server-level objects, databases, storage—helps avoid surprises.
An HA feature that fails over the SQL Server instance (like an FCI) preserves the illusion of “the same instance moved,” including SQL Server Agent jobs and logins because the instance metadata is on shared storage. In contrast, an HA feature that fails over at the database level (like an AG) moves databases but not all server-level objects, so you must synchronize logins, SQL Agent jobs, linked servers, and other instance-scoped items separately.
There are also DR-first patterns—log shipping and backup/restore—that can meet strict RPO with enough automation, but they typically require more operational work to achieve low RTO.
Before diving into architectures, keep two cross-cutting requirements in mind.
First, every HA design depends on reliable networking and name resolution. Client connectivity is often the real outage: the database might fail over cleanly, but applications still point to the old node or cache IPs.
Second, backups remain non-negotiable. HA is not a substitute for backups because HA does not protect you from logical corruption, accidental deletes, ransomware, or a bad deployment that commits incorrect data.
Choose between Always On availability groups and failover cluster instances based on failure mode
Always On availability groups and failover cluster instances are both built on Windows Server Failover Clustering (WSFC) when deployed on Windows, but they solve different problems.
Always On availability groups: database-level HA with flexible replicas
An availability group is a set of user databases that fail over together. Each database has a primary replica (read-write) and one or more secondary replicas (typically read-only) that receive transaction log records from the primary.
AGs are strong when you need multiple replicas, read scale-out, or geographically distributed DR. They are also useful when you want to separate read-only workloads (reporting, ETL reads, backups) from the primary.
However, AGs do not automatically include everything in the instance. SQL Server Agent jobs, logins, credentials, server configuration, SSIS packages stored in msdb, and other server-level objects require separate synchronization. This is not a deal-breaker, but it must be part of the design, not a post-incident discovery.
AGs support synchronous commit (aiming for zero data loss, at the cost of latency) and asynchronous commit (allowing lag, improving throughput over distance). They also support automatic failover only when synchronous commit is in use and the secondary is configured for automatic failover.
Failover cluster instances: instance-level HA with shared storage
A failover cluster instance is a single SQL Server instance installed across multiple nodes, with shared storage (SAN, Storage Spaces Direct, or a supported shared disk). At any time, one node owns the instance resources (SQL Server service, IP, network name, disks), and if that node fails, WSFC brings the instance up on another node.
FCIs excel at protecting against compute/node failure while keeping a single copy of the databases on shared storage. Because the instance is effectively “the same instance moving,” server-level objects are preserved without separate synchronization.
The trade-off is that FCIs do not inherently protect against shared storage failure or corruption. You can mitigate that with resilient storage, storage replication, or combining an FCI with an AG, but at that point you are building a more complex system.
A first decision framework
If your most likely or most critical failure is “a node dies,” and your storage is enterprise-grade and already highly redundant, an FCI can be straightforward and operationally familiar.
If you need multiple replicas, read-only secondaries, or a secondary in another site or region with independent storage, availability groups are usually the starting point.
Many enterprises end up with both across the estate, because different applications have different constraints: vendor apps that require a stable instance name and depend on SQL Agent jobs often fit FCIs well; bespoke apps that can use listener-based routing and want read scale-out often fit AGs.
Design around client connectivity: listeners, DNS, and application behavior
High availability is only as fast as client reconnection. In SQL Server, that typically means designing around a stable network endpoint and ensuring application connection logic is compatible with failover.
Availability group listener basics
An AG listener provides a stable DNS name and virtual IP (or multiple IPs) that routes to the current primary replica. Applications connect to the listener rather than a specific node. In multi-subnet designs, the listener can have an IP in each subnet.
Client libraries vary in how they handle multi-subnet listeners. For Windows-based clients using Microsoft data providers, enabling MultiSubnetFailover=True in the connection string significantly reduces failover detection time by parallelizing connection attempts to multiple IPs.
For example, a typical .NET connection string might look like:
Server=tcp:OrdersAGListener,1433;Database=Orders;Integrated Security=true;MultiSubnetFailover=True;Connect Timeout=15;
FCI connectivity basics
An FCI exposes a network name and IP that moves with the instance. Applications connect the same way they connect to a standalone instance. In practice, this simplicity is one of the biggest operational advantages of FCIs for legacy workloads.
Be explicit about timeouts and retry behavior
Even with a listener, applications can create self-inflicted outages if they do not retry connections or if their connection pools pin sessions to the old primary. Coordinate with application teams on connection timeout, retry/backoff, and whether the app caches DNS aggressively.
A useful operational exercise is to measure the time from initiating failover to “application healthy.” This typically includes WSFC detection, SQL Server startup or role change, DNS/client resolution, and application pool reconnection.
Engineer quorum and failure detection so the cluster behaves predictably
When SQL Server HA uses WSFC, cluster health depends on quorum. Quorum is the mechanism WSFC uses to avoid split-brain scenarios (two nodes believing they are the active owner). If quorum is lost, the cluster stops to protect data integrity.
The practical implication is that you must plan the voting configuration and witness placement. Two-node clusters are common for AGs and FCIs, but they are also quorum-sensitive: if one node is down and the remaining node cannot reach a witness, the cluster may go offline.
A file share witness or cloud witness provides an additional vote. The witness should be placed in an independent failure domain relative to the nodes. In a two-site design, the witness is often placed in a third location (or at least a location that does not fail with either site).
For Windows clusters, you can view cluster quorum configuration with PowerShell:
powershell
Import-Module FailoverClusters
Get-ClusterQuorum
Get-ClusterNode | Select-Object NodeName, State, DynamicWeight
Get-ClusterResource | Where-Object {$_.ResourceType -match "Witness"}
Avoid tuning failure detection settings without a clear reason. Overly aggressive heartbeat thresholds can cause failovers during transient network issues, which can be worse than a short pause.
Always On availability groups: architecture choices that matter in production
Once you decide to use AGs for SQL Server high availability, the primary architecture choices are replica count and placement, commit mode, failover mode, and how you handle read-only and backup workloads.
Synchronous vs asynchronous commit
Synchronous commit waits for the secondary replica to harden log records before the primary commits the transaction. This can deliver near-zero data loss (RPO ≈ 0) when the secondary is healthy, and it enables automatic failover. The cost is added latency per transaction, which depends on network RTT and secondary write performance.
Asynchronous commit allows the primary to commit without waiting, improving throughput and making cross-site replicas feasible, but it permits data loss if the primary fails before the secondary catches up.
A common pattern is to use synchronous commit within a data center (or within low-latency connectivity) for HA, and asynchronous commit to a remote site for DR.
Automatic failover requirements
Automatic failover in AGs requires:
- WSFC in a healthy state.
- The secondary configured for synchronous commit.
- The secondary configured for automatic failover.
- The secondary sufficiently synchronized.
If any of these conditions are not met, failover becomes manual. In business continuity planning, that distinction is critical: “we have a DR replica” is not the same as “we have automatic HA failover.”
Read-only routing and read scale-out
AG secondaries can serve read-only workloads. Read-only routing uses a listener to direct read-intent connections to secondaries, based on routing lists you configure. This can offload reporting queries from the primary, but it introduces new operational concerns: ensuring statistics and indexes meet read workload needs, ensuring read queries tolerate slightly stale data if asynchronous, and preventing read workloads from saturating I/O on the secondary.
Even if you do not use read-only routing, you can direct specific reporting services to connect directly to a readable secondary to reduce load on the primary.
Backup preferences and operational efficiency
AGs allow you to set a backup preference (primary, secondary only, prefer secondary, any). This helps offload backups to a secondary, but you must validate that your backup tooling respects AG preferences and that you can restore from those backups in all scenarios.
Also consider where backups are written. If secondaries are in a different site, writing backups across a WAN to a central repository may become the bottleneck. In that case, using local backup targets with replication to a central location can be more reliable.
Failover cluster instances: storage and patching are the real design decisions
FCIs are often described as “simple,” but the real complexity shifts into storage, cluster validation, and patching workflows.
Storage architecture and failure domains
Because FCIs depend on shared storage, the availability of the SQL Server databases depends heavily on the storage layer. Multipath I/O, redundant fabrics, and storage controller failover are essential. You also need a clear plan for what happens if the shared disk becomes unavailable or corrupt.
In practice, many FCI outages are not SQL Server issues but storage pathing misconfigurations, firmware problems, or maintenance that unexpectedly impacts the LUN.
Patching and maintenance windows
With an FCI, you patch passive nodes first, then fail over, then patch the other node. This can reduce downtime, but SQL Server binaries are installed on each node and must be kept in sync.
Similarly, Windows patching must be coordinated with cluster behavior, ensuring the instance has a healthy failover target before restarting nodes.
Application compatibility
For some third-party applications, an FCI is the only supported HA configuration because they assume a single instance name and rely on instance-level objects. If you’re supporting such an application, an FCI may reduce risk even if AGs look more modern on paper.
Hybrid designs: combine AGs and FCIs carefully
Hybrid designs exist because no single mechanism covers every failure domain. The most common hybrid is an FCI for local HA (node failure) combined with an AG to a remote site for DR.
In that design, each site can run an FCI using shared storage local to that site, and the primary databases replicate via AG to the DR site. This provides instance-level continuity locally and database-level replication remotely.
The cost is complexity: WSFC resources for the FCI plus WSFC resources for the AG, plus the operational task of ensuring server-level objects and agent jobs are consistent across sites. You also need to be careful with failover sequencing and role ownership during site-wide events.
Hybrid designs make sense when you need FCI semantics for a vendor workload but also need cross-site DR with independent storage.
Recovery-focused patterns: log shipping and backup/restore still matter
Even in environments with AGs or FCIs, recovery-focused patterns remain part of SQL Server high availability because they cover scenarios HA does not.
Log shipping
Log shipping continuously backs up transaction logs from the primary, copies them to a secondary, and restores them. It can provide a predictable RPO (based on log backup frequency) and is conceptually simple.
However, log shipping failover is typically manual and requires bringing the secondary online by recovering the database. It also does not provide a single endpoint like a listener; you must handle connection redirection.
Log shipping remains useful for:
- Creating a warm standby for DR when AGs are not available (for example, edition limitations or platform constraints).
- Offloading reporting by restoring logs with a delay (though delayed restore has operational trade-offs).
- Providing an additional recovery path if AG replication is disrupted.
Backup and restore as a continuity mechanism
Backups are often treated as “last resort,” but for some systems they are the primary continuity plan: periodic full backups, frequent log backups, and automated restores into a standby environment.
For less critical systems with higher RTO tolerance, investing in excellent backup validation (restore tests), fast storage for backup repositories, and scripted restore procedures can deliver better real-world reliability than a fragile HA configuration that nobody tests.
From a business continuity perspective, you should define:
- How quickly you can restore a full backup.
- Whether you can do point-in-time restore (PITR) and how you track the target time.
- Whether encryption, key management, and access controls allow restoring under pressure.
Real-world scenario 1: low-latency intra-site HA with strict RPO
Consider a payments service running in a single data center with strict requirements: RPO of 0 and RTO under 60 seconds for node failures. The workload is write-heavy and sensitive to latency.
A common fit is a two-node AG with synchronous commit and automatic failover, plus a witness for quorum. The key design step is to validate network RTT between nodes and ensure the secondary storage is fast enough to harden logs without introducing unacceptable commit latency.
Operationally, the team sets MultiSubnetFailover=True even though it’s a single subnet, because many organizations later expand to multi-subnet and want consistent client settings. They also configure backup preference to run log backups on the secondary to reduce primary I/O, after validating restore performance.
This scenario highlights a recurring theme: the HA feature is only part of the solution. Without validating commit latency impact and client reconnection behavior, “synchronous commit with auto failover” may not meet the service target.
Real-world scenario 2: vendor app requiring instance-level semantics
A manufacturing ERP system depends on SQL Server Agent jobs, linked servers, and instance-level configuration that the vendor expects to exist identically after failover. The vendor supports FCIs but not AG listeners for the application layer.
Here, an FCI provides the cleanest operational model. The team invests in highly redundant shared storage, validates multipath and firmware compatibility, and uses cluster validation tests as part of change management.
For DR, they add log shipping to a remote site with a runbook for manual cutover. The DR plan accepts a larger RTO because the business can operate in a degraded mode for several hours, but it demands predictable RPO achieved with frequent log backups.
This scenario demonstrates why SQL Server high availability is not “one size fits all.” The “best” design is constrained by supportability, not just technical preference.
Real-world scenario 3: multi-site design balancing HA and DR
A customer-facing SaaS platform runs SQL Server in two data centers. The requirement is rapid local failover and protection against site outage, but the sites have higher latency between them.
A common approach is a three-replica AG: two replicas in the primary site configured for synchronous commit with automatic failover, and a third replica in the DR site configured for asynchronous commit. Quorum is designed so that loss of the primary site does not strand the DR replica without a majority.
The team also configures read-only routing to direct analytics workloads to a readable secondary, but they cap the secondary’s resource usage by sizing it appropriately and ensuring reporting queries are optimized. They periodically perform planned failovers during maintenance windows, measuring actual RTO from the application perspective.
This scenario emphasizes that “DR replica exists” is not enough; you must test site-loss behavior, quorum decisions, and cutover procedures.
Build operational readiness: what must be synchronized and documented
Once you pick an HA topology, operational readiness becomes the differentiator. Many outages happen during failover because the secondary environment lacks required objects or because runbooks are incomplete.
Keep server-level objects consistent (especially with AGs)
With availability groups, databases move but the instance does not. That means logins, SQL Server Agent jobs, operators, alerts, linked servers, credentials, and SSIS catalog objects (if used) may not be present or may not match.
At minimum, document which server-level objects are required for the application to function after failover and choose a synchronization approach. Some teams script objects from source control and deploy them to all replicas. Others use periodic export/import or configuration management.
One practical example is login synchronization. SQL logins have SIDs; if they differ across replicas, users can become orphaned after failover. Many organizations standardize on Windows authentication where possible to reduce this problem, but SQL logins still appear in vendor apps and integration components.
Validate SQL Server Agent behavior
Jobs often must run only on the primary. With AGs, you can design jobs to check whether the local replica owns the primary role before running. For example, a SQL Agent job step can query sys.dm_hadr_availability_replica_states to determine role and exit when not primary.
Here is a T-SQL pattern you can use in a job step to guard primary-only execution:
sql
DECLARE @IsPrimary bit = 0;
SELECT @IsPrimary = CASE WHEN ars.role_desc = 'PRIMARY' THEN 1 ELSE 0 END
FROM sys.dm_hadr_availability_replica_states ars
JOIN sys.availability_replicas ar
ON ars.replica_id = ar.replica_id
WHERE ar.replica_server_name = @@SERVERNAME;
IF @IsPrimary = 0
BEGIN
RAISERROR('Not primary replica. Skipping job.', 10, 1);
RETURN;
END
-- Primary-only work below
This is not a replacement for proper scheduling and coordination, but it reduces the chance of duplicate processing after failover.
Document failover procedures for planned and unplanned events
Business continuity requires both a technical design and a practiced procedure. Planned failover (for patching) should be routine and low risk. Unplanned failover should be as automated as possible, but you still need a clear escalation path.
Document:
- Who is authorized to initiate manual failover.
- How to verify the new primary (or active FCI node) and confirm application health.
- How to handle partial failures (for example, primary down but storage still accessible).
- How to handle a split-site network partition where both sites are up but connectivity is impaired.
The act of writing and testing these procedures tends to reveal gaps—missing DNS permissions, firewall rules, backup path assumptions, or monitoring blind spots.
Plan capacity and performance for steady state and failover state
High availability is not only about surviving failure; it’s about ensuring performance remains acceptable after failure. Many systems are sized for normal operation but not for the “one node down” condition.
For AGs, ensure that any secondary that might become primary can handle the full write workload plus any read-only workloads you intend to keep during failure. If you use readable secondaries for reporting, plan what happens when that secondary becomes primary—do you pause reporting, or can the system tolerate reporting on the new primary?
For FCIs, the passive node must be sized to run the full instance workload after failover. This sounds obvious, but it’s frequently violated in practice when the passive node is treated as “cold standby.”
Also account for the performance impact of synchronous commit. For latency-sensitive workloads, test under load with synchronous commit enabled. The limiting factor is often log write latency on the secondary or network jitter, not raw CPU.
Handle maintenance with minimal risk: patching, upgrades, and configuration changes
Business continuity is as much about safe change as it is about failover.
With AGs, patching is often done via rolling upgrades: patch a secondary, fail over, patch the other node(s). For major SQL Server upgrades, you must follow supported upgrade paths and be careful with mixed-version scenarios. Some upgrade steps require a brief outage or a controlled failover.
With FCIs, patching is similarly rolling, but you must also consider shared storage maintenance windows and cluster updates. Coordinating with storage teams matters because storage maintenance can have the same impact as a node crash.
Regardless of topology, keep configuration drift under control. “Minor” differences in trace flags, max server memory, tempdb configuration, or security settings can become major after a failover.
PowerShell can help baseline SQL Server configuration across nodes (example uses the SqlServer module if available):
powershell
# Requires the SqlServer PowerShell module
Import-Module SqlServer
$instances = @("SQLNODE1","SQLNODE2")
foreach ($i in $instances) {
$srv = New-Object Microsoft.SqlServer.Management.Smo.Server $i
[PSCustomObject]@{
Instance = $i
Version = $srv.VersionString
MaxMemoryMB = $srv.Configuration.MaxServerMemory.RunValue
MinMemoryMB = $srv.Configuration.MinServerMemory.RunValue
MaxDOP = $srv.Configuration.MaxDegreeOfParallelism.RunValue
}
}
If you can’t standardize via automation, at least standardize via a documented baseline that is verified periodically.
Security and identity considerations that impact HA recoverability
HA incidents frequently become security incidents when access to keys, certificates, or secrets is not available in the failover environment.
If you use Transparent Data Encryption (TDE), ensure that the certificate (or asymmetric key) used to protect the database encryption key is backed up and available for restore scenarios. In AGs, TDE-protected databases replicate, but if you ever need to restore a backup to a new server, you still need the certificate and private key.
If you use SQL Server credential objects for backups to URL, or for accessing external resources, ensure these credentials exist on all relevant replicas. This is another example of server-level objects not automatically included in AG failover.
For Windows authentication and Kerberos, ensure service principal names (SPNs) are correct for the listener or FCI network name. Authentication issues after failover can look like “SQL is down” when the real problem is clients failing to authenticate.
Monitoring and alerting: measure the signals that predict an outage
Monitoring is part of high availability because it helps you detect replica health issues before they become outages and because it provides the evidence you need during an incident.
For AGs, monitor synchronization health, redo queue size, log send queue size, and replica connection state. A growing log send queue indicates the secondary is falling behind; if it’s a synchronous replica, that can eventually impact primary commit latency.
For FCIs, monitor cluster resource health, storage path redundancy, and failover events. Storage latency spikes often precede application-visible issues.
At the SQL Server layer, track key wait stats, I/O latency (especially log write latency), and error log patterns that indicate hardware or driver issues.
Even if you use a full monitoring platform, it helps to know the basic DMVs you can query during a health check. For example, to see AG replica state:
sql
SELECT
ag.name AS availability_group,
ar.replica_server_name,
ars.role_desc,
ars.connected_state_desc,
ars.synchronization_health_desc,
drs.database_id,
DB_NAME(drs.database_id) AS database_name,
drs.synchronization_state_desc,
drs.is_suspended,
drs.log_send_queue_size,
drs.redo_queue_size
FROM sys.availability_groups ag
JOIN sys.availability_replicas ar
ON ag.group_id = ar.group_id
JOIN sys.dm_hadr_availability_replica_states ars
ON ar.replica_id = ars.replica_id
JOIN sys.dm_hadr_database_replica_states drs
ON ars.replica_id = drs.replica_id
ORDER BY ag.name, ar.replica_server_name, database_name;
This query is not a monitoring system, but it’s useful for verifying health before and after planned failovers.
Validate failover regularly without turning production into a test lab
If you only fail over during an outage, your first real failover is also your first real test. That’s a risky operating model.
Planned failovers should be scheduled and measured. The goal is not to prove that SQL Server can fail over (it generally can), but to prove that your environment can: DNS updates, firewall rules, authentication, application behavior, job control, monitoring, and on-call procedures.
For AGs, practice both local automatic failover and manual failover to a remote asynchronous replica (if that’s part of your DR plan). For FCIs, practice node drain and failover during maintenance.
Include a validation checklist that focuses on user-visible outcomes: application endpoints respond, background jobs run once, reporting works where expected, and error rates remain within tolerance.
Map common continuity requirements to practical SQL Server HA patterns
By this point, the major features and operational constraints should be clear. The last step is mapping them into patterns that you can apply consistently.
For strict RPO and low RTO within a site, synchronous AGs with automatic failover are commonly used, assuming latency is acceptable and the application can connect via listener.
For instance-level continuity with minimal application changes, FCIs remain a strong option, especially for vendor apps and older architectures.
For DR across distance, asynchronous AG replicas are common, often combined with periodic DR drills and documented cutover. When AGs are not feasible, log shipping or automated restore pipelines can meet RPO targets with more manual RTO.
For protection against logical corruption and operator error, backups with tested restores and, where appropriate, delayed log shipping or immutable backup storage provide coverage that HA cannot.
As you choose a pattern, ensure that the design is coherent: the client connectivity model matches the failover model, quorum design matches site resiliency expectations, and operations teams can actually maintain the system with predictable change processes.
Implementing an AG-based design: a practical, repeatable workflow
The exact steps to implement an availability group vary by environment, but a repeatable workflow reduces mistakes.
Start by standardizing prerequisites: consistent SQL Server version and patch level across replicas, consistent collation where required, aligned instance configuration (max memory, tempdb), and verified network connectivity.
Then build the WSFC with a quorum model that matches your topology. Validate cluster networking and ensure required firewall rules are in place.
After that, create the AG and add databases. Plan for seeding: automatic seeding can simplify deployment for smaller databases, but large databases often require manual backup/restore seeding to control bandwidth and time.
Finally, configure the listener and validate application connectivity with appropriate connection string settings.
Where scripting helps, it should be used to reduce variability. For example, you can validate basic endpoint connectivity with PowerShell:
powershell
$nodes = @("SQLNODE1","SQLNODE2")
$port = 5022
# default database mirroring endpoint port is often 5022
foreach ($n in $nodes) {
Test-NetConnection -ComputerName $n -Port $port
}
The goal here is not to automate every action in this article, but to show the type of repeatable checks that make HA implementations reliable.
Implementing an FCI-based design: focus on validation and storage discipline
For FCIs, your workflow starts with storage and cluster readiness. Build the WSFC, attach shared storage, and run cluster validation. Ensure that your storage supports the required features and that the disks are correctly presented.
Then install SQL Server as an FCI, choosing the appropriate network name and IP configuration. Validate that the instance fails over cleanly between nodes and that application connectivity follows it.
After initial build, invest in operational discipline: coordinate storage maintenance, validate backups and restores, and practice patching with controlled failovers.
Because FCIs rely heavily on shared storage, keep storage change management tight. A small change in multipath policy or a firmware update can have as much impact as a SQL Server patch.
Keep the design supportable: standardize tiers rather than customizing every system
Enterprises often end up with too many bespoke HA designs. That makes operations brittle because every failover is “special.” A more sustainable approach is to define a small set of HA tiers that map to business needs.
For example, you might standardize on:
- Tier 0/1: synchronous AG with automatic failover within site, plus asynchronous DR replica.
- Tier 2: FCI within site plus log shipping to DR.
- Tier 3: standalone SQL Server with strong backups and a scripted restore plan.
The tier names don’t matter; what matters is that each tier includes not only technology choices but also operational commitments: testing frequency, monitoring depth, and the expected RTO/RPO.
When new applications arrive, you fit them to a tier rather than inventing a one-off. This reduces long-term risk and helps ensure your SQL Server high availability posture is predictable across the organization.