SQL Log Shipping

In this tutorial we will see how to perform log shipping of sql server 2008 release 2 database from one server to another server. (I.e. Primary and Standby)

Before starting the activity we need to make sure that both the databases (primary and standby) must be of same version and must on same operating system version as well.

Ports between the two databases should be open bio-directionally so we can perform DR drill if required.

Sql server agent must be running on both the servers.

To perform this activity I have created TEMP (primary) and TEMP_1 (standby) database.

When to use log shipping

You can use log shipping when you want to maintain a standby server but you do not require automatic failover.

Replication is another alternative for maintaining a standby server without automatic failover.

 Alternatively, you could use a failover clustering solution. This has the advantage of providing automatic failover. However, it is more difficult than log shipping to set up, requires more expensive licenses, and has other limitations such as on the locations of the servers.

Database mirroring provides another alternative, but it does not support multiple standby databases.

Why use SQL Backup for log shipping?

Using SQL Backup makes log shipping much simpler compared with using native log shipping.

The SQL Backup graphical user interface provides a Log Shipping wizard to assist you with setting up the log shipping. The wizard guides you through the configuration.

When you have reviewed a summary of the configuration, the Log Shipping wizard can automatically make a full database backup of the primary database and restore this backup on the standby database. This is to ensure that the standby database is consistent with the primary database and in the correct state for restoring the transaction log backups.

 The wizard then sets up SQL Server Agent jobs that use SQL Backup to perform the backup and restore operations. A SQL Server Agent job is created on the primary server to periodically back up the transaction logs and copy the backup files to the shared folder; another job is created on the standby server to periodically retrieve the backup files from the shared folder and restore the transaction logs.

As well as simplifying the configuration process and protecting against network outages, SQL Backup is up to ten times faster than native backup and restore. You can also compress the backups to save space, and encrypt the backups for added security.

After considering all above points we can start log shipping activity:-

Let’s see, how log shipping works graphically.

First of all our primary database must be in FULL or BULK LOGGED recovery model. Simple recovery model will not allow log shipping to function or converting primary server in simple log model will cause log shipping to stop functioning. So let’s convert it into FULL recovery model:-

Right click on the PRIMARY database for which you need to start log shipping. In my case it’s TEMP.

Click on properties.

Click on options in select a page tab.

Select recovery model as FULL recovery model.

Now Click on Transaction Log Shipping option under select a page.

Check the checkbox “ENABLE THIS AS A PRIMARY DATABASE IN LOG SHIPPING CONFIGURATION”.

Next step is to configure and schedule transaction log backups. Click on backup settings to configure transaction log backup.

Specify the network path of the backup folder. In my case suppose my primary server IP address is 10.111.10.111 then I can specify the network path like below and schedule the job as per requirement:-

Next step is to add a secondary (standby) server. Click on add button.

When you click on the add button, it will bring you on the below screen where we have to specify the secondary (standby) server and the database. Click on the connect button to “connect to the secondary database”.

After clicking Connect add a secondary database name in the in the “secondary database”. In my case it’s TEMP_1.

In the initialized secondary database tab select the appropriate option as per your requirement. In my case I am generating backup of primary database and restoring it into the secondary database. I am doing this since I don’t have data in GB’s. If you have huge data then you should backup the database and transaction log and restore it on the secondary database manually and select “no, the secondary database is initialized option”.

Next step, in the copy files tab, specify the destination folder where you want to copy the files coming from primary server (or database). This path usually located on the secondary server. In my case I have provided below path. After specifying the path you can schedule the copy job as per your required time interval. This usually is the same as specified in the backup job.

  

Next step, in the restore transaction log tab, select standby mode or no recovery mode. In standby mode user’ can read data from the standby database but in no recovery mode no one can read until standby database is online. After specifying you can schedule restore job as per your required time interval. Generally it is the same as backup and copy job.

   

After specifying restore mode and schedule job time interval click OK. After clicking ok you could see the server name and database name added in the secondary database tab.

Next step is configuring monitoring server, we will skip that one since it’s not mandatory. 

We have configured all the parameters. Now click on OK button on the database properties window. After clicking ok we will land in below screen. If you see “success” statuses then click on the close button.

Click Close to start the log shipping.

After closing the above window, create a test table in TEMP (primary database) database and see if it is available in the TEMP_1 (standby database) database. If yes then you have successfully configure the log shipping.

Finally in case, if your primary database is not available (failover situation) then you can switch to standby database by executing below command:-

Restore database database_name with recovery;

In my case I will execute the command:-

Restore database temp_1 with recovery;

Above statement will restore the entire transaction log backup if not restore earlier and this database will be ready to use by users.

Read Also:-