Data Disaster Recovery with Log Shipping

Introduction

In SQL Server, Log Shipping can be used to implement a Data disaster recovery mechanism. Typically, log shipping is used as a disaster recovery option which is implemented at many organizations due to its simplicity among many other factors.

What is Log Shipping?

Log shipping is configured for the database from which you can have one or more disaster recovery instances of SQL Server. Log shipping is initiated by restoring a database at the secondary instance. Subsequently, log files will be backed up from the primary instance. Those files are copied to the secondary SQL server instance. Then, it will be restored to the secondary instance. This means that log shipping consists of three main asynchronous jobs such as Backup, Copy, and Restore. Since these are asynchronous jobs, you have the option of scheduling them in different schedules. Due to this option, we can configure log shipping as a data disaster recovery system.

Further, in log shipping, you have the option of configuring the secondary instance in the same primary instance. This is another reason where log shipping can be used as a data disaster recovery.

Prerequisites

Unlike many other SQL Server database recovery options, log shipping can be implemented even in the SQL Server standard version. The database should have a full or bulk-logged recovery model as log backups can be enabled only for these recovery models. If a log backup is scheduled already, that log backup has to be removed or disabled. If not log backup chain will be broken and need to re-establish the log chain by reinitializing the log shipping.

Besides, the SQL Server agent should be running as scheduling is done from the SQL Server Agent.

Configuring Log Shipping

Let us create a database with the recovery model is set to Full as shown in the below screenshot.

Next, we need to set up the primary backup option as shown in the following screenshot.

Since log shipping needs a shared folder, you need to provide a shared folder even if you are enabling Data Disaster recovery in a single server.

You need to make sure that the necessary folder permission should be provided to the folder. The account under which the SQL Server agent is executing should have read and write permission to the folder. In a Log shipping environment, you can configure only one primary server while you can have many secondary servers. Therefore, when configuring, even in the Data disaster recovery option, you need to be very careful when configuring the primary instances.

You can configure to how much old files you need to keep and when you should be alerted when there are no backups. Typically, log backup is done every fifteen minutes that can be changed to suit your environmental conditions.

Now we are ready with the primary server and let us configure the secondary server. Since we are looking at a data disaster recovery option, we can configure the secondary in the same SQL server. However, you have the option of configuring the secondary instance in a different SQL Server instance.

You can add multiple secondary instances to the list from the same screen as shown in the below screenshot.

First of all, we need to initiate a secondary database. This can be done from the same wizard as shown in the below screenshot.

Since this is a very small database, the first option can be used. In this option, from the tool, backup is created and restored as indicated in the Secondary Database. In this example, the secondary database is named as DDR_SampleDB in the same Primary SQL Server Instance.

Apart from the above option, you can restore the database from an existing backup. If you are configuring this in a high transactional system, you can restore the database during an off-peak time as the database backup and restore will have an adverse impact on the transaction system.

Next is configuring the copy file option. Even if we are configuring the primary and secondary in the same server, we need to provide a destination folder along with how long the file should be stored. Similar to the Primary backup configuration, the default schedule is fifteen minutes that can be changed to suit your environment.

Finally, we need to configure the Restore option in the Data Disaster recovery in SQL Server. It is important to note that, this is the major difference to the standard log shipping in SQL Server.

Let us look at the configuration of the restoring option as shown in the below screenshot.

First, we need to set the secondary database to Standy Mode. When it is set to Standby mode, the secondary database is a read-only database. Since it is a read-only database, users can access the data in the secondary database. However, during the restoring of log files to the database, it needs exclusive access to the database. If the exclusive permission is not available, log restoring will fail. By selecting the Disconnect users in the database when restoring backups, users will be disconnected before the restoring. This might have a negative user experience for the users who are accessing the secondary database. However, if this option is not selected, there will be a chance that restoring will not happen at all when there are a large number of users are accessing the database.

By default, there won’t be any delays when restoring the secondary database. However, to set up the Data Disaster recovery, you can set a delay to the restoring. In the above example, this value is set to three hours. This means that there are three hours of delay between the primary and the secondary servers. This configuration means, if there is an invalid data entry to the primary servers, data will be transferred only to the second instance after three hours. You have three hours to restore data from the secondary to the primary. If you think that three hours are not enough, you can disable the restoring backup job.

Verifying the Data Disaster Recovery

We can easily verify the data disaster recovery by running queries but you can verify this from the Log shipping report by right-clicking the server instance and selecting the Transaction Log Shipping Status report from the standard report option.

This report indicates you not only the restoring file but also the health of the log shipping in the server.

The report is divided into two screenshots as it is a wide report.

By looking at the above report, you can see that the latest backup file is copied to the secondary but the restoring is delayed. However, it is important to note that though you have delayed restoring, you have the latest files in the secondary instance. In case of a need, you can restore these files manually in case of an urgent requirement.

Maintenance

When it comes to maintenance, it is important to note that, you have minimum complexities when log shipping is configured as a Data Disaster Recovery option. Mostly, there will be four jobs, Backup, Copy, and Restore, Alert as shown in the below screenshot.

During any maintenance, you can disable any jobs as these are asynchronise jobs and when you enable them again, it will start from where it halted.

Conclusion

Data Disaster recovery is an important requirement for the administrators when they need to recover data. In this article, we have looked at the options of using SQL Server log shipping for the Data Disaster Recovery option. In this mechanism, resorting is delayed and secondary is accessible so that in case of a need you can recover data from the secondary instance. Further, if you are looking at historical reports, you can use the secondary instance as well. However, in the interest of the recovery system, connections will be disconnected during the restoration of the log file.

Since this is a very less complex and simple, configuration, this technique can be used very effectively.

Author
Recent Posts

Dinesh Asanka

Dinesh Asanka is MVP for SQL Server Category for last 8 years. He has been working with SQL Server for more than 15 years, written articles and coauthored books. He is a presenter at various user groups and universities. He is always available to learn and share his knowledge.

View all posts by Dinesh Asanka