How to configure Log Shipping in SQL Server 2008

Log shipping is use to implement the High availability solution. By using log shipping we can synchronize standby database by primary database with the help of transaction log backup on scheduled time. Log shipping is used as recovery option in case of any disaster or loss of data
It is easy to configure, easy to maintain and standby database can be use as reporting server. It’s also easy to troubleshoot.
Log shipping is slows down the Disk I/O subsystem. It adds the addition burden to the primary server due to frequently transaction log backup to the disk.
In log shipping secondary server should be setup on different location for High availability.

Minimum requirement for configuration of log shipping

Hardware resource –> 2 physical servers (both should be on different location), RAM 512, 80GB Disk
Primary Server –> Primary database located on this server
Secondary Server –> standby database will be located on this server
Monitor server –> Optional server (generally don’t use)

Software: Windows Server 2003 sp2
MS SQL Server 2005 with sp4/2008 with sp2
.Net 2.5
Window installer 4.0

We are using SQL Server 2008 and windows Server 2003 as operating system.

Configure logshiping in sql server 2008

Before we start of Log shipping configuration, we should know what things require.

Two SQL Server Instance: one server for primary and other one standby database.
CHEIWTAP01- primary Server
Log shipping Database – LOGSHIP
CHEIWTAP02- Secondary server
Two Shared Folder backup folders one on primary server for backup and other one should be there on secondary server.
Three Jobs get created while configuring the log shipping
Backup Job –> scheduled for taking transaction log backup
Copy Job –> Scheduled for copy backup file from primary server to secondary server.
Restore Job –> Scheduled for restoring the transaction log backup on secondary database server

 

1. Right Click on database for configuring log shipping.

sql server explore step1 for configuring logshipping

2. Click on the Log shipping option window and enable the same.

sql2

3. To enable Log Shipping click on check box

To enable Log Shipping click on check box

4. Click on Backup Settings

Click on Backup Settings for Logshipping in sql server

Click on Backup Settings for Logshipping in sql server

Give the Network backup location for database, Click on OK

Network backup location

To Add secondary server click on Add.

Click on add for secondary server

Click on Connect to Connect secondary server for log shipping.

Connect to secondary server

 

Initialize the database tab click on first option which will take the backup of database and restore the same backup on secondary server
with same database name (if does not exist).

Give the path of the folder where transaction log backup should be copied (should be on secondary server)

Select database and give the restoring path of secondary server

Select the Standby mode or No recovery mode In Standby mode user can connect to the database for generating reports and retrieving data from tables where as in No recovery mode secondary database will be in restoring mode. In this mode user cant to connect to database.

Click on option Disconnect users in the database when restoring backup
This will automatically disconnect the user session while restoring the tlog backup on secondary database. Click on OK

Restore transactional log with no recovery mode or standby mode

 

If you are using monitor server for alerts then click on Use a monitor server instance and give the necessary details as we are not using monitor server in this case will click on OK.
Note : Monitor server is optional in log shipping configuration.
By clicking on Add we can add more secondary database for the primary server

Select the Standby mode or No recovery mode,In Standby mode user can connect to the database for generating reports

As message shows SUCCESS status that means we have successfully completed the Log shipping configuration.

Done with logshiing settings in sql server

Now we can check the functionality of the log shipping concept.

 

Leave a Reply

  

  

  


5 + 3 =