SQL Server DBA Interview Questions and Answers – Log Shipping

This blog is about one of the High Availability feature supported by Microsoft SQL Server- Log Shipping.

1. What is High Availability in SQL Server?

A high availability solution masks the effects of a hardware or software failure and maintains the availability of applications so that the perceived downtime for users is minimized.SQL Server high-availability solutions improve the availability of servers or databases.

2. What all High Availability options are available in SQL Server?

At a high level, there are five main high availability options including a new feature set to be release with SQL Server 2012:

• Log Shipping
• Mirroring
• Replication
• Clustering
• AlwaysON

3. What is Log Shipping?

Log shipping is the process of automating the backup of database and transaction log files on a production SQL server, and then restoring them onto a standby server. But this is not all. The key feature of log shipping is that it will automatically backup transaction logs throughout the day (for whatever interval you specify) and automatically restore them on the standby server. This in effect keeps the two SQL Servers Databases in “synch”.

4. What are the Benefits of Log Shipping?

Benefits of log shipping, let’s take a more comprehensive look:

• Log shipping doesn’t require expensive hardware or software. While it is great if your standby server is similar in capacity to your production server, it is not a requirement.
• Once log shipping has been implemented, it is relatively easy to maintain.
• Assuming you have implemented log shipping correctly, it is very reliable.
• The manual failover process is generally very short, typically 15 minutes or less.
• Implementing log shipping is not technically difficult.

5. What the drawbacks of Log Shipping?

• Log shipping failover is not automatic. The DBA must still manually failover the server, which means the DBA must be present when the failover occurs.
• The users will experience some downtime. How long depends on how well you implemented log shipping, the nature of the production server failure, your network, the standby server, and the application or applications to be failed over.
• Some data can be lost, although not always. How much data is lost depends on how often you schedule log shipping and whether or not the transaction log on the failed production server is recoverable.
• The database or databases that are being failed over to the standby server cannot be used for anything else. But databases on the standby server not being used for failover can still be used normally.
• When it comes time for the actual failover, you must do one of two things to make your applications work: either rename the standby server the same name as the failed production server (and the IP address), or re-point your user’s applications to the new standby server. In some cases, neither of these options is practical.

6. In which recovery model Log Shipping can be configured?

Log Shipping works with Full and Bulk Logged recovery model.

7. Is it possible to configure Log Shipping from lower version to upper version and Vice versa?

Yes it is possible to configure Log Shipping from lower to upper version. But it is not possible vice versa.

8. What is Log Shipping Monitor Settings?

This setting enables us to setup a monitor on the Log shipping through which we can monitor the log shipping process.

9. What all jobs are created after configuring the Log Shipping?

Internally when Log Shipping is configured, there are 4 jobs created between Primary Server and Secondary Server, they are Backup Job, Copy Job, Restore Job and Alert Job

• Backup job: This job is created on Primary Server; this job takes the transaction log backup of the Database on a scheduled time
• Copy Job: This job is created on Secondary Server, this job Copies the transaction log Backup from Primary Server to the Standby/Secondary Server.
• Restore Job: This job is created on Secondary Server; this job restored the copied transaction log backup on the Secondary Server.

10. What permissions are required for shared folders on Primary and secondary for the service accounts?

• For the backup job, read/write permissions to the backup directory are required to the following:

SQL Server service account on the primary server instance.

Proxy account of the backup job. By default, this is the SQL Server Agent account on the primary server instance.

• For the copy job, read permissions to the backup directory and write permissions to the copy directory are required by the proxy account of the copy job. By default, this is the SQL Server Agent account on the secondary server instance.
• For the restore job, read/write permission to the copy directory are required by the following: The SQL Server service account on the secondary server instance. The proxy account of the restore job. By default, this is the SQL Server Agent account on the secondary server instance.
  

11. What is the copy Job in Log shipping and on which instance it’s created?

• Created in secondary server for every log shipping configuration.
• Copy the backup files from backup folder into copy folder.
• It deletes old files and old history from copy folder.
• On backup folder we have to grant read permission to secondary server account and read-write permissions on copy folder.

12. What is Monitor server?

An optional instance of SQL Server that tracks all of the details of log shipping, including:

• When the transaction log on the primary database was last backed up.
• When the secondary servers last copied and restored the backup files.
• Information about any backup failure alerts.

13. What are Log Shipping System Tables and in which database these are stored?

SQL Server Log Shipping System Tables

