Skip to content

Category: Disaster Recovery

log Shipping block diagram
Disaster Recovery 0

Log Shipping

Log shipping is the process of automatically backing up the transaction logs on a primary database server. Then those logs are restored on to the standby server.

This technique provides the disaster recovery solution for a single primary database. It also provides it for one or more secondary databases. Each database resides on a separate instance of SQL server.

During the interval between restore jobs, we can access secondary databases for read only purpose.

  • For log shipping the recovery model of the database must be full or bulk logged.
  • Collation must be same.
  • We cannot take the backup of secondary database.
  • It does not support automatic failure.
log Shipping block diagram
log Shipping block diagram

Log Shipping Involves below steps

  1. Backup Job – Backup the transaction log file on the primary SQL Server Instance or any backup share.
  2. Copy Job – Copy the transaction log backup file across the network to one or more Secondary SQL server instances
  3. Restore Job – Restore the copied transaction logs on the secondary SQL server instances.

Primary Server

The instance of SQL Server, which is your production server is called as the primary server.

Primary Database

The database on the primary server which you want to backup to another server is called as the primary database.
All the administrative activities are performed from the primary database with the help of SQL Server management studio.

Secondary Database

The worm standby copy of the primary database is called as secondary database.
The secondary database can be in below two states

  1. Recovering
  2. Standby

Monitor server

Monitor Server tracks all the details of this process. It includes below things

  • Keep the information about when the transaction log on the primary database was last backed up.
  • When the secondary server last copied and restored the backup files.
  • Information about any backup failure alert.

Monitor server is an optional.
Note – We cannot make any changes in the monitor server configuration without breaking or removing log shipping configuration.

Operating Modes

There are two operating modes and they are based on the state in which the secondary database will be

  1. Standby mode – The database is available for querying and users can access it, but in read only mode. The database will not
    be available when restore process is running.
  2. Restore mode – The database is not at all accessible

TUF file

TUF stands for “Transaction Undo File”.
This file contains information about any changes. These changes are related to incomplete transactions at the time the backup was done.
It is required if a database is loaded in read state. In this state further transaction log backups may be applied.
It is created while performing log shipping to a server in standby mode.
If TUF file is corrupted or lost, log shipping will not work, and we need to setup it again.

WRK File

The WRK files are produced when the transaction log backups are copied from the backup location to a secondary server.
Once the copy operation completed successfully file automatically renamed to “.trn” file.
It ensures that the files are not picked up by restore job until it successfully copied.

Check all related articles

By Tushar Kothawade Posted on

Disaster Recovery 2

Configure AlwaysOn: Windows & SQL Server Requirements

Nowadays, most suitable high availability and disaster recovery solution is AlwaysOn. It is a mixture of SQL Server Failover Cluster Instance and Database Mirroring. Here we are going to see requirements to configure AlwaysOn.

Requirements from Windows server side to Configure AlwaysOn:

  1. Ensure that the server is not a Domain Controller. Availability Group is not supported on Domain Controllers.
  2. Also make sure that each server participating in this configuration is running on Windows server 2012 or Later.
  3. Each server has to be the part of the Windows Server Failover Cluster (WSFC).
  4. For best performance use separate network adapter for AlwaysOn availability group.
  5. All the nodes must have sufficient disk space. As the primary database grows, their corresponding replica also grows by the same amount.
  6. To manage the Windows Server Faiolver Cluster, the user must have administrator access on each node of the cluster.
  7. The instances of SQL Server that hosts replicas for an Availability group resides on separate nodes of the same cluster.
  8. In all the nodes you need to use same SQL Server Service Account.
  9. You need to register the Service Principal Name (SPN) with Active Directory (AD). Do this on the SQL Server Service Account. This is required for the Virtual Network Name (VNN) of the availability group listener.
  10. Keep in mind that, if you change the SQL Server Service Account, you need to manually re-register the SPN.

Requirements from SQL Server side:

  1. All nodes of cluster need to have the same version of SQL Server.
  2. Also, you must use same SQL Server Collation for all the instances.
  3. Enable AlwaysOn feature on each server instance.
  4. Availability replicas must be hosted on different nodes of one Windows Server Failover Cluster (WSFC).
  5. Availability group name must be unique and length should not be more than 128 characters.
  6. Each Availability group supports one primary replica and up to 8 secondary replicas.
  7. Up to 3 replicas will run on Synchronous-Commit mode. This includes 1 primary and 2 secondary. Other replicas will run on Asynchronous-Commit mode.
  8. Do not use the Failover Cluster Manager to fail over availability groups. You need to use the SQL server management studio to do it.

Note:

  1. We can’t add System databases in availability group.
  2. We can’t add read only databases to Availability group.

To know more about the SQL server availability groups in AlwaysOn Configuration, please check below article.

SQL Server Availability Groups in AlwaysOn Configuration

Patching of SQL servers in Availability Group

Check all related articles

By Tushar Kothawade Posted on