Log Shipping in SQL Server 2008
Log shipping allows us to automatically send transaction log backups from a primary database server instance to one or more secondary databases on separate secondary server instances. The transaction log backups are applied to each of the secondary databases individually. There is option to include another server instance known as the monitor server which will record the history and status of backup and restore operations and be configured to raise alerts if these operations fail to occur as scheduled.
Log shipping consists of three operations:
1. Back up the transaction log at the primary server instance.
2. Copy the transaction log file to the secondary server instance.
3. Restore the log backup on the secondary server instance.
These operations can be illustrated as:
1. The primary server instance runs the backup job to back up the transaction log on the primary database. This server instance then places the log backup into a primary log-backup file, which it sends to the backup folder
2. Each of the secondary server instances runs its own copy job to copy the primary log-backup file to its own local destination folder.
3. Each secondary server instance runs its own restore job to restore the log backup from the local destination folder onto the local secondary database.
The log can be shipped to multiple secondary server instances. In such cases, operations 2 and 3 are duplicated for each secondary server instance.
A log shipping configuration does not automatically fail over from the primary server to the secondary server. If the primary database becomes unavailable, any of the secondary databases can be brought online manually.
A typical Log Shipping Scenario is shown below:
Primary Server/Database
The primary database is the database on the primary server that you want to back up to another server. All administration of the log shipping configuration through SQL Server Management Studio is performed from the primary database.
The primary database must use the full or bulk-logged recovery model. And switching the database to simple recovery model will cause log shipping to stop functioning.
Secondary Server/Databases
Secondary server/Servers will contain the warm standby copy of your primary databases which can be from several different primary servers. The secondary database must be initialized by restoring a full backup of the primary database. The restore can be completed using either the NORECOVERY or STANDBY option. This can be done manually or through SQL Server Management Studio.
Monitor Server
The optional monitor server tracks all of the details of log shipping, including:
• When the transaction log on the primary database was last backed up.
• When the secondary servers last copied and restored the backup files.
• Information about any backup failure alerts.
The monitor server should be on a server separate from the primary or secondary servers to avoid losing critical information and disrupting monitoring if the primary or secondary server is lost. A single monitor server can monitor multiple log shipping configurations. In such a case, all of the log shipping configurations that use that monitor server would share a single alert job.
Log Shipping Jobs
Log shipping involves four SQL Server Agent jobs. These jobs include the backup job, copy job, restore job, and alert job.
The user controls how frequently log backups are taken, how frequently they are copied to each secondary server, and how frequently they are applied to the secondary database. To reduce the work required to bring a secondary server online, for example after the production system fails, you can copy and restore each transaction log backup soon after it is created. Alternatively, perhaps on a second secondary server, you can delay applying transaction log backups to the secondary database. This delay provides an interval during which you can notice and respond to a failure on the primary, such as accidental deletion of critical data.
Backup Job
A backup job is created on the primary server instance for each primary database. It performs the backup operation, logs history to the local server and the monitor server, and deletes old backup files and history information. By default, this job will run every 15 minutes, but the interval is customizable. When log shipping is enabled, the SQL Server Agent job category “Log Shipping Backup” is created on the primary server instance.
SQL Server 2008 Enterprise and later versions support backup compression. When creating a log shipping configuration, you can control the backup compression behavior of log backups
Copy Job
A copy job is created on each secondary server instance in a log shipping configuration. This job copies the backup files from the primary server to a configurable destination on the secondary server and logs history on the secondary server and the monitor server. The copy job schedule, which is customizable, should approximate the backup schedule.
When log shipping is enabled, the SQL Server Agent job category “Log Shipping Copy” is created on the secondary server instance.
Restore Job
A restore job is created on the secondary server instance for each log shipping configuration. This job restores the copied backup files to the secondary databases. It logs history on the local server and the monitor server, and deletes old files and old history information. The SQL Server job category “Log Shipping Restore” is created on the secondary server instance when log shipping is enabled.
On a given secondary server instance, the restore job can be scheduled as frequently as the copy job, or the restore job can delayed. Scheduling these jobs with the same frequency keeps the secondary database as closely aligned with the primary database as possible to create a warm standby database.
Alert Job
If a monitor server is used, an alert job is created on the monitor server instance. This alert job is shared by the primary and secondary databases of all log shipping configurations using this monitor server instance. Any change to the alert job (such as rescheduling, disabling, or enabling the job) affects all databases using that monitor server. This job raises alerts (for which you must specify alert numbers) for primary and secondary databases when backup and restore operations have not completed successfully within specified thresholds. You must configure these alerts to have an operator receive notification of the log shipping failure. The SQL Server Agent job category “Log Shipping Alert” is created on the monitor server instance when log shipping is enabled.
If a monitor server is not used, alert jobs are created locally on the primary server instance and each secondary server instance. The alert job on the primary server instance raises errors when backup operations have not completed successfully within a specified threshold. The alert job on the secondary server instance raises errors when local copy and restore operations have not completed successfully within a specified threshold.
The Step by Step process of Log Shipping will be posted soon.