What is a SQL Server disaster recovery?

A SQL Server disaster is an event that causes data loss or any serious SQL Server disruption. Causes of a disaster can be numerous: power failure, hardware failure, virus attack, natural disaster, human error, etc. Some SQL Server disasters cannot be prevented. That’s why a good disaster recovery plan is important

A SQL Server disaster recovery plan (DRP) is a process to have SQL Server up and running, and to overcome data loss after a disaster. A disaster recovery plan must be planned and documented in order to prevent catastrophic data loss and incidents

High-Availability is the term that shows the percentage of time a system needs to be available. In some environments, it goes as high as 99.999% or just 5.26 minutes downtime per year

A good SQL Server disaster recovery plan must take into account numerous factors: sensitivity of data, data loss tolerance, required availability, etc. The plan can be based on few a solutions:

Failover clustering
Database mirroring
Replication
Log shipping
Backup and restore

Each solution has its own advantages and cost of implementing. Based on the needs, a SQL Server disaster recovery plan should include on one or more available solutions

Failover clustering is a concept where a SQL Server instance is installed on the shared storage. It provides the infrastructure that supports high-availability and disaster recovery scenarios of hosted server applications. If a cluster node fails, the services that were hosted on that node can be automatically or manually transferred to another available node in a process known as failover. There is a short period of downtime while SQL Server is failing over

Database mirroring is a solution for increasing availability of a SQL Server database. It maintains two exact copies of a single database. These copies must be on different SQL Server instances. Two databases form a relationship known as a database mirroring session. One instance acts as the principal server, while the other is in the standby mode and acts as the mirror server. Two SQL Server instances that act in mirroring environment are known as partners, the principal server is sending the active portion of a transaction log to the mirror server where all transactions are redone

There can be two types of mirror servers: hot and warm. A hot mirror server has synchronized sessions with quick failover time without data loss. A warm mirror server doesn’t have synchronized sessions and there is a possibility of data loss

This solution will be removed in future versions of SQL Server

Replication can be used as a technology for coping and distributing data from one SQL Server database to another. Consistency is achieved by synchronizing. Replication of a SQL Server database can result in benefits like: load balancing, redundancy, and offline processing. Load balancing allows spreading data to a number of SQL Servers and distributing the query load among those SQL Servers. A replication consists of two components:

Publishers – databases that provide data. Any replication may have one or more publishers
Subscribers – databases that receive data from publishers via replication. Data in subscribers is updated whenever data the publisher is modified

SQL Server supports three types of replication:

Merge replication: publisher and subscriber independently make changes to the SQL Server database. The merge agent monitors the changes on the publisher and subscriber, if needed it modifies the databases. In case of a conflict, predefined algorithm determinates the appropriate data
Snapshot replication: the publisher makes a snapshot of the entire database and makes it available for all subscribers
Transactional replication: uses replication agents which monitor changes on the publisher and transmit these changes to the subscribers

Log shipping is based on automated sending of transaction log backups from a primary SQL Server instance to one or more secondary SQL Server instances. The primary SQL Server instance is a production server, while the secondary SQL Server instance is a warm standby copy. There can be a third SQL Server instance which acts as a monitoring server. The log shipping process consists of three main operations: creating a transaction log backup on the primary SQL Server, copying the transaction log backup to one or more secondary servers, and restoring the transaction log backup on the secondary server

The Backup and restore technique should be used as basic option for assurance. There are two major concepts involved: backing up SQL Server data and restoring SQL Server data. Backed up data is moved to a neutral off-site location and restore is tested to assure data integrity. There are different types of backups available in SQL Server: a full backup, differential backup, transaction log backup, and partial backup. The backup strategy defines the backup type and frequency, how backups will be tested, and where and how backup media will be stored. The restore strategy defines who is responsible for performing restores and how restores should be performed to meet availability and data loss goals

Author
Recent Posts

Ivan Stankovic

Ivan is a SQL Server professional and computer geek with years of IT and SQL Server experience. He has startedwith playing computer games, continued with computer programming and system administration. His areas of expertise are SQL Server disaster recovery, auditing, and compliance

View all posts by Ivan Stankovic

Latest posts by Ivan Stankovic (see all)

Using Extended Events to review SQL Server failed logins - August 5, 2014
SQL Server backup – models and types - May 26, 2014
SQL Server Policy Based Management – Categories and Database Subscriptions - May 21, 2014

Related posts: