SQL Server Log shipping provides a DR (disaster recovery) solution for the SQL Server databases and it can be configured at the database level. In a specific time-gap, SQL Server Transaction Log backup will be taken and copied to the destination site and will be restored. This complete activity or solution will be done by SQL Server job and each step is configured by the user. The learner may confront the difficulties in a couple of steps or while troubleshooting but for the experienced user, it is too easy to set up and handle the SQL Server log shipping set up errors.
Transaction logs contain a log of all the transactions happening in a SQL Server database. This is extremely helpful in preventing data loss in case of a system failure, in case, you are new to transaction logs in SQL Server, these logs are described in great detail here, A beginner’s guide to SQL Server transaction logs.
SQL Server instance from which the transaction log backup is shipping is called as primary and the SQL Server instance where transaction log backup is copying and restoring is called as the secondary in the SQL Server log shipping. Before beginning the setup of SQL Server log shipping, the database must be in full recovery model or Bulk-logged is the mandate thing in the SQL Server log shipping. If your database is not in the full recovery model or Bulk logged then below the T-SQL proclamation can assist with making it in Full or Bulk-logged model.
To make your database in the Full recovery model:
|
1 2 3 4 |
USE [master] GO ALTER DATABASE [publisher] SET RECOVERY FULL WITH NO_WAIT GO |
To make your database in the Bulk Logged recovery model:
|
1 2 3 4 |
USE [master] GO ALTER DATABASE [publisher] SET RECOVERY BULK_LOGGED WITH NO_WAIT GO |
Here, the publisher is the database name at the primary server end. Even the database recovery model can be changed by SSMS also with utilizing the directory as below:
Right-click on database name >> Property >> Option >> Recovery Model
If your database is not in Full or Bulk Logged recovery model, then SQL Server Log shipping step will return an error as “This database cannot be a primary database in a log shipping configuration because it uses the simple recovery model. You must use the full or bulk-logged recovery model before transaction logs can be generated.“.
Configure SQL Server Log shipping with the help of SSMS
As an initial step, the publisher database must be enabled for the SQL Server log shipping configuration. To enable the SQL Server log shipping at the primary end, open the property of the database or expand the task list of a database in the SSMS.
Click on the Ship Transaction Logs; a checkbox will be available with the title as Enable this as a primary database in a log shipping configuration. Select this checkbox to proceed ahead:
Here, we are going to setup SQL Server Log shipping for the publisher database.
Enable the database for SQL Server Log shipping configuration
As stated above, a database needs to be enabled for this SQL Server Log shipping configuration. When this checkbox is selected, the user will be allowed to schedule the SQL Server transaction log backup job.
A Backup Setting button will allow the user to set up the scheduler of SQL Server transaction log backup.
Transaction Log Backup Settings
SQL Server transaction log backup setting screen allows addressing the backup folder path. Both the Network path and the file system path should be specified in the form. This is the file system location where a backup job will put the transaction log and it will be picked up for copy and restore to the secondary server. You will notice a few more fields in the dialog box.
Delete files older than parameter is to delete the transaction log after (n) Hours/Minutes/Days.
Alert if no backup occurs within parameter is to alert if a backup does not occur within (n) Hours/Minutes/Days.
For example, 72 hours is defined for the Delete files older than the parameter value. So here, the file will be deleted from the file system after 72 hours. Make sure that the file should be copied to the destination or secondary server before deleting it. If any backup does not occur on the primary database within 1 hour, then an alert will be triggered because Alert if no backup occurs setting value is 1 hour in the above screen.
Users can have the right to change the backup job name as well if the user wants to modify it.
Backup Job Schedule
Click on the Schedule button in the Transaction Log Backup Setting form. The user will allow configuring the job properties. The scheduler can be defined to run with a polling period in a unit of minutes, hour or day. By default, the setting is to run a backup job in a time gap of 15 minutes. The job execution polling period depends on the backup plan because it is dependent on the reading of a number of transactions in (n) minutes in the database, Transaction Log backup size and very important thing RPO (Recovery Point Objective) and RTO(Recovery Time Objective).
Configure secondary server instance and databases
Once database backup is scheduled, the SQL Server transaction log backup process will be started to create one full backup of the database. Now, at the secondary or disaster recovery site, that transaction log backup needs to be restored to make it in use as primary when disaster happens.
Click on the Add button, SQL Server will ask to add the instance name and database details on which transaction log backup is going to be restored.
Secondary Database settings (Initialize the Secondary Database)
The secondary server instance field can be filled up by SQL Server instance name or SQL Server instance IP address and Port number. On a successful connection, the database name will be visible in the drop-down list of the Secondary database field. The user needs to select the database name, on which a user wants to restore the SQL Server Transaction log backup.
Before shipping the transaction log at the secondary site, a full backup of the primary database must be required to restore at a disaster site or secondary instance end. To perform the restoration of a full backup, SSMS will ask to perform restore operation with three options.
Yes, generate a full backup of the primary database and restore it into the secondary database (and create the secondary database if doesn’t exist). This option will take a full backup of the primary database on the backup folder directory by the backup job and then the backup will be copied to the secondary server and then the backup will be restored on the secondary database.
Yes, restore an existing backup of the primary database into the secondary database (and create the secondary database if it doesn’t exist). If already database full backup is generated by the user or exists, then the user will be asked to specify the network path of that full backup. Once a backup is verified, a copy job will move that backup file to the secondary site and backup will be restored by the scheduler job.
No, the secondary database is initialized. When a backup is manually restored by the user at the secondary site, then this option will be selected. When a user wants to configure SQL Server Log shipping for the large-sized database, then database administrators always use this option. But make sure that the database needs to be restored with No recovery or Standby mode.
Secondary Database Settings (Copy Files)
Copy files screen will ask the user to fill up the destination folder for copied files on the secondary or disaster site. Usually, that folder directory path should be located on the secondary server. Transaction log backup will be restored on the secondary database from this directory. You will find one more parameter, Delete copied files after. Same as the primary site, transaction log backup will be deleted from the file system after the (n) Hours/Days.
Here, 72 Hours is defined for the above SQL Server Log shipping configuration. So, the SQL Server Transaction log backup file will be deleted after 72 hours. The user will allow modifying the Copy job name as well.
Copy Job Configuration
Users can configure the copy job scheduler property as defined in the below screen. Occurs every parameter will allow defining the polling period of job execution. Here, we have used default values as 15 minutes for this setup.
Secondary Database Settings (Restore Transaction Log)
The last step at the secondary site is the configuration of the restore process of SQL Server Transaction log backup. The database must be either in No recovery mode or Standby mode when the Transaction log is shipped on the secondary. Users will be asked to select any one of them (No recovery / Standby).
Delay restoring backups at least and Alert if restore occurs within are two options in the Transaction log restore step. If a user wants to cause some delay in the restore of the database after copying, then the delay can be defined with (n) in Minutes/Hours/Days. If any backup is restored within the (n) minutes, then alert will be triggered to the configured mail list.
Here, the user is allowed to modify the Restore job name.
Restore job configuration
Users can configure the restore job scheduler property as defined in the below screen. Occurs every parameter will allow defining the polling period of job execution. Here, we have used default values as 15 minutes for this setup.
Save SQL Server Log Shipping Configuration
On save Log shipping configuration, the configuration will be saved and the job will be created at the primary and secondary end. The below screen will show you the status of each action of the Log shipping. If any of the action is failed, then Status will be failed with the error message, refer to the below screen.
On a successful set up of Log shipping for the database, the user can check the secondary database at the disaster site. In the below screen, the secondary database subscriber is in the restoring mode, which means SQL Server transaction log backup is getting restored.
After a successful setup of a log shipping, a user must monitor the complete process of Log shipping on a daily basis.
Summary
In this article, we learned how to configure the SQL Server Log Shipping feature in SQL Server and how it works with transaction log backups to handle Disaster Recovery. You can read more about this topic here, what is SQL Server log shipping.
View all posts by Jignesh Raiyani
- Page Life Expectancy (PLE) in SQL Server - July 17, 2020
- How to automate Table Partitioning in SQL Server - July 7, 2020
- Configuring SQL Server Always On Availability Groups on AWS EC2 - July 6, 2020