SQL Server Log shipping allows administrator to automatically send transaction log backups from a primary SQL instance to one or more secondary SQL instances using agent job in background. Following are the benefits of using log shipping feature.

• Provides a disaster-recovery solution.
• Supports read-only access on secondary databases.
• Accidental changes can be avoided as the delay of log copy can be configure based on requirement.

Let’s now see how log shipping can be configured in test environment. Following steps are performed on SQL 2014 with Windows 2012 R2 server as an operation system.

Open SQL Server Manager Studio (SSMS), execute following query to create a sample database, sample table then populate it with 100 initial rows.

USE [master]
CREATE DATABASE [LogShippingDemo]
GO
ALTER DATABASE [LogShippingDemo] SET RECOVERY FULL 
GO
 
--Create a sample table that holds name & city. 
Use [LogShippingDemo]
Create Table EmployeeCity
( 
ID INT IDENTITY(1,1), 
NAME VARCHAR (100),  
City VARCHAR(100)    
)
GO
 
--Insert 100 records
Use [LogShippingDemo]
INSERT INTO EmployeeCity
Values ('Pradeep', 'Pune')
GO 100

USE LogShippingDemo
SELECT COUNT(*) FROM EmployeeCity 

The basic requirement for log shipping is to have SQL agent service running under domain account having permission on shared location that is accessible from SQL server.

Open SQL Server Configuration Manager (SSCM) to confirm agent service is running under domain account.

Open SQL server management studio, select ‘LogShippingDemo’ database then select ‘Transaction log shipping’ option from the database properties.

Define shared location that is accessible from all the SQL servers participating in log shipping. Additionally define agent job properties that will be responsible for taking backup of transaction log from source (or primary) server.

Connect to secondary SQL server and configure secondary database settings. Administration can either take a new set of full backup OR use an existing set of full backup.

Define the local folder and agent name of secondary SQL server that is responsible for copy transaction log from shared location.

Select restore job properties and define secondary database state.

Restoring – This mode is also known as NORECOVERY mode and cannot be used for read operations.

Standby / Read-Only – This mode is also known as STANDBY mode and can be used for read operations. Transactions which are in process on the primary or any uncommitted transactions cannot be read on the secondary database when the database is in a read-only state.

After completion of above configuration, don’t forget to generate script for above operation for your future reference.

A successfully completion of log shipping will show following window.

Let’s now insert, 100 more records in primary database.

USE LogShippingDemo
INSERT INTO EmployeeCity
Values
     ('Pradeep', 'Pune')
GO 100

You may not see updated rows immediately as there could be configuration delay in backup, copy & restore of transaction log. If you wish to see the change then run following job manually.

LSBackup_logShippingDemo Runs on primary server, responsible for copying transaction log on source server and paste it to shared location.

LSCopy_SQL1_LogShippingDemo Runs on secondary server, responsible for copying transaction log from share location to local location of secondary server.

LSRestore_sql1_LogShippingDemo Runs on secondary server, restore transaction log to secondary database.

Please note:-

All content provided on this blog is for informational purposes only. The reason for this blog is to document the things that I have learned reading blogs/books/self about Windows Server. We do not own any responsibility for the events that may happen because of using/applying the code/concepts mentioned in this blog.