Read time 4 minutes
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.
Accelerated database recovery (ADR) – introduction
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:
- Persistent Version Store (PVS) Mechanism –
This mechanism allows persisting the row versions in the database only rather keeping it in the old tempdb store. Through PVS,- More Readable Secondaries
- Resource isolation is enabled
- Logical Revert Process –
It is an asynchronous procedure which offers quick rollback/undo operations. Through Logical Revert,- Rollback for all user transactions via Persistence Version Store
- Immediate releasing of all locks once transaction is aborted
- Storing track for all the aborted transactions
- sLog Service –
It is a low-volume, secondary in-memory log stream for storing log records for non-versioned operations. Through sLog,- Periodic truncation of sLog
- Accelerated Redo and Undo processes via non-versioned operations only
- Faster log transaction with only required log records
- Page versions Cleaner –
As the name suggests, it cleans unrequired page versions, works periodically and in non-synchronized manner.
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?
Accelerated database recovery (ADR) – working process
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
- Introduction of reconstructing sLog
- Storing log records for non-versioned operations
This phase is improved by breaking it into 2 phases.
- In the first phase, Redo is performed through sLog which starts from the last uncommitted transaction to the latest checkpoint.
- In the second phase, Redo is performed through transaction log which starts from the latest checkpoint.
- First phase is faster operation than the second phase as few records are needed to be processed in sLog.
This Undo phase with ADR is performed very quickly.
- Using sLog to undo non-versioned operations.
- Row-level version -based Undo is performed using Persisted Version Store with Logic Revert.
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.
