During a recent visit to my hometown, I had an interesting conversation with my cousin who is studying engineering. We started discussing InMemory databases and how they can be made resilient. While this topic may seem complex, it is actually quite fascinating.

My cousin, who had a SQL Server 2016 evaluation version, decided to explore this concept further. However, he encountered an error while trying to create an InMemory table. The error message he received was: “Cannot create memory optimized tables. To create memory optimized tables, the database must have a MEMORY_OPTIMIZED_FILEGROUP that is online and has at least one container.”

Upon analyzing the error message, it became clear that he had created a normal database without the InMemory filegroup. To help him resolve this issue, I provided him with a script that would eliminate the error:

-- Create the Database
-- Note the filegroup of type MEMORY_OPTIMIZED_DATA
USE MASTER
GO

IF DB_ID('InMem_OLTP') IS NOT NULL
BEGIN
    ALTER DATABASE [InMem_OLTP] SET SINGLE_USER WITH ROLLBACK IMMEDIATE
    DROP DATABASE [InMem_OLTP]
END

CREATE DATABASE [InMem_OLTP] ON PRIMARY
(
    NAME = N'InMem_OLTP_data',
    FILENAME = N'C:\DATA\InMem_OLTP_data.mdf',
    SIZE = 50MB
)
LOG ON
(
    NAME = N'InMem_OLTP_log',
    FILENAME = N'C:\DATA\InMem_OLTP_log.ldf',
    SIZE = 10MB
)
GO

--- Step 2 - Can we create this table?
USE InMem_OLTP
GO

CREATE TABLE [Customer]
(
    [CustomerID] INT NOT NULL PRIMARY KEY NONCLUSTERED HASH WITH (BUCKET_COUNT = 10000)
)
WITH
(
    MEMORY_OPTIMIZED = ON,
    DURABILITY = SCHEMA_AND_DATA
);
GO

As you can see, running the script without adding the InMemory filegroup will result in the same error message. To resolve this, we need to add the InMemory filegroup:

-- Step 3 -- Add a Filegroup that is MEMORY_OPTIMIZED.
USE InMem_OLTP
GO

ALTER DATABASE InMem_OLTP ADD Filegroup [InMem_OLTP_InMemory] CONTAINS MEMORY_OPTIMIZED_DATA
GO

ALTER DATABASE InMem_OLTP ADD FILE (NAME = InMem_OLTP_InMemory, FILENAME = N'C:\Data\InMem_OLTP_mopt') TO FILEGROUP [InMem_OLTP_InMemory]

After adding the InMemory filegroup, the table can be created without any errors. It’s important to note that my cousin’s confusion stemmed from not understanding what a container is in the context of InMemory tables. While this error may seem simple to experienced users, it can be a valuable troubleshooting resource for beginners like my cousin.

Understanding InMemory databases in SQL Server opens up a world of possibilities for optimizing performance and resilience. By leveraging the power of InMemory tables, you can significantly improve the speed and efficiency of your database operations.

So, the next time you encounter an error while working with InMemory databases, remember to check if the necessary filegroup is in place. This simple step can save you a lot of time and frustration.