Log Shipping on Microsoft SQL Server is a proven method that can be used to create and maintain a standby database for High Availability or Disaster Recovery. However, it also comes with real limitations around automation, usability, and troubleshooting. This blog will delve into how StandbyMP builds on SQL Server's log shipping technology to deliver an easy to implement, highly resilient, automated, and intuitive DR solution.
Overview of Log Shipping
Log shipping is a technique that involves two or more SQL Server instances and the copying of a transaction log file from a primary SQL Server database to a secondary database (usually offsite). At the secondary server, the copied transaction log backups are restored to the secondary database.
Basic steps of Log Shipping
1. Backing up the transaction log file from one or more databases on the primary SQL Server instance.
2. Copying the transaction log backup file across the network to one (or more) secondary SQL Server instances.
3. Restoring the transaction log backup file to the database on the secondary SQL Server instances.
Strengths and weaknesses of Log Shipping
It is generally agreed that log shipping is an easy to implement, reliable, and low bandwidth method for Disaster Recovery on SQL Server. However, log shipping also lacks key features such as automated failover, planned switchovers, multiple database actions, intuitive troubleshooting, or integration with scripts or APIs.
Comparison with StandbyMP
Improve creation speed and efficiency
Both Log Shipping and StandbyMP have the benefit of being easy to implement without additional requirements, such as windows failover clusters, and they support a wide range of versions.
StandbyMP goes beyond this to improve creation speed and efficiency. Unlike log shipping where each standby database must be individually created, Standby's guided process can create all of your standby databases across multiple instances with just a few clicks. Moreover, built-in pre-checks detect errors before creation, not after - saving you both time and patience!
Automate Maintenance Tasks
Disaster Recovery must assist in patching and DR testing, as well as make general maintenance tasks quick and easy.
- Zero data-loss graceful switchovers - This advanced switchover functionality allows you to switch your primary database to your secondary server and back again without any interruption of log shipping (even with multiple standby databases). This makes it perfect for the facilitation of DR Testing or Patching.
- Auto standby creation on DB creation - When a new database is added to the primary instance, it will be detected, a new standby database created, and log shipping initiated without any input from the DBA.
- One-click maintenance actions - StandbyMP facilitates testing with a one-click read-only mode, which prevents the need to rebuild the standby database with one-click resynchronization, and ensures logins are replicated across instances with login replication.
Addressing the largest weakness of Log Shipping, StandbyMP brings a very full-featured Observer that can automate failover and run specific scripts after failover. The Observer also provides real-time detailed notifications on the dashboard - to slack, and email. You can also orchestrate your failovers, failing over multiple databases at the same time or in series using the multi-database actions or observer scripts.
It's no good having tools that look good on paper, but in reality, are difficult to use, and therefore often not used.
Central Control Center brings clarity
StandbyMP's central Control Center allows you to clearly view the status of all your DR configurations across multiple instances. Actions can then be performed on one, or multiple databases with just a few clicks.
MultiPlatform Disaster Recovery
For organizations with both SQL Server and Oracle Databases (and soon PostgreSQL), you can view and control both your SQL Server and Oracle DR configurations from the single Control Center. This ensures Gold Standard Disaster Recovery is implemented across all databases and enables DBAs to work across platforms in the event of a disaster.
Log shipping's lack of automated switchover, planned switchover functionality, and efficient workflows are all fixed by StandbyMPs automation and intuitive user interface. With StandbyMP you can now easily implement and maintain highly resilient standby databases in the cloud or on-premise! Want to learn more about how StandbyMP compares to log shipping? Register for one of our 30-minute webinars.