Deciphering Row level Encryption in MS SQL Server
This article briefs on the row level security feature in sql server and how to implement the same in a sql server environment
Accelerated Database Recovery(ADR):
SQL Server 2019 provides a way to limit the impact of large or Rolling back large running transactions on the entire process of database recovery with accelerated database recovery.
Apart from this if you want database to recover soon after an unexpected shut down or failover, Accelerated Database Recovery will overcome this situation .
By default Accelerated Database Recovery (ADR) is OFF in SQL Server.We can set ADR =’ON’ to each individual Database in SQL Server 2019.
The Recovery process is carried out in three ways.
(Source: Microsoft)
1.Analysis(figuring out changes that matter):Reads the transaction log forward from the last checkpoint
2.Redo (replaying changes):It will start from the oldest uncommited transaction and reads the log forward
3.Undo (reversing changes):It will start from the end of the log then reads backward and rolls back any transaction and not commited to the log
Some of the main features of ADR:
1.Persisted Version Store(PVS):It is stored in user database rather than tempdb and similarly contains previous versions of data modified or updated by transactions. The previous version of each row is written to PVS.
2.Logical revert: It is a row level undo using persisted version store if a transaction rolls back, logical revert allows a second transaction to use the persisted version store for the same row immediately, when it would otherwise be blocked waiting for the first transaction to finish rolling back.
3.SLog:In memory log contains activity that is not written to the PVS
4.Cleaner:Cleans up unneeded row versions from the PVS.
I Would like to preview ADR in two scenarios
1.During Sudden Shut down or Crash of SQL Server
2.While rolling back of transaction
-
Sudden shut down or crash of Sql Server
I Created two different database one with ADR=OFF(default) and other with ADR=ON
Create Database DBA1 — (default with ADR=OFF)
Go
Create Database DBA2
Alter Database DBA2 set accelerated_database_recovery =ON; — ( Database with ADR=ON)
Go
Then i executed the query which will take few minutes to execute in first window for Database DBA1 nearly at 8:40:00
WAITFOR TIME ‘08:41:30’;
USE DBA1
GO
DROP TABLE IF EXISTS dbo.fl1, dbo.fl2, dbo.fl3;
SELECT s2.* INTO dbo.fl1 FROM sys.all_columns AS s1 CROSS JOIN sys.all_objects AS s2;
SELECT s2.* INTO dbo.fl2 FROM sys.all_columns AS s1 CROSS JOIN sys.all_objects AS s2;
SELECT s2.* INTO dbo.fl3 FROM sys.all_columns AS s1 CROSS JOIN sys.all_objects AS s2;
Next to few seconds i executed same query for Database DBA2 in new window
WAITFOR TIME ‘08:41:30’;
USE DBA2;
GO
DROP TABLE IF EXISTS dbo.fl1, dbo.fl2, dbo.fl3;
SELECT s2.* INTO dbo.fl1 FROM sys.all_columns AS s1 CROSS JOIN sys.all_objects AS s2;
SELECT s2.* INTO dbo.fl2 FROM sys.all_columns AS s1 CROSS JOIN sys.all_objects AS s2;
SELECT s2.* INTO dbo.fl3 FROM sys.all_columns AS s1 CROSS JOIN sys.all_objects AS s2;
In third new window i executed this to result a Stop or Crash of SQL Server in a Minute.
WAITFOR DELAY ‘00:01:00’;
SHUTDOWN WITH NOWAIT;
By Executing above command the Sql server working will stop
Again after starting the SQl server and checking in Log File Viewer the database DBA2 is recovered very earlier than DBA1 .This is because ADR is enabled on DBA2 database.
As we can see that the database DBA1 with ADR OFF took 91 seconds to recover and database DBA2 with ADR ON took 6 Seconds to recover
2.While rolling back of transaction
For the same Databases i will perform Transaction and Enable statistics time to measure transaction time taken
SET STATISTICS TIME ON
GO
By executing below query for DBA1 with ADR =OFF
use DBA1
GO
BEGIN TRANSACTION
DROP TABLE IF EXISTS GEOPITS;
SELECT TOP 10000000 a1.*
INTO GEOPITS
FROM sys.all_objects AS a1
CROSS JOIN sys.all_objects AS a2
CROSS JOIN sys.all_objects AS a3
GO
After executing this Rolling back the transaction
ROLLBACK
GO
In this case it took 16 seconds to rollback transaction as we can see in below image as well
Now let us try for the Database DBA2 with ADR=ON with the same transaction.
use DBA2
GO
BEGIN TRANSACTION
DROP TABLE IF EXISTS GEOPITS;
SELECT TOP 10000000 a1.*
INTO GEOPITS
FROM sys.all_objects AS a1
CROSS JOIN sys.all_objects AS a2
CROSS JOIN sys.all_objects AS a3
GO
— After executing this Rolling back the transaction
ROLLBACK
GO
In this case it took 0 seconds to rollback transaction, hence it is one of the wonderfull feature which accelerate lot of work with minimum time.
