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.