Log Shipping

What is Log Shipping?

Log Shipping is one of feature high availability in SQL Server. Log shipping is technology from SQL Server to synchronize databases on 2 or more servers, utilize backup and restore technology for High availability or reporting purpose. Log Shipping works to ships Transaction log file from the one server to another server. Log shipping consist three operations:

  1. Backup transaction log at primary server.
  2. Copy backup transaction log file to the secondary server.
  3. Restore the log backup on the secondary server instance.

Prerequisite for implementing log shipping

  • Log shipping required Full or Bulk-Logged Recovery Model.
  • SQL server Agent logins: Make sure SQL Agent Services  on a both the servers have sysadmin role. This logins use for Backup and Restore database logs on the servers.
  • Share folders: Make sure SQL Services logins have Read and write access for share folders on a both servers. Share folders for copy and write log backup on both share folder.

Log shipping step by step

Create sharing folders on both servers.

  1. Create share folder in drive C with following structures.  
  2. Make sure SQL Agent service login are same on both servers. This is for Backup database log file on primary server and then restore database log file to secondary server.
  3. Give permission for share folders on both server to use SQL Agent Logins.

Make sure Database for log shipping used Full or Bulk Logged Recovery Model

We will use AdventureWorksDW2008 or AdventureWorksDW database for this tutorial. Use the following Query.  

to change database Recovery model use following query

ALTER DATABASE <YourDatabase Name> SET RECOVERY FULL

Backup Database on Primary Server

Once database have Full or Bulk copy Recovery Model, next step is backing up full and transaction log on the Primary Server. Use the following query.

 

Restore Database on Secondary Server

We have Full & Transaction Log database backup on primary server, next step is to restore database on Secondary Server. For Restore database on the secondary server we have to option:

  1. RESTORE WITH NO RECOVERY, We can use this option to have database with restore mode on secondary server which is mean database on the secondary server can’t be access.
  2. RESTORE WITH STANDBY, We can use this option to have Read-Only database, every changes on object or data in primary database on Primary server can be view or access in secondary database on secondary server.

First restore full database backup WITH NO RECOVERY option, use the following query.

Second restore transaction log backup WITH STANDBY option and create standby file, use the following query.

Now we have standby/read-only database on the secondary server.

Configure Log Shipping Agent Job on Primary Server

Let’s start to configure Log Shipping on primary database, use the following steps

1. Right click on database AdventureWorksDW2008 or AdventureWorksDW-Task-Ship Transaction Logs. Click Enable this as a primary database in a log shipping configuration. Click button Backup settings.

 

2. Once we click button Backup Settings, we will configure Log Shipping on primary server. Configuration of Log Shipping on primary server are:

  1. Network share folder for backup transaction log.
  2. Local folder which located on primary server.
  3. How long transaction log should retain on primary server.
  4. Log Shipping SQL Agent job name on primary server.
  5. Backup compression type (Non Compressed Backup, Compressed Backup, use Default server setting).

3. Configure schedule for Backup Job, we will configure Log Shipping on primary server. Configuration of Backup Job on primary server are:

  1. Log Shipping Backup job name on primary server.
  2. Backup Job Frequencies.
  3. Frequency of Backup Job Frequency.
  4. Duration of Backup Job.

 4. Configure Log Shipping for Secondary Server. Click button add from secondary database box for add Log Shipping secondary server, Log Shipping  Configuration for secondary server are:

  1. SQL Server secondary instance. Server name of secondary server.
  2. Secondary database. Database name on secondary server for restore backup transaction log file.
  3. Initialize secondary database. Secondary database initialization, configuration of initialization secondary database are:

A. Secondary server will create full backup database of primary database in network share folder and restore backup to create new database on secondary server. These options allow us to initialize folder for data and log file.

B. Secondary server will create full backup database of primary database and restore backup on secondary server with default restore option. These options allow us to initialize folder for data and log file.

C. Secondary database has been initializing on secondary server.

