Official Mafiree Blogs

Keeping you informed about Databases

 Home  /  Microsoft SQL Server  
Achieving High Availability Using Log Shipping
By Sujith   |   February 21, 2018   |   Posted in : Microsoft SQL Server

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:

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

2. 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:

1. Failover must be manually initiated on the secondary server.

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

Takeaways:

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




2 Comments


Oliver      March 15, 2018 07:41 am


Will this work on a high transactional system without delays?

Sujith      March 16, 2018 12:50 pm


Hi Oliver, We can use this on a high transactional systems. Also, sync delay depends on the time interval we configured.

Leave a Comment

Name *

Email *

Comment *



Search All Blogs



Need Support?


Solutions      Services      Resources      About Mafiree

More than 3000 servers monitored

Certified database Administrators

24X7X365 Support

Happy Clients


ENQUIRE NOW
For Sales Related Queries, Please Call Our Sales Experts at

 +91-80-41155993


Meet Mafiree on social networks!

     

PCI Certificate

Copyright © 2019 - All Rights Reserved - Mafiree