Log Shipping in SQL Server is a high-availability feature that allows you to automatically send transaction log backups from a primary database on one server to a secondary database on another server. It provides a way to maintain a backup server that can take over if the primary server fails. Here's an overview of how it works:
Key Components of Log Shipping:
- Primary Database: The source database where the transaction log backups are created.
- Secondary Database: The destination database that receives the log backups and applies them to maintain a synchronized copy of the primary database.
- Monitor Server (optional): A separate server that tracks the status of log shipping operations, such as backup, copy, and restore status.
Log Shipping Process:
- Backup: Transaction log backups are taken on the primary database at a scheduled interval.
- Copy: The transaction log backup files are copied to the secondary server(s).
- Restore: The transaction logs are restored to the secondary database, keeping it in a standby or no-recovery state.
Benefits of Log Shipping:
- Automatic Failover: When the primary server fails, you can manually failover to the secondary server by bringing the secondary database online.
- Disaster Recovery: Log shipping ensures minimal data loss during disaster recovery since it keeps a near-real-time copy of the primary database.
- Read-Only Access: The secondary database can be set up in read-only mode for reporting purposes while log shipping is ongoing.
Steps to Configure Log Shipping:
- Enable Full Recovery Model: Ensure the primary database is set to the Full or Bulk-Logged recovery model.
- Create a Transaction Log Backup Job: Schedule the transaction log backups on the primary server.
- Create Copy and Restore Jobs: Set up jobs on the secondary server to copy the backup files and restore them.
- Monitor Log Shipping: Use a monitor server or SQL Server Management Studio to track log shipping performance and status.