5. Configure Copy files option on secondary server. Copy files configuration on secondary server are:

  1. Destination folder on secondary server for copied transaction log file backup.
  2. Length of time for keep transaction log files on secondary server.
  3. Configure Copy Job on Secondary Server. Setting SQL Agent Copy schedule on secondary server.

 6. Configure Restore Transaction Log option on secondary server. Configuration for Restore Transaction Log on secondary server are:

1.  Configure Database State on Secondary Server when SQL Agent trying to restore transaction log. No Recovery Mode mean database on secondary server couldn’t be access. Standby Mode mean database on secondary server have read-only mode.

2. Configure SQL Agent Restore Job on Secondary server. setting up schedule for Restore Job on secondary server.

We Already configure Log shipping on Primary and secondary server. to configure Log Shipping & Backup Job on Primary Server use the following query :

DECLARE @LS_BackupJobId ASuniqueidentifier

DECLARE @LS_PrimaryId   ASuniqueidentifier

DECLARE @SP_Add_RetCode Asint

 

 

EXEC @SP_Add_RetCode =master.dbo.sp_add_log_shipping_primary_database

–Database Name on Primary Server

@database =N’AdventureWorksDW2008′

–Backup directory on Primary Server

,@backup_directory =N’C:\LogShipping\Backup\Primary’

–Network share folder for share Backups

,@backup_share =N’\\localhost\LogShipping\Backup\Primary’

–Backup Job Name on Primary Server

,@backup_job_name =N’LSBackup_AdventureWorksDW2008′

–Retention Period for Transaction Log Files on Primary Server (in Minute)

,@backup_retention_period = 2880

–Type of Backup Compression (0:No Compressed, 1:Compressed, 2:use server default)

,@backup_compression = 1

,@history_retention_period = 5760

,@backup_job_id = @LS_BackupJobId OUTPUT

,@primary_id = @LS_PrimaryId OUTPUT

,@overwrite = 1

 

 

IF (@@ERROR= 0 AND @SP_Add_RetCode = 0)

BEGIN

 

DECLARE @LS_BackUpScheduleUID Asuniqueidentifier

DECLARE @LS_BackUpScheduleID  ASint

 

EXEC msdb.dbo.sp_add_schedule

@schedule_name =N’LSBackupSchedule_XMS1′

,@enabled = 1

,@freq_type = 4

,@freq_interval = 1

,@freq_subday_type = 4

,@freq_subday_interval = 15

,@freq_recurrence_factor = 0

,@active_start_date = 20120707

,@active_end_date = 99991231

,@active_start_time = 0

,@active_end_time = 235900

,@schedule_uid = @LS_BackUpScheduleUID OUTPUT

,@schedule_id = @LS_BackUpScheduleID OUTPUT

 

EXEC msdb.dbo.sp_attach_schedule

@job_id = @LS_BackupJobId

,@schedule_id = @LS_BackUpScheduleID 

 

EXEC msdb.dbo.sp_update_job

@job_id = @LS_BackupJobId

,@enabled = 1

END

 

EXECmaster.dbo.sp_add_log_shipping_alert_job

EXECmaster.dbo.sp_add_log_shipping_primary_secondary

–Primary database name

@primary_database =N’AdventureWorksDW2008′

–Secondary server

,@secondary_server =N’XMS\SQL2KM’

–Secondary database name

,@secondary_database =N’AdventureWorksDW2008′

,@overwrite = 1

 

Use the following query to configure Copy & Restore Job on Secondary Server.

 

 

DECLARE @LS_Secondary__CopyJobId    ASuniqueidentifier

DECLARE @LS_Secondary__RestoreJobId ASuniqueidentifier

DECLARE @LS_Secondary__SecondaryId  ASuniqueidentifier

DECLARE @LS_Add_RetCode Asint

 

 

EXEC @LS_Add_RetCode =master.dbo.sp_add_log_shipping_secondary_primary

@primary_server =N’XMS’

,@primary_database =N’AdventureWorksDW2008′

,@backup_source_directory =N’\\localhost\LogShipping\Backup\Primary’

,@backup_destination_directory =N’\\localhost\LogShipping\Backup\Secondary’

