Achieving High Availability Using Log Shipping

To simplify, log shipping gets a transaction from the source server and applies the same transaction to destination server instantly or at a scheduled time frame. This feature can be deployed for high availability. Say, if the source servers goes down, the destination server can be promoted as primary.

Work flow: Every SQL Server instance would have a transaction log which would log all the changes occurring in a database. A SQL Server Agent on the primary collects all the transactional log and stores as backup log at the configured location. All the log history details, log retention will be monitored and maintained by monitoring server.Copy job on the primary server copies those backup files to the secondary at the configured location. Again, all the details will get monitored by monitoring server. Restore job restores the copied backup files to the secondary databases. Restored information and log history will be maintained locally on the secondary and the monitoring servers.

Hope you understand the basic workflow of log shipping. There are two modes of secondary servers.

Standby mode – the database is available for querying and users can access but in read-only mode.

Restore mode – the database is not accessible while restoring the transactional log backup.

We can configure more than one secondary server instance for a primary instance. Mix and match of standby and restore modes are also possible. Backups logs can be compressed, but that requires additional CPU time. Most common configurations use network locations, for storing the backups in order to, access the backup folder from multiple instances.

Pros:

SQL Server log shipping is primarily used as a disaster recovery solution for a primary database.

Log shipping can be combined with other disaster recovery options such as AlwaysOn Availability Groups, database mirroring (deprecated from SQL Server 2016), and database replication.

Cons:

Failover must be manually initiated on the secondary server.

Secondary server databases are not readable while the restore process is running.

Takeaways:

Log shipping is preferable for DR, analytics and reporting applications.