A server disaster causes to loss of data and disrupts the server. It can result from various incidents such as failure of power and/or hardware, natural disaster, virus attack, human-induced error, etc. This necessitates a good server disaster recovery plan.

An SQLServer disaster recovery plan (DRP) is necessary to keep the SQL server running and recover any data that is lost during a disaster. A good plan ensures least or zero downtime.

Various factors are considered in a Server disaster recovery such as data sensitivity, tolerance towards data loss, availability, etc.

Following are a few solutions on which the SQL Server disaster recovery can be based:

1.Failover clustering

In this case, an instance of the SQL server is installed on a shared storage. The high-availability infrastructure suited to disaster recovery situations of the applications on the hosted server is provided. In case of the failure of a cluster node, failover would transfer the services hosted by it to some other available node, through a short downtime.

2.Database mirroring

This increases the availability of the SQL Server database by maintaining 2 duplicates of a database on different instances of SQL Server, of which one acts as a principal server and the other as standby or mirror server. This is known as database mirroring and the 2 servers are called partners.

There are warm and hot mirror servers. There are less failover time and no data loss during synchronization session in hot mirror server but warm mirror servers have no synchronized sessions leading to chances of data loss.

There are 2 categories of mirror servers:

a.The synchronized sessions of a “hot mirror server” have less failover time without loss of data.

b. A “warm mirror server” has no synchronized sessions and data loss can occur.

3.Replication Disaster Recovery:

It is used for distribution of the copied data from a single SQL server to another. Data synchronization leads to consistency. Replication has various advantages such as redundancy, offline processing, and load balancing, Load balancing enables spreading and distribution of data to many SQL Servers.

3 kinds of replication are supported by SQL server, i.e. merge replication, snapshot replication, and transactional replication.

There are 2 components involved in replication:

a. Publishers-There can be more than one publisher in a replication database, that provides the data.

b. Subscribers – These databases receive data from the publishers through replication which get updated when the data in the publisher database is altered.

4.Log shipping:

This deals with the automatic transfer of the transaction logs from the instance of the production server to the secondary Server instances.A 3rd SQL server carries out the monitoring operation

Basically, log shipping involves the following steps:

a. Creation of backup of the transaction log on the primary SQL server.

b. Creation of a copy of backup of the transaction log on the secondary servers.

c. Restoration of the backup of transaction logs onto the secondary server.

5.The restore and backup technique:

It involves:

a. Creating a backup of the SQL server data., which is transferred to an off-site location. It can be of 4 types, i.e. differential backup, full backup, partial backup and transaction log backup.

b. Restoration of the SQL server data, which is tested to ensure data integrity.