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:

b2ap3_thumbnail_AG1.jpg

When I try to query the disk table to know how many rows it contains, I have this result:

b2ap3_thumbnail_AG2.jpg

But if I try to access my In-Memory table it fails…

b2ap3_thumbnail_AG3.jpg

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

b2ap3_thumbnail_AG4.jpg

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:

b2ap3_thumbnail_AG5.jpg

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 😉


Thumbnail [60x60]
by
Stéphane Savorgnano