Log Shipping (or LS) in SQL Server is a disaster recovery solution that automates the backup, copy, and restore of transaction logs from a primary server to a secondary server. It is critical for maintaining high availability and minimizing data loss in a primary server failure.
Table of Contents
Introduction to Log Shipping in SQL Server
Log Shipping (LS) is an SQL Server feature that allows for continuous backup and restoration of transaction logs between a primary database and one or more secondary databases. This technique ensures data is synchronized regularly, making it a solid disaster recovery alternative. Log Shipping is frequently used to keep a warm standby server for high availability.
A Glimpse into the History of Log Shipping
Introduced in SQL Server 2000, Log-Shipping was created to offer a simple, built-in solution for database failover and recovery. Before this, administrators relied on manual backup and restore methods for disaster recovery. With Log Shipping, SQL Server simplified and automated these tasks, significantly improving disaster recovery mechanisms.
Advantages and Disadvantages of Log Shipping
Advantages of Log Shipping in SQL Server
A few advantages of LS are given below for more clarity & better understanding:
Simple Setup & Configuration
It is simple to build and manage, and it requires less skill than more complex high-availability systems such as Always On Availability Groups or failover clusters.
Cost-Effective
It is accessible in SQL Server Standard and Enterprise editions, giving it a cost-effective disaster recovery option that does not require additional licenses for advanced functionality.
Multiple Secondary Databases
It allows you to configure many secondary (standby) servers, providing redundancy and load distribution for read-only activities or reporting.
Asynchronous Replication
Transactions are delivered asynchronously, ensuring little impact on production systems. Therefore, the process does not affect the core database’s performance.
Read-Only Secondary Databases:
Secondary databases can be utilized for read-only operations, allowing you to delegate read-intensive tasks like reporting and analysis.
Automated backup, copy, and restore
SQL Server Agent jobs automate the log movement process by scheduling regular transaction log backups, moving them to secondary servers, & restoring them, ensuring that the procedure runs consistently with minimal manual involvement.
Data Protection
LS serves as a reliable disaster recovery solution. It lowers the chance of data loss by regularly sending transaction logs to alternative servers.
Disadvantages of Log Shipping in SQL Server
A few disadvantages of LS are given below for more clarity & better understanding:
Failover Process Manually
One major disadvantage of LS is the unavailability of automatic failover. If the primary server goes down or fails, we must manually failover and the whole recovery process will require downtime.
Time Lag Between Primary and Secondary
The replication procedure is asynchronous, so there is always a lag between the primary and backup servers. Depending on the backup interval, some recent transactions may be lost during the failover process,
Secondary databases are not fully available.
Secondary databases are in restoration mode and cannot be entirely accessible for routine operations or changes. Hence, we can use the secondary node for read-only or standby roles.
Potential for Data Loss
If the primary server fails before the most recent transaction logs are backed up, duplicated, and restored to the secondary server, those transactions will be lost.
No Automatic Monitoring for Lag
LS lacks built-in functionality to automatically monitor replication lag and guarantee that secondary databases are closely synchronized with the primary.
High Maintenance of SQL Server Agent Jobs
It relies mainly on SQL Server Agent jobs. If these jobs fail or cease unexpectedly, they might interrupt the entire LS process, necessitating close monitoring and maintenance.
Limited to SQL Server instances
It is only supported between SQL Server instances. It cannot be used with Azure SQL Database. However, it is compatible with Azure SQL Managed Instances.
No load balancing
Unlike more complex systems like Always On Availability Groups, Log Shipping does not support load balancing or the distribution of read/write operations across several servers.
How the Log Shipping Works in SQL Server
It automates the backup, copy, and restore of transaction logs from a primary database to one or more secondary databases. This enables high availability and disaster recovery. The log shipping feature is an asynchronous technique that ensures that the database on the secondary server is regularly updated with the most recent transaction logs from the primary server.
This is how the log-sending procedure works.
Critical Steps for Log Shipping
Transaction Log Backup on the Primary Server:
A transaction log backup is generated regularly on the primary SQL Server instance. This backup includes all transactions that occurred since the last log backup.
SQL Server Agent produces and controls transaction log backup jobs, ensuring that logs are backed up according to a predetermined schedule.
The log backup file is saved in a shared folder accessible to primary and secondary servers.
Copy the Transaction Log Backup to the Secondary Server(s):
An SQL Server Agent job on the secondary server moves the transaction log backup file from the shared folder to the secondary server’s local directory.
Restore the Transaction Log to the Secondary Server:
Another SQL Server Agent operation on the secondary server loads the duplicated transaction log into the secondary database.
When we start the database restoration on any server, the database on the secondary server will be in either standby mode or in NoRecovery mode
to ensure the database is ready to receive new logs while remaining inaccessible.
If the Standby option is selected, the secondary database can be accessed in read-only mode, usually for reporting purposes.
Monitor the Log Shipping Process:
SQL Server monitors the log shipping process by tracking backup, copy, and restore actions using the LS Monitor.
Alerts and notifications can be set up to notify the DBA of any problems, such as delays in backup, copy, or restoration activities.
Why do we require log shipping in SQL Server?
It provides a robust, low-cost disaster recovery solution for SQL Server environments requiring high availability. It is excellent for organizations that want to keep a standby server active without investing in complex clustering solutions or Always-On Availability Groups.
Specific permissions are required to configure log shipping
Sysadmin privileges on both the primary and auxiliary servers.
Backup Operator and db_owner roles are needed to manage the backup and restore procedures.
Prerequisites to Configure Log Shipping
1. SQL Server Enterprise or Standard Edition.
2. More than one SQL Server instance (primary and secondary).
3. To copy transaction log backups, utilize a shared network folder.
4. The primary database has the Full Recovery Model enabled.
5. There is enough disk space on both the primary and secondary servers.
6. The SQL Server Agent must be running.
Step-by-Step Guide for Configuring Log Shipping Using SSMS (GUI)
Step 1: Set the Recovery Model to Full on the primary database.
Open the SQL Server Management Studio (SSMS).
Right-click the primary database and select Properties.
In the Options tab, change the Recovery Model to Full.
ALTER DATABASE <UserDatabaseName> SET RECOVERY FULL.
Step 2: Enable Log Shipping on the Primary Database.
In SSMS, right-click the primary database and select Properties.
Click on the Transaction Log Shipping tab.
Check and enable this as the primary database in an LS setup.
Step 3: Configure the Transaction Log Backup Settings
In the Backup Settings section, select Backup Settings.
Specify the network path for storing transaction log backups (e.g., \\DBPathForLS\Backup\).
Set the backup schedule (how frequently transaction log backups will be performed).
Click OK.
Step 4: Add the Secondary Server.
Click Add under the Secondary Databases section.
Connect to the secondary server.
Configure how the secondary database should be established.
Yes, create a full backup of the primary database and restore it. Then, automatically make and restore a full backup to the secondary server.
No, the secondary database has already been initialized. Select this option if the secondary database has already been restored manually.
Configure the copy and restore settings.
Copy the settings: Select the folder to which transaction log backups will be copied on the secondary server.
Database Restore Settings: Log shipping allows us to select the secondary database recovery mode. It should be either in Standby (for read-only purposes) or No Recovery mode (unreadable).
Set the restore schedule for how frequently transaction logs will be restored to the backup server.
Step 5: Configuring Alerts and Notifications
In the Monitor Server Settings section, you can optionally provide a monitor server to monitor the status of log shipments.
Set up alarms and email notifications to let you know if the log shipping procedure fails.
Step 6: Finalize Configuration
Review all of the settings.
Click OK to finish the log shipping configuration.
Steps & Scripts to Failover in SQL Server
Log Shipping’s failover method is manual, which means that in the event of a primary server failure, you must manually bring the secondary server online. Here’s how to do a failover with T-SQL commands:
Step 1: on the Primary Server, Disable the LS Backup Job.
Firstly, turn off transaction log backups on the primary server. This will ensure no additional transaction log backups are performed, preventing further log shipping synchronization from the primary server to the secondary server.
To turn off the log shipment backup job on the primary server, use the following command:
--Disable log shipment SQL agent backup job on the primary server.
EXEC mode. dbo.sp_update_job @job_name = 'LSBackup_UserDatabaseName', @enabled = 0;Step 2: Bring the Secondary Database Online.
To back up the secondary server, you must first restore the database using recovery. This phase applies any pending transaction logs and exits the database’s restoration mode, allowing it to be utilized as the new primary.
Identify any leftover log backups.
Check to see if any outstanding transaction log backups need to be restored. If there are any pending logs, manually restore them before recovering the database.
--Restore all leftover transaction log backups.
RESTORE LOG UserDatabaseName FROM DISK = '<Log File Path + Name.trn>', NORECOVERY;To recover the secondary database, restore all log backups and bring it online using the WITH RECOVERY option.
--Bring the secondary database online.
RESTORE DATABASE UserDatabaseName WITH RECOVERY;This command completes the LS procedure and fully operationalizes the secondary database.
Step 3: Reconfigure Log Shipping Setup
If the old primary server is restored, you can set it as a secondary server for the new primary database. This entails setting up log shipments in reverse.
Configure the original primary as the new secondary.
Set up LS from the newly promoted primary server (earlier, it was secondary) to the original server.
Step 4: Update the Application Connection Strings.
Following the failover, all applications or services that use the database must be routed to the new primary server. Update the connection strings and ensure the application points to the newly promoted primary server.
Step 5: Optional but Important – Clean Up Old Jobs.
After the failover, remove any leftover LS jobs from the original primary server or change them if you wish the former primary server to become a secondary.
-- Remove LS jobs from the previous primary
EXEC msdb.dbo.sp_delete_log_shipping_primary_secondary @primary_database = 'UserDatabaseName';Summary of the Failover Steps
Disable transaction log backups on the primary server.
Restore any remaining transaction log files from the secondary server.
Recover the secondary database with the WITH RECOVERY option.
Reconfigure the LS in reverse to synchronize with the original primary server.
Update the application connection strings to point to the new primary server.
Remove or alter LS jobs on the primary server.
Significant concerns with the Los Shipping
There are a few significant concerns with the LS in SQL Server.
Here are some common primary concerns faced while LS in SQL Server:
1. The database is out-of-the-sync on the secondary server
Problem: The secondary database needs to be in sync with the primary database due to delays in log backups, copy, and restore procedures.
Cause: The backup, copy, or restoration job may have failed, or network connectivity issues may be causing delays.
Solution: Examine the job history in SQL Server Agent for errors and confirm network connectivity between the primary and secondary servers. Adjust the frequency of log backups and restores to reduce lag.
2. The Log Shipping Job Failures in SQL Servera.
Problem: SQL Server Agent jobs, such as the backup, copy, and restore jobs, fail, disturbing the LS process.
Cause: The cause of this issue depends on the task’s permissions, disk space, and misconfigurations.
Solution:
Solution: For error. We need to check the job history.
Ensure that SQL Server Agent has the required permissions and sufficient disk space for backups and restores.
Reconfigure the jobs as needed.
3. Need for disk space for transaction log backups.
Problem: The disk or shared folder containing transaction log backups needs additional capacity.
Cause: Large transaction logs or poor maintenance of old log files might cause storage concerns.
Solution: Monitor disk space utilization at regular intervals. Set the retention period to clear old log backups. Expand the disk space if needed.
4. Corruption in Transaction Log Backup Files
Problem: The transaction log backup files get corrupted and cannot be recovered on the secondary server.
Cause: A network issue or disk failure issue may cause log file corruption during file transfers.
Solution:
Run integrity tests to ensure backups are valid.
Ensure that the network and storage devices are operational.
Re-run the log backup job if necessary.
5. Log Shipping Monitor Alerts.
Problem: The LS Monitor warns you when there is too much lag between the primary and secondary servers, when a process fails, or when there is a connectivity problem.
Cause: Job delays, network difficulties, and unsuccessful log backups/restores can all result in alarms.
Solution: We must adequately Monitor the log shipping-related alerts to find the root cause of the delays, such as failed jobs or network issues.
6. Network Latency and Connectivity Issues.
Problem: Network speed should be consistent. Slow or inconsistent network connections between the primary and the secondary servers can delay the log shipping process.
Cause: Causes of transaction log copying delays include high latency, failed connections, and bandwidth restrictions.
Solution: Ensure your network connections are stable and have enough capacity to transfer log files. Consider adjusting the backup and restore times to meet network performance.
7. Primary Server Crashes
Problem: Data may be lost if the primary server fails before the most recent transaction log backup is shipped and restored.
Causes: Include hardware failures, software crashes, and incorrect backup handling.
Solution: To reduce data loss, check the log shipping process regularly and ensure that backups are performed regularly. Initiate a manual failover in a crash and bring the secondary server online.
8. Secondary Database is in ‘Restoring’ Mode
Problem: The secondary database is stuck in restore mode and cannot be brought online.
Cause: This is normal behaviour for LS, as the backup database is in continuous recovery mode until a failover occurs.
Solution: If failover is required, apply the final log backup and restore the database using RECOVERY to put it back online.
9. Permission issues
Problem: LS jobs may fail due to insufficient permissions on shared files or the SQL Server Agent.
Cause: Insufficient SQL Server service account privileges or incorrectly configured network share access permissions.
Solution: Ensure the SQL Server Agent has the rights to access the shared folder and conduct backup, copy, and restore activities.
10. Log Shipping Lag Alerts.
Problem: LS Monitor alerts when the time difference between the last log backup on the main and the latest log restore on the secondary reaches a certain level.
Cause: Delays in LS, unsuccessful jobs, or network problems.
Solution: Look into task failures or delays in the LS process. Adjust the backup and restore task frequency to reduce lag. There are a few significant concerns with the Los shipping in SQL Server.
Conclusion
Log Shipping in SQL Server provides a low-cost, primary disaster recovery option that allows enterprises to ensure high availability without requiring sophisticated setups. Although it isn’t as fast as more complex solutions, its reliability and ease of use make it a vital tool in many database setups.
FAQs
Q: What is Log Shipping in SQL Server?
Ans: It is a DR feature that automatically saves, copies, and recovers transaction logs.
Q: Which SQL Server editions enable log shipping?
Ans: Log Shipping is available in SQL Server Standard and Enterprise editions.
Q: Can Log Shipping be utilized for read-only reports?
Ans: Yes, but the secondary database is still read-only and in restore mode.
Q: What distinguishes Always On Availability Groups from Log Shipping?
Ans: Unlike Always On, which offers synchronous replication and automated failover, Log Shipping is asynchronous and necessitates human failover.
Q: Can we set up many secondary servers?
Ans: Yes, Log Shipping can handle numerous secondary servers.
Q: Is log shipping appropriate for real-time synchronization?
Ans: No; a temporal lag makes real-time synchronization inappropriate.
Q: What occurs when a SQL Server Agent job is unsuccessful?
Ans: You can create alerts and notifications to detect and handle job failures.
Q: Does Log Shipping work with Azure SQL?
Ans: Log Shipping is not natively supported in Azure SQL Database. However, it may be used with an Azure SQL Managed Instance.
Q: What recovery model is needed for log shipping?
Ans: The Full Recovery Model is necessary for Log Shipping.
Q: How do you keep track of Log Shipping health?
Ans: You can monitor the Log Shipping status using T-SQL scripts or SQL Server Management Studio.
Review the articles below also
Dbcc Freeproccache: A powerful command
Extended Events in SQL Server: A Deep Dive
Understand Deadlocks in SQL Server
SQL Server Pivot: Top 5 Concepts