SQL Server log shipping is a technique which involves two or more SQL Server instances and copying (shipping) of a transaction log file from one SQL Server instance to another. The process of transferring the transaction log files and restoring is automated across the SQL Servers resulting 2 copies of the database on two separate server/locations .A log shipping session involves the following steps:
- Backing up the transaction log file on the primary SQL Server instance
- Copying the transaction log backup file across the network to one or more secondary SQL Server instances
- Restoring the transaction log backup file on the secondary SQL Server instances
- One of the common log shipping scenarios is the environment with two servers (SQLServer-1 – primary and SQLServer-2 – secondary), two SQL Server instances (SQLInstance-1 and SQLInstance-2), and one SQL Server database named SQLDB-1 with log shipping running on it
- Another common configuration is the environment with three (or more) servers (SQLServer-1 – primary, SQLServer-2 – secondary, and SQLServer-3 – secondary), three SQL Server instances (SQLInstance-1, SQLInstance-2, and SQLInstance-3), and one SQL Server database named SQLDB-1 with log shipping running on it
There are two available modes and they are related to the state in which the secondary, log shipped, SQL Server database will be:
- Standby mode – the database is available for querying and users can access it, but in read-only mode
- The database is not available only while the restore process is running
- Users can be forced to disconnect when the restore job commence
- The restore job can be delayed until all users disconnect themselves
- Restore mode – the database is not accessible and remain in restoring state
Log shipping uses Sqlmaint.exe to back up and to restore databases. When SQL Server creates a transaction log backup as part of a log shipping setup, Sqlmaint.exe connects to the monitor server and updates the log_shipping_primaries table with the last_backup_filename information. Similarly, when you run a Copy or a Restore job on a secondary server, Sqlmaint.exe connects to the monitor server and updates the log_shipping_secondaries table.
Advantages of using SQL Server log shipping:
- SQL Server log shipping is primarily used as a disaster recovery solution.
- It’s reliable and tested in details.
- It’s relatively easy to set up and maintain.
- There is a possibility for fail-over between SQL Servers; data can be copied on more than one location.
- Log shipping can be combined with other disaster recovery options such as Always On Availability Groups, database mirroring, and database replication.
- SQL Server log shipping has low cost in human and server resources.
Disadvantages in the SQL Server log shipping technique are:
- Need to manage all the databases separately.
- There isn’t possibility for an automatic fail-over
- Secondary database isn’t fully readable while the restore process is running