Snapshots and In-Memory OLTP

One of the many things I speak about regularly at Pure Storage is using storage-array based snapshots to create crash consistent snapshots of SQL Server data & log files. But what if one of my databases is using In-Memory OLTP?

TL;DR

Can I still take storage-array snapshots and if yes, will I lose data in my memory-optimized tables? What about data inside my non-durable tables?

Yes, you can take storage-array level crash consistent snapshots on FlashArray and data in your memory-optimized tables will remain intact in all volumes cloned from the snapshot.

How Does All Of This Work?

In-Memory OLTP is all about storing your data in RAM. However, there are two different types of table constructs: memory-optimized tables and non-durable tables.

• Memory-optimized tables have a secondary copy of their contents stored on disk, but only for the case of server crashes.
  
• Non-durable tables are also memory-optimized tables. However, they differ in that they are defined with their DURABILITY property set to SCHEMA_ONLY. This means the structure of the table is persisted to disk, but never the underlying data.

Because of the need for durability, we can still take storage-array level snapshots that are crash consistent, and use those snapshots for various purposes like dev/test database refreshes. We just would not get the ephemeral data in the non-durable tables, but that’s no different than not getting data in temp tables or table variables.

And remember that crash consistent snapshots differ from application consistent snapshots. Most of us SQL Server professionals are familiar with the latter, that have to go through VSS and stun SQL Server. Crash consistent snapshots do not stun the server though there’s other trade-offs (I ought to just write a blog breaking that difference down, shouldn’t I?).

Have a Demo?

At some point in the future, I’ll make a video recording of me doing this demo, which I’ll post to YouTube and link to here.

I’ve taken the liberty of creating some scripts that will help demonstrate this. This specific example is for two SQL Server instances on VMware, with FlashArray behind the scenes, using vVols. The example database, AdventureWorks_EXT, has the data and log files all on the same vVol for demo simplicity.

There are 5 scripts in the demo, which you can find on my personal github here.

1. 1_SETUP – SourceSvr – AdvWrks.sql
   Restores a copy of AdventureWorks on the SOURCE server. Get a backup file here. Then adds memory-optimized objects and data, using sample code from here.
   
2. 2_SETUP – TargetSvr – AdvWrks.sql
   Creates a stub of AdventureWorks on the TARGET server. Was generated from SSMS using ‘Script Database as CREATE’ on the SOURCE server’s copy of AdventureWorks. You can substitute your own instead of running this script.
   
3. 3_DEMO – SourceSvr – AdvWrks.sql
   First step of the actual demo, which executes the in-memory OLTP sample code to create some durable and non-durable data.
   
4. 4_DEMO – PowerShell Snapshots.ps1
   Second step of the actual demo, that uses PowerShell to take a crash consistent snapshot of the Source vVol and overlay the Target vVol.
   
5. 5_DEMO – TargetSvr – AdvWrks.sql
   Final step of the demo, to query the memory-optimized and non-durable tables on the Target, to see if the data created in ‘3_DEMO – SourceSvr – AdvWrks’ was replicated to the Target server.
   

One More Caveat

If you also use delayed durability, note that transactions that have not yet been hardened will also not be present in a crash consistent snapshot. It is a trade-off of delayed durability, but is no different than losing a non-hardened transaction with a SQL Server crash/failure.

Hope this was helpful – thanks for reading!