Utilizing Memory-Optimized Tables Without Enabling Always On Availability

Question

Utilizing Memory-Optimized Tables Without Enabling Always On Availability

Mohammad Iranishad 20

Hello,

I'm currently working on a SQL Server project where I extensively use memory-optimized tables to enhance performance. My development environment is SQL Server Developer Edition on Windows 11. While attempting to configure my database for memory-optimized tables, I encountered a requirement to enable Always On Availability Groups (AG) after creating a filegroup for memory-optimized data and trying to add a file to it. The exact error message was: "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." Given that Always On AG is typically associated with Windows Server environments and my setup is on Windows 11, I'm facing a dilemma on how to proceed. I understand Always On AG requires Windows Server Failover Clustering (WSFC), which isn't directly available on Windows 11. My questions are:

Is there a way to use memory-optimized tables without the need to enable Always On AG, especially considering my development environment on Windows 11?
Alternatively, is there any workaround or method to enable Always On AG functionality in a Windows 11 environment?

I appreciate any guidance or recommendations on how to address this issue, as I aim to continue my development work without switching to a Windows Server environment if possible. Thank you.

ZoeHui-MSFT 41,551 Reputation points

2024-02-12T01:56:47.6133333+00:00

Hi @Mohammad Iranishad, If the below reply helps on your issue, you could mark it as answer so other user with similar problem could see this easier. :)

Answer accepted by question author

Greg Low 2,110 Microsoft Regional Director

Yes, I have definitely used them on my laptop, running Windows 11 and the developer edition of SQL Server. And it doesn't have an AG in place. This seems to have been a known issue with the RTM version of SQL Server 2022: https://learn.microsoft.com/en-us/answers/questions/1131043/sql-2022-bug-filestream It affected filestream as well. That article says it was fixed with CU1. Do you have the latest CU for SQL Server 2022 installed? That might well fix it. Also, have you tested in-memory OLTP before? We've been testing it since 2014, and with every version, it gets more usable but it's no panacea for performance, except in very, very narrow use cases. What are you hoping to achieve with it? Almost all the real performance benefit comes from moving your code into in-memory native code, but the coding surface for that is very narrow, and it's very hard to make use of it. If all you do is move the tables, it's usually of little benefit. In fact, it can make the performance worse, as the way it works with interop and the way it spools data from in-memory back to legacy tables can lead to signficant performance penalties. Most people have used this feature more in theory than in practice. Be wary.

Mohammad Iranishad 20 Reputation points

2024-02-12T05:52:55.0133333+00:00

Hi Greg, First off, thank you immensely for your advice. After following your suggestion and installing the latest Cumulative Update for SQL Server 2022, I'm thrilled to report back that the issue has been resolved! I'm genuinely grateful for your guidance, which was pivotal in overcoming this hurdle. I'm currently working on a database project where all tables are designed to utilize In-Memory OLTP, with procedures written in native compiled code. The goal is to achieve real-time data analytics, ensuring that calculations are performed as swiftly as possible. This endeavor has shown promising early results, significantly speeding up our data processing capabilities. Given your expertise, I'd be keen to hear any thoughts you might have on optimizing the use of In-Memory OLTP and native compiled procedures for achieving top-notch performance. Your insights could be incredibly valuable as we aim to fine-tune our approach to real-time analytics. Once again, thank you for taking the time to share your knowledge. It's been immensely helpful. Best regards,
Greg Low 2,110 Reputation points Microsoft Regional Director

2024-02-12T05:59:19.77+00:00

Hi Mohammad, I'm glad it helped. With your code, is all the processing done with natively-compiled code and in-memory tables? Do you have to ever join it to legacy tables, or update legacy tables (via interop)? If so, that's the part of the code you need to check very carefully. I've found that it often does bizarre work where it spools it all to tempdb first, etc. and ends up being slower than if I'd not used in-memory. I'd really hoped that non-durable tables would make a good replacement for temp tables, but I ran into the same sorts of issues. I also tried using in-memory table types for parameters, but kept running into stability issues i.e., under load I'd see "a severe error occurred on the connection" yet changing them to traditional table types, no errors occurred. I think this last one is now fixed (although they told me for so long that it wasn't a problem - clearly it was if they've fixed it).
The other brutal aspect to this is that it's a feature-set that almost no-one is using. That's not a good place to be.
Mohammad Iranishad 20 Reputation points

2024-02-12T06:32:14.31+00:00

