Sql-server – DLL Error While Creating an In-Memory Table in SQL Server 2014

memory-optimized-tablessql serversql server 2014

While trying to create an in-memory table on a pre production box we get the following error.

CREATE TABLE [stage].[MyData]
(
   [ID] [VARCHAR](50) COLLATE Latin1_General_100_BIN2 NOT NULL INDEX imxMyID NONCLUSTERED,
   [Col1] [VARCHAR](15) NULL,
   [Col2] [VARCHAR](5) NULL,
   [Col3] [NUMERIC](12,2) NULL
) WITH (MEMORY_OPTIMIZED = ON, DURABILITY = SCHEMA_ONLY)

Msg 41309, Level 16, State 1, Line 0 Unable to load the compiled DLL
for database ID 9.

I understand the architecture of in-memory tables is such that SQL Server generates a DLL out of the in memory object which it then loads into memory. My guess is that operation is the source of this error – however, either my bing-fu skills are failing me, or there is really nothing in the wild that might lead me to resolution for this problem.

My gut is that it's some type of group policy or security issue as this is a pre-production stage environment with anti-virus, parity, the works – just like production has – but need to solve as this is meant for production some day. The only GPO mentioned in documentation is 'Instant File Initialization' for performance reasons.

Anyone deal with this error before or understand the internals of in-memory objects enough to point me in the right direction? Exact same setup, database, statement works on my own local machine.

Best Answer

We have an internal "parity" server to protect our production and pre-production environments. After some research and help from the #sqlhelp tag on twitter we found the location of the DLLs that SQL Server generates prior to loading into memory. Once those were loaded into the parity server as approved locations for DLLs and EXEs - things were all good. So this was completely an environmental error - nothing to do with SQL Server per se.

Related Question