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 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
Log shipping 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
Log Shipping 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 shipping 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
Log Shipping relies largely 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
Log Shipping 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 read/write operations distribution across several servers.
How the Log Shipping Works in SQL Server
Log Shipping in SQL Server 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?
Log Shipping 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 (main 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 a log shipping 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 Log Shipping 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 log-shipping 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 log shipping 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 log-shipping jobs from the original primary server or change them if you wish the former primary server to become a secondary.
-- Remove log shipping 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 log shipping jobs on the primary 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. While it lacks the real-time capabilities of more sophisticated solutions, its simplicity and dependability make it an essential tool in many database environments.
FAQs
Q: What is Log Shipping in SQL Server?
Ans: Log Shipping is a disaster recovery function that automatically saves, copies, and restores 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 remains in restore mode and is read-only.
Q: How is Log Shipping different from Always On Availability Groups?
Ans: Log Shipping is asynchronous and requires human failover, whereas Always On supports synchronous replication and automatic 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, there is a temporal delay, which is unsuitable for real-time synchronization.
Q: What happens if a SQL Server Agent job fails?
Ans: You can create alerts and notifications to detect and handle job failures.
Q: Does Log Shipping work with Azure SQL?
Ans: Log Shipping in SQL Server is not natively supported in Azure SQL Database, but we can use it 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: To monitor the Log Shipping status, utilize SQL Server Management Studio or T-SQL scripts.
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