Does Azure SQL Database support In Memory Optimized Tables?

Question

I'm trying to look at the SQL Server 2014 and In Memory Optimized Tables and it integration with Power BI. And I'm thinking about what is the best way to migrate existing database (contains several In Memory Optimized tables) to Azure? Does Azure SQL Database support In Memory Optimized Tables?

Thanks.

Answer 1

As of Q4 2020, Azure SQL supports In-Memory OLTP (aka MEMORY_OPTIMIZED) in only a couple of pricing-tiers. This is half-documented in this (hard to find) Azure SQL documentation page, the other half I sourced from ancient blog posts and trial-and-error within the Azure Portal.

(Note again, this only applies to "Azure SQL" and not running the full-fat SQL Server in a VM or the Managed Instance service).

Here's a flowchart I made:

• Are you using DTUs or vCores?
  • DTUs:
    • Are you using the Basic tier (5 DTUs)? If so, then no, In-Memory is not supported.
    • Are you using the Standard tier (10-3000 DTUs)? If so, then no, In-Memory is not supported.
    • Are you using the Premium tier (125-4000 DTUs)? If so, then yes, In-Memory is supported.
  • vCores:
    • Are you using the General Purpose tier? If so, then no, In-Memory is not supported.
    • Are you using the Hyperscale tier? If so, then no, In-Memory is not supported.
    • Are you using the Business Critical tier? If so, then yes, In-Memory is supported.

You can find out if MEMORY_OPTIMIZED features are enabled in your database by opening SSMS, connecting to your database (your actual database, not master) and running this:

SELECT DatabasePropertyEx( DB_NAME(), 'IsXTPSupported' ) AS IsXTPSupported;

If you get 0 then In-Memory OLTP is not supported in your database. If you get 1 then it is supported.

And if you try to do CREATE TYPE dbo.Foo AS TABLE ( ... ) WITH ( MEMORY_OPTIMIZED = ON ); you'll get this error:

Msg 40536, Level 16, State 2, Line 2
'MEMORY_OPTIMIZED tables' is not supported in this service tier of the database. See Books Online for more details on feature support in different service tiers of Windows Azure SQL Database.

---

Wrly, I'm disappointed that for all the hype Microsoft has been building for In-Memory OLTP, it isn't available for the majority of Azure SQL customers. Even more surprising is that In-Memory is meant to reduce the IO burden, which should mean that In-Memory OLTP will reduce Microsoft's own operating-costs for Azure. I guess they're waiting to see how customers use it. Given the extra technical details and thorough-understanding of the system required to use it, it's possible that customers will end-up mis-using it - gotta protect users from themselves, y'know.