Creating log shipping in SQL server

A step by step approach of creating log shipping in SQL server

Log shipping is one of the high availability solutions in SQL server, starting in 2005 version till date. It is a database level feature

Basic principal behind log shipping is backup and restores of database. 
Catch point in here is, process would be restoring Transaction logs to database in No recovery mode or standby mode (read only).

Requirements:

  1. Sysadmin  role is needed for the login to setup log shipping   

  2. SQL server edition other than express would work  for implement

  3. Primary database need to be in full or bulk-logged recovery model

  4. SQL user NT:service\Sqlagent need to have  access to the network folders

Step 1:

Right click on the database for which you trying to achieve log shipping 

 Step 2:

Check the box which says enables this as a primary database in a log shipping configuration

i.e would enable backup settings ..

Step 3:

Click on the backup setting to configure ,ie would pop up below screen

Step 4:

Network folder, where we would have our transaction log backups, if you are trying to achieve in same machine, do it with a share by sharing the folder   

Step 5: Configure back up option.. By clicking schedule, i.e frequency of occurrence, for minimum latency I would change it to 5 mins or leave it as 15 mins based on the requirement.

 Compression levels of the database backup.

Step 6: 

 Adding Secondary server, with log shipping we can have more than one secondary server.

Step 7:

Connect to the secondary server and chose the databases.

Initialize secondary Database:

We do have 3 options
1.      To generate a full back up on primary DB and restore it into secondary server (not recommended for large database and Prod DB’s 
2.      Restore using existing copy of full backup from Primary DB 
3.    Secondary DB is initialized (Second option  when done already by human ) 

Step 8:

Copy File tab, in here we need to provide the secondary server location where you like to have transaction logs copied.  And at the same time you have option to change the schedule of copy job

 Step 9:

Restore transaction Log tab, has the option to leave db in either no recovery mode (you cannot access the DB) or standby mode (read only DB)

Step 10:

Click on the okay to return to the main screen where you have option to set up a motoring instance, it’s like a witness server in mirroring concept, it uses a SQL Agent job to check on log shipping.

Check the box for using a monitor server instance. Settings button need to be clicked and would let you connect to SQL server which you would like to use as monitor instance (Preferable other than primary or secondary)

Set 11 : Job history retention is the option provided  

Setp 12 :

Click okay to finish, based on whether you have monitor instance or not total would vary, with monitor instance total steps would be 5 without 4

Script to check if log shipping is work

Create a table on primary db

  CREATE TABLE TEST_11

  (

   ID INT IDENTITY(1,1),

   Name varchar(20)

  )

  

Wait for 10 – 15 mins

ON THE SECONDARY DB run to script

IF (EXISTS (SELECT *

                 FROM INFORMATION_SCHEMA.TABLES

                 WHERE TABLE_SCHEMA = 'dbo'

                 AND TABLE_NAME = ' TEST_11'))

BEGIN

    Print 'working'

END