10 SQL Server Disaster Recovery Solutions

Leave a comment (0) Go to comments

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 ?

10 SQL Server Disaster Recovery Solutions top sql server disaster recovery solution sql server recovery sql server high availibility solutions sql server disaster recovery

What are the top SQL Server Disaster Recovery Solutions ?

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.

10 SQL Server Disaster Recovery Solutions top sql server disaster recovery solution sql server recovery sql server high availibility solutions sql server disaster recovery

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

If you want get answer, “How to Implement HADR”, please check my previous post, “How to Implement HADR – Step by Step Guide with screenshots

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.

10 SQL Server Disaster Recovery Solutions top sql server disaster recovery solution sql server recovery sql server high availibility solutions sql server disaster recovery

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.

If you want get answer, “What is Clustering”, “Installing Windows 2008 clustered environment”, “
How to Install SQL Server clustered instance” please check my video tutorial “SQL Server 2008 Clustering on Windows 2008 Video

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

10 SQL Server Disaster Recovery Solutions top sql server disaster recovery solution sql server recovery sql server high availibility solutions sql server disaster recovery

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.

If you want get answer, “How to Set up Database Mirroring in SQL Server 2008”,please check my video tutorial “How to Set up Database Mirroring in SQL Server 2008 (Video)

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

10 SQL Server Disaster Recovery Solutions top sql server disaster recovery solution sql server recovery sql server high availibility solutions sql server disaster recovery

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.

If you want to know, “How to Set up SQL Server Log Shipping”,please check my here and also learn “How to Monitor SQL Server Log Shipping

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

If you want to know, “What are the type of replication available in SQL Server”or “How to set up Replication“, Video Tutorial, check my previous post, “Replication with SQL Server 2008

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

If you want to know, “How to Backup SQL“, check my Video Tutorial, check my previous post, “How to Backup SQL–Video Tutorial”, which answers

  • WHY Database backed is required ?
  • Ways to Take SQL Server Database Backup
  • Different Types of Backup
  • DEMO : How to Take Backup
    • Taking SQL Server FULL Database backup Manually using SSMS – Graphically
    • Taking SQL Server FULL Database backup Manually using TSQL CODE
    • Schedule a Database Backup
  • Compress SQL Database Backup
  • DEMO : How to Enable Backup Compression
    • How to Enable Database Compression
    • Explicitly compress on demand backup

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

If you want to know, “How to build sessions / transaction level DR solution” check my previous post, “Restoring Data with Database Snapshots

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.

Careers in database management require in-depth knowledge of SQL and relational database management systems (RDBMS). The ability to use SQL and manage databases are a key component in many business analyst degree programs.

EOF - 10 SQL Server Disaster Recovery Solutions, SQL Server 2012 , 2008 R2 , 2008 , 2005 , 2000

Leave a Reply

Your email address will not be published. Required fields are marked *


*


You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>

Notify me of followup comments via e-mail. You can also subscribe without commenting.