Step by Step implementing Log Shipping

Log Shipping enables you to automatically send transaction log backups from a primary database on a primary server instance to one or more secondary databases on separate secondary server instances. The transaction log backups are applied to each of the secondary databases individually. 

 

ADVANTAGES OF LOG SHIPPING- 

1. Easy setup. 
2. Standby databases can be available for read -only queries. 
3. Low Maintenance.
4. Multiple standby servers can be configured.
5. Provide both disaster recovery and high availability solution. 
6. It supports both bulk logged and full recovery model. 

 

DISADVANTAGES OF LOG SHIPPING- 

1. It is possible to loss data when the main database is corrupt. 
2. It only works at database level and not at SQL server instance level. 
3. There will be data transfer latency.
4. Failover take time. 
5. No automatic failover.

 

 TYPES OF LOG SHIPPING -

In SQL Server two types of log shipping which are given below-:  

1. Standby Mode 
2. Restore Mode

Standby Mode-The database is available for querying and users can access it, but in read-only mode. 

Restore Mode- In this mode the database is not accessible.    

 

STEPS TO CONFIGURE LOG SHIPPING-

• I have used SQL 2019 to set up Log Shipping.  
• First, connect to your primary instance and select a database. 
• Take a full back up from primary instance.  
  

• Now connect to secondary server. 
• Here, restore the full back up on the secondary server with the help of move command with norecovery.

• Now, restore the log backup on the secondary server which we have taken from primary server. 

• Now, configure log shipping between both the server and Go to primary server->Go to database->Right click->Go to properties->Transaction Log Shipping

• Select network path and local path in transaction log shipping. 

• Now, go to add and connect with initialize secondary server. 
• Add the path for copied files. 
• Go through standby mode which means you can read from secondary server.

• Now, at primary server insert some data in a selected database. 
• Go to SQL Server agent and start the LS backup job at the primary server. 

• Now, go to secondary server and go to SQL Server agent and start the LS Copy and LS Restore job at the secondary server.   

• Now, go to secondary server and open a new query window and write a query- 

• It means the secondary server is totally shrink with primary server.

JOBS CREATED FOR LOG SHIPPING-

There are 3 types of jobs in log shipping as given below-   

• Backup job (LS Backup)- A SQL Server Agent job that performs the backup operations, logs history to the local server and the monitor server and deletes old backup file and history information. 

Go to SQL Agent->Jobs->LS Backup job

• Copy job (LS Copy)- A SQL Server Agent job that copies the backup files from primary server to a configurable destination on the secondary server and logs history on the secondary server and the monitor server. 

   
  

                                       Go to SQL Agent->Jobs->LS Copy job

• Restore job (LS Restore)- A SQL Server Agent job that restores the copied backup file to the secondary database. 

Go to SQL Agent->Jobs->LS Restore job