SQL DBA LOG SHIPPING

Published on August 17 2015 by LakshmiSaahul,Dhana Royal

Log Shipping

Introduction

Shipping of Transaction Log files from Primary server database to secondary database to get both databases in continuous Synchronization. It is a continuous process in the form of batches.

Log Shipping requires 3 Servers:

· Primary Server

· Secondary Server

· Monitor Server (Optional)

Primary Server:

Primary Server contains source database for sending the transaction logs. In Primary Server database is ONLINE state where the applications or users are connected.

Secondary Server:

Secondary Server contains destination database sending the transaction logs. In Secondary Server database is in STANDBY mode where we can read data or RESTORING mode. We can have multiple Secondary Servers.

Log shipping mainly depend on 3 jobs:

1. Backup job

Backing up transaction logs on primary server database.

This job always runs in Primary Server.

2. Copy job

Copies transaction log backup from Primary Server to Secondary Server.

Copy job runs from Secondary Server.

3. Restore job

Restore job also runs on Secondary Server to restore backups

4. Alert job

It is created in Monitor Server. If monitor server is not used it is created in primary and secondary servers. Only one alert job will be created per server.

Frequently Asked Question’s

1. What is log shipping and purpose of log shipping?

To achieve High availability, Log shipping takes backup of transaction log from a ‘primary server’ and restores in ’secondary server’. In log shipping, T.log backups are sent to one or more secondary servers and then restored to the destination servers individually. If the primary database becomes unavailable, any of the secondary database can brought into online manually.

2. What is primary server, secondary server & monitor server?

Refer the above Description for Answer

3. What are the Jobs running for log shipping and explain them?

Refer the above Description for Answer

4. What are permissions required for log shipping?

· We must have Sysadmin on each server instance to configure log shipping.

5. In log shipping which recovery models can we used?

· We can use either full or bulk logged recovery model for log shipping.

6. Where you monitor Log shipping in Primary Server?

Log shipping information is stored in MSDB tables. We can use the below tables for monitoring Log shipping information.

· Log_shipping_primary_databases: Consists of information related to Log Backup folder path and Last log Backup file name and also Database names those are involved in Log shipping.

· Log_shipping_primary_secondaries: This table contains the details of Server name and Database name.

· Log_shipping_monitor_error_detail: The table contains Errors related to Log shipping.

· Log_shipping_monitor_history_detail: To view complete step by step process related to Log shipping.

7. What are errors occurred in log shipping?

The two Errors occurred in log shipping are:

· 14420: This error occurs in Primary Server when the backup job fails.

· 14421: This error occurs in Secondary Server when the restore job fails.

8. How to monitor Log shipping in Secondary Server?

MSDB database tables contain Log shipping information.

· Log_shipping_secondary: Details of Copy Last Restore file.

· Log_shipping_secondary_databases: Details of Last Restore file.

9. How to view Log shipping status report using GUI?

Log shipping status can be viewed graphically using “Log shipping status report”

· Go to Primary Server

· Right click on Server name

· Reports

· Standard Reports

· Transaction Log shipping

Primary Server: In Primary Server the details shown are Last T-Log Backup file name and its Location so on.

Secondary Server: In Secondary Server, details like Last Copied T-Log file and Last Restored T-Log file.

10. Stored Procedures those are used for viewing Log shipping job history?

We can use the following stored procedure for current status of Log shipping

sp_help_log_shipping_monitor

11. If the primary server becomes unavailable, How to bring secondary server online?

If the primary server becomes un-available, then

· First Remove log shipping configuration from primary server

· Take the Tail Log Backup primary server

Restore Tail Log Backup in secondary database with Recovery OR use Query to bring Online “Alter database <DB Name> set online”.

· Create Logins and Users in the Secondary Server.

12. What Permissions required on Shared folders for service accounts on Primary and Secondary servers?

· For Backup job the service account requires Read/Write permissions on Backup Directory.

