Disaster Recovery & Business Continuity for SQL Servers Through a Standby Approach

Introduction

Businesses of all sizes must take backup and disaster recovery seriously. As companies grow and data becomes more and more prevalent, users of all types, either internal or external, require access to all this information. This is essential at a time when even the smallest business may find operations impossible with any single point of failure. With the launch of MS SQL Server 2005, MS SQL Servers are becoming increasingly popular for use in mission critical environments. With such important dependencies on software solutions, recovering from failures is crucial for business continuity.

To initiate movement towards redundancy and failure protection, Microsoft offers SQL Server Log Shipping (SQL Server 2000 and 2005) and SQL Server Database Mirroring (SQL Server 2005) in its core products. In addition to Microsoft, third party vendors, including Microsoft Gold Certified Partners such as Sonasoft, provide enhanced software solutions that simplify disaster recovery scenarios through easy to use Management Console and automating most of the manual steps. These components are focused on minimizing downtime while maximizing data retention in the event of catastrophic failure.

High Availability Solutions for SQL Servers

Failover Clustering

Microsoft Clustering enables users to prevent against hardware failures by stringing redundant hardware, called nodes, together through a central cluster manager that coordinates load balancing and data activity. Microsoft SQL Server supports both active-active and active-passive cluster environments. As protection against hard disk failure, RAID configurations are also common. However, from a performance standpoint, this significantly slows down I/O in the subsystems due to writing the data to multiple disks at the same time. Again RAID option is to protect against any hard disk failure but it cannot prevent site disasters.

In direct contrast to this storage dependency, using a Standby solution prevents against hardware, software and storage failures. Standby servers or databases are normally installed on unique, usually geographically independent, SQL Servers which serve as a barrier to failures of any type. SQL Server Clustering environments are more cost-intensive compared to the Standby option. The primary reason for this is the high hardware and software requirements. On the other hand, a Standby server does not have any special hardware requirements and is simply a software solution to meet disaster recovery needs. An additional cost, LAN connectivity is required between SQL Server cluster nodes to send and receive what is called a heartbeat signal, among other communications.

SQL Server Native Backups/Restores

A simple and inexpensive solution to recovering from failure is to take backups of all your databases. Out-of-the-box Microsoft SQL Server disaster recovery operation allows for Native SQL Server Backups that can take full, differential or transaction log backups of a database. Another disadvantage to SQL Server Native Restore procedures involves high downtime. In cases of disastrous failure, time is a crucial factor. Unfortunately, significant delays are introduced when trying to find the correct backup files and locations from which to restore databases. Typically, when these restores need to be performed, the inability to restore is something most organizations cannot tolerate.

Automated Standby Solution

A Standby Server is a server where the data from the primary databases is restored periodically using scripts, Microsoft Log Shipping or through a third party software. It acts as a hot standby which can be promoted to a primary role if the primary server goes down so that business operations continue to function. An automated standby solution bridges the gap between a highly expensive Failover Clustering solution and time-consuming Native SQL Server Backups and Restores. An automated standby solution provides a high-availability solution, protecting data from hardware and software failures as well as from human errors.

Sonasoft Standby Solution

The Sonasoft Standby Solution builds easy-to-manage standby plans that effectively replicate your data without interfering in normal business operations. Because the Sonasoft Standby Solution transfers only data that has changed since the last backup as well as uses high speed compression through the network to the standby server, data transmission can be handled by a low-bandwidth T1 or a low-cost DSL line. When disaster strikes, role switching is just one click away, as a series of complex database operations are performed in the background to switch the destination server(s) to the primary role. Sonasoft’s product, SonaSafe for SQL Server contains a unique architecture that not only creates easy to use backup tasks and schedules, but allows for efficient and simple recovery options all while minimizing chances of data loss.

Conclusion

Standby servers are a cost-effective and viable way for businesses to maintain SLA and business continuity efficiently. While other solutions do exist, they are limited by either being error-prone or failing to protect against site failures. One can deploy standby servers using third-party software that utilize easy to use interfaces for a fraction of the cost associated with some alternatives as discussed above. Also, these standby servers can be functional for reporting or testing purposes.