Abstract
In today's world every single data is very important, data keep growing day by day, managing the data becoming challenging, thanks we have got machines which can store our data, what all our brain have to do is to manage those machines and make sure data is safe. The question is how you will make sure that the machine which is storing our data is going to be safe, if tomorrow something happens disaster and machine destroyed then where about your data which was stored in the machine since long time, is that gone ??.
We don't want to lose our data so is the disaster recovery plan comes into the picture, you can restore your data even your current database machine is dead. Busy database administrators can easily push this task onto the back burner with promises to themselves that they'll "take care of backups right after I finish this big project." However, take a moment and consider the value of your data to your organization? What would be the result if an hour's worth of database changes were lost? A day's worth? What about a complete loss of the database?
In this article we are going to learn if your data storage machine is Microsoft SQL Server then what are the ways you can plan your disaster recovery.
Table of content
- Disaster Recovery
- Disaster recovery in SQL Server
- Timely Database Backup Strategies
- Database Replication
- Log shipping
- Database Mirroring
- Conclusion
Disaster Recovery:
Disaster Recovery plan is your business continuity plan. Disaster recovery is a process that you can use to help recover information systems and data, if a disaster occurs
Suppose you have a database system storing financial application data, an organization whole business is running on some applications and data is stored in database. Lets your database system machine is placed in the same organization, now if tomorrow database machine crashed then???, all your data gone, what about that organization business records, is that organization will be able to continue business?
So if you had solid Disaster Recovery plan for your organization data then your organization business will not stop and it can continue, for the organization only it will be a matter of one system crash. So in disaster recovery what you do is configure some backup mechanism on some other machine to keep the same piece of information what you have on your current database machine. So if your current database machine dead then you can recover your all data from backup machine.
The first step in developing a solid disaster recovery plan is to develop an idea of what constitutes an acceptable loss for your organization. First, consider the impact of losing data stored in your database. Would you be able to recover from the loss of an hour's worth of data? If you're managing a human resources database, chances are that you could deal with this situation by instructing your personnel to reenter data entered during that period. If you're running the database supporting a financial institution, the loss of an hour's data could bring the instant wrath of clients and industry regulators along with significant monetary losses. Protection against these types of losses is provided by backups of the database and use of transaction logging. We'll discuss these in further detail.
Disaster recovery planning is the work that is devoted to preparing all the actions that must occur in response to a disaster. The planning includes the selection of a strategy to help recover valuable data. The selection of the appropriate disaster recovery strategy depends on your business requirements.
Disaster recovery in SQL Server
Microsoft SQL Server database system provides multiple ways to configure disaster recovery.
- Timely database Backup strategies
- Database Replication
- Log shipping
- Database Mirroring
Timely Database Backup Strategies
If your disaster recovery plan you are planning for timely databases backup then all what you have to do is:
- Take backup of all database from the database server (you can schedule a job in sql server to do take backup of all databases)
- Copy all back up files to your file server or some other directory
- You can write schedule daily/weekly backup plan in sql server
- Write your daily backup / weekly back up in disk and store the disk at some safe place
SQL Server replication, a software package included in Microsoft SQL Server, is used to move data from server to server in a transitionally consistent state from one instance to another.
Replication topology consists of three servers – the subscriber, publisher and distributor. The subscribers are the servers receiving the data. The publisher is the server that holds the initial set of data being made available to the subscribers. The distributor is the server that contains many of the settings. It also holds the data as it is moved from the publisher to the subscribers.
Snapshot Replication Snapshot replication is the easiest replication type to understand because it is conceptually similar to full backup and restore. With snapshot replication the server copies entire set of data to the subscriber at scheduled time, rewriting the data at subscriber with each copy of operation. However snapshot replication does not operate on the complete the database as backup and restore does. Snapshot replication copies only specified articles from publisher to the subscribers. Keep in mind because snapshot replication copies entire set of data every time it runs, you should use this replication type only when the amount of data is small and rather static.
Transactional Replication Peer-to-peer transactional replication is designed for applications that might read or might modify the data in any database that participates in replication. Additionally, if any servers that host the databases are unavailable, you can modify the application to route traffic to the remaining servers. The remaining servers contain identical copies of the data.
Log shipping Log shipping is the process of automating the backup of database and transaction log files on a production SQL server, and then restoring them onto a standby server. But this is not all, the key feature of log shipping is that it will automatically backup transaction logs throughout the day (for whatever interval you specify) and automatically restore them on the standby server. This in effect keeps the two SQL Servers in sync. Should the production server fail, all you have to do is point the users to the new server. Well, it's not really that easy, but it comes close if you put enough effort into your log shipping setup
Log shipping involves one Primary Server and one or many secondary server and one monitor server. First transaction log backup of database on primary server is taken and then the backup files are moved to the secondary servers and transaction log backups are applied to each of the secondary databases individually. An optional third server instance, known as the monitor server, records the history and status of backup and restores operations and, optionally, raises alerts if these operations fail to occur as scheduled.
Log shipping consists of three operations:
- Back up the transaction log at the primary server instance.
- Copy the transaction log file to the secondary server instance.
- Restore the log backup on the secondary server instance.
Each of the three secondary server instances runs its own copy job to copy the primary log-backup file to its own local destination folder.
Each secondary server instance runs its own restore job to restore the log backup from the local destination folder onto the local secondary database.
Database mirroring
The primary goal of Database Mirroring technology is to increase the database availability. When enabled, every update to the principal database is immediately reproduced onto the mirror database
Database mirroring is a primarily software solution for increasing database availability. You can only implement mirroring on a per-database basis. Mirroring only works with databases that use the full recovery model. The simple and bulk-logged recovery models do not support database mirroring. Therefore, all bulk operations are always fully logged. It also offers failover capabilities, which may be made automatic depending on how you configure mirroring. The mirrored copy is a standby copy that cannot be accessed directly. It is used only for failover situations
Principal Server: In a database mirroring configuration, there are two copies of a single database, but only one copy is accessible to the clients at any given time. The copy of the database that the applications connect to is called the principal database. The server that hosts the principal database is known as the principal server.
Mirror Server: The mirror is the copy of the principal database. The mirror is always in a restoring state; it is not accessible to the applications. To keep this database up-to-date, the log records are transferred from the principal and applied on the mirror database. The server that hosts the mirror database is known as the mirror server.
Witness Server: The optional witness is an SQL Server instance in a database mirroring configuration. It is separate from the principal and mirror instances. When database mirroring is used in synchronous mode, the witness provides a mechanism for automatic failover
Conclusion
In this article we had discussed about disaster recovery and disaster recovery plan in sql server. Disaster recovery plan is very important for an organization running business by using software application, so data recovery plan you can say is the business continuity plan. There are many ways you can configure disaster recovery for your database, in sql server database you can use Database Replication, Timely Backup Strategies, Log Shipping, and Database Mirroring.
