What is SQL Server Log Shipping and How to Set it Up

SQL Server Log Shipping

Logshipping in SQL provides database redundancy to multiple servers in a cost effective way since no specialized hardware or shared storage is required. The setup is fully integrated into SQL Server Management Studio. Using Wizards you can perform a straightforward setup and easy database administration. In this post, I am going to talk about how to setup SQL Server Log Shipping.

The main advantage of Log Shipping is the ability to service “read-only queries. This enables you to do “Select’s” such as running reports on the secondary server. This however will have licensing implications. Please refer to my earlier post on Licensing SQL Server for High Availability deployments.

Users are disconnected when log
restore occurs

Unlike Database Mirroring, You can maintain multiple secondary servers and enable Read-only operations on them. Optionally, you can add a Monitor server to monitor the entire deployment setup and check for errors. It also records history and status of backup/restore jobs. You can set up  the monitoring server to raise alerts when jobs fail.

Considerations before implementing Log-Shipping

Source: https://msdn.microsoft.com/en-in/library/ms190640.aspx

• Unlike Database Mirroring, Log-shipping is not a Real-Time solution. The secondary servers will have a lag of several minutes or more between backup-restore jobs.
• Additional license is required, if you want to use any of the Secondary servers for Read-Only or reporting queries.
• For implementing Log-Shipping, “Full Recovery Model” is required, which can result in significant increase in Log File sizes. Backup maintenance jobs needs to be implemented with a proper “retention policy” for log files.
• Additional overhead for running the “Backup” “Copy” & “Restore” jobs, compared to Database mirroring which uses a lightweight log compression algorithm for its synchronization method.
• Dedicated DBA is required for the additional Administrative overhead. You need to have full-time IT staff in order to maintain the above setup.

Using “Backup Compression” is recommended in order to reduce the size of the backup files. Additional CPU overhead needs to be taken into consideration. This option is available from SQL Server 2008 and above.

In the next post, I will talk about Windows Clustering for implementing a High Availability solution.