Sql Server 2019 Supports Database Snapshots with In-Memory Filegroups

Today I want to touch on rather obscure and unfairly under appreciated features in Sql Server – Hekaton (aka In-Memory) and Database Snapshots.
Database Snapshots can serve for so many great purposes, such as Quick Restores and DWH/BI/Reporting reading operations, between others.
In-Memory is still the feature that I love a lot, and while the most active Programming Feature has not seen a lot (or enough) development, because frankly we are back to “Chicken & Egg, Who was first” kind of problem, I see little but important developments and most importantly feature integration. The original implementation in Sql Server 2014 and further improvements in Sql Server 2016 & Sql Server 2017 have improved the programming surface to being useful member of the toolkit, but some of the current limitations are dreading for a number of people and projects.

Well, it seems that Microsoft has been silently working on the improvements and one of them is the support for the Database Snapshots of the In-Memory File Groups in the Sql Server 2019.

The current state of the Sql Server 2017 and previous product versions were like this – here is the script I have executed on Sql Server 2017, with the help of the restore of a copy from the free ContosoRetailDW database (but you can just create an empty one):

USE [master]
 
alter database ContosoRetailDW
    set SINGLE_USER WITH ROLLBACK IMMEDIATE;
 
RESTORE DATABASE [ContosoRetailDW] 
    FROM  DISK = N'C:\Install\ContosoRetailDW.bak' WITH  FILE = 1,  
        MOVE N'ContosoRetailDW2.0' TO N'F:\Data\ContosoRetailDW.mdf', 
        MOVE N'ContosoRetailDW2.0_log' TO N'F:\Data\ContosoRetailDW.ldf',  
        NOUNLOAD,  STATS = 5;
 
alter database ContosoRetailDW
    set MULTI_USER;
GO

GO
ALTER DATABASE [ContosoRetailDW] SET COMPATIBILITY_LEVEL = 140
GO
ALTER DATABASE [ContosoRetailDW] MODIFY FILE ( NAME = N'ContosoRetailDW2.0', SIZE = 2000000KB , FILEGROWTH = 128000KB )
GO
ALTER DATABASE [ContosoRetailDW] MODIFY FILE ( NAME = N'ContosoRetailDW2.0_log', SIZE = 400000KB , FILEGROWTH = 256000KB )
GO

Let’s add an In-Memory File Group to our Database (the requirement to be in the same folder still seems to be quite weird to me after all these years):

 USE master;

ALTER DATABASE [ContosoRetailDW] 
	ADD FILEGROUP [ContosoRetailDW_Hekaton] 
	CONTAINS MEMORY_OPTIMIZED_DATA
GO

ALTER DATABASE [ContosoRetailDW]
	ADD FILE(NAME = ContosoRetailDW_HekatonDir, 
	FILENAME = 'F:\Data\ContosoRetailDW') 
	TO FILEGROUP [ContosoRetailDW_Hekaton];

GO

Let’s create a new In-Memory table as a structural copy of the table FactOnlineSales and load 200.000 rows into it:

CREATE TABLE [dbo].[FactOnlineSales_Hekaton](
	[OnlineSalesKey] [int] NOT NULL,
	[DateKey] [datetime] NOT NULL,
	[StoreKey] [int] NOT NULL,
	[ProductKey] [int] NOT NULL,
	[PromotionKey] [int] NOT NULL,
	[CurrencyKey] [int] NOT NULL,
	[CustomerKey] [int] NOT NULL,
	[SalesOrderNumber] [nvarchar](20) NOT NULL,
	[SalesOrderLineNumber] [int] NULL,
	[SalesQuantity] [int] NOT NULL,
	[SalesAmount] [money] NOT NULL,
	[ReturnQuantity] [int] NOT NULL,
	[ReturnAmount] [money] NULL,
	[DiscountQuantity] [int] NULL,
	[DiscountAmount] [money] NULL,
	[TotalCost] [money] NOT NULL,
	[UnitCost] [money] NULL,
	[UnitPrice] [money] NULL,
	[ETLLoadID] [int] NULL,
	[LoadDate] [datetime] NULL,
	[UpdateDate] [datetime] NULL,
	Constraint PK_FactOnlineSales_Hekaton PRIMARY KEY NONCLUSTERED HASH ([OnlineSalesKey])
		WITH (BUCKET_COUNT = 2000000)
) WITH (MEMORY_OPTIMIZED = ON, DURABILITY = SCHEMA_AND_DATA);

insert into dbo.FactOnlineSales_Hekaton
	select top 200000 *
		from dbo.FactOnlineSales;

CHECKPOINT;

Advancing to the Database Snapshot Creation within our Sql Server 2017:

CREATE DATABASE ContosoRetailDW_Snapshot ON  
	( NAME = [ContosoRetailDW2.0], FILENAME = 'C:\Data\ContosoRetailDW.ss' )  
		AS SNAPSHOT OF ContosoRetailDW;  

The error message is quite explicit saying that having MEMORY_OPTIMIZED_DATA filegroup is not supported for the Database Snapshots.

Or better was not until Sql Server 2019.

Database Snapshots in Sql Server 2019

Running pretty much the same code (with some according modifications for the locations) on Sql Server 2019

USE [master]
 
alter database ContosoRetailDW
    set SINGLE_USER WITH ROLLBACK IMMEDIATE;
 
RESTORE DATABASE [ContosoRetailDW] 
    FROM  DISK = N'C:\Install\ContosoRetailDW.bak' WITH  FILE = 1,  
        MOVE N'ContosoRetailDW2.0' TO N'C:\Data\ContosoRetailDW.mdf', 
        MOVE N'ContosoRetailDW2.0_log' TO N'C:\Data\ContosoRetailDW.ldf',  
        NOUNLOAD,  STATS = 5;
 
alter database ContosoRetailDW
    set MULTI_USER;
GO

GO
ALTER DATABASE [ContosoRetailDW] SET COMPATIBILITY_LEVEL = 150
GO
ALTER DATABASE [ContosoRetailDW] MODIFY FILE ( NAME = N'ContosoRetailDW2.0', SIZE = 2000000KB , FILEGROWTH = 128000KB )
GO
ALTER DATABASE [ContosoRetailDW] MODIFY FILE ( NAME = N'ContosoRetailDW2.0_log', SIZE = 400000KB , FILEGROWTH = 256000KB )
GO

and in my tests changing the compatibility level to the lower (140, 130) did not change the functionality.

Adding the In-Memory Filegroup and a copy of 200.000 rows into the new In-Memory FactOnlineSales_Hekaton table:

 USE master;

ALTER DATABASE [ContosoRetailDW] 
	ADD FILEGROUP [ContosoRetailDW_Hekaton] 
	CONTAINS MEMORY_OPTIMIZED_DATA
GO

ALTER DATABASE [ContosoRetailDW]
	ADD FILE(NAME = ContosoRetailDW_HekatonDir, 
	FILENAME = 'F:\Data\ContosoRetailDW') 
	TO FILEGROUP [ContosoRetailDW_Hekaton];

GO
USE ContosoRetailDW;
GO

CREATE TABLE [dbo].[FactOnlineSales_Hekaton](
	[OnlineSalesKey] [int] NOT NULL,
	[DateKey] [datetime] NOT NULL,
	[StoreKey] [int] NOT NULL,
	[ProductKey] [int] NOT NULL,
	[PromotionKey] [int] NOT NULL,
	[CurrencyKey] [int] NOT NULL,
	[CustomerKey] [int] NOT NULL,
	[SalesOrderNumber] [nvarchar](20) NOT NULL,
	[SalesOrderLineNumber] [int] NULL,
	[SalesQuantity] [int] NOT NULL,
	[SalesAmount] [money] NOT NULL,
	[ReturnQuantity] [int] NOT NULL,
	[ReturnAmount] [money] NULL,
	[DiscountQuantity] [int] NULL,
	[DiscountAmount] [money] NULL,
	[TotalCost] [money] NOT NULL,
	[UnitCost] [money] NULL,
	[UnitPrice] [money] NULL,
	[ETLLoadID] [int] NULL,
	[LoadDate] [datetime] NULL,
	[UpdateDate] [datetime] NULL,
	Constraint PK_FactOnlineSales_Hekaton PRIMARY KEY NONCLUSTERED HASH ([OnlineSalesKey])
		WITH (BUCKET_COUNT = 2000000)
) WITH (MEMORY_OPTIMIZED = ON, DURABILITY = SCHEMA_AND_DATA);

insert into dbo.FactOnlineSales_Hekaton
	select top 200000 *
		from dbo.FactOnlineSales;

CHECKPOINT;

All functioned well as in Sql Server 2017.

Advancing to the Database Snapshot Creation within our Sql Server 2019 RC1 results in the following success message:

CREATE DATABASE ContosoRetailDW_Snapshot ON  
	( NAME = [ContosoRetailDW2.0], FILENAME = 'C:\Data\ContosoRetailDW.ss' )  
		AS SNAPSHOT OF ContosoRetailDW;  

Looks fine in action and opening this Database Snapshot in SSMS brings you the following screen allowing you to see all available objects with our just created table dbo.FactOnlineSales_Hekaton.

Running a simple test against both tables results in the original Database and in the Snapshot in the following:

use ContosoRetailDW;

select top 100 *
	from ContosoRetailDW.dbo.FactOnlineSales_Hekaton

use ContosoRetailDW_Snapshot;

select top 100 *
	from dbo.FactOnlineSales_Hekaton

I have played inserting and deleting the data in the original Database into regular and memory-optimised tables and have not found any obvious problems so far, but I did not do an exhausting testing.

Restoring from the Database Snapshot

The easy thing to test that was missing from this rather simple blog post is a restore from the Database Snapshot containing InMemory Filegroup and here is the code to do this operation (Naturally you will have to exit contexts of both databases before advancing for this task):

use master;

ALTER DATABASE ContosoRetailDW
    SET SINGLE_USER WITH ROLLBACK IMMEDIATE;

RESTORE DATABASE ContosoRetailDW 
	FROM DATABASE_SNAPSHOT = 'ContosoRetailDW_Snapshot';  
GO  

ALTER DATABASE ContosoRetailDW
    SET MULTI_USER;
GO

The error is very much disappointing:

Msg 3138, Level 16, State 2, Line 6
The database cannot be reverted because FILESTREAM BLOBs are present.
Msg 3013, Level 16, State 1, Line 6
RESTORE DATABASE is terminating abnormally.

It is not like a total killer, one can always copy data over to the database from the snapshot … but doing that for thousands of tables does not sound like a solid plan.

Final Thoughts

New Technology is always 2 steps forward and then a step back.
We are not in the times when compatibility of the features with usual suspects are kept. We live in the times when a feature is asked and demanded for a healthy financial return.
When so many great features are not even blogged about, I can see the reasons why they are not being used.
The Snapshots with Filegroup themselves are not the principal target in this implementation in my understanding, this is a building stone for other functionalities and I am anxious to see the next steps after Sql Server 2019.