In this blog, we will be discussing about the Accelerated Database Recovery feature of SQL Server and how is it an advantage for users waiting for so long for completing the running recovery transactions.
There are many scenarios when an SQL Server needs to run the automatic database recovery like when the administrator is running a large DML query update and then due to unfortunate reasons (deadlock, performance issues), this query needs to be terminated in the mid-way. Then this query will go into the Rollback state which is a long-recovery procedure and then user has to wait for its completion.
Let us take one more example, if the user starts inserting large numbers into any table and then the SQL Server system has got crashed. On restarting the system, the user needs to restart the SQL Services, the services will show online but still in the recovery mode for long time for which the users need to wait again.
To overcome this long-waiting process for SQL database recovery, a new feature Accelerated Database Recovery is introduced. Let us know about this feature.
ADR is a new feature introduced lately in the SQL database engine with improved database recovery process which helps in delivering the quicker recovery of database along with running transactions. It is available for single and pooled databases in Azure SQL database and all for all databases in Azure SQL Data Warehouse. ADR includes the following components:
So, we now know what Accelerated Database Recovery feature is and about its components. Let us discuss more on its working process.
Read Also : How to Recover Deleted Records in SQL Server?
ADR includes a redesigned SQL database engine recovery process. It helps in attaining faster recovery by versioning all the physical database changes while undoing limited logical operations. And the transactions active while the system got crashed is considered as aborted which would let concurrent queries avoid any transactions generated through these transactions.
The 3 improved phases in Accelerated Database Recovery are as follows:
This phase has improvements like
This phase is improved by breaking it into 2 phases.
This Undo phase with ADR is performed very quickly.
So, this Accelerated Database Recovery process is a boon for the SQL users stuck in the long running transactions and are experiencing long database availability period. Apart from this, if the SQL database is severely corrupted due to any unfortunate user and not accessible from any way, you have Kernel for SQL Database software along with it. This SQL Recovery software handles all errors and corruption issues quite smoothly and recovers database of all SQL versions in a very short time.