SQL Server 2014 Log Shipping Deployment (1): Log shipping Overview

13.1 Log Shipping Overview

13.1.1 How log shipping works

Log shipping is a transaction log backup delivery technique. Log shipping allows you to automatically send transaction log backups from one database (the primary database on the primary server) to multiple databases (that is, secondary databases) on another server (that is, a secondary server). On the secondary server, these transaction log backups are restored to the secondary database and remain in sync with the primary database. An optional tertiary server (that is, a monitoring server) that records the history and status of transaction log backups, replication and recovery operations, and alerts when these operations do not occur as scheduled.

In log shipping, you can configure a single master server instance to transfer transaction logs to multiple secondary server instances, which can be configured with a monitoring server instance or without a monitoring server instance in a log shipping scenario. Shows the log shipping configuration with the primary server instance, three secondary server instances, and one monitoring server instance. This figure illustrates the steps performed by the backup job, the copy job, and the restore job:

650) this.width=650; "title=" image "style=" border-top:0px;border-right:0px;background-image:none;border-bottom:0 px;padding-top:0px;padding-left:0px;border-left:0px;padding-right:0px; "border=" 0 "alt=" image "src=" http:// S3.51cto.com/wyfs02/m00/57/fc/wkiom1sln2qyry_yaaew0lbzxwc139.jpg "height=" 575 "/>

Log shipping consists of four operations:

1. Back up the transaction log in the primary server instance.

2. Copy the transaction log backup file to the secondary server instance.

3. Restore the log backups in the secondary server instance.

4. The primary and secondary server instances send log shipping status and history to the monitoring server. (optional)

Logs are delivered to multiple secondary server instances, in which case the operations 2 and 3 are repeated for each secondary server instance. The log shipping configuration does not automatically fail over from the primary server to the secondary server. If the primary database becomes unavailable, you can bring any of the secondary databases online manually.

Primary server and Primary database

The primary server in the log shipping configuration is an instance of the SQL Server database engine that is the production server. The primary database is the database on the primary server that you want to back up to a different server. All log shipping configuration management can be performed in the primary database through SQL Server Management Studio.

Tips: The primary database must use the full or bulk-logged recovery model, and switching the database to the simple recovery model will cause log shipping to stop working.

Secondary server and secondary database

The secondary server in the log shipping configuration is the server where you want to keep the standby copy of the primary database. A secondary server can contain backup copies of databases from several different primary servers. For example, a department might have five servers, and each server would run a critical database system. In this case, you can use only one secondary server without having to use five separate secondary servers. Backups on five primary systems can be loaded into this backup system, reducing the amount of resources required and saving money. It is unlikely that multiple primary systems will fail at the same time. In addition, to cope with the rare cases where multiple primary systems are not available at the same time, the secondary server can have a higher specification than the primary server.

The secondary database must be initialized by means of a full backup of the primary database. You can use the NORECOVERY or STANDBY option when restoring, or through SQL Server Management Studio.

Monitoring Server

The monitoring server is optional and can track all the details of log shipping, including:

1. The time of the most recent backup of the transaction log in the primary database.

2. The last time the secondary server copied and restored the backup file.

3. Information about any backup failure alerts.

The monitoring server should be independent of the primary and secondary servers to avoid loss of critical information and interrupt monitoring due to loss of primary or secondary servers. A single monitoring server can monitor multiple log shipping configurations. In this case, all log shipping configurations that use the monitor server will share an alert job.

Tips: After you configure the monitoring server, you must remove log shipping before you can make changes to it.

The following four types of SQL Server Agent jobs are scheduled and support the operation of log shipping:

Backup Jobs

It performs a backup operation, logs the history information to the local server and the monitoring server, and deletes the old backup file and history information. When log shipping is enabled, the job category "Log shipping Backup" is created on the primary server instance.

Copy Job

It copies the backup files from the primary server to the configurable targets in the secondary server and records the history on the secondary server and the monitoring server. When log shipping is enabled on the database, the job category "Log shipping Replication" is created on each secondary server in the log shipping configuration.

Restore Job

It restores the copied backup files to the secondary database. It records the history information on the local server and the monitoring server, and deletes old and old history information. When log shipping is enabled on the database, the job category "Log shipping Restore" is created on the secondary server instance.

Alert Job

It raises an alert to the primary and secondary databases when a backup or restore operation does not complete successfully within a specified threshold. When log shipping is enabled on the database, the job category "Log shipping Alert" is created on the monitoring server instance.

Tips: For each alert, you need to specify the alert number. Also, be sure to configure the alert to notify the operator when an alert is raised.

13.1.2 support for log shipping for each version of SQL Server 2014

Feature name	Enterprise	Business Intelligence	Standard	Web	Express with Advanced Services	Express with Tools	Express
Log shipping	Support	Support	Support	Support	Only monitor servers are supported	Only monitor servers are supported	Only monitor servers are supported

This article is from the "Margin with Wish" blog, please be sure to keep this source http://281816327.blog.51cto.com/907015/1598309

SQL Server 2014 Log Shipping Deployment (1): Log shipping Overview