In-Memory OLTP is one of the best new functionalities of SQL Server 2014 as it was the case with AlwaysOn and SQL Server 2012. In-Memory OLTP is fully integrated with AlwaysOn and also with AlwaysOn Availability Group. During my test with AlwaysOn Availability Group under SQL Server 2014 CTP2, I discovered that a Readable Secondary Replica was not able to access In-Memory OLTP objects.
I have a Readable Secondary Replica named pc1 with a database containing two tables, one disk table and one In-Memory table:
When I try to query the disk table to know how many rows it contains, I have this result:
But if I try to access my In-Memory table it fails…
Apparently it is impossible to access In-Memory objects from a Readable Secondary replica…
Bad news! Readable Secondary functionality is very interesting for reporting, but if we cannot query all objects from our database, it is really restricted…
After some research, I found the solution. A trace flag will solve this issue.
To enable it you have, there are two solutions, as usual:
With SQL Server Configuration Manager:
- Open it
- Navigate to SQL Server Services
- Right click on your SQL Server instance name and select Properties
- In pane “Startup Parameters”, specify a startup parameter, here: -T9989
- Click the Add button and Apply
- You will have to restart your SQL Server Services
Via script:
- Open a new query screen
- Type: DBCC TRACEON(9989,-1) to enable the trace flag for the complete instance
- Type: DBCC TRACEON(9989) to enable the trace flag for the current session
- Execute the script
Now, our new trace flag is enabled and we are able to query Memory Optimized Tables and also to use Natively Compiled Stored Procedure:
Hopefully, there is a way to bypass this restriction and to use the full functionality of Readable Secondary.
I will try to go through all new available trace flags for SQL Server 2014 in the following blog postings 😉