SQL Server high-availability log shipping

Original: SQL Server high-availability log shipping

I. Log shipping Overview

SQL Server uses log shipping, which automatically sends a transaction log backup of the primary server to one or more secondary databases.
An optional monitoring server that records the history and status of backup and restore operations.

Advantages
Providing Disaster recovery solutions
Supports restricted read-only access to the secondary database
Allow users to define the delay time of log sending (long delays can be useful if the data on the primary database is accidentally changed)

Terminology and Definitions
Backup jobs
The agent job of the primary server, which performs the backup operation, logs the transaction log to the local server and the monitoring server, and deletes the old records.
Copy Job
The agent job of the primary server, which copies the backup files from the primary server to the secondary server and records them on the secondary server and the monitoring server.
Restore Job
Agent jobs for secondary servers, backup files are restored to the secondary database, records are logged on the local server and the monitoring server, and old files are deleted.
.
Repeat the copy job and restore job for multiple secondary servers.

Two. Preparatory work

Two instances of the same service database (can be multiple servers within the LAN)

Primary database SQL Server R2 Mssqlservertwo

Secondary database SQL Server R2 MSSQLSERVER

The demo library Logshipping_test, primary and secondary, the same library, the same table structure and data. The same SQL login name, password

Three. Configure Log shipping

-- Step 1: Set the master server recovery mode to full backup ALTER DATABASE SET  Full

-- Step 2: Make a full backup of the primary service database BACKUP DATABASE  to  DISK = ' D:\LogShipping\LogShipping_Test.bak '  with Noformat,init

--Step 3:

Establish a shared directory on the primary server C:\LogShipping\primary has job access rights

Establish a shared directory on the secondary server C:\LogShipping\secondary has job access rights

---step 4:

Primary server Logshipping_test Library----Properties----and after the transfer of the transaction log to the primary server and the secondary database is configured successfully, set up replication and restore every two minutes to make a transfer as

After the log shipping configuration succeeds: The secondary database is identified as (standby/read-only) as

The primary database SQL Server Agent job is as follows:

Backup Job (lsbackup_logshipping_test)

Warning Job (lsalert_{computer name})

Secondary database SQL Server Agent jobs such as

Copy Job (lscopy_{computer name}_logshipping_test)

Restore Job (lsrestore_{computer name}_logshipping_test)

Warning Job (lsalert_{computer name}\mssqlservertwo)

The primary server is backed up in a 2-minute file share directory as

The secondary server replicates the file share directory for 2 minutes as

Finally: Check that log shipping is correct

To see if log shipping is working in SQL Server Agent jobs with visual operations

SQL query to see if log shipping is working properly

   -- (primary database query)        exec   Master.. Sp_help_log_shipping_monitor          exec  'logshipping_test'    --(Secondary database query)        exec  ' logshipping_test '

Four. master-Slave database manually switch configuration

Step 1: In the primary database, make it in the process of restoring

   Use Master         Backup Log [logshipping_test]  to Disk = ' C:\LogShipping\LogShipping_Test1.bak '  with

--Manually run the copy and restore jobs on the secondary database (Quick copy restore to from table)
--Manually disable backup and warning jobs on the primary database (stop backup)

Step 2: On the secondary database, use the steps of the spare parts file restore

 Use Master Restore Log [logshipping_test]  from Disk = ' C:\LogShipping\LogShipping_Test1.bak '  with

Delete data from the folder where you previously backed up and copied (d:\logshipping\primary,d:\logshipping\secondary)
Reconfigure log shipping to make the secondary database the primary database on the secondary database (Logshipping_test------------------------)
Delete Log shipping for the original primary database (Logshipping_test----------------------Transfer the transaction log, uncheck OK).

SQL Server high-availability log shipping