Log Shipping was introduced in SQL Server 2000 , The main advantage of this is maintain multiple servers as standby server,robust ,we can decide the time interval.

Requirement for Implementing Log Shipping

Both the All the SQL Server Services should be started with the same domain User Name,if  individual server then Start them with Same User Name and Password.Folder level Sharing should be enabled between the Server, SQL Server Agent Services should be started . SQL Server Express Edition is not Possible For Implementing Log Shipping because SQL Server Agent Services are not available.User must be Sysadmin to configure Log Shipping

Internally  when Log Shipping is configured,there are 4 jobs create between Primary Server and Secondary Server, they are  Backup Job,Copy Job,Restore Job Alert Job

Backup job:

This job is created on Primary Server,this job takes the transaction log backup of the Database on a scheduled time

Copy Job:

This job is created on Secondary Server,this job Copies the transaction log Backup from Primary Server to the Standby/Secondary Server.

Restore Job:

This job is created on Secondary Server,this job restored the  copied transaction log backup on the Secondary Server.

Alert Jobs:

This job is created on Primary Server and Secondary Server.and gives the alerts as email, when there  is a delay in backup ,copy,restore jobs.

Log shipping has disadvantages too ,network usage consumption.

Step 1:

Take a Full Backup and a transaction log Backup from the Principle server and Restore it on the Standby by Server with No recovery mode or Standby Mode.

Step2:

Go to the Primary Server right click on the database ->Task_> Ship Transactin Logs..

1

Step 3:

Click in “Enable this as a primary Database in a Log Shipping configuration”

2

Step 4:

Give the network path and the Physical path of the shared folder of the database, to know the IP of your server run ipconfig /all command in the command prompt. give the backup time schedule,alert schedule, if using SQL Server 2008 you can even select the Compress Backup Option.Click on schedule and the windows opens as below

4

Step 5:

Click on the add Secondary Server button Option. connect to the Secondary server .As we have Restored a Full Backup and a transaction log backup. so leave the first tab.

5

Step 6:

click on Copy Files tab,give the physical path of the folder in Secondary Server.and set the schedule for restore Transaction log too.

6

Now Lo Shipping has been sucessfully configured ,to monitor the log shipping you can use

sp_help_log_shipping_monitor stored procedure

2 thoughts on “Log Shipping in Simple Steps

Leave a Reply

Your email address will not be published. Required fields are marked *