Microsoft SQL In-Memory OLTP in SQL Express 2019/2022

Question

I am trying to use SQL In-Memory OLTP. The limitation is that the solution needs to be compatible with SQL Express 2019 and 2022.

This link claims that In-Memory OLTP is compatible with all versions of SQL, including Express.

With version 2022 I am not managing to get started:

EXECUTE master.dbo.xp_create_subdir 'C:\data\'

GO

ALTER DATABASE S2X ADD FILEGROUP S2x_mod
CONTAINS MEMORY_OPTIMIZED_DATA;

GO

 ALTER DATABASE S2x ADD FILE (
 name='S2x_mod1', filename='c:\data\S2x_mod1')
 TO FILEGROUP S2x_mod;

I get as far as the last part of the above query and get the following error:

Could not process the operation. Always On Availability Groups replica manager is disabled on this instance of SQL Server. Enable Always On Availability Groups, by using the SQL Server Configuration Manager. Then, restart the SQL Server service, and retry the currently operation. For information about how to enable and disable Always On Availability Groups, see SQL Server Books Online.

When trying to enable the option via the SQL Server Configuration Manager, it says that turning on "Always On Availability Groups" is not possible with this version of SQL Express.

With the 2019 version I get past the above query and manage to create a memory-optimized table and native stored procedure:

-- Creating a memory-optimized table
CREATE TABLE dbo.State
(
    CID NVARCHAR(50) NOT NULL PRIMARY KEY NONCLUSTERED HASH WITH (BUCKET_COUNT = 1000),
    PNO INT NOT NULL,
    IsStart BIT NOT NULL,
    IsEnd BIT NOT NULL,
    CMPID NVARCHAR(50) NOT NULL
) 
WITH (MEMORY_OPTIMIZED = ON, DURABILITY = SCHEMA_AND_DATA);

GO

CREATE PROCEDURE dbo.GetStateByCID
@CID NVARCHAR(50)
WITH NATIVE_COMPILATION, SCHEMABINDING, EXECUTE AS OWNER
AS
BEGIN ATOMIC WITH
(
    TRANSACTION ISOLATION LEVEL = SNAPSHOT,
    LANGUAGE = N'English'
)
    SELECT CID, PNO, IsStart, IsEnd, CMPID
    FROM dbo.State
    WHERE CID = @CID;
END;

but when executing it:

EXEC    @return_value = [dbo].[GetStateByCID]
    @CID = N'1234'

I get the following error:

An error occurred while executing batch. Error message is: Internal connection fatal error. Error state: 15, Token : 0

I did not find a lot of help online on this; but it seemed to be an issue which was fixed in the 2017 version (see here). This does not make much sense to me, as I would have expected the fix to be included in the 2019 version by default. In any case, using In-Memory OLTP would only be possible for me, if it is compatible with both Express 2019 and 2022.

What am I missing here? Is In-Memory OLTP compatible with SQL express or not? Am I doing something wrong?

Answer 1

Is In-Memory OLTP compatible with SQL express or not?

Yes, as documented, it is.

Am I doing something wrong?

Yes.

Could not process the operation. Always On Availability Groups replica manager is disabled on this instance of SQL Server. Enable Always On Availability Groups, by using the SQL Server Configuration Manager. Then, restart the SQL Server service, and retry the currently operation. For information about how to enable and disable Always On Availability Groups, see SQL Server Books Online.

This is because you're running RTM and didn't apply any patches from the last ~3 years. This was fixed in CU1, the very first cumulative update. Once applied (CU14 is the latest at time of writing) you won't have this issue. I hope this imparts the importance of applying patches.

but when executing it [...] I get the following error [...]

Your execution is incorrect based on your supplied example. Your example has @ConnectionID = N'1234' as a parameter, yet the natively compiled stored procedure has the parameter of @CID which means you should receive an error about expected parameters are missing. Most likely, due to not patching, it's having an issue that is already fixed. I could not get my batch killed from the server side such as yours was, though I also didn't try very hard given it's incorrect on many levels which when fixed shouldn't have any issues.

Memory optimized tables are all about throwing lots and lots and lots of memory on your SQL Server.

That's one use case, if you're running into hot paths on locking and latching. Most of these aren't large tables, but smaller ones that are frequently updated or used by multiple concurrent connections, such as the session state in asp.net, however there are other uses such as shock absorbing for loading data (though I agree with the original comment of this scenario isn't very useful for Express edition), having tables with no logging (You don't care about the data, it doesn't' need to be recovered but needs to be available), etc.

I would like to use this as a state between Windows based micro-services. The idea would be to load the data once from normal (rather slow) table, and for the rest of the session, each micro-service would load the state from the (faster) in-memory table (deleting the data after the session).

Data in SQL Server is always acted upon in memory. SQL Server has various caching layers but the most notable (and noticeable) one is the buffer pool, which is responsible for holding database pages. The data is always in memory, except when there is the initial start up of the database or if there is memory pressure and the data page can be aged out.

The updates will also be done in memory, the disk will come into play when reading from it during database startup or when writing to it as part of checkpoint or lazy writer.

It sounds like some premature optimization here and I'd suggest using a normal table and see how that works for you. Unless you start running into massive locking and latching contention with a tuned workload, I don't foresee any major reason why in-memory only would be required (I also have no statistics for your workload). I have witnessed very few actual needs for Hekaton usage in SQL Server and most of those are from very high end workloads pushing hundreds of thousands of transactions per second - which Express Edition isn't going to have the capacity to handle.