Click here to Skip to main content
15,867,330 members
Articles / Database Development / SQL Server
Article

An Overview of Log Shipping in SQL Server 2005

Rate me:
Please Sign up or sign in to vote.
5.00/5 (18 votes)
25 Oct 2008CPOL3 min read 125.4K   29   8
Overview of Log Shipping in SQL Server 2005

Table of Contents

  • Introduction
  • What is Log Shipping?
  • Component of Log Shipping
  • Log Shipping Prerequisites
  • SQL Server 2005 Version that Supports Log Shipping

Introduction

For distributed database application environment, it is always required to synchronize different database servers, back up, copy Transaction Logs, etc. If we are going to implement using application we have to put lots of efforts to build up the application. SQL Server 2005 provides an advanced feature called Log Shipping. Log shipping is an Automated Process for backing up, restoring, copying the transaction logs and synchronizing the database for distributed database server application which can improve the application performance and availability of database. In my recent project, I have done some short of experiment on it. I am going to explain it in this article.

What is Log Shipping?

Log Shipping is used to synchronize the Distributed Database Server. Synchronize the database by copying Transaction logs, Backing up, Restoring data. SQL Server used SQL Server Job Agents for making those processes automatic. Log Shipping does not involve automatic transfer of server if there is any failure. This means it has just synchronized the databases but if the primary server fails, it will not redirect your application to a secondary server. This has to be done manually.

The main functions of Log Shipping are as follows:

  • Backing up the transaction log of the primary database
  • Copying the transaction log backup to each secondary server
  • Restoring the transaction log backup on the secondary database

Components of Log Shipping

For implementing Log Shipping, we need the following components - Primary Database Server, Secondary Database Server, and Monitor Server.

  • Primary Database Server: Primary Sever is the Main Database Server or SQL Server Database Engine , which is being accessed by the application. Primary Server contains the Primary Database or Master Database.

  • Secondary Database Server: Secondary Database Server is a SQL Server Database Engine or a different Server that contains the backup of primary database. We can have multiple secondary severs based on business requirements.

  • Monitor Server: Monitor Server is a SQL Server Database Engine which Track the Log Shipping process.

Log_Sh2.jpg

Figure 1: Log Shipping Database Server Configuration

We can have different SQL Servers for each of these servers or we can use a single server for these three. In my example, I have used a single server for these three.

You can start Log Shipping by Right Click on Database >Properties >Transaction Log Shipping > Select Check Box, and Configure your settings.

Setting1.JPG

Figure 2: Enable Log Shipping

Use the following settings for more configurations:

Log_Sh1.jpg

Figure 3: Transaction Log Backup Settings for Primary Server

Configure Secondary Server:

setting3.JPG

Figure 4: Settings for Secondary Server

Log Shipping Prerequisites

  • Must have at least two Database Servers or two SQL Server 2005 Database Engines.
  • Configuration user should have Admin privilege on that server
  • SQL Server Agent Service Configured properly
  • Configuration mode of Primary database should be a Full or Bulk Logged recovery model.
  • Shared folder for copying the transaction logs.

SQL Server 2005 Version that Supports Log Shipping

SQL Server 2005 VersionAvailable
SQL Server 2005 Enterprise EditionYes
SQL Server 2005 Workgroup EditionYes
SQL Server 2000 Standard EditionYes
SQL Server 2005 Developer EditionYes
SQL Server 2005 Express EditionNo

Reference

History

  • Written on 25th October, 2008

License

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)


Written By
Technical Lead
India India
.NET Consultant | Former Microsoft MVP - ASP.NET | CodeProject MVP, Mentor, Insiders| Technology Evangelist | Author | Speaker | Geek | Blogger | Husband

Blog : http://abhijitjana.net
Web Site : http://dailydotnettips.com
Twitter : @AbhijitJana
My Kinect Book : Kinect for Windows SDK Programming Guide

Comments and Discussions

 
GeneralMy vote of 5 Pin
Member 1507871611-Jul-22 10:32
Member 1507871611-Jul-22 10:32 
QuestionAdding second target database Pin
PatE_PD114-Oct-11 6:53
PatE_PD114-Oct-11 6:53 
had log shipping running for a month with no problems. However a second target has been requested. I do not see how to add a second target without removing the first target and readding with two targets. Also if I have .ndf files how do I designate their directories?

Thanks for any help.
Generalmore powerful log shipping Pin
itayl28-May-09 21:56
itayl28-May-09 21:56 
GeneralRe: more powerful log shipping Pin
Abhijit Jana1-Jun-09 20:44
professionalAbhijit Jana1-Jun-09 20:44 
GeneralNice article Pin
Brij23-Dec-08 2:58
mentorBrij23-Dec-08 2:58 
PraiseRe: Nice article Pin
Member 1507871611-Jul-22 10:33
Member 1507871611-Jul-22 10:33 
GeneralDo not mind Pin
slemaryino25-Oct-08 20:41
slemaryino25-Oct-08 20:41 
GeneralRe: Do not mind PinPopular
Abhijit Jana26-Oct-08 18:06
professionalAbhijit Jana26-Oct-08 18:06 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Praise Praise    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.