Log Shipping vs. Database Mirroring in SQL Server
High availability in SQL Server is a question of tradeoffs, and the tradeoffs only become visible when something goes wrong at 2 a.m. Two technologies that database administrators debated for years—log shipping and database mirroring—made very different bets about what matters most when a primary server fails.
Note: Database mirroring was deprecated in SQL Server 2012 and removed in later versions. Microsoft recommends using Always On Availability Groups for new deployments. This post covers both features for historical reference and to illuminate the design decisions that shaped what came after.
Log shipping
Log shipping works by periodically backing up the transaction log on the primary server, copying those backups to one or more secondary servers, and restoring them there. It’s a loosely coupled mechanism—deliberately simple, which is both its strength and its limitation.
Key characteristics:
| Property | Detail |
|---|---|
| Components | Primary server, secondary server(s), optional monitor server |
| Failover type | Manual — requires explicit administrator intervention |
| Data transfer unit | Transaction log backup files |
| Number of secondaries | Multiple secondary databases supported |
| Data latency | Present — depends on backup/copy/restore schedule |
| Secondary usable for reporting | Yes, in standby (read-only) mode |
| Transactions transferred | Both committed and uncommitted (full log backup) |
| Recovery models supported | Full and bulk-logged |
| Typical failover time | Can exceed 30 minutes |
| Client redirection after failover | Manual |
The simplicity pays off in flexibility. Log shipping supports multiple secondary servers, which makes it useful for distributing read workloads across geographies or maintaining copies in different data centres. The cost is that failover is entirely manual—a human has to decide the primary is gone, promote a secondary, and redirect clients—and the data on that secondary is only as fresh as the last completed restore cycle. Under a busy schedule that lag might be minutes; under a lenient one, it could be hours.
Database mirroring
Database mirroring takes a fundamentally different approach. Rather than shipping backup files on a schedule, it transfers individual transaction log records in real time from the principal server to the mirror server over a dedicated TCP endpoint.
Key characteristics:
| Property | Detail |
|---|---|
| Components | Principal server, mirror server, optional witness server |
| Failover type | Automatic (with witness) or manual |
| Data transfer unit | Individual transaction log records |
| Number of mirrors | One mirror database per principal |
| Data latency | None in synchronous (high-safety) mode |
| Mirror usable for reporting | Not directly — requires creating a database snapshot |
| Transactions transferred | Committed transactions only |
| Recovery models supported | Full recovery model only |
| Typical failover time | Under 3 seconds; usually under 10 seconds |
| Client redirection after failover | Automatic via .NET 2.0 connection string |
The optional witness server is what makes automatic failover possible. When the principal becomes unavailable, the mirror and witness together achieve quorum and promote the mirror to principal without administrator intervention. The .NET client driver handles connection redirection transparently, so application connection strings don’t need to change. From a client’s perspective, the database just reappears on a different host.
The significant constraint is that you cannot query the mirror database directly. If you need read access to the secondary for reporting, you have to create database snapshots on the mirror—which works, but adds operational overhead and means your reporting data is only as current as the last snapshot.
Side-by-side comparison
| Feature | Log Shipping | Database Mirroring |
|---|---|---|
| Failover | Manual | Automatic (with witness) |
| Failover time | 30+ minutes | < 10 seconds |
| Multiple secondaries | Yes | No (one mirror only) |
| Read access to secondary | Yes (standby mode) | Via snapshots only |
| Data latency | Yes | None (synchronous mode) |
| Recovery model required | Full or bulk-logged | Full only |
| Client redirection | Manual | Automatic (.NET) |
Which to choose
For most scenarios today, neither—Always On Availability Groups supersede both technologies and combine their strengths: automatic failover, multiple readable secondaries, and no data loss in synchronous mode.
If you maintain a legacy SQL Server environment running 2008 R2 or earlier, the choice comes down to what you can’t compromise on. If you need multiple secondaries, read access on the secondary without snapshot overhead, or support for a bulk-logged recovery model, log shipping is the pragmatic choice. If sub-10-second automatic failover is a hard requirement and a single mirror is sufficient, database mirroring delivers it cleanly.
What these two technologies reveal is how the same goal—keeping a secondary database current—leads to architectures with almost opposite characteristics depending on whether you optimise for simplicity or for speed. Always On Availability Groups eventually resolved that tension, but understanding why the tension existed in the first place makes it easier to reason about the failure modes that remain.