Log Shipping Interview Questions & Answers

Question 1. Which Edition Support Log Shipping?

Answer :

• SQL Server 2005 – Enterprise, Standard, and Workgroup
• SQL Server 2008 - Enterprise, Standard, Web, and Workgroup
• SQL Server 2008 R2 - Datacenter, Enterprise, Standard, Web, and Workgroup
• SQL Server 2012 - Enterprise, Business Intelligence, Standard, and Web

Question 2. Who Can Setup Log Shipping?

Answer :

Members of Sysadmin fixed server role

Question 3. Required Recovery Models In Log Shopping?

Answer :

Full or Bulk logged Recovery Model

Question 4. Which Jobs Are Created In Log Shipping?

Answer :

On Primary server – Backup job

On Secondary Sever – Copy, Restore and Alert Jobs

Question 5. Whether Log Shipping Supports Automatic Failover?

Answer :

No, it support only manual failover.

Question 6. Login Can Be Transferred Through Manual Failover?

Answer :

No, you have to create logins and users on secondary server.

Question 7. Types Of Secondary Server?

Answer :

Restoring Mode and Stand by mode

Question 8. Difference Between Restoring Mode And Stand By Mode In Log Shopping?

Answer :

Restoring mode – Database is in restoring mode.  You can not access the database.

Stand by mode – Database is in read-only node.  You can select the data from the database.  This database can be used for reporting purpose.

Question 9. What Are The Benefits Of Log Shipping?

Answer :

• Easy to setup/configure
• Easy to maintain
• Easy to failover
• Can have multiple secondary servers
• Can be configure with mirroring or replication.

Question 10. What Are The Drawbacks Of Log Shipping ?

Answer :

• Need to manage each database separately
• Does not support automatic failover.
• Logins can not be moved automatically

Question 11. Which System Database Store Log Shipping Related Information?

Answer :

Msdb

Question 12. What Is The Difference Between Log Shipping 2000 And 2005?

Answer :

Log shipping 2005 uses link Server to communicate between Primary, Monitor, and Secondary Servers.

For 2000, Check connectivity of Primary, Monitor, and Secondary Servers.

Please note, always check remote connections are enabled on all servers.

Question 13. How To Monitor Log Shipping?

Answer :

• Reports- Transaction log shipping standard report
• 3rd party tool
• SQL Server System tables and stored procedures

Question 14. Default Time Of Log Shopping ?

Answer :

15 minutes

Question 15. Full Form Of Tuf?

Answer :

Transaction Undo file

Question 16. List Of Tables Used In Log Shipping?

Answer :

Primary server Tables:

Log_shipping_primary_databases, Log_shipping_monitor_primary, log_shipping_monitor_error_detail, log_shipping_monitor_alert

Secondary server Tables:

Log_shipping_secondary_databases, Log_shipping_monitor_secondary, Log_shipping_monitor_error_detail, Log_shipping_monitor_alert, Log_shipping_monitor_history_detail, Log_shipping_secondary

Question 17. Backup Can Be Compressed In Log Shipping?

Answer :

Yes, SQL server 2008 onwards

Question 18. How To Troubleshoot Log Shipping Issues?

Answer :

• Check error log for issue
• Check backup, copy, and restore job history.
• Check Log shipping standard report