Official Mafiree Blogs

Keeping you informed about Databases

Achieving High Availability Using Log Shipping
By Sujith   |   February 21, 2018   |   Posted in : SQL Server
Here we will get the detailed explanation of how we can achieve HA using Log Shipping.

SQL Server On Linux
By Sujith   |   March 30, 2019   |   Posted in : SQL Server
SQL Server 2017 brings the best features of the Microsoft relational database engine to the enterprise Linux ecosystem, including SQL Server Agent, Azure Active Directory (Azure AD) authentication, best-in-class high availability/ disaster recovery, and unparalleled data security.

Types of SQL Server Replication
By Sujith   |   February 19, 2018   |   Posted in : SQL Server
Understanding on the types of replication in SQL Server and the type apt for your traffic pattern.

Data File Splitting in SQL Server
By Murali   |   March 15, 2021   |   Posted in : SQL Server
Huge data blocks resides under single MDF file might cause a performance of the query and impact the application services. This blog help you to understand how we can the split the single MDF file into multiple data files.

Dynamic query routing with ProxySQL
By Sabareesh   |   March 15, 2021   |   Posted in : Proxysql
How ProxySQL helps in achieving query routing without making a application changes.

 Home  /  SQL Server  
Achieving High Availability Using Log Shipping
By Sujith   |   February 21, 2018   |   Posted in : 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.




0 Comments


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