SQL Server Disaster Recovery Solutions

What is SQL Server Disaster?

Disaster is an event that makes continuation of normal functions impossible. In business terms, any incident which result affects business or stops business transactions is a disaster for example

·         Orders can not be placed, think about Amazon where every business transaction is online and customer is unable to place an order
·         Accounting activities freeze, Think about a PayPal for a minute, where money can not be disbursed
·         Data is unavailable - Think about Google for a minute where Data or even a index is not available for a minute.
·         Electronic Communications Halt – Think about system is not available
·         Unable to access Decision-critical Information

Any of above event cost companies and effect their revenue, market share and credibility.

What are the major potentials disaster Scenarios that company might face?

There are N number of disaster Scenarios but here are quick list of most potential scenarios cases
·         Human error – System owner manually made some mistake
·         System Failure / Malfunction – Services Hangs / not responding
·         Virus Attack – Any sort of virus attack which result denial of service
·         Operational Errors – any sort or operational error.
·         Accidents – 09/11 is history
·         Natural Disaster- Japan earthquake and tsunami
·         Hardware Failure – System Crashed / Hard Drive not responding
·         Sabotage – killing competition

·         What is Disaster Recovery ?

·         Disaster recovery is the process to continue business work after any disaster scenario
·         What is BCP (Business Continuity Plan) / DR (Disaster Recovery) Plan?
·         DR Plan / BCP plan is a formal document, which describes how a business is going to deal with potential  disasters and will continue to resume operations.
·         For example, in case Server 1 goes down or application is unable to get a response for 10 seconds from Server 1 then application should connect to Alternative Server.
·         What is the difference between High Availability vs Disaster recovery ?






















SQL Server is an enterprise solution which provides various solution to cover DR scenarios. A disaster recovery includes money and solution depends on how much money you want to spend on DR solution. Here is list of solutions, which range from few $$$ to million dollar solutions.

Solution 1 – SQL Server HADR

 

“HADR” is a high-availability and disaster recovery solution introduced in SQL Server 2012. This is the only solution which provides both high availability as well as data recovery in case of a disaster on any nature.

 

PROS

• Multi-Database Failover
• Multiple Secondary’s
• Active Secondary’s
• Fast Client Connection Redirection
• Windows Server Core
• Multisite Clustering

CONS

• Expensive Solution
• Complicated / Complex Environment
• Woks only with SQL Server 2012 and Window Server 2008 onward only

Solution 2 – SQL Server Clustering

This is one of the best High Availability industry proven Solution. As part of this solution, be bind SQL Services with two Physical Servers which shares the common storage in such a way that SQL Services will automatically failover to other available node if a hardware failure or a software failure occurs on first node.

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

PROS

• HIGH availability solution as Services were always available
• Supported by all versions of SQL Server starting from SQL Server
• Automatic Failover
• No loss of Data
• Coupled with SAN as shared array for storage

CONS

• This is not a DR solution - Clustering doesn’t cover disk array (Storage) failure as all clustered nodes shares the same storage.
• Restricted to use local subnet only ** This restriction is removed in Windows Server 2008 onwards.
• High COST as identical hardware, shared storage is required
• Failover clustering does not allow you to create failover clusters at the database level or at the database object level, such as the table level.

Solution 3 – Database Mirroring

 

 Database mirroring is an alternative high-availability solution and it also offers DR also. Database mirroring supports automatic failover, but does not require cluster-capable hardware, and can therefore provide a cost-effective alternative to failover clustering.

In a database mirroring solution, a database is stored on one server and copied to another, providing a standby copy of the database that can service clients in the event of a server failure.

 

 

 

 

PROS

• Increases data protection.
• Increases availability of a database.
• Improves the availability of the production database during upgrades.
• Solution at Granular level instead of Server level this solution works ate database level.
• can be used as a DR solution too

CONS

• Standby copy (Mirrored) can not be used, it’s just a stand by
• Database mirroring is limited to only two servers
• Database mirroring is limited to only 10 database per servers
• Need to configure your DSN string to have both mirrored servers in it so that when they switch you notice nothing- In case of Clustering and HADR, this is not required
• Partners must use the same edition.
• Asynchronous database mirroring (high-performance mode) is supported only by Enterprise Edition
• Can not be used for system databases like master, msdb, model databases.

Solution 4 – Log Shipping

 

Log shipping is primarily a failover solution. As part of this solution, log shipping provides database-level redundancy for SQL Server database by automatically backing up, copying, and restoring transaction logs on standby servers. 

 

 

 

PROS

• Standby databases can be available for read-only queries.
• Multiple standby servers can be configured

CONS

• Possible data loss when the primary server fails
• Manual Failover, there is no automatic failover
• Purely works on LSN, If case database log is truncated by some one, then need to again copy entire database.

 

Solution 5 – Replication

 

Replication is the mechanism for creating and maintaining multiple copies of the same data. Replication allows,
• Multiple copies of data can be kept in sync.
• Allows data be closer to users, improving performance.
• Allows a branch office to work when not connected to the main office.
• Separate process and offload work from production servers

 

PROS

• Work at Granular level, Can set replication for a specific table, set of tables, or subset of data within a table or tables on one or many other servers.

CONS

• Need more DBA efforts to manage replication
• need to manual failover
• can loose data

Solution 6 – Native SQL BACKUPS

 

This is the most basic form of disaster recovery for SQL Server and one that was being practiced in every situation.

 

PROS

• built in and not other component is required as data can be backed on local disk, network storage and even on USB device.

CONS

• can loose data
• Manually recovery
• No High Availability

Solution 7 – RAID (redundant array of independent disks)

 

It is a method of storing Data on multiple hard disks for greater protection. It provides redundancy for disk / storage from failure.

PROS

• Can be built in the stand alone server only
• Provide Disk level protection

CONS

• can loose data
• It’s always local

Solution 8 – Disaster recovery solutions from Virtual Computing

 

PROS

• In case of disasters entire server can be built in seconds
• Onfly entire server copy

CONS

• Very specific to Virtual Servers
• Works at OS level

Solution 9 – Database SNAPSHOTS / Triggers / CDC or

SET IMPLICIT_TRANSACTIONS ON

 

These are multiple transition / sessions level DR solutions. These solutions used by developer and DBA’s To avoid manual and human errors, we use this method

SET IMPLICIT_TRANSACTIONS ON provides transaction level DR solution :), I love this

PROS

• Work at Granular level, Can set replication for a specific table
• Providing Auditing data, who make and when changes were made.
• Can be customized to store and old and new values
• SET IMPLICIT_TRANSACTIONS ON can help you to explicitly commit data after changes. I personally use this method every time to make sure everything is OK

CONS

• Require development efforts
• Persons need to be trained on this

Solution 10 – Third Party Solutions

 

Deciding SQL Server Disaster solution totally depends on business requirement and cost. There are lot of third party tools available which provides customized high Availability and Disaster Recovery solutions.