How to Set Up Log Shipping for SQL Server Replication

Log shipping is a reliable SQL Server disaster recovery (DR) solution that automatically backs up transaction logs from a primary database and restores them on one or more secondary servers. While log shipping is primarily used for high availability (HA) and DR, it can also complement SQL Server replication in certain scenarios.

This guide covers:
✅ What is Log Shipping?
✅ How It Works
✅ Step-by-Step Setup
✅ Monitoring & Failover
✅ Best Practices

1. What is Log Shipping?

Log shipping automates the process of:

  • Backing up transaction logs on the primary server

  • Copying them to a secondary server

  • Restoring them to keep the secondary database in sync

Key Benefits

✔ Disaster Recovery – Maintain a warm standby server
✔ Read-Only Reporting – Use the secondary for queries (with a delay)
✔ Low-Cost HA – No need for expensive clustering

2. How Log Shipping Works

The process involves three main jobs:

  1. Backup Job – Runs on the primary server to back up transaction logs.

  2. Copy Job – Moves backup files to the secondary server.

  3. Restore Job – Applies logs to the secondary database.

⚠ Note: Log shipping is not real-time—there’s always a delay (minutes to hours).

3. Step-by-Step Setup

Prerequisites

  • SQL Server Standard or Enterprise Edition

  • Primary & secondary servers connected via network

  • Sufficient disk space for log backups

Step 1: Configure the Primary Database for Log Shipping

  1. Enable Full or Bulk-Logged Recovery Model

    ALTER DATABASE [YourDB] SET RECOVERY FULL;

  2. Take a Full Backup (Initial synchronization)

    BACKUP DATABASE [YourDB] TO DISK = 'C:\Backups\YourDB_Full.bak' WITH INIT;

  3. Set Up Log Shipping in SSMS

    • Right-click YourDB → Properties → Transaction Log Shipping

    • Check "Enable this as a primary database"

    • Click "Backup Settings" and configure:

      • Backup folder location (e.g., \\PrimaryServer\LogShipping)

      • Schedule (e.g., every 15 minutes)

Step 2: Add a Secondary Server

  1. Restore the Full Backup on Secondary (WITH NORECOVERY)

    RESTORE DATABASE [YourDB] FROM DISK = 'C:\Backups\YourDB_Full.bak' 
WITH NORECOVERY, REPLACE;

  2. Configure Secondary in SSMS

    • Click "Add" under Secondary Servers

    • Enter secondary server details

    • Set Restore Mode:

      • Standby Mode (Readable with log restore delay)

      • No Recovery Mode (No user access until failover)

Step 3: Monitor Log Shipping

  • Check Status in SSMS:

    • Under Database Properties → Transaction Log Shipping

  • Query Log Shipping Tables:

    SELECT primary_database, secondary_server, last_restored_file, last_restored_date  
FROM msdb.dbo.log_shipping_monitor_secondary;

4. Failover to Secondary (Disaster Recovery)

If the primary fails:

  1. Apply Any Remaining Log Backups

    RESTORE LOG [YourDB] FROM DISK = 'C:\Backups\YourDB_Last.trn' WITH RECOVERY;

  2. Reconfigure Applications to point to the secondary server.

5. Best Practices

✔ Monitor Log Shipping Gaps (Avoid large delays)
✔ Test Failover Regularly (Ensure DR works when needed)
✔ Use Compression (Reduce backup size & transfer time)
✔ Secure Backup Location (Prevent unauthorized access)

Conclusion

Log shipping is a simple, cost-effective way to maintain a standby SQL Server database. While it doesn’t replace Always On Availability Groups for real-time HA, it’s ideal for disaster recovery and reporting offloading.

Comments

Post a Comment

Popular posts from this blog

Migrating SQL Server to Azure SQL Database: A Step-by-Step Guide

Migrating from on-premises SQL Server to Azure SQL Database offers scalability, cost-efficiency, and built-in high availability. However, the process requires careful planning to avoid downtime and compatibility issues. This guide covers: ✅ Pre-Migration Assessment ✅ Choosing the Right Azure SQL Option ✅ Step-by-Step Migration Methods ✅ Post-Migration Validation ✅ Common Pitfalls & Best Practices 1. Pre-Migration Assessment A. Check Compatibility Issues Azure SQL Database has some limitations compared to SQL Server. Verify compatibility using: 1. Microsoft Data Migration Assistant (DMA) Download & Install : Microsoft DMA Steps : Create a new assessment project. Select "Azure SQL Database" as the target. Analyze for: Unsupported features (e.g., SQL Agent Jobs , Cross-DB Queries ) Syntax differences (e.g., WITH (NOLOCK) → WITH (READUNCOMMITTED) ) 2. Azure SQL Migration Extension (VS Code) Useful for automated schema and T-SQL validat...
Read more

MS SQL Server Performance Optimization: Best Practices & Tips

Microsoft SQL Server is a powerful relational database management system, but as data grows and queries become more complex, performance can take a hit. Optimizing SQL Server performance is crucial for ensuring fast query execution, efficient resource utilization, and a seamless user experience. In this blog, we’ll explore essential MS SQL Server performance optimization techniques, covering indexing, query tuning, memory management, and more. 1. Optimize Indexing Strategy Indexes play a vital role in improving query performance by speeding up data retrieval. Here’s how to optimize them: Use the Right Index Types Clustered Index : Stores data in a sorted structure. Best for primary keys and frequently searched columns. Non-Clustered Index : Improves query performance on frequently filtered columns. Filtered Index : Ideal for queries that filter a subset of data. Avoid Over-Indexing Too many indexes slow down INSERT, UPDATE, DELETE operations. Regularly analyze index usag...
Read more

Common Causes of Slow Queries in SQL Server and How to Fix Them

Slow queries are one of the most common performance issues in SQL Server environments, often leading to frustrated users and degraded application performance. Identifying and resolving these bottlenecks is crucial for maintaining an efficient database system. In this article, we'll explore the most frequent causes of slow queries in SQL Server and provide actionable solutions to address them. 1. Missing or Inefficient Indexes Problem: One of the most common reasons for slow queries is the lack of proper indexes or the existence of inefficient indexes. Symptoms: High table scan operations (shown in execution plans) High logical reads in query statistics Slow performance on WHERE, JOIN, or ORDER BY clauses Solutions: -- Create appropriate indexes CREATE INDEX IX_Customer_LastName ON Customers(LastName) INCLUDE (FirstName, Email); -- For complex queries, consider filtered indexes CREATE INDEX IX_Orders_Recent ON Orders(OrderDate) WHERE OrderDate > DATEADD(year, -1...
Read more