A lot of people have written off using Log Shipping these days and have opted to use AlwaysOn Availability Groups. Whilst advances have been made with Replicating databases for High Availability and Disaster Recovery purposes thanks to the introduction of Availability Groups, Log Shipping is still a viable solution for replicating databases mainly due to the below scenarios:

• A delayed restore on the Secondary Database required
• SQL Server Standard Edition is in use

For those of you who haven’t looked into Log Shipping, it’s essentially a mechanism that automatically performs transaction log backups of a database on one SQL instance (aka Primary Server) and then restores these backups onto databases on one or more other servers (aka Secondary Servers).

In theory you could manually run transaction log backups yourself, but predominantly these are scheduled within SQL Server Agent. You can either perform the transaction log backups natively using Microsoft SQL Server tools or use a third party backup tool, such as Redgate’s SQL Backup Pro. To delay the restores all you simply do is change the schedule for the restore job in SQL Agent on the instance where the secondary database is hosted.

Both options tend to have user-friendly GUI’s to assist with setting up Log Shipping, however if you wish to have your transaction log backups compressed and encrypted, I recommend looking to use a tool such as RedGate’s SQL Backup Pro. If you are looking to have compressed transaction log backups, I recommend that you don’t use TDE to encrypt your databases. Otherwise the Compression rate will be extremely poor.

If you are thinking of implementing Log Shipping so that you have a copy of your database(s) on a DR Server, remember the licensing implications involved. You will still need to license the server your Secondary database(s) are hosted on, unless no workloads are performed on that instance. In other words, no queries on the databases, no backups performed, DBCC not run, etc.

You can't backup Secondary Databases used for Log Shipping natively in SQL Server, so you might have thought it was a bit strange I mentioned backups in the above paragraph. However, there is a workaround for this, you can choose a maintenance window where restores are not taking place and then use a third party backup application, such as Veritas BackUp Exec, to perform an open file backup on all the database files. To restore the database elsewhere, first create a blank database with the file sizes exactly the same as the existing database files, then perform a restore writing over the top of the newly created files.

As you can see, Log Shipping is not as graceful as AlwaysOn, however there are still very valid reasons for using this older method to replicate databases, even in 2016.