- Accidental DBA
- Posts
- SQL Server Log Shipping
It’s not glamorous, but it works
In a world where shiny new HA/DR features get all the press, there’s one SQL Server technology that just keeps doing its job.
Log Shipping has been around since SQL Server 2000. It doesn’t make headlines, it doesn’t have fancy dashboards, and it’s not going to win you any architecture awards. But for the right environment and use case, it’s rock solid and can save your bacon job in a disaster.
What is Log Shipping?
At its core, log shipping is an automated process that:
Backs up the transaction log from your primary database.
Copies that backup to one or more secondary servers.
Restores the log onto the secondary.
Do that on a schedule (15 minutes is the default) and your secondary database stays nearly up to date with production.
Requirements:
Database in FULL recovery model
SQL Agent running (Log Shipping is powered by jobs)
Shared/network path for moving the log backups
SQL Server Versions & Editions
Works in Standard, Enterprise, and Developer Editions, but not in Express.
Available since SQL Server 2000, still here in 2022.
Works in on-premises or cloud/colo VM deployments (Azure SQL VM, AWS EC2, etc.).
Why It’s Disaster Recovery, Not High Availability
HA means automatic failover with near-zero downtime. That’s not log shipping.
With log shipping:
Failover is manual - you bring the secondary online when needed, database by database
RPO (data loss) is whatever gap exists between the last backed up log and the failure.
RTO (downtime) is how long it takes to restore the last backups and bring the database online.
It’s DR because you can be up in minutes to hours, but not instantly.
Setting Up Log Shipping (The Basics)
You can walk through the wizard in SSMS, and this really the easiest way in my opinion:
Primary server: Configure the log backup job.
Secondary server: Configure the copy and restore jobs.
Schedule jobs: As a general rule I backup frequently, copy frequently (if using the copy at all) and build in a delay on the restore side. This helps give you a window to catch an “oops” moment, rather than restoring the oops to the secondary server.
Initial restore: Seed the secondary with a full backup (WITH NORECOVERY).
Monitoring & Maintenance
Log Shipping has some built-in monitoring functionality:
1. Instance level report: Server properties>>Reports>>Standard Reports>>Transaction Log Shipping Status
2. Alert jobs created for thresholds not being met. You may have to adjust these for databases that can go for long periods of time with no activity to avoid false alerts.
3. Set up alerts for job failures on the Backup, copy and restore jobs. If you backup to and restore from the same file location, you don’t need the copy job.
Failover & Failback
Failover (manual):
Stop the log shipping jobs.
Restore any remaining logs.
Bring the secondary online (RESTORE DATABASE myDatabase WITH RECOVERY).
Failback:
Rebuild the original setup from scratch, in the opposite direction. This may leave you with copies of all of the jobs on each server in a 2 node setup. Make sure to disable the jobs that don’t make sense, such as LS_restore on the Primary.
When to Use Log Shipping
Low budget DR solution
Simple read-only reporting copy (standby mode)
Decent for setting up a reporting server when near real-time data isn’t a requirement.
The Bottom line:
Log Shipping isn’t shiny, but it’s dependable. If you can live with a few minutes of potential data loss and a manual failover, it’s a cost-effective way to add resilience to your SQL Server environment.
New Pocket DBA® clients get the first month FREE!
Book a call, and mention “Newsletter”
SQL Tidbit
Put a log-shipped secondary in standby mode and you can run read-only queries between restores. Just remember: every restore will disconnect those users.
Please share with your friend that thinks replication is a DR solution. Its not.
Reply