Does In-Memory OLTP Work with Always Encrypted? (AKA That time Bob Ward asked us a question)

Last week was the PASS Summit, which is the biggest confab of SQL Server professionals on the planet (and educational as ever), Denny Cherry  (b|t) and I ran into Bob Ward (b|t) of Microsoft and of 500 level internals presentations. And for the first time ever, Bob asked us a question about SQL Server—of course we didn’t know the answer of the top of our heads, but we felt obligated to research it like we’ve made Bob do so many times. Anyone, the question came up a Bob’s internals session on Hekaton (In-Memory OLTP) and whether it supported the new Always Encrypted feature in SQL Server 2016. I checked books online, but could not find a clear answer, so I fired up SSMS and setup a quick demo.

Set up Hekaton

Let’s get started with setting up Hekaton.

CREATE DATABASE imoltp  
GO 
 
————————————– 
— create database with a memory-optimized filegroup and a container. 
ALTER DATABASE imoltp ADD FILEGROUP imoltp_mod CONTAINS MEMORY_OPTIMIZED_DATA  
ALTER DATABASE imoltp ADD FILE (name=’imoltp_mod1′, filename=’c:\temp\imoltp_mod1′) TO FILEGROUP imoltp_mod  
ALTER DATABASE imoltp SET MEMORY_OPTIMIZED_ELEVATE_TO_SNAPSHOT=ON 
GO 

In that code, I’m just doing the setup for In-Memory OLTP—you need to added a filegroup, as well as enable the MEMORY_OPTIMIZED_ELEVATE_TO_SNAPSHOT_ON.

Always Encrypted Setup

Next, we needed to setup up Always Encrypted.

In order to configure this, you’ll first need to setup a Column Master Key, and then a Column Encryption Key. I took a sample table from books online, and attempted to encrypt it.

CREATE TABLE [Employee_Encrypted](
    [BusinessEntityID] [int] NOT NULL IDENTITY(1,1) ,
    [NationalIDNumber] [char](11) COLLATE Latin1_General_BIN2
        ENCRYPTED WITH (ENCRYPTION_TYPE = DETERMINISTIC,
        ALGORITHM = ‘AEAD_AES_256_CBC_HMAC_SHA_256’,
        COLUMN_ENCRYPTION_KEY = ColumnKey1)
        NOT NULL,
    [LoginID] [nvarchar](256) NULL,
    [BirthDate] [date]
        ENCRYPTED WITH (ENCRYPTION_TYPE = RANDOMIZED,
        ALGORITHM = ‘AEAD_AES_256_CBC_HMAC_SHA_256’,
        COLUMN_ENCRYPTION_KEY = ColumnKey1)
        NOT NULL,
         CONSTRAINT [PK_Employee_Encrypted_BusinessEntityID] PRIMARY KEY NONCLUSTERED
(
    [BusinessEntityID] ASC
)
)
WITH  (MEMORY_OPTIMIZED=ON)

With that T-SQL (and a couple of attempts at getting my parentheses in the right place) it just ran. Nothing major to do—just create table. I confirmed this behavior with Jos de Bruijn, who is the program manager for Hekaton.