HADR - Logshipping

Servers:
Method 1 >> 1 Primary  - 1 Secondary

1. Copy the Primary Log file to Destination Shared Folder
2. Restore in the Secondary 

Method 2 >> 1 Primary - n Secondary servers

1. Copy the Primary Log file in BackupFolder .
2. From Backup folder, copy to each Secondary servers
3. Restore in individual secondary

DB Mode:-

1. Primary Server >> Full Recovery Mode
2. Secondary >> Standby or NoRecovery

Advantage :

• can define more than one server, and this server can be in a secondary role
• have a monitored server, which can record the history and the status of the backup, copy, and restore operations and, optionally, raise alerts if these operations fail to occur as per the defined schedule

What is SQL Server log shipping? by sqlshack.com

Step by Step by mssqltips

==============================================================

1         Failover Process

1.1         Check

-          What is last transaction happened

-          What is the last back up taken

-          What is the last transaction copied

-          What is the last file restored

1.2         After above Check

1)      Disable all logshipping jobs from secondary server

2)      Identify what is last transaction before crash, check whether there is any possibility to take tail log backup

3)      Manually copy the files from Network shrae path

4)      Restore all copied files one by one in the same order

5)      Now secondary server is ready, change the state of this server by using

a.       Restore database dbName with Recovery

6)      Ask the application team to change connection string

a.       Application will check whether the connection string have connection if not check other strings. Store the string in IIS session variable

7)      If everything is find reconfigure all logshipping jobs by making secondary server as primary in log shipping.

==============================================================
Errors:

Error	Solution
Error: 14420, Severity: 16, State: 1 The log shipping primary database %s.%s has backup threshold of %d minutes and has not performed a backup log  operation for %d minutes. Check agent log and logshipping monitor information.	Examine whether the data and time of both primary and secondary servers are identical or not. If yes, then go to step (b), else modify them to make it identical and then proceed to the next step. Examine whether the monitor server is offline or not. If it is offline, then change it to online mode and wait for several minutes then go for the further processing or else go to step (c). Check that the authentication status between monitor server and primary server is complete. If the backing procedure fails, then analyze the job history to examine the reason behind the error occurrence. Moreover, resolve that error and then restart your system.
Error: 14421, Severity: 16, State: 1 The log shipping secondary database %s.%s has restore threshold of %d minutes and is out of sync. No restore was performed for %d minutes. Restored latency is %d minutes. Check agent log and logshipping monitor information.	Check that the authentication status between monitor server and primary server is complete. Examine the Copy job and the network connectivity because such error occurs due to improper connectivity between servers. If the restoring procedure gets terminated, then analyze the job history to examine the reason behind the error occurrence. Moreover, resolve that error and then restart your system.
Backup Error At the primary server side, the backup message fails due to some disruption.	The error occurs because the data is never backed up completely. Therefore, perform a full backup of primary database and its transaction log files for solving the issue.
Restoration Error At the secondary side of the server, the restoration procedure terminates because of some interruption.	Apply NORECOVERY and REPLACE option, for restoring the backed up file on the secondary server.
Error Caused Due to Machine Disk At the production server side, the log shipping error continuously occurs while a user is backing up the databases or restoring it.	here exist two solutions to handle this error and they are mentioned below: You may configure the log shipping feature again on all servers that will initialize your secondary database. This solution may involve the following scenario to be followed: Terminate all operations of primary database Move to SIMPLE model of recovery Now switch to FULL recovery model Generate a backup of complete database At the end, start the Log Shipping operations