Resources‎ > ‎

Business continuity

The information in your database is probably vital to the continued operations of your organisation. Indeed your organisation may well be unable to function if you either lose significant amounts of data or if the system is unavailable for a sustained period of time, such as if the server crashes.

Most organisations can cope with occasional loss of a small amount of data, say, a one in a hundred chance of losing up to 10 minutes worth of data once in any given year or a one in five chance of the server being out of action for an hour during work during the year. These types of loses and downtimes are usually sustainable and business continuity can be provided for by means of either a cool or a warm standby server. A disaster recovery situation may then cause inconvenience but it would not normally threaten the existence of the organisation. If any interruption is unacceptable (for example at a financial institution) then you need a SQL Server failover cluster, which completely eliminates all data loss and allow users to continue uninterrupted operations even during the total failure of one of the servers. Unfortunately such high availability solutions are both complicated to set-up and expensive.

This page will discuss what software options are available for database disaster recovery including Access databases SQL Server databases, log shipping and warm and hot standby servers.

Access Databases

There are only a limited number of options available with Access/JET databases. Essentially all you can do is to back-up the relevant .mdb file at regular intervals. As a minimum this should be done every night with the backups then removed off-site (in case of a major catastrophe such as a fire). You can also copy the database to a disk drive on a different computer at regular intervals during the working day, but be aware that taking copies whilst the database is in use may result in corruption and so is not recommended practice. The bottom line for most well-run Access installations is that you may still lose up to one day's worth of data if the worst happens. If this is not acceptable then either throw everyone off the database at regular intervals so it can be backed-up more frequently or migrate the database engine to a more robust solution such as SQL Server.

SQL Server

SQL Server provides a lot more possibilities than does Access.
One very important factor is that SQL Server can perform back-ups whilst the database is operational. You do not need to shut down SQL Server or stop people from using the database in order to make a back-up.

Another crucial factor is SQL Server's use of Transaction Logs. When an update is applied to SQL Server it is not saved directly into the database but is stored in a temporary location called the transaction log. Only when the client computer indicates that it has finished with the update is the information within the transaction log committed to the underlying database. This ensures that half-completed updates, which might put the database into an incorrect state, are not applied. It is the lack of this transaction log that makes Access susceptible to corruption if the network suffers from a temporary glitch or if a workstation crashes or is switched off at the wrong time.

There are two types of back-up regimes that can be used with SQL Server, the Simple and the Full Recovery models (there is also a third, Bulk-logged model, which is used in specialised situations). The Simple Model is only suitable for certain low transaction environments, such as during development. In most operational situations the database should be set up in Full Recovery mode. This allows you to back up both the database AND the Transaction Logs, which will only contain data changed since the last Transaction Log backup was taken and hence tend to be very small. As a result they back-up quickly and can be run at frequent intervals, say every 15 minutes. If disaster strikes you would backup the current Transaction Log, restore the last full database backup then all subsequent Transaction Log backups to get you back to the situation you were in just before the problem occurred.

Note: Backups should ideally NOT be stored on the same physical disk as the database. Instead they should be stored on a different physical disk and preferably copied on another server located in a different building.

Standby Server

A standby server comprises of a secondary server that can take over from the primary server in the event of a disaster and comes in cool, warm and hot flavours! Without a standby server then you are reduced to obtaining another server, configuring it, setting up the security, restoring the databases etc, with a potential downtime of anywhere from one day upwards (and remember that, in an emergency, you can always use a desktop PC as a temporary server).
A cool standby server is a spare server that is configured similarly to the primary server and ideally should be running the same version of Windows, SQL Server etc. and with all the same service packs applied. If the primary server suffers a failure then the SQL database is restored to the secondary server from the primary's backup files. This provides you with a reliable means to recover your database with minimal loss although there will still be a significant number of manual processes that must be followed in order to get you back up and running again, giving a typical downtime of between 2 hours and 1 day (a lot depends upon what the client applications are written in, how the client applications connect to the database, how easy it is to switch them to another database name, how many clients are involved, etc.).

To get to the next level of having a 'Warm' standby server you need to implement a range of automated procedures to keep the maintain same data on the two servers. Typically this involves some form of automatic synchronisation between the databases such as is provided by Log Shipping. Automated Log Shipping is included in the more expensive Enterprise edition of SQL Server. If you are using the lower cost Standard edition then you can still use Log Shipping but it requires manual setting-up and a good understanding of the processes involved (see our Backup/Standby Server/Log Shipping page if you would like us to set this up for you).

Log Shipping essentially consists of automating and integrating the process of backing up, copying and restoring the database from the primary server to the secondary server. This maintains the secondary server's database as an identical copy of the primary server's database apart from a small time latency of between 5-15 minutes.

Be aware that whilst Log Shipping keeps the databases in synchronisation you still need other procedures for SQL data held outside of the database such as logins, permissions, DTS packages, SQL Agent jobs, server configurations, etc. With a warm standby sever and log shipping you should be able to get up and running again in a disaster recovery scenario with a probable downtime of between 10 minutes and 1 hour depending upon how easy it is to change your client applications over to another server and how many clients you have.

Note: For our .NET applications we have developed an automated failover support facility. If the main database server is out of action our users just need to restart their application, at which point our applications recognise that the primary server is out of action and switch over automatically to the secondary server. This means that nobody needs to visit our client's user's machines to change them over manually!

An alternative to Log Shipping for a warm standby server is to use SQL Server Replication. This can be a good option in certain situations, such as when the secondary database is used as a read-only report server, but it is generally more complicated to set-up and administer than Log Shipping and, unlike Log Shipping, does not migrate changes to tables / stored procedures etc. so requires more support to keep operational. In most warm standby situations Log Shipping is a therefore a better option than Replication.

With both Log Shipping and Replication the warm standby server need not be in the same physical room as the main server. Indeed it is a good idea to locate the secondary server in a different building /location so as to protect against issues such as theft, fire and flooding.

To achieve a high availability situation whereby there is no downtime if a server crashes then you need a hot standby server with instant failover capability. For SQL Server databases this can either be provided by clustering, which requires the high end versions of both SQL Server and Windows Server, or by using a third-party solution such as DoubleTake or XoSoft. These solutions are invariably expensive and complex needing expertise to set-up correctly but if you cannot take the risk of even a small amount of downtime or data loss then these are what is required.

Summary

There are various options to protect your data in the event of disaster. The solution that is right for you depends upon your attitude to risk, how important your database is to you, how long it would take you to recreate it, how much downtime you can allow, how much data loss is acceptable and how much money are you prepared to pay. What is essential is that you at least consider the issues, come to a conclusion and then implement and test it. Despite all the warnings and disaster stories in the press we still get people phoning us up with corrupt/broken databases and no backup.

"I knew that I should have kept proper backups but somehow I just never got round to it, there was always something more important to do!"