Configuring Logshipping

Log shipping works on log backup and restoration , where log backup is taken in primary database , copied to secondary database and restored in secondary database in non recovery or standby mode so that further log backups can be restored

So we can split log shipping in to 3 process

1)Log backup in primary
 
       There will be a log backup job in primary server so this job will take log backup based on the schedule we have given

2) Copy from production to secondary server
 
     This job is located in secondary server and it will copy the log  backup from primary server to secondary , for this secondary server sql agent's service account should be having the read permission in the folder where log backups are kept in primary server

3)Restoring log backup in secondary server
 
     This job is located in secondary and it will restore copied log backup in no recovery or stand by mode

We have job in log shipping to do these process
1)Log backup job (In primary)
2)Copy Job(In secondary)
3)Restore job(In secondary)
4)Alert job(Raise alert in primary and secondary when backup or restore doesn't complete sucessfully within a specified threshold)

Now lets look at how to configure log shipping

Before we begin

1)Before configuring Log shipping make sure your primary database is in full recovery model if not change it to full recovery model with below TSQL
 ALTER DATABASE [database name] SET RECOVERY FULL WITH NO_WAIT
2)I normally take a full backup of primary database and restore in secondary with no recovery        manually before configuring  log shipping as i feel more comfortable with it if you want you can do this while configuring log shipping

Configuring log shipping

1)Log shipping configuration can be opened from database properties[right click(database)->properties] or from [right click(database)->task->ship transaction logs

2)Check Enable this as primary database in log shipping check box as show in below image

3)Now let us do backup setting ,click on backup settings

• Here we need to specify the network path of the backup folder and service account of  sql agent of primary should be having read write permission and secondary instance should be having read  permission in this folder
• If the backup path is on primary server you can give the local path even though it is not mandatory
• You can specify how old log backup should be deleted and the threshold of alert
• Now decide how frequently the backup should happen and schedule the backup job

Now we have done the backup configuration

4) Now lets add secondary instance and database by clicking add button

    4.1 Initialize the secondary database you can do it by taking a fresh backup or by using an already             existing backup or chose already existing database if you have restored database before                       configuring log shipping

   4.2  In the copy file setting specify the folder in the secondary server the backup to be copied and               how frequently the copy should happen

 4.3 In the restore transaction log tab you can chose whether you want the secondary database to be in        restoring mode or standby mode and specify how frequently the restore job should happen

   Click ok  and we have successfully configured log shipping