Log Shipping

1. Log Shipping Configuration?

A:

Permissions

To setup a log-shipping you must have sysadmin rights on the server.

Minimum Requirements

1. SQL Server 2005 or later
2. Standard, Workgroup or Enterprise editions must be installed on all server instances involved in log shipping.
3. The servers involved in log shipping should have the same case sensitivity settings.
4. The database must use the full recovery or bulk-logged recovery model
5. A shared folder for copying T-Log backup files
6. SQL Server Agent Service must be configured properly

In addition, you should use the same version of SQL Server on both ends. It is possible to Log Ship from SQL 2005 to SQL 2008, but you can not do it the opposite way. Also, since Log Shipping will be primarly used for failover if you have the same versions on each end and there is a need to failover you at least know you are running the same version of SQL Server.

Steps to Configure Log-Shipping:

1. Make sure your database is in full or bulk-logged recovery model. You can change the database recovery model using the below query. You can check the database recovery model by querying sys.databases 

SELECT name, recovery_model_desc FROM sys.databases WHERE name = 'jugal'

USE [master]
GO
ALTER DATABASE [jugal] SET RECOVERY FULL WITH NO_WAIT
GO

2. On the primary server, right click on the database in SSMS and select Properties. Then select the Transaction Log Shipping Page. Check the "Enable this as primary database in a log shipping configuration" check box.

3. The next step is to configure and schedule a transaction log backup. Click on Backup Settings… to do this.

If you are creating backups on a network share enter the network path or for the local machine you can specify the local folder path. The backup compression feature was introduced in SQL Server 2008 Enterprise edition. While configuring log shipping, we can control the backup compression behavior of log backups by specifying the compression option. When this step is completed it will create the backup job on the Primary Server.

4. In this step we will configure the secondary instance and database. Click on the Add… button to configure the Secondary Server instance and database. You can add multiple servers if you want to setup one to many server log-shipping.

When you click the Add… button it will take you to the below screen where you have to configure the Secondary Server and database. Click on the Connect… button to connect to the secondary server. Once you connect to the secondary server you can access the three tabs as shown below.

Initialize Secondary Database tab

In this step you can specify how to create the data on the secondary server. You have three options: create a backup and restore it, use an existing backup and restore or do nothing because you have manually restored the database and have put it into the correct state to receive additional backups.

Copy Files Tab

In this tab you have to specify the path of the Destination Shared Folder where the Log Shipping Copy job will copy the T-Log backup files. This step will create the Copy job on the secondary server.

Restore Transaction Log Tab

Here you have to specify the database restoring state information and restore schedule. This will create the restore job on the secondary server.

5. In this step we will configure Log Shipping Monitoring which will notify us in case of any failure. Please note Log Shipping monitoring configuration is optional.

Click on Settings… button which will take you to the “Log Shipping Monitor Settings” screen. Click on Connect …button to setup a monitor server. Monitoring can be done from the source server, target server or a separate SQL Server instance. We can configure alerts on source / destination server if respective jobs fail. Lastly we can also configure how long job history records are retained in the MSDB database. Please note that you cannot add a monitor instance once log shipping is configured.

6. Click on the OK button to finish the Log Shipping configuration and it will show you the below screen.

2. How to Reverse Log Shipping Roles

Reversing log shipping is an often overlooked practice. When DBAs need to fail over to a secondary log shipping server, they tend to worry about getting log shipping back up later. This is especially true in the case of very large databases. If you're using log shipping as your primary disaster recovery solution and you need to fail over to the secondary log shipping server, you should get log shipping running as quickly as possible. With no disaster recovery failover in place, you might be running exposed.

Reversing log shipping is simple. It doesn’t require reinitializing the database with a full backup if performed carefully. However, it’s crucial that you remember the following:

·         You need to preserve the log sequence number (LSN) chain.

·         You need to perform the final log backup using the NORECOVERY option. Backing up the log with this option puts the database in a state that allows log backups to be restored and ensures that the database’s LSN chain doesn’t deviate.

·         The primary log shipping server must still be accessible to use this technique.

To fail over to a secondary log shipping server, follow this 10-step process:

1.    Disable all backup jobs that might back up the database on both log shipping partners.

2.    Disable the log shipping jobs.

3.    Run each log shipping job in order (i.e., backup, copy, and restore).

4.    Drop log shipping.

5.    Manually back up the log of the primary database using the NORECOVERY option. Use the command

BACKUP LOG [DatabaseName]
  TO DISK = 'BackupFilePathname'
  WITH NORECOVERY;

where DatabaseName is the name of the database whose log you want to back up and BackupFilePathname is the backup file’s pathname (e.g., Z:\SQLServerBackups\TLog.bck).

6.    Restore the log backup on the secondary database using the RECOVERY option, and bring the secondary database online. The primary and secondary databases have now switched positions.

7.    Back up the log of the new primary database (optional).

8.    Restore the log on the new secondary database using the NORECOVERY option (optional).

9.    Reconfigure log shipping.

10. Re-enable any backup jobs that were disabled.

