SQL Server Log shipping

What is LOG SHIPPING in SQL Server DBA?

Log shipping:

Shipping of transaction log files from the primary server database to second server database to get both database in continuous synchronization. It is a continuous process in the form of batches.

Log shipping requires 3 servers

1. Primary: Primary server contains source database for sending the transaction logs.

2. Secondary: Secondary  server contains destination database receiving the transaction logs.

3. Monitor: Monitor server tracks information related to log shipping jobs and sends alerts in case of failure.

Log shipping mainly depend on 3 jobs:

1. Backup job: For Backup transaction logs on primary server database. Always run on primary server.

2. Copy job:  Copy job runs from secondary server, copies transaction log backup from primary server to secondary server.

3. Restore job: Restore run on secondary server to restore backups.

Log shipping Architecture

Log shipping steps:

1. Before log shipping configuration, take full backup of primary database and restore it on secondary server. This is initial synchronization.

2. Create backup job on primary server to backup the transaction logs of primary database.

3. Create copy job on secondary server to copy the transaction logs.

4. Create restore job on secondary server to restore the transaction logs.

5. Create an alert jobs on monitor server to indicate a failure if the database are out of synchronization beyond the configuration threshold.

Failover steps:

Planned failover:

Situations

1. Upgrade from 2008 R2 to sql server 2012

2. Hardware failure

In these situations keep primary server in offline and make secondary server database online.

1. Disable all log shipping shops on both servers.
2. Make sure copy all backups copied to secondary server.
3. Restore all T-log backup in the same order.
4. Change connection to secondary server and start jobs unplanned/disaster.

Need to identify  crash time, when last backup, copy and restoration happened on server using ‘db0.log shipping-monitor’ tables of MSDB databases.

1. Disable all log shipping jobs
2. Perform tail log backup if log files available
3. Manually copy last backups from network share to secondary server.
4. Restore all T-log backups in same sequence.
5. Now secondary database will come online
6. Sync all logins, jobs then find orphaned user and fix them.

Advantages:

1. Only log shipping allows to keep secondary database in standby mode.
2. We can maintain multiple secondary databases.
3. No additional hardware (or) software required.

Disadvantages:

1. More down possible, we cannot bring secondary server immediately.
2. Data loss possible, as jobs run every 15 minutes.
3. We have to perform manual failover.
4. Need to perform several steps to bring secondary server online which we call warm standby.

Components of log shipping

For configuring log shipping we require 3 servers and 3 jobs and we need backup destination, there are 2 types

1. Local share
2. Remote (or) Network share

In local share, backup copies into primary server share whenever primary goes down data loss is more.

In remote share,  backup copies to network share copy job of secondary server goes to this share and copies backup to local share of secondary server. We have to grant proper permissions to sql agent services for this network share folder on both  servers.

Pre-requisites:

1. Make sure recovery model should be full.
2. Maintain same hardware between both server to avoid performance issue.
3. Service account running on both servers must have access to network share where log backups store.
4. Keep secondary server always in No recovery.
5. Make sure number of drives and name of drives should be same on both server.

Configuring log shipping

1. Select database → Go to properties →Select options

From recovery model drop down choose full recovery model

By using T-sql

Use master

Alter database databasename set RECOVERY FULL;

2. From properties → select transaction log shipping

• Enable this as a primary database in log shipping

Click Backup setting button

3. Transaction log backup settings

Provide network path to backup folder and local path if folder is local to primary server.

4. Using schedule option we can change time interval by default it will be 15 minutes (Latency)

5. Delete files older than (Retention period)  – 72 hours

Alert if no backup occurs with in                – 1 hours

6. Keep compression as default.

Based on retention period old backup files will be deleted. Whenever there is no backup for particular period we need to get alert.

Backup setting completed, secondary server setting enabled.

Secondary database settings

Select secondary server name:

Initialize secondary database

We have to create database on secondary server, if not present.

Select one option from the 3 options.

• Yes, generate a full backup of primary and restore

create secondary database if not available by using above option

• Use existing backup of primary into secondary

In case backup already exist we use this option (It is not recommended, LSN mismatch may happen).

• Secondary database is already initialized.

Restore options

In this we specify location of MDF and LDF files.

Copy files

Destination folder for copied files: \\win2KS13\secondary

Delete copied after (retention period) : 24 hours

Provide name to copy job

Schedule setting → specify setting

Restore Transaction Log

No recovery mode will not allow user to access secondary db standby mode, allows users to run select command till next restoration.

Disconnect users when restoring, kills all user connections.

No recovery → To keep database in restoring mode

Stand by → we can execute select commands

Disconnect users → It skills user who connect to server while restoring

Delay restoring → If we want , wantedly delaying restoring.

Log shipping configuration completed.

Failover steps in log shipping:

1. Disable all log shipping jobs on primary and secondary server.
2. Check whether we can take a tail log backup (or) not.

If log files available then only we can perform tail log backup.

1. Manually copy last backup and tail log backup from Network share to secondary server.
2. Restore all available log backups with no recovery and last log backup with recovery.
3. Now secondary database will become online.
4. Sync all logins, jobs
5. Finds orphaned users and fix it.

Advantages and Disadvantages

Advantages:

1. Whenever we have requirement, we can keep secondary database in standby mode.
2. We can get multiple secondaries.

Disadvantages:

1. More downtime and data loss possible
2. Manual failover.
3. In log shipping database as warm standby, we have to perform more steps to bring server online.

TUF file in Log shipping

TUF file stands for Transaction undo file (TUF)

TUF file contains the modifications that were not committed on primary database. When transaction log backup was in progress and when log was restoring on secondary database. When next transaction restoring in secondary server, sql server uses that TUF file and starts restoring incomplete transactions.

Monitoring Logstripping:

1. Right click on sql server name in SSMS  → go to reports → select standard report  → Transaction log shipping status.
2. We can query MSDB tables select * from msdb. Log_shipping_monitor_error_detail
3. We can query system job history

Select * from msdb.. Sysjobhistory where message

Like ‘% operating system error%’

Below topics further included in this series

• SQL Server Database Administration Introduction
• SQL Server Architecture
• SQL Server Storage Architecture
• SQL Server Database Architecture
• SQL Server Transaction Log Architecture
• SQL Server installation
• SQL Server Databases
• SQL Server Backups
• SQL Server Restoration Tutorial
• SQL Server Security
• SQL Server Automation Tutorial
• SQL Server Migration
• SQL Server Upgradation
• SQL Server Log Shipping in SQL Server