One of the foremost responsibility of a SQL Server administrator is to prepare plans for recovering data in instances of potential disasters.Nothing turns out to be more futile than a well-designed and pretested backup and restoration plan for recovering data from SQL Server in case of disasters.When a disaster recovery plan is designed the administrators keeps account of various things like the different disasters (both natural and technical), whether the plan will suit the business and environmental needs, etc.
This blog covers the various recovery plans that can be created in SQL and will help the users to understand what the disaster recovery plans in SQL Server 2008 are. In addition to this, the advantages and disadvantages associated with various techniques will also be discussed.
Various Disaster Recovery Techniques in SQL Server 2008
1) Database Mirroring
Database Mirroring is the process of creating and maintaining redundant copies of a database in SQL Server. The main purpose of database mirroring is to ensure uninterrupted database availability in downtime scenarios. It can be implemented on per-database basis i.e. one mirror database is created for every single database. It works only with full recovery model and goes unsupported in bulk-logged and simple recovery models.
Advantages
- It increases database availability.
- Ensures the availability of viable database copy at the time of system upgradation
- It guarantees that the both the server instances contain identical information.
Disadvantages
- Continuous synchronization of the main and the mirror database may slow down the performance of SQL Server.
2) Failover Clustering
Failover clustering is the process in which a working server replaces a failed server in scenarios of hardware or software failure. A user can create a single failover cluster for a single SQL Server instance or can create a single failover cluster for multiple databases.
At times of server failure, failover clustering enables the database system to switch the instance processing automatically from a failed server to a working server. Since failover clustering is designed in order to ensure high server availability, the nodes should be situated close to each other.
Advantages
- Ensures high availability of SQL Server instances.
- It occurs automatically in case the primary server fails.
Disadvantages
- The overall cost of maintaining two servers at the same time is very high.
- Since the servers or the clustered nodes should be situated close to each other geographically, this cannot be used in organizations whose branches are spread globally.
- It does not turn out to be helpful in cases of disk array failure.
- Failover clustering cannot be implemented at database level or object level.
3) Peer-to-Peer Transactional Replication
Peer-to-Peer transactional replication is the process of maintaining several copies of data on multiple server instances that are referred as nodes. It is designed for applications which are intended to read or modify data in the databases that take part in replication.
For instance, if the server hosting the database becomes unavailable, the application can be modified in order to divert the traffic towards the other available servers.
Advantages
- Improved read performance because the activity can be distributed across all the nodes
- Processes done in a single node like the update performance, insert performance and delete performance are propagated across all the nodes
Disadvantages
- All the databases taking part in the replication process must have identical data and schema
- Tables and objects cannot be included in multiple peer-to-peer operations in a single database.
- It does not provide conflict detection or resolution.
- There may have performance issues in SQL Server transaction replication
4) Creation Of Warm Standby Server
A warm standby server is a secondary server that runs in background. The entire data is replicated or mirrored to this secondary server regularly. This also means that there are times when both the servers do not contain exact data. A warm standby server can be created by using Log Shipping.
Log Shipping
Log shipping is the process of creating automatic backup of the primary database and transaction log files and then restoring them on a secondary server. During the process of restoration, the database exists in an exclusive mode and is unusable.
Advantages
The entire database including objects such as tables and views can be recovered
The data restoration is done faster. The fast restoration process leads to fast data recovery
Disadvantages
- Since the database is in an exclusive lock mode during the process it will be unusable
- The user cannot apply changes to selective tables through the restoration process. All the changes made in the primary server are entirely restored in the secondary server.
Conclusion
The solutions discussed in this write up provides a general account of the technologies that one can use in order to safeguard their data against data disasters. It is to be noted that before deciding which methods suits your organization, a thorough investigation of the concerned method should be done from the user’s end.
