In-Memory Table on SQL 2014 HA cluster not queriable on Secondary nodes

Question

I've set up a simple in-memory table on a high-availability group database on SQL 2014. Querying the table from the primary node directly or from the Listener works fine. If I change the query string to ReadOnly Intent or try to query directly from either of the 2 secondary nodes I get the following error: -

Msg 41341, Level 16, State 1, Line 1 Table 'tbl_GetMakes' is not yet available on the secondary replica.

There is very little information on this message online. It talks about it being replicated properly when the "REDO" process takes place. As far as I read it, the "REDO" process should be automatic, the table has been in place now for just over 24 hours and still isn't working. Any ideas anyone? Thanks, Jon

strange,was the table present during initial synchronization — TheGameiswar, Mar 30 '17 at 14:03
Yes, can see it in sys.tables. I've taken out an existing regular table and replaced it with the In-Memory table. The database was already in the HA group when I did this. — Proxide78, Mar 30 '17 at 14:12

Proxide78 · Accepted Answer · 2017-04-04T11:15:12.157

Thanks to Sean Gallardy who answered this over on the Microsoft Forum

https://social.msdn.microsoft.com/Forums/sqlserver/en-US/a6cbeb89-bc99-491c-8c2f-9937d5ddfa6a/inmemory-table-on-sql-2014-ha-cluster-not-queriable-on-secondary-nodes?forum=sqldisasterrecovery

I've reproduced the answer from the above link below: -

That's not correct, they can absolutely be read from a readable secondary, assuming: The in memory table was made with Durability = schema_and_data A checkpoint has run on the primary The proper isolation level is used I can easily get this to work, here is a repro: 1. Create Database on primary and put in AG

CREATE DATABASE [Test1]
 CONTAINMENT = NONE
 ON  PRIMARY
( NAME = N'Test1', 
  FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL12.MSSQLSERVER\MSSQL\DATA\Test1.mdf' , 
  SIZE = 4096KB , MAXSIZE = UNLIMITED, FILEGROWTH = 1024KB ), 
  FILEGROUP [IMOLTP] CONTAINS MEMORY_OPTIMIZED_DATA  DEFAULT
  ( NAME = N'imoltp_dir', 
  FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL12.MSSQLSERVER\MSSQL\DATA\imoltp_dir' , 
  MAXSIZE = UNLIMITED)
LOG ON 
( NAME = N'Test1_log', FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL12.MSSQLSERVER\MSSQL\DATA\Test1_log.ldf' , SIZE = 1280KB , MAXSIZE = 2048GB , FILEGROWTH = 10%)
GO

Create the IM Table and populate

CREATE TABLE dbo.IMOLTP ( C1 INT NOT NULL PRIMARY KEY NONCLUSTERED HASH WITH (BUCKET_COUNT = 1000) ) WITH (MEMORY_OPTIMIZED = ON, DURABILITY = SCHEMA_AND_DATA)

INSERT INTO dbo.IMOLTP(C1) VALUES (1), (2), (3) GO

CHECKPOINT

SELECT * FROM dbo.IMOLTP

Read from a READABLE secondary

Use Test1 GO

SELECT * FROM dbo.IMOLTP GO

It works just fine for me on: Microsoft SQL Server 2014 (SP2-CU3) (KB3204388) - 12.0.5538.0 (X64)

-Sean

Your edit makes this answer *less* useful since it's now effectively a link-only answer. Answers can link to resources but they should be *useful* in their own right, whether or not the site(s) that they're linking to is currently available or not. — Damien_The_Unbeliever, Apr 03 '17 at 09:16

In-Memory Table on SQL 2014 HA cluster not queriable on Secondary nodes

1 Answers1