• log_shipping_monitor_primary – Stores one monitor record per primary database in each log shipping configuration.
• log_shipping_monitor_secondary – Stores one monitor record per secondary database in a log shipping configuration.
• log_shipping_primary_databases – Stores one record for the primary database in a log shipping configuration.
• log_shipping_secondary – Stores one record per secondary ID

14. What are Log Shipping System Store Procedures?

SQL Server will issue a series of steps to synchronize the information between primary/secondary server and the monitor server. This can be implemented by running the below undocumented log shipping stored procedures:

• sp_processlogshippingmonitorprimary
• sp_processlogshippingmonitorsecondary

15. Is it possible load balance in Log Shipping?

Yes, it’s possible in log shipping, while configuring log shipping we have the option to choose standby or no recovery mode, there we select STANDBY option to make the secondary database readonly.

16. What is STANDBY Mode on the secondary database?

We can reduce the load on our primary database by using a secondary server for read-only query processing. To do this, the secondary database must be in STANDBY mode.

17. What’s the difference between the secondary being in “Restoring” vs. “Standby”?

There are two options for configuration when we place the secondary database in standby mode:

• We can choose to have database users disconnected when transaction log backups are being restored. If we choose this option, users will be disconnected from the database each time the log shipping restore job attempts to restore a transaction log to the secondary database. Disconnection will happen on the schedule you set for the restore job.
• We can choose not to disconnect users. In this case, the restore job cannot restore transaction log backups to the secondary database if there are users connected to that database. Transaction log backups will accumulate until there are no user connections to the database.

18. What is TUF file?

TUF stands for Transaction Undo file.

19. What will happen to Log Shipping if TUF file is corrupted or lost?

The log shipping will not work. We have to setup the Log Shipping again.

20. If you create a Job on the Primary database server, will it automatically be created on the secondary server or not?

No, it will not be created on the secondary server.

21. If you create a user on the Primary database, will it automatically be created on the secondary or not?

Yes, it will be created automatically on the secondary database.

22. If you add a file on the Primary database in the same location which exists on the target, will it automatically be created on the secondary or not?

Yes, it will be created automatically on the Secondary database if the file is added to the Primary database.

23. If you add a file on the Primary database in a location which does not exist on the target, will it automatically be created on the secondary or not?

No, Log Shipping will hang. We have to manually restore the Log backup with MOVE option on the secondary database to rectify the issue.

24. Is it possible to configure Log shipping on the database server with different collation?

No.

25. Can we configure Log Shipping between the different domains?

Yes, we can configure Log Shipping on the server residing in different domains.

26. What are the store procedures to monitor Log Shipping?

We can execute the below Log Shipping System Stored Procedure to monitor log shipping and get detailed information about log shipping.

• sp_help_log_shipping_monitor – This is the how SQL Server generates the Log Shipping Status report by executing
• sys.sp_help_log_shipping_monitor – This procedure returns the log shipping status (whether it is healthy or not) as well as metadata such as primary and secondary database names, time since last backup, last backup file, last restore file, etc…
• sp_help_log_shipping_monitor_primary – returns all columns from the log_shipping_monitor_primary table for the specified primary log shipping database. It returns server name, database name, time of last backup, backup threshold, threshold alert and history retention period.
• sp_help_log_shipping_monitor_secondary – returns all columns from log_shipping_monitor_secondary table for the specified secondary log shipping database. It will return database name, server name, restore threshold, last copied file, time of last copy / restore and history retention period.

27. Can we setup multiple secondary databases in Log Shipping?

Yes, we can setup multiple secondary databases in Log Shipping.

28. Can we shrink log shipped database log file?

Yes, we can shrink the log file, but we shouldn’t use WITH TRUNCATE option. If we use this option obviously log shipping will be disturbed.

29. Can we take full backup of the log shipped database in primary server?

Yes it’s possible. We can take full backup of log shipped database and this won’t affect the log shipping.

30. What editions of SQL Server is log shipping available in?

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

31. Can we take full backup of the log shipped database in secondary server?

No, we won’t be able to execute BACKUP command against a log shipped database in secondary server.

 References: Thanks to the all the SQL Server techies who wrote and shared the valuable information in the below blogs which helped me a lot to prepare this series of Questions. Also big thanks to Microsoft Documentation which contains each and everything about their product.

http://www.mssqltips.com/sqlservertip/2799/steps-to-add-log-shipping-monitor-into-an-existing-sql-server/
http://www.sql-server-performance.com/2002/log-shipping-70/
http://www.mssqltips.com/sqlservertip/2553/different-ways-to-monitor-log-shipping-for-sql-server-databases/