SQL server Log Shipping overview and practical demonstration.

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.

2log

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

1log


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.

4log.jpg


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

5log.jpg


Select restore job properties and define secondary database state.

6log.jpg

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.

7log.jpg


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

8log.jpg

A successfully completion of log shipping will show following window.

9log.jpg


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.

If you like above content then feel free to share it with your colleague  and friends. If you wish to improve the content the feel free to comment and contact me directly.
Advertisements

#disaster-recovery, #log-shipping, #sql