Log Shipping In SQL Server : Day 1

                 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

Example:

1. 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

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

Operating modes

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