Log shipping can be configured in diff domain or in diff workgroups.
There are two modes in Logshipping
Standby/Readonly mode : we can access the database for read only operations
Norecovery mode : restoring mode where we cannot access the database
For backup folder , Need Read/write permissions for Sql agent service account from secondary server.
Monitoring Log shipping :
Monitoring log shipping can be done through
1. Error logs- EXEC xp_readerrorlog 0,1,"Shipping",Null
2. System Stored procedure--> sp_help_log_shipping_monitor, sp_help_log_shipping_monitor_primary, sp_help_log_shipping_monitor_secondary
3. SSMS Report- Management Studio > Reports > Standard Reports > Transaction Log Shipping Status.
4.Querying MSDB database-->
SELECT * FROM [msdb].[dbo].[log_shipping_monitor_error_detail] WHERE [message] like '%Operating system error%'
5. Application/Event Viewer log.
How to failover in the log shipping
Steps to failover in the Log shipping.
1. Disable the Log shipping jobs on both Primary and secondary server.
Primary server :
Backup Job
Secondary server
Copy Job
Restore backup job
2. Backup the database in the primary server with tail log backup with norecovery.
This can be achieved via backup Script with NO_truncate cmd or in the GUI -backup-T log - Tail log backup option. With no recovery the db will be moved to recovery state.
3. Manually copy the tail log backup to secondary server .
4. Restore the tail log backup with recovery
The secondary DB will be restored with recovery.
5. Migrate the logins (orphaned) to server via script or manually for the missing logins.
Run the login script and sp_helprevlogin in primary server which will script out the login command and exec these scripts in secondary server . The user will be migrated once the db is restored in secondary.
Courtesy : https://www.youtube.com/watch?v=-1M_Xdrq1SY
