github/sql-server-samples
1
0
Fork 0
mirror of https://github.com/Microsoft/sql-server-samples.git synced 2025-12-08 14:58:54 +00:00
Code Issues Releases Wiki Activity
Files
master
sql-server-samples/samples/features/accelerated-database-recovery/recovery_adr.ipynb
T
pmasl 49aba07072 Updated ADR Notebooks
2019-11-02 20:27:48 -07:00

36 KiB
Raw Permalink Blame History

Accelerated Database Recovery - Speed up Recovery

In this example you will learn how Accelerated Database Recovery will speed up recovery

Step 1: Setup the database

This demo uses the WideWorldImportersDW database. You can get the WideWorldImportersDW-Full.bak database backup file from https://github.com/Microsoft/sql-server-samples/releases/tag/wide-world-importers-v1.0.

The log file is enlarged to 4GB to avoid any autogrow.

The database assumes a path for a standard SQL Server Windows installation.

Depending on the speed of your server, enlarging the database and data could take several minutes.

Note: For Linux installations the default path to use is /var/opt/mssql

In [6]:
USE [master]
GO
IF EXISTS (SELECT [database_id] FROM sys.databases WHERE [name] = 'WideWorldImportersDW')
ALTER DATABASE [WideWorldImportersDW] SET SINGLE_USER WITH ROLLBACK IMMEDIATE
GO

DECLARE @datafilepath VARCHAR(8000) = CAST(SERVERPROPERTY('InstanceDefaultDataPath') AS VARCHAR(4000)) + 'WideWorldImportersDW.mdf'
DECLARE @logfilepath VARCHAR(8000) = CAST(SERVERPROPERTY('InstanceDefaultLogPath') AS VARCHAR(4000)) + 'WideWorldImportersDW.ldf'
DECLARE @inmemfilepath VARCHAR(8000) = CAST(SERVERPROPERTY('InstanceDefaultDataPath') AS VARCHAR(4000)) + 'WideWorldImportersDW_InMemory_Data_1'
DECLARE @secondaryfilepath VARCHAR(8000) = CAST(SERVERPROPERTY('InstanceDefaultDataPath') AS VARCHAR(4000))+ 'WideWorldImportersDW_2.ndf'

-- Change @backupfile file path as needed
DECLARE @backupfile VARCHAR(8000) = 'E:\SampleDBs\WideWorldImportersDW-Full.bak'
RESTORE DATABASE WideWorldImportersDW
FROM DISK = @backupfile 
WITH MOVE 'WWI_Primary' TO @datafilepath,
    MOVE 'WWI_UserData' TO @secondaryfilepath,
    MOVE 'WWIDW_InMemory_Data_1' TO @inmemfilepath,
    MOVE 'WWI_Log' TO @logfilepath, NOUNLOAD, REPLACE, STATS = 10
GO

USE [master]
GO
ALTER DATABASE [WideWorldImportersDW] MODIFY FILE ( NAME = N'WWI_Log', SIZE = 4GB )
GO
Commands completed successfully.
Nonqualified transactions are being rolled back. Estimated rollback completion: 0%.
Nonqualified transactions are being rolled back. Estimated rollback completion: 100%.
10 percent processed.
Processed 1192 pages for database 'WideWorldImportersDW', file 'WWI_Primary' on file 1.
Processed 28184 pages for database 'WideWorldImportersDW', file 'WWI_UserData' on file 1.
Processed 26 pages for database 'WideWorldImportersDW', file 'WWI_Log' on file 1.
Processed 24 pages for database 'WideWorldImportersDW', file 'WWIDW_InMemory_Data_1' on file 1.
Converting database 'WideWorldImportersDW' from version 852 to the current version 904.
Database 'WideWorldImportersDW' running the upgrade step from version 852 to version 853.
Database 'WideWorldImportersDW' running the upgrade step from version 853 to version 854.
Database 'WideWorldImportersDW' running the upgrade step from version 854 to version 855.
Database 'WideWorldImportersDW' running the upgrade step from version 855 to version 856.
Database 'WideWorldImportersDW' running the upgrade step from version 856 to version 857.
Database 'WideWorldImportersDW' running the upgrade step from version 857 to version 858.
Database 'WideWorldImportersDW' running the upgrade step from version 858 to version 859.
Database 'WideWorldImportersDW' running the upgrade step from version 859 to version 860.
Database 'WideWorldImportersDW' running the upgrade step from version 860 to version 861.
Database 'WideWorldImportersDW' running the upgrade step from version 861 to version 862.
Database 'WideWorldImportersDW' running the upgrade step from version 862 to version 863.
Database 'WideWorldImportersDW' running the upgrade step from version 863 to version 864.
Database 'WideWorldImportersDW' running the upgrade step from version 864 to version 865.
Database 'WideWorldImportersDW' running the upgrade step from version 865 to version 866.
Database 'WideWorldImportersDW' running the upgrade step from version 866 to version 867.
Database 'WideWorldImportersDW' running the upgrade step from version 867 to version 868.
Database 'WideWorldImportersDW' running the upgrade step from version 868 to version 869.
Database 'WideWorldImportersDW' running the upgrade step from version 869 to version 875.
Database 'WideWorldImportersDW' running the upgrade step from version 875 to version 876.
Database 'WideWorldImportersDW' running the upgrade step from version 876 to version 877.
Database 'WideWorldImportersDW' running the upgrade step from version 877 to version 878.
Database 'WideWorldImportersDW' running the upgrade step from version 878 to version 879.
Database 'WideWorldImportersDW' running the upgrade step from version 879 to version 880.
Database 'WideWorldImportersDW' running the upgrade step from version 880 to version 881.
Database 'WideWorldImportersDW' running the upgrade step from version 881 to version 882.
Database 'WideWorldImportersDW' running the upgrade step from version 882 to version 883.
Database 'WideWorldImportersDW' running the upgrade step from version 883 to version 884.
Database 'WideWorldImportersDW' running the upgrade step from version 884 to version 885.
Database 'WideWorldImportersDW' running the upgrade step from version 885 to version 886.
Database 'WideWorldImportersDW' running the upgrade step from version 886 to version 887.
Database 'WideWorldImportersDW' running the upgrade step from version 887 to version 888.
Database 'WideWorldImportersDW' running the upgrade step from version 888 to version 889.
Database 'WideWorldImportersDW' running the upgrade step from version 889 to version 890.
Database 'WideWorldImportersDW' running the upgrade step from version 890 to version 891.
Database 'WideWorldImportersDW' running the upgrade step from version 891 to version 892.
Database 'WideWorldImportersDW' running the upgrade step from version 892 to version 893.
Database 'WideWorldImportersDW' running the upgrade step from version 893 to version 894.
Database 'WideWorldImportersDW' running the upgrade step from version 894 to version 895.
Database 'WideWorldImportersDW' running the upgrade step from version 895 to version 896.
Database 'WideWorldImportersDW' running the upgrade step from version 896 to version 897.
Database 'WideWorldImportersDW' running the upgrade step from version 897 to version 898.
Database 'WideWorldImportersDW' running the upgrade step from version 898 to version 899.
Database 'WideWorldImportersDW' running the upgrade step from version 899 to version 900.
Database 'WideWorldImportersDW' running the upgrade step from version 900 to version 901.
Database 'WideWorldImportersDW' running the upgrade step from version 901 to version 902.
Database 'WideWorldImportersDW' running the upgrade step from version 902 to version 903.
Database 'WideWorldImportersDW' running the upgrade step from version 903 to version 904.
100 percent processed.
RESTORE DATABASE successfully processed 29426 pages in 4.707 seconds (48.839 MB/sec).
Commands completed successfully.
Commands completed successfully.
Total execution time: 00:01:18.619

Step 2: Create the table

Create a table with 1.8 million rows so that when we delete them all recovery will take a long time

In [7]:
IF DB_NAME() != 'WideWorldImportersDW' 
USE WideWorldImportersDW
SET NOCOUNT ON
GO

IF EXISTS (SELECT [object_id] FROM sys.objects (NOLOCK) WHERE [object_id] = OBJECT_ID(N'[Fact].[OrderHistory]') AND [type] IN (N'U'))
DROP TABLE [Fact].[OrderHistory];
GO

IF NOT EXISTS (SELECT [object_id] FROM sys.objects (NOLOCK) WHERE [object_id] = OBJECT_ID(N'[Fact].[OrderHistory]') AND [type] IN (N'U'))
BEGIN
    SELECT [Order Key], [City Key], [Customer Key], [Stock Item Key], [Order Date Key], [Picked Date Key], [Salesperson Key], [Picker Key], [WWI Order ID], [WWI Backorder ID], [Description], Package, Quantity, [Unit Price], [Tax Rate], [Total Excluding Tax], [Tax Amount], [Total Including Tax], [Lineage Key]
    INTO [Fact].[OrderHistory]
    FROM [Fact].[Order];
END;

ALTER TABLE [Fact].[OrderHistory]
ADD CONSTRAINT PK_Fact_OrderHistory PRIMARY KEY CLUSTERED([Order Key] ASC, [Order Date Key] ASC) WITH (DATA_COMPRESSION = PAGE);
GO

-- Enlarge Table
IF (SELECT COUNT(*) FROM [Fact].[OrderHistory]) < 1851296
BEGIN
	DECLARE @i smallint
	SET @i = 0
	WHILE @i < 3
	BEGIN
		INSERT INTO [Fact].[OrderHistory] ([City Key], [Customer Key], [Stock Item Key], [Order Date Key], [Picked Date Key], [Salesperson Key], [Picker Key], [WWI Order ID], [WWI Backorder ID], Description, Package, Quantity, [Unit Price], [Tax Rate], [Total Excluding Tax], [Tax Amount], [Total Including Tax], [Lineage Key])
		SELECT [City Key], [Customer Key], [Stock Item Key], [Order Date Key], [Picked Date Key], [Salesperson Key], [Picker Key], [WWI Order ID], [WWI Backorder ID], Description, Package, Quantity, [Unit Price], [Tax Rate], [Total Excluding Tax], [Tax Amount], [Total Including Tax], [Lineage Key]
		FROM [Fact].[OrderHistory];

		SET @i = @i +1
	END;
END
GO
Commands completed successfully.
Commands completed successfully.
Commands completed successfully.
Commands completed successfully.
Total execution time: 00:00:39.966

Step 3: Delete all the rows in the table

Delete all the rows in the table in a user defined transaction

In [8]:
USE master
GO
ALTER DATABASE WideWorldImportersDW SET ACCELERATED_DATABASE_RECOVERY = OFF
GO
-- Try to delete a bunch of rows
USE WideWorldImportersDW
GO
BEGIN TRAN
DELETE FROM [Fact].[OrderHistory]
GO
Commands completed successfully.
Commands completed successfully.
Commands completed successfully.
Commands completed successfully.
Total execution time: 00:00:36.659

Step 4: Checkpoint the database, shutdown SQL Server WITH NOWAIT, and restart it

Checkpoint will flush any dirty pages but the transaction is not committed. Shutting down the server with NOWAIT will not attempt to roll back active transactions. Therefore, SQL Server has to run undo to rollback this transaction.

Separate from this notebook, use a New Query in Azure Data Studio and run the following commands as a sysadmin.

Restart SQL Server and then examine the ERRORLOG to see how long recovery takes for the database. SQL Server must fully recover the database before you go to the next step. Because there is so much to recover this could take 5 minutes or more

USE WideWorldImportersDW
GO
CHECKPOINT
GO
SHUTDOWN WITH NOWAIT
GO

As seen below, SQL Server took just over 5 minutes to recover the database:

Recovery

Step 5: Enable ADR

The previous DELETE transaction was rolled back and the 1.8M rows are still in the table.

Turn on Accelerated Database Recovery and try to delete the rows again.

Note: If you prefer to enable ADR in the existing database file in the PRIMARY filegroup, just execute the last ALTER DATABASE without the ( PERSISTENT_VERSION_STORE_FILEGROUP = [PVS]) statement.

You may first see a connection error first since you shutdown SQL Server in the previous step but the cell should retry the connection and run the T-SQL statements.

In [8]:
USE master
GO
-- Add a new filegroup specific for the Persistent Version Store (optional)
ALTER DATABASE [WideWorldImportersDW] ADD FILEGROUP [PVS]
GO

DECLARE @adrdatafilepath VARCHAR(8000) = CAST(SERVERPROPERTY('InstanceDefaultDataPath') AS VARCHAR(4000)) + 'ADR.mdf'
DECLARE @sqlcmd VARCHAR(8000) = 'ALTER DATABASE [WideWorldImportersDW] ADD FILE ( NAME = N''ADR'', FILENAME = ''' + @adrdatafilepath + ''', SIZE = 1GB , FILEGROWTH = 65536KB ) TO FILEGROUP [PVS]'
EXEC (@sqlcmd)
GO

-- Enable ADR
ALTER DATABASE WideWorldImportersDW SET ACCELERATED_DATABASE_RECOVERY = ON ( PERSISTENT_VERSION_STORE_FILEGROUP = [PVS])
GO
Commands completed successfully.
Commands completed successfully.
Commands completed successfully.
Commands completed successfully.
Total execution time: 00:00:00.265

Step 6: Repeat test with ADR enabled

In [9]:
-- Try to delete a bunch of rows
USE WideWorldImportersDW
GO
BEGIN TRAN
DELETE FROM [Fact].[OrderHistory]
GO
Commands completed successfully.
Commands completed successfully.
Total execution time: 00:00:29.762

Step 7: Checkpoint the database, shutdown SQL Server WITH NOWAIT, and restart it with Accelerated Database Recovery

Checkpoint will flush any dirty pages but the transaction is not committed. Shutting down the server with NOWAIT will not attempt to roll back active transactions. Therefore, SQL Server has to run undo to rollback this transaction.

Separate from this notebook, use a New Query in Azure Data Studio and run the following commands as a sysadmin. Then restart SQL Server to examine the ERRORLOG to see how recovery takes for the database. You should see that recovery is significantly faster than before.

USE WideWorldImportersDW
GO
CHECKPOINT
GO
SHUTDOWN WITH NOWAIT
GO

As seen below, with ADR it only took approximately 56 seconds to recover the database:

Recovery

Reference in New Issue View Git Blame Copy Permalink