Note that step 7 and step 8 are listed as optional because they’re not required for establishing log shipping. However, I recommend performing these steps to ensure that the log shipping configuration will proceed without any problems.

With a few minor adjustments, this 10-step process works with multiple secondary log shipping databases. You perform the same basic steps, keeping in mind that the other secondary databases will still be secondary databases after the failover. After you back up the log on the new primary database, you should use the NORECOVERY option to restore that backup on all the planned secondary databases. You can then add them as secondary databases to the new primary database.

3. Is it possible configuration of log shipping without domain?

You could try to move the logs using FTP then you don't need a domain account for the copy just FTP access

4. What is TUF?

A:

·   TOUF file is known as transaction undo file

·   This file is created when logshipping is configured in SQL Server

·   This is consists of list of uncommitted transactions while backup is going on the primary server in logshipping.

·   if this is deleted you have to reconfigure the logshipping as the secondary server.

·   This  file is located in the path where transaction log files are saved.

.TUF file is the Transaction Undo File, which is created when performing log shipping to a server in Standby mode.

When the database is in Standby mode the database recovery is done when the log is restored; and this mode also creates a file on destination server with .TUF extension which is the transaction undo file.

This file contains information on all the modifications performed at the time backup is taken.

The file plays a important role in Standby mode… the reason being very obvious while restoring the log backup all uncommited transactions are recorded to the undo file with only commited transactions written to disk which enables the users to read the database. So when we restore next transaction log backup; SQL server will fetch all the uncommited transactions from undo file and check with the new transaction log backup whether commited or not.

If found to be commited the transactions will be written to disk else it will be stored in undo file until it gets commited or rolledback.

5.  What is TUF file?

The transaction undo file contains modifications that were not committed on the source database but were in progress when the transaction log was backed up AND when the log was restored to another database, you left the database in a state that allowed addition transaction log backups to be restored to it (at some point in the future. When another transaction log is restored, SQL Server uses data from the undo file and the transaction log to continue restoring the incomplete transactions (assuming that they are were completed in the next transaction log file). Following the restore, the undo file will be re-written with any transactions that, at that point, are incomplete.

TUF file is known as "Transaction Undo File"

This file is created when LogShipping is configured in SQL Server.

This file consists of list of uncommitted transactions while backup is going on the primary server in Log Shipping.

If this file is deleted you have to reconfigure the LogShipping as the secondary server.

This file is located in the path where transaction log files are saved.

• TUF File: It’s Transaction Undo File. It Generated only when you Have Configured Log Shipping With Stand by Option. Since In Stand by Log Shipping Secondary Database is Available to User. So TUF Keep Pending Transaction Which are in Log File Came from Primary So That when Next Log Backup Will Come From Primary They Can Be Synchronized At Secondary.
• .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. 

6. Today I faced an issue where one of secondary server box is now not available due to some circumstances, now I have to delete this secondary server Name and Database entry from primary server’s database. If we go through log shipping wizard from Database property page and try to remove secondary server it will ask to connect secondary server but in my case secondary server is now not available with us. To resolve this, here is a script to delete secondary server entry from primary server’s database is: ( in this case there is no need to connect secondary server)

EXEC Master.dbo.sp_delete_log_shipping_primary_secondary

@primary_database = N’VirendraTest’,
@secondary_server =  N’VIRENDRA_PC’,
@primary_database =N’LSVirendraTest’;

GO

7.My log file is corrupted in Log shipping..?

If your log file is corrupted in a log shipping setup, it's crucial to address this issue promptly to ensure data integrity and the continuity of your log shipping process. Here are steps you can take to handle a corrupted log file in log shipping:

1.       Identify the Cause:

·       Determine the cause of the log file corruption. Common causes include disk failures, storage issues, hardware problems, or unexpected interruptions during log file copying or applying.

2.       Take Immediate Action:

·       Stop the log shipping restore job to prevent further attempts to apply the corrupted log file.

·       Ensure that backups of the primary database are intact and available for recovery.

3.       Restore Last Good Log Backup:

·       Restore the last good log backup on the secondary server to restore the database to a consistent state before the corruption occurred.

·       If the last good log backup is also corrupted, consider using an earlier backup that is known to be valid, although this may result in data loss.

4.       Fix the Corrupted Log File:

·       If possible, attempt to repair the corrupted log file using DBCC CHECKDB with the REPAIR_ALLOW_DATA_LOSS option. Note that this should be done cautiously as it can result in data loss.

·       If repair is not feasible, consider restoring the log file from a known good backup or creating a new log file.

5.       Resume Log Shipping:

·       Once the database is restored to a consistent state without the corrupted log file, reconfigure log shipping by setting up a new log shipping plan.

·       Ensure that log backups are taken regularly on the primary database and successfully applied to the secondary databases to maintain synchronization.

6.       Monitor for Issues:

·       Monitor the log shipping process and database integrity regularly to detect any further issues or inconsistencies.

·       Set up alerts or notifications to be notified of any log shipping failures or errors promptly.

7.       Review and Improve Backup and Recovery Strategies:

·       Review your backup and recovery strategies, including log backup frequency, retention policies, and backup verification procedures, to prevent future log file corruptions and ensure data protection.