SQL Server database log shipping disaster recovery

Https://www.cnblogs.com/Aldj/p/8605163.html

SQL Server database log shipping disaster recovery (Part1)

1. Overview

The database backup process provided by Log Shipping for SQL Server. It can copy the entire database to another server. In this case, the transaction log is also regularly sent to the backup server for data recovery, which keeps the server in a hot backup state and updates when the data changes. It also provides a monitoring server (Monitor Server) that can be used to monitor shipping signals sent at specified intervals. If the signal is not received within the specified time, the monitoring server logs the event to the event log. This mechanism makes Log Shipping often the solution used in disaster recovery planning.

two。 Configuration preparation

In order to configure the Log Shipping process, you need to make the following preparations.

Hardware preparation

The Log Shipping process involves two database (DB) servers, one is a working DB server (hereinafter referred to as machine A) and the other is a backup DB server (hereinafter referred to as machine B). The two machines must be in the same domain and connected so that they can access each other.

Software preparation

1. Before you configure the Log Shipping process, you need to confirm the following software environment and configuration:

2. Windows SQL Server Enterprise Edition should be installed on both An and B machines and patched with SP3 (recommended).

3. Configure MSSQLSERVER services and SQLSERVERAGENT services for An and B machines to start for domain accounts and set the SQLSERVERAGENT service startup mode to automatic.

4. Set the database that needs to be configured with the Log Shipping procedure to full recovery mode.

5. Create shared folders on both An and B machines, and ensure that the SQL Server service startup accounts of An and B machines have access to these two folders.

6. Verify that there are no other backup tasks and maintenance plans on the A machine database that needs to be configured with Log Shipping.

3. Configuration steps:

Master server (A machine) settin

(1) enable Log Shipping Configuration

Right-click the database to configure log transfer-> Tasks- > Ship Transaction Logs...- > Enable this as a primary database in a log shipping configuration tick

(2) Click Backup Setting

Create folders for backup, Network path and local path

How long ago to delete the log file

Warning. How long has no log backup issued a warning?

Backup Job Schedule, set detailed backup time and Sunday

Compression selection, generally choose Compress Backup, can reduce the IO of the network

Secondary server (B machine) settin

(1) in Secondary databases, click Add

(2) Connect to the secondary server

There are three tabs:

Initialize Secondary Database, initialize the secondary server with the full backup of the primary database.

Copy Files, where the primary server log files are backed up.

Restore Transaction Log, restore transaction log option.

Note: multiple secondary servers can be added.

(3) warning server settings (optional)

Through the above steps, the configuration of logshipping is basically completed.