,@copy_job_name =N’LSCopy_XMS_AdventureWorksDW2008′

,@restore_job_name =N’LSRestore_XMS_AdventureWorksDW2008′

,@file_retention_period = 10080

,@overwrite = 1

,@copy_job_id = @LS_Secondary__CopyJobId OUTPUT

,@restore_job_id = @LS_Secondary__RestoreJobId OUTPUT

,@secondary_id = @LS_Secondary__SecondaryId OUTPUT

 

IF (@@ERROR= 0 AND @LS_Add_RetCode = 0)

BEGIN

 

DECLARE @LS_SecondaryCopyJobScheduleUID   Asuniqueidentifier

DECLARE @LS_SecondaryCopyJobScheduleID    ASint

 

 

EXEC msdb.dbo.sp_add_schedule

@schedule_name =N’DefaultCopyJobSchedule’

,@enabled = 1

,@freq_type = 4

,@freq_interval = 1

,@freq_subday_type = 4

,@freq_subday_interval = 15

,@freq_recurrence_factor = 0

,@active_start_date = 20120707

,@active_end_date = 99991231

,@active_start_time = 0

,@active_end_time = 235900

,@schedule_uid = @LS_SecondaryCopyJobScheduleUID OUTPUT

,@schedule_id = @LS_SecondaryCopyJobScheduleID OUTPUT

 

EXEC msdb.dbo.sp_attach_schedule

@job_id = @LS_Secondary__CopyJobId

            ,@schedule_id = @LS_SecondaryCopyJobScheduleID 

 

DECLARE @LS_SecondaryRestoreJobScheduleUID      Asuniqueidentifier

DECLARE @LS_SecondaryRestoreJobScheduleID ASint

 

 

EXEC msdb.dbo.sp_add_schedule

@schedule_name =N’DefaultRestoreJobSchedule’

,@enabled = 1

,@freq_type = 4

,@freq_interval = 1

,@freq_subday_type = 4

,@freq_subday_interval = 15

,@freq_recurrence_factor = 0

,@active_start_date = 20120707

,@active_end_date = 99991231

,@active_start_time = 0

,@active_end_time = 235900

,@schedule_uid = @LS_SecondaryRestoreJobScheduleUID OUTPUT

,@schedule_id = @LS_SecondaryRestoreJobScheduleID OUTPUT

 

EXEC msdb.dbo.sp_attach_schedule

@job_id = @LS_Secondary__RestoreJobId

            ,@schedule_id = @LS_SecondaryRestoreJobScheduleID 

 

 

END

 

 

DECLARE @LS_Add_RetCode2      Asint

 

 

IF (@@ERROR= 0 AND @LS_Add_RetCode = 0)

BEGIN

 

EXEC @LS_Add_RetCode2 =master.dbo.sp_add_log_shipping_secondary_database

@secondary_database =N’AdventureWorksDW2008′

,@primary_server =N’XMS’

,@primary_database =N’AdventureWorksDW2008′

,@restore_delay = 0

,@restore_mode = 1

,@disconnect_users      = 0

,@restore_threshold = 45  

,@threshold_alert_enabled = 1

,@history_retention_period    = 5760

,@overwrite = 1

 

END

 

 

IF (@@error= 0 AND @LS_Add_RetCode = 0)

BEGIN

 

EXEC msdb.dbo.sp_update_job

@job_id = @LS_Secondary__CopyJobId

            ,@enabled = 1

 

EXEC msdb.dbo.sp_update_job

@job_id = @LS_Secondary__RestoreJobId

            ,@enabled = 1

 

END

FailOver LogShipping

In case of emergency we will failover on secondary database, for failover on secondary database use the following query.

USE master

RESTORE DATABASE <DatabaseName>

WITH RECOVERY

Next article will will test log shipping server.

 

Advertisements

6 thoughts on “Log Shipping

  1. ini d 1 mesin ap beda mesin??
    krn mw cba d mesin yg berbeda jd masing2 server ad IP public’a jd DB primary d mesin A dn DB sekunder d mesin B….

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s