FAST Recovery with SQL Server September 16th, 2010

Vinod Kumar

Microsoft SQL Server 2005 onwards the Enterprise Edition has an improved hidden feature on availability of databases recovering from a crash or during a database mirroring failover by introducing the fast recovery feature. image

Fast recovery makes the database available during the undo phase. SQL Server crash recovery and database mirror failover include multiple phases. The first phase involves applying logged transactions to roll forward the data. This is known as the redo phase. After the redo phase has rolled forward all the log transactions, a database typically contains uncommitted changes made by transactions. The recovery process then rolls back all uncommitted transactions in the undo phase. In this case, SQL Server allows users to access the database during this undo phase. This makes the DB available faster than normal … This is very critical for availability SLA’s of databases within the enterprises.

More on the recovery of databases and the stages are outlined over MSDN SQL Server BOL. Feel free to read the complete process from documentation.

Fast recovery is possible because transactions that were uncommitted when the crash or failover occurred reacquire whatever locks they held before the crash or failover. While these transactions are being rolled back, their locks protect them from interference by users. If you recollect this is based on the snapshot capability that was introduced with the SQL 2005 and above Enterprise editions.

Biggest benefits of Fast recovery lets users access the database during the undo phase, reducing the time that a database is unavailable during these already time-sensitive activities. Earlier versions of SQL Server and SQL Server 2005 Standard Edition do not allow access to the database until completion of the undo phase.

Tags: , , , , , , , ,

This entry was posted on Thursday, September 16th, 2010 at 05:41 and is filed under Uncategorized. You can follow any responses to this entry through the RSS 2.0 feed. You can leave a response, or trackback from your own site.

One Response to “FAST Recovery with SQL Server”

  1. Michael says:

    This is useful thing. When my SQL server broke down I didn’t know what to do. It take half a day for me to recover it.

Leave a Reply