Log Shipping

• 1. 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. 

• 1. 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 fail over process is generally very short, typically 15 minutes or less.

  • Implementing log shipping is not technically difficult.

• 1. 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 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.

• 1. In which recovery model Log Shipping can be configured?

Log Shipping works with Full and Bulk Logged recovery model.

• 1. 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.

• 1. 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.

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

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.

• 1. 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.

• 1. 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.

• 1. 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

• 1. 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

  1. 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.

• 1. 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.

• 1. 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.

• 1. What is TUF file & WRK?

TUF stands for Transaction Undo file.

A .TUF file is a Microsoft SQL Server Transaction Undo file. The TUF file basically contains the information with respect to any modifications that were made as part of incomplete transactions at the time the backup were performed. A transaction undo (.TUF) file is required if a database is loaded in read-only state.

A WRK :  This Extension Is Given To A File Which is  Being Copied From Primary Backup Location to Secondary and Once Copy Process has been completed these file are renamed with .trn file. 

• 1. 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.

• 1. 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.

• 1. 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.

• 1. 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.

• 1. 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.

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

No.

• 1. Can we configure Log Shipping between the different domains?

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

• 1. 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.

• 1. Can we setup multiple secondary databases in Log Shipping?

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

• 1. 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.

• 1. 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.

• 1. 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

• 1. 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