Log Shipping (SQL Server)
Log shipping is a solution for distributing one or more databases to one or more secondary servers. It is a disaster recovery and business continuity solution.
Tip: Log shipping is a simple and effective way to ensure data availability and protect against data loss.
How Log Shipping Works
Log shipping involves three main jobs:
- Backup Job: This job backs up the transaction log of the primary database and copies the backup files to one or more secondary servers.
- Copy Job: This job copies the transaction log backup files from a shared location to the log shipping directories on the secondary servers.
- Restore Job: This job restores the transaction log backups to the secondary databases.
Configuring Log Shipping
Log shipping can be configured using SQL Server Management Studio (SSMS) or Transact-SQL.
Using SQL Server Management Studio (SSMS)
- In SSMS, connect to the primary SQL Server instance.
- Right-click on the database you want to configure for log shipping.
- Select Tasks > Ship Transaction Logs....
- Follow the wizard to configure the primary, secondary, and monitor servers.
Using Transact-SQL
The process typically involves:
- Creating a full backup of the primary database.
- Creating transaction log backups periodically.
- Restoring the full backup and subsequent transaction log backups on the secondary server(s).
-- Example of creating a full backup
BACKUP DATABASE YourDatabaseName
TO DISK = '\\YourServer\BackupShare\YourDatabaseName_Full.bak'
WITH INIT, STATS = 10;
-- Example of creating a transaction log backup
BACKUP LOG YourDatabaseName
TO DISK = '\\YourServer\BackupShare\YourDatabaseName_Log.trn'
WITH NOINIT, STATS = 10;
Note: Ensure that the SQL Server service accounts have the necessary permissions to access the backup share.
Log Shipping Scenarios
- Disaster Recovery: Keep a hot or warm standby of your database on a secondary server.
- High Availability: While not a true high-availability solution like Always On Availability Groups, log shipping can provide a quick failover option.
- Reporting: Offload read-only reporting workloads to secondary servers.
Monitoring Log Shipping
SQL Server Agent jobs are used to automate the backup, copy, and restore processes. You can monitor the status of these jobs in SSMS.
Important: Regularly check the SQL Server Agent job history for any failures or warnings to ensure log shipping is functioning correctly.
Considerations
- Network Latency: High network latency can impact the freshness of data on secondary servers.
- Backup Retention: Define a clear backup and log file retention policy.
- Disk Space: Ensure sufficient disk space on both primary and secondary servers for backups.
- Monitoring: Implement robust monitoring to detect any issues promptly.