· For Copy job, Service account requires Read Permissions on Backup Directory and Write Permission on Copy Directory.

· Service account of Restore job requires Read/Write permissions on Copy Directory.

13. If you add a new File group in the primary server will Log shipping continue or not?

If both the servers (Primary and Secondary) contain same Disk configuration settings then Log shipping will not disturb.

Otherwise, have to restore Failed T-Log Backup manually using script with MOVE option and the remaining Log Backups will restore automatically once the failed Backup restores successfully.

14. What are Restoring modes of Log shipping?

· With No Recovery

· Standby/Read-only

15. What is TUF file?

· TUF file stands for Transaction Undo file. It will create when we configure Log shipping and only if the secondary database is in STANDBY mode only.

· The location of TUF file is under the data folder of the Secondary Server OR Copy/Restore folder of the Secondary Server.

· It contains the information regarding any modifications that were made as part of incomplete transactions at the time of Backup was performed.

· Once the TUF file gets Commit Statement for the stored records TUF file will be truncated after the Logs are applied to the Database.

16. What happens if the TUF file was deleted or corrupted during Server downtime?

If the TUF file was deleted or corrupted then Log shipping will not work. In that case we have to reconfigure the Log shipping by taking full back up in the Primary and restore it in Secondary with STANDBY.

Ø Otherwise If you have OS level Backup then we can restore that TUF file and the Process will continue automatically.

17. In what situations Log shipping may fail?

· In case someone has added Additional File group in the Primary Server.

· Changed the Recovery model from FULL/BULKLOGGED to SIMPLE.

· Someone removed the permissions on the Shared folder (Either in Primary or Secondary).

· Due to any DST activities (Date/Time) on Operating System.

· I/O and N/W issues

· If the Jobs scheduled time was same.

· If the T-Log backup files are corrupted.

· If the TUF file was Corrupted or Deleted.

· Unfortunately Sqllogship.exe was removed.

· If you set incorrect value for the Out of Sync Alert threshold.

· If the storage was full.

· Service packs/Hot fixes not applied properly

· In case MSDB database was Full.

18. Why Log Shipping Configured in STANDBY mode?

· To reduce the Downtime for the applications or users in the event of production server failure.

· To implement load balancing i.e. we can allow reading or reporting from secondary server and manipulations in primary server.

19. Can we configure Log shipping between databases of SQL 2000 and SQL 2005?

· It’s impossible to configure Log shipping between 2000 and 2005 because Log shipping architecture was changed so we can’t restore T-Log Backups from 2000 to 2005 or vice versa.

20. In case if we get Error message like “Log Backup Set Which is too late to apply to the database”. Then what’s the issue and how to solve that?

If we get Error Message like that it means someone has taken Ad-hoc backup (Manual Transnational log Backup). In this case Scheduled Log backups will disturb so that LSN mismatch may occur. So to overcome this problem Check the error log for that backup and restore it manually.

21. What happens if we take Full back up in the Primary server?

There is no affect for Log shipping if we take Full back up in primary server because full backup will not truncate the Log file.

22. What happens if we shrink the Log file on which Log shipping was configured?

If we shrink Log file with TRUNCATE option then Log shipping will disturb and we have to reconfigure it again otherwise no problem if we shrink the Log file.

23. Is that possible to take Full back up in the Secondary server of Log shipped database?

No it’s not possible to take Full Back up because Backup command will not execute in the secondary server of log shipped database.

24. If we get error like “Exclusive access could not be obtained because the database is in us e”. How to solve the issue?

We all know that the mode of database in secondary server is STANDBY and in this mode Users may connect so while Restoring Log back up Users are not disconnected. To overcome this problem Disconnect the users those are connected and then Re-run the job.

At the time of configuration we have to check the Check box indicates Disconnects Users while Restoring.

25. What is Index operation logging in log shipping?

It is fully logged operation so it will replicate on secondary as wel