SQL Server: Replication, Log Shipping, Failover Clusters, and Always On

English version

SQL Server high availability is not one feature. It is a set of patterns that solve different failure modes.

Old study notes often group everything under “DB duplication,” but the important question is more specific: what are we trying to survive?

• A reporting workload that should not hit the primary database
• A disk or server failure
• A regional or site-level disaster
• A planned maintenance window
• A need to copy only selected tables to another location

Replication, log shipping, failover cluster instances, and Always On availability groups can all appear in that conversation, but they are not interchangeable.

The mental model

Pattern	Main purpose	Data movement	Automatic failover	Readable secondary	Shared storage
Replication	Distribute data and database objects	Snapshot, transactional, or merge replication	No, not a HA failover feature by itself	Depends on topology	No
Log shipping	Warm standby and disaster recovery	Transaction log backups copied and restored on schedule	No built-in automatic failover	Possible in standby/read-only mode between restores	No
Failover Cluster Instance	Instance-level high availability	Shared storage, one active SQL Server instance at a time	Yes, through Windows Server Failover Clustering	No separate readable copy	Yes
Always On Availability Group	Database-level high availability and DR	Log blocks sent to secondary replicas	Yes, with synchronous commit and proper configuration	Yes, readable secondaries are supported	No shared storage required for AGs

The important correction is that “copying data” and “failing over the workload” are different problems. Replication copies selected data. Log shipping keeps a secondary database close to the primary by applying log backups. A failover cluster instance moves ownership of the SQL Server instance across nodes. An availability group maintains one or more secondary replicas for a set of databases.

Replication: distribution, not automatic HA

SQL Server replication is a set of technologies for copying and distributing data and database objects from one database to another, then synchronizing changes to maintain consistency.

The three common models are:

• Snapshot replication: send data as it exists at a specific point in time.
• Transactional replication: start with a snapshot, then deliver later data modifications.
• Merge replication: allow changes at multiple nodes and reconcile them.

Replication can be useful when another system needs selected tables, reporting data, or geographically distributed copies. But it should not be treated as the main automatic failover mechanism. If the primary server fails, replication alone does not move the application workload safely to another SQL Server instance.

Log shipping: backup, copy, restore

Log shipping is easier to reason about because it is built around three jobs:

1. Back up transaction logs on the primary database.
2. Copy those log backup files to a secondary server.
3. Restore the log backups on the secondary database.

A monitor server can also be used to track whether backup, copy, and restore jobs are running within the expected threshold.

This makes log shipping a practical disaster recovery pattern. It is usually cheaper and simpler than more complex HA architectures, but it is not real-time synchronization. The secondary database lags behind the primary based on backup, copy, and restore schedules. Failover is also a manual operational procedure, not an automatic application handoff.

Log shipping requires the database to use the full or bulk-logged recovery model, because it depends on transaction log backups.

Failover Cluster Instance: instance-level HA

A SQL Server Failover Cluster Instance, or FCI, uses Windows Server Failover Clustering. Multiple nodes participate in the cluster, but the SQL Server instance runs actively on one node at a time. If the active node fails, the cluster can bring the SQL Server instance online on another node.

The key idea is instance-level failover. The SQL Server instance name, network identity, and storage ownership move together.

In traditional FCI designs, shared storage is central. That is why older notes often describe SAN storage and SAN switches. The shared storage contains the database files, and cluster ownership determines which node currently has access.

This protects against a server/node failure, but it does not create a separate readable secondary database. If the shared storage layer has a problem, the architecture needs storage-level protection as well.

Always On Availability Groups: database-level HA and readable replicas

Always On availability groups work at the database group level. A primary replica hosts the primary databases, and secondary replicas maintain copies of those databases. Depending on configuration, secondary replicas can support read-only workloads and backups.

This is why Always On availability groups are often more flexible than classic database mirroring. They can support readable secondary replicas, backup offloading, and multiple secondary replicas. They also do not require shared storage in the same way an FCI does.

Automatic failover is possible, but only under the right conditions. For example, the replicas involved need synchronous commit and a configuration that supports automatic failover. It is not enough to simply say “Always On means automatic failover.”

Choosing the right pattern

Use replication when selected data needs to be distributed.

Use log shipping when you want a lower-cost warm standby and can tolerate restore lag plus manual failover.

Use a failover cluster instance when the goal is instance-level HA and the environment can support Windows clustering and shared storage.

Use availability groups when the goal is database-level HA/DR, readable secondaries, or backup offloading without shared storage.

In production, these are often combined. For example, an FCI can protect the SQL Server instance at one site, while an availability group can provide database replicas across sites.

Korean notes

예전 Velog 메모에서는 DB 이중화를 하나의 큰 개념으로 정리했는데, 실제로는 “무엇을 보호하려는가”에 따라 선택지가 달라진다.

단순히 데이터를 복사하는 것과 장애가 났을 때 서비스가 다른 노드로 넘어가는 것은 다른 문제다.

Replication

Replication은 데이터와 database object를 다른 데이터베이스로 복사하고 동기화하는 기술이다.

종류는 크게 세 가지로 볼 수 있다.

• Snapshot replication: 특정 시점의 데이터를 전달
• Transactional replication: 초기 snapshot 이후 변경 내용을 전달
• Merge replication: 여러 위치에서 변경된 데이터를 병합

중요한 점은 replication을 자동 장애 조치 기능으로 보면 안 된다는 것이다. 필요한 테이블을 다른 서버로 보내거나 reporting workload를 분산하는 데는 유용하지만, primary 장애 시 application workload를 자동으로 넘겨주는 기능은 아니다.

Log shipping

Log shipping은 이름 그대로 transaction log backup을 다른 서버로 보내고 restore하는 방식이다.

흐름은 단순하다.

1. Primary database에서 log backup 생성
2. Secondary server로 backup file 복사
3. Secondary database에 restore

Monitor server를 두면 backup, copy, restore job이 정상적으로 수행되는지 감시할 수 있다.

장점은 구조가 비교적 단순하고 비용이 낮다는 점이다. 단점은 실시간 동기화가 아니고, 장애 조치도 자동이 아니라 운영자가 절차에 따라 수행해야 한다는 점이다.

그리고 log shipping은 transaction log backup을 사용하므로 full 또는 bulk-logged recovery model이 필요하다.

Failover Cluster Instance

Failover Cluster Instance는 SQL Server instance 단위의 HA 방식이다. Windows Server Failover Clustering 위에서 동작하고, active node가 장애를 만나면 다른 node에서 SQL Server instance를 online 상태로 올린다.

전통적인 FCI 구조에서는 shared storage가 핵심이다. 그래서 SAN storage, SAN switch 같은 구성이 자주 나온다.

다만 FCI는 별도의 readable secondary database를 만드는 구조는 아니다. 하나의 SQL Server instance가 어느 node에서 실행되는지가 바뀌는 구조에 가깝다.

Always On Availability Groups

Always On availability group은 database 단위로 primary replica와 secondary replica를 구성하는 방식이다.

장점은 secondary replica를 read-only workload나 backup offloading에 사용할 수 있다는 점이다. 공유 스토리지가 필수인 FCI와 다르게 database copy를 replica마다 유지한다.

하지만 “Always On이면 무조건 자동 failover”라고 말하면 부정확하다. 자동 failover는 synchronous commit, failover mode, cluster 구성 등 조건이 맞아야 가능하다.

정리

• Replication: 선택한 데이터 분산
• Log shipping: 저비용 warm standby와 DR
• Failover Cluster Instance: SQL Server instance 단위 HA
• Always On Availability Group: database 단위 HA/DR, readable secondary, backup offloading

DB 이중화를 공부할 때는 기능 이름보다 RPO, RTO, 읽기 분산 필요 여부, shared storage 가능 여부, 자동 failover 필요 여부를 먼저 정리하는 게 더 중요하다.

Sources

• Microsoft Learn: SQL Server Replication
• Microsoft Learn: About Log Shipping
• Microsoft Learn: Always On Availability Groups
• Microsoft Learn: Always On Failover Cluster Instances

References

1. Microsoft Learn: SQL Server Replication
2. Microsoft Learn: About Log Shipping
3. Microsoft Learn: Always On Availability Groups
4. Microsoft Learn: Always On Failover Cluster Instances