Log Shipping
1. Production Server send transaction log backups to 1 or more Secondary Server
2. Secondary Server’s disk number and disk letter setting must be same as Production Server
3. Optional monitor server can be setup to monitor status and send alert
4. Database’s recovery model must be full
5. SQL Server Agent service is required
6. No auto failover, we need to failover manually
7. DotNet Framework 3.5 Features is required

Prepare of Log Shipping
A. Auto-start “SQL Server Agent” at Production & Secondary Server
1. In SQL Server, “Start” > “Microsoft SQL Sever 2016” > “SQL Server Configuration Manager”
2. Select “SQL Server Services” > Right-Click “SQL Server Agent” > “Properties”
3. Select “Service” tab > change “Start Mode” from “Manual” to “Automatic”

B. Install DotNet Framework 3.5 Features
1. Insert Windows Installation Disc
2. Using Dism.exe to install, in this example CD-ROM is D:
Dism.exe /online /enable-feature /featurename:NetFX3 /All /Source:D:\sources\sxs /LimitAccess

C. Setup Shared Folder
At Shared Folder at Production Server
1. “NT Service\SQLSERVERAGENT” & “NT Service\MSSQLSERVER” have full control
2. Computer account of Secondary Server have Read ONLY

At Secondary Folder at Secondary Server
1. “NT Service\SQLSERVERAGENT” & “NT Service\MSSQLSERVER” have full control

D. Create Database for lab

CREATE DATABASE LOG_SHIPPING;
GO
USE LOG_SHIPPING;
GO
-- Create Table_A
CREATE TABLE dbo.LOG_SHIPPING_Table_A
(
ID_A int not null IDENTITY(1,1) PRIMARY KEY,
Value_A nvarchar(80) NULL
)
-- Create Table_B
CREATE TABLE dbo.LOG_SHIPPING_Table_B
(
ID_B int not null IDENTITY(1,1) PRIMARY KEY,
Value_B nvarchar(80) NULL
)
ALTER DATABASE LOG_SHIPPING SET RECOVERY FULL WITH NO_WAIT;
-- Insert data to tables
INSERT INTO dbo.LOG_SHIPPING_Table_A(Value_A) VALUES ('ability'),('able'),('about'),('above'),('accept')
INSERT INTO dbo.LOG_SHIPPING_Table_B(Value_B) VALUES ('baby'),('back'),('bad'),('bag'),('ball')

Setup Log Shipping
In Microsoft SQL Server Management Studio
1. “Start” > “Microsoft SQL Sever Tools 17” > “Microsoft SQL Server Management Studio 17”
2. Connect to Production Server > Expand “Databases”
3. Right-Click “LOG_SHIPPING” DB > “Properties”

4. Select “Transaction Log Shipping” > Click “Enable this as a primary database in a log shipping configuration”

5. Click “Backup Settings…” to go to “Transaction Log Backup Settings”
6. In “Transaction Log Backup Settings”, Enter Production Server’s Shared folder at “Network path to backup folder”.
7. In “Transaction Log Backup Settings”, Click “Schedule…” at “Backup job” to change “Daily frequency” from Occurs every 15 minutes to 1 minute.
8. Click “OK” to back to “Transaction Log Backup Settings”
9. Click “OK” to back to “Transaction Log Shipping”

10. Click “Add…” at Secondary databases” to go to “Secondary Database Settings”
11. Click “Connect…” to connection to to Secondary Server
12. In “Initialize Secondary Database” tab, Using Default Setting.
13. In “Copy Files” tab, Enter Secondary Server’s Shared folder at “Destination folder for copied files:”.
14. In “Copy Files” tab, Click “Schedule…” at “Copy job” to change “Daily frequency” from Occurs every 15 minutes to 1 minute.
15. Click “OK” to back to “Secondary Database Settings”
16. In “Restore Transaction Log” tab, select “standby mode” at “Database state when restoring backups: and Check “Disconnect users in the database when restoring backups”
17. In “Restore Transaction Log” tab, Click “Schedule…” at “Restore job” to change “Daily frequency” from Occurs every 15 minutes to 1 minute.
18. Click “OK” to back to “Transaction Log Shipping”

19. In “Transaction Log Shipping”, Click “OK” to start Log Shipping configuration

Verify Log Shipping using Microsoft SQL Server Management Studio
At Production Server
1. Log Shipping create a job named as LSBackup_<DB’s name>
2. Expand “SQL Server Agent” > double click “Job Activity Monitor” to verify the job’s status
3. Right-Click Production Server > “Reports” > “Standard Reports” > “Transaction Log Shipping Status”

At Secondary Server
1. Expand “Database” to verify that the Standby/Read-Only DB is created.
2. Log Shipping create two job named as LSCopy__<DB’s NAME> and LSRestore___<DB’s NAME>
3. Expand “SQL Server Agent” > double click “Job Activity Monitor” to verify the job’s status
4. Right-Click Production Server > “Reports” > “Standard Reports” > “Transaction Log Shipping Status”