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 ?
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.
- Multi-Database Failover
- Multiple Secondary’s
- Active Secondary’s
- Fast Client Connection Redirection
- Windows Server Core
- Multisite Clustering
- 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.
- 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
- 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
- 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
- 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
- Standby databases can be available for read-only queries.
- Multiple standby servers can be configured
- 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
- 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.
- 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.
- built in and not other component is required as data can be backed on local disk, network storage and even on USB device.
- can loose data
- Manually recovery
- No High Availability
- 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.
- Can be built in the stand alone server only
- Provide Disk level protection
- can loose data
- It’s always local
Solution 8 – Disaster recovery solutions from Virtual Computing -
- In case of disasters entire server can be built in seconds
- Onfly entire server copy
- 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
- 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
- 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.