Thanks a ton for sharing your insights. Regarding my project, I've designed the entire database utilizing in-memory technology, with about 200 tables all set to DURABILITY SCHEMA_AND_DATA to ensure stability. I've avoided using any legacy tables because I wanted to make sure everything is optimized for achieving the fastest possible data processing in real time. The points you raised about potential limitations and challenges were very timely and helped me focus more carefully on this approach. Given our focus on real-time processing and leveraging our strong hardware resources, I'm hopeful we can achieve our goal. Anyway, I'm truly grateful for you sharing your experiences and perspectives with me. This exchange has made me proceed with greater caution. Best,

1 additional answer

Your answer

ZoeHui-MSFT 41,551 Reputation points

2024-02-12T01:56:47.6133333+00:00

Hi @Mohammad Iranishad, If the below reply helps on your issue, you could mark it as answer so other user with similar problem could see this easier. :)
Mohammad Iranishad 20 Reputation points

2024-02-12T05:52:55.0133333+00:00

Hi Greg, First off, thank you immensely for your advice. After following your suggestion and installing the latest Cumulative Update for SQL Server 2022, I'm thrilled to report back that the issue has been resolved! I'm genuinely grateful for your guidance, which was pivotal in overcoming this hurdle. I'm currently working on a database project where all tables are designed to utilize In-Memory OLTP, with procedures written in native compiled code. The goal is to achieve real-time data analytics, ensuring that calculations are performed as swiftly as possible. This endeavor has shown promising early results, significantly speeding up our data processing capabilities. Given your expertise, I'd be keen to hear any thoughts you might have on optimizing the use of In-Memory OLTP and native compiled procedures for achieving top-notch performance. Your insights could be incredibly valuable as we aim to fine-tune our approach to real-time analytics. Once again, thank you for taking the time to share your knowledge. It's been immensely helpful. Best regards,
Greg Low 2,110 Reputation points Microsoft Regional Director

2024-02-12T05:59:19.77+00:00

Hi Mohammad, I'm glad it helped. With your code, is all the processing done with natively-compiled code and in-memory tables? Do you have to ever join it to legacy tables, or update legacy tables (via interop)? If so, that's the part of the code you need to check very carefully. I've found that it often does bizarre work where it spools it all to tempdb first, etc. and ends up being slower than if I'd not used in-memory. I'd really hoped that non-durable tables would make a good replacement for temp tables, but I ran into the same sorts of issues. I also tried using in-memory table types for parameters, but kept running into stability issues i.e., under load I'd see "a severe error occurred on the connection" yet changing them to traditional table types, no errors occurred. I think this last one is now fixed (although they told me for so long that it wasn't a problem - clearly it was if they've fixed it).
The other brutal aspect to this is that it's a feature-set that almost no-one is using. That's not a good place to be.
Mohammad Iranishad 20 Reputation points

2024-02-12T06:32:14.31+00:00

Thanks a ton for sharing your insights. Regarding my project, I've designed the entire database utilizing in-memory technology, with about 200 tables all set to DURABILITY SCHEMA_AND_DATA to ensure stability. I've avoided using any legacy tables because I wanted to make sure everything is optimized for achieving the fastest possible data processing in real time. The points you raised about potential limitations and challenges were very timely and helped me focus more carefully on this approach. Given our focus on real-time processing and leveraging our strong hardware resources, I'm hopeful we can achieve our goal. Anyway, I'm truly grateful for you sharing your experiences and perspectives with me. This exchange has made me proceed with greater caution. Best,

Answer 1

Erland Sommarskog 134.7K MVP Volunteer Moderator

As Greg says, this was a bug in SQL 2022 RTM with named instances. This is a common mistake by developers who download SQL Server Developer Edition. They are unaware of the fact that Microsoft regularly publishes Cumulative Updates. You can get the most recent Cumulative Update here. And make sure to keep your instance updated to avoid running into known issues that have been fixed.

Mohammad Iranishad 20 Reputation points

2024-02-12T06:04:12.7933333+00:00

Thanks a lot for the heads-up about Cumulative Updates for SQL Server. I took your advice, updated to the latest CU, and it fixed my issue! Really appreciate the reminder to keep everything up-to-date to avoid such problems, and thanks for the link too. I'll make sure to keep my SQL Server instance on the latest version moving forward. Your help means a lot!

Share via

Utilizing Memory-Optimized Tables Without Enabling Always On Availability

1 additional answer

Your answer