in memory oltp and HA

  • tcronin 95651

    SSCertifiable

    Points: 6735

    I plead ignorance just diving into this, looking at moving some high volume OLTP tables to in memory.  They do have foreign keys which is supported in 2016, my question is on redundancy of table in standard edition 2016 in these scenarios

    1. Single instance server crashes is the table rebuilt through log on restart?
    2. In HA such as a VMOTION sql instance does results of episode above the same
    3. In HA such as Always On (enterprise edition) if you have a read only node is OLTP in memory table available.

    Any real world experience with these issues are similar appreciated

  • sqlnyc

    SSCommitted

    Points: 1726

    I realize this post is slightly old, but wanted to give some feedback --

    Keep in mind that FK on memory-optimized tables can only reference other memory-optimized tables. 

    The edition of SQL Server has no effect on the "redundancy" as you mentioned. 

    If your tables are created with DURABILITY = SCHEMA_ONLY, then after database restart (or server crash), the table is recreated, but none of the data that it contained exists. 

    If your tables are created with DURABILITY = SCHEMA_AND_DATA then after database restart (or server crash), the table is recreated, and ALL of the data that it contained exists. All durable memory-optimized data must be re-streamed from files to memory, and this can affect RTO.

    For FCI failover, there is no difference in what I wrote - durable data must be re-streamed from storage to memory. But for Availability Groups, the data does not have to be re-streamed, because as REDO occurs on Secondary replicas, memory-optimized tables are updated. Whether or not the Secondary is readable makes no difference.

    Also note that as of SQL 2016/SP1, you no longer have to use Enterprise edition to have memory-optimized data, but there are limits on how much you can have. 

    If interested, I have many posts on In-Memory OLTP at http://www.nedotter.com

  • tcronin 95651

    SSCertifiable

    Points: 6735

    thanks getting to find all the gotchas like can't do native compiled for sp's that mix in memory and disk based tables

Viewing 3 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply