SQL 2019 – Database Snapshot for In-Memory OLTP table

By | November 25, 2019

Traditionally Database snapshots have been used mainly to get Database mirroring secondary’s to act like Readable secondary. As you might be aware in mirroring etc. the databases are restored with no recovery. Since the transaction log file isn’t in a consistent state the database engine prevents users from querying them. However in the interest if being able to utilize hardware more efficiently companies often create a database snapshot of the database being mirrored. The snapshot creates a Transactional consistent version of the database and thus enables querying against it.

For a long time this was the accepted industry practice for dealing with such situations. But when Microsoft announced that Database Mirroring was going defunct in SQL 2012 and then Introduced In-memory OLTP in SQL 2014 they had a choice to make. Switch to Always ON for readable secondary databases or use replication. This matter was made worse by the fact that In-Memory OLTP database didn’t support Database Snapshots. The below screenshot shows the error you would get in SQL 2016 if you tried to create a database snapshot.

In SQL 2019 we can now create snapshots of databases that have in memory objects in its catalog. This makes for a very interesting use case since companies have shifted from database mirroring over the years.

The syntax for creating the snapshot remains the same as before


DATABASE inmem_snp ON

= inmem,

‘G:\Program Files\SQL Server\SQL2019B\MSSQL15.SQL2019B\MSSQL\DATA\inmemnapshot.ss’

OF inmem;



The end result is the snapshot is created as always but this time without any errors because of the presence of in memory objects.

The copy of in memory data resides in the RAM so there is a need to provision additional RAM for storing the data.

Category: Databases

About opsadmin

This post was written by Jayanth Kurup. A Microsoft SQL Server Consultant and Trainer based out of Bangalore, India. Jayanth has been working on MS SQL Server for over 15 years. He is a performance tuning and Business Intelligence expert. Having worked with companies like Microsoft, DELL, Wells Fargo, Thomson Reuters and many other fortune 100 companies. Some other technologies Jayanth works on include Microsoft Azure, PowerBI, Python and AWS. When he isn’t consulting or training, Jayanth like to travel, paint and read. He is also very active in social causes and the founder of Enabled Business Solutions. Visit his company by clicking the link in the menu or email him directly.