SQL Log Shipping: Implementation, Monitoring, and Troubleshooting

SQL Server log shipping is a disaster recovery technique that involves automatically sending transaction log backups from a primary database on a primary server instance to one or more secondary databases on other SQL Server instances 1. This allows businesses to maintain high availability of their databases and recover from disasters quickly. This article will cover the implementation, monitoring, and troubleshooting of SQL log shipping, while also touching upon alternative solutions like Availability Groups 2.

Implementing SQL Log Shipping

Before implementing SQL log shipping, ensure the following prerequisites are met:

• The SQL Server versions must be 2005 or later. All SQL Server instances must have the Workgroup, Standard, or Enterprise editions installed 3.

• All the SQL Servers used in log shipping must have the same case sensitivity settings 3.

• The primary database must use the full or bulk-logged recovery model 3.

• A shared folder is required to copy T-Log Backups 3.

• Ensure the SQL Server Agent is configured to start automatically 4.

• Create a share to make the transaction log backups available to the secondary server 5.

To configure log shipping, follow these steps:

Configure the Primary Database

1. Right-click the primary database and select Properties 5.

2. Click Transaction Log Shipping under Select a page.

3. Select Enable this as a primary database in a log shipping configuration.

4. Under Transaction log backups, click Backup Settings.

5. Specify the network path to the backup folder. If the backup folder is located on the primary server, type a local path in the backup folder box 5.

6. Configure the Delete files older than and Alert if no backup occurs within parameters.

7. Customize the backup schedule as needed.

8. Choose the backup compression behavior. SQL Server supports backup compression, and you can control this behavior by choosing one of the following options: Use the default server setting, Compress backup, or Do not compress backup 5.

Configure the Secondary Database

1. Under Secondary server instances and databases, click Add 5.

2. Click Connect and connect to the instance of SQL Server that you want to use as your secondary server.

3. Choose a database from the list or type the name of the database you want to create.

4. On the Initialize Secondary database tab, choose how to initialize the secondary database. If you choose to have Management Studio initialize the secondary database from a database backup, the data and log files of the secondary database are placed in the same location as the data and log files of the master database 5. This location is likely to be different than the location of the data and log files of the primary database.

5. On the Copy Files tab, specify the destination folder for copied files. This folder is often located on the secondary server 5.

6. Customize the copy schedule as needed. This schedule should approximate the backup schedule 5.

7. On the Restore tab, choose the database state when restoring backups (No recovery mode or Standby mode). Standby mode is only an option when the version of the primary and secondary server are the same. When the major version of the secondary server is higher than the primary, only No recovery mode is allowed 5.

8. Configure the delay time, alert threshold, and restore schedule as needed.

Configure the Monitor Server

1. Under Monitor server instance, select Use a monitor server instance and click Settings 5.

2. Connect to the instance of SQL Server that you want to use as your monitor server.

3. Choose the connection method and history retention settings.

Finally, click OK on the Database Properties dialog box to begin the configuration process 5.

SQLBackupAndFTP

SQLBackupAndFTP is a tool that can simplify the setup of SQL log shipping 4. It provides a user-friendly interface for configuring backup jobs, log shipping jobs, and monitoring the process.

Monitoring SQL Log Shipping Health

Monitoring the health of SQL log shipping is crucial to ensure that the process is running smoothly and that the secondary databases are up-to-date. It is important for early issue detection and to prevent the breaking of the LSN chain 7. Here are some ways to monitor SQL log shipping health:

• Monitor Server: Use a monitor server to track the details of log shipping, including the last transaction log backup time, the last copy and restore times, and any backup failure alerts 1.

• Log Shipping Report: View the Transaction Log Shipping Status report in SQL Server Management Studio (SSMS) to see the status of log shipping activity 9.

• History Tables: Query the history tables in the msdb database to monitor the status of log shipping. These tables contain information about the backup, copy, and restore operations 8.

• Stored Procedures: Use stored procedures like sp_help_log_shipping_monitor to get information about the status of log shipping 7.

• Alerts: Configure alerts to notify you if log shipping operations fail to occur as scheduled 8.

• Restore History: Check the restore history table in the msdb database to verify log shipping synchronization. This can help identify any discrepancies between the monitor history and the actual restore operations 10.

Fixing Failed SQL Log Shipping

Log shipping can fail due to various reasons, such as network issues, disk space issues 11, or incorrect configurations 12. Here are some common log shipping failure errors and their solutions:

| Error Message |

Works cited

1. About log shipping (SQL Server) - SQL Server | Microsoft Learn, accessed January 24, 2025, https://learn.microsoft.com/en-us/sql/database-engine/log-shipping/about-log-shipping-sql-server?view=sql-server-ver16

2. log shipping what if I need transaction logs backup on my primary database. - SQLServerCentral, accessed January 24, 2025, https://www.sqlservercentral.com/forums/topic/log-shipping-what-if-i-need-transaction-logs-backup-on-my-primary-database

3. Configure SQL Server Log Shipping Step by Step - Explained - SysTools, accessed January 24, 2025, https://www.systoolsgroup.com/updates/sql-server-log-shipping-step-by-step/

4. SQL Server Log Shipping - SqlBak Blog, accessed January 24, 2025, https://sqlbak.com/blog/sql-server-log-shipping/

5. Configure Log Shipping (SQL Server) - SQL Server | Microsoft Learn, accessed January 24, 2025, https://learn.microsoft.com/en-us/sql/database-engine/log-shipping/configure-log-shipping-sql-server?view=sql-server-ver16

6. Configure Log Shipping for MSSQL Server - Rackspace Technology, accessed January 24, 2025, https://docs.rackspace.com/docs/configure-log-shipping-for-mssql-server

7. Monitor Transaction Log shipping using T-SQL and SSMS - SQLShack, accessed January 24, 2025, https://www.sqlshack.com/monitor-transaction-log-shipping-using-t-sql-and-ssms/

8. Monitor Log Shipping (Transact-SQL) - SQL Server | Microsoft Learn, accessed January 24, 2025, https://learn.microsoft.com/en-us/sql/database-engine/log-shipping/monitor-log-shipping-transact-sql?view=sql-server-ver16

9. View log shipping report (SSMS) - SQL Server - Microsoft Learn, accessed January 24, 2025, https://learn.microsoft.com/en-us/sql/database-engine/log-shipping/view-the-log-shipping-report-sql-server-management-studio?view=sql-server-ver16

10. Log Shipping In SQL Server - Rob Volk - YouTube, accessed January 24, 2025, https://www.youtube.com/watch?v=_0OHk5Vz4VQ

11. How To Fix Log Shipping Failure Errors In SQL Server | Datavail, accessed January 24, 2025, https://www.datavail.com/blog/3-common-log-shipping-failure-errors-a-dba-can-fix/

12. Logshipping failure - Forums - SQLServerCentral, accessed January 24, 2025, https://www.sqlservercentral.com/forums/topic/logshipping-failure