Enable OLTP in memory table configuration in SF for cloud (Azure SQL, etc)

Hi Mike,

Are the various environments tied to a branching structure in the Software Factory?

If so, you could configure this in the generation process.

E.g. a meta control procedure with the following code:

if @branch_id in ('RELEASE', 'MAIN')
begin
    -- Set the tables to memory optimized for release and main
    update t
    set t.memory_optimized = 1
    from tab t
    where t.model_id = @model_id
      and t.branch_id = @branch_id
      and t.memory_optimized = 0
      and exists(
        select 1
        from tab_tag tt
        where tt.model_id = t.model_id
          and tt.branch_id = t.branch_id
          and tt.tab_id = t.tab_id
          and tt.tag_id = 'OLTP_RELEASE')
end
else
begin
    -- Set the tables to memory optimized for all other branches
    update t
    set t.memory_optimized = 0
    from tab t
    where t.model_id = @model_id
      and t.branch_id = @branch_id
      and t.memory_optimized = 1
      and exists(
        select 1
        from tab_tag tt
        where tt.model_id = t.model_id
          and tt.branch_id = t.branch_id
          and tt.tab_id = t.tab_id
          and tt.tag_id = 'OLTP_RELEASE')
end

To fully automatically perform these changes based on detection of features of the current rollout can be a bit tricky. Besides the table creation, storage is provisioned and  memory_optimized_elevate_to_snapshot is set. 

Besides this, the model wouldn’t fully be in sync with the database.

Marking a table as no longer memory optimized will generate an (unneccessary) upgrade for your Dev. Likewise, a back-up restored from Dev to UAT would not have their tables upgraded to memory optimized because there is no change in the model between them.

Hi @mperrott,

Could you answer the question Anne asked? Thanks!

Hi @Anne Buit ,

We were planning to have the in memory dedicated tables created as additional in the model and a seperate functionality to use them. We would control which functionality was gonna be used in-memory or not based on a flag we will set in place. So in DEV they are not created and not used. While on UAT they are there and the functionality enabled.

So we would ignore errors during deployment for the particular environment on DEV which is not equivalent in resources to UAT which supports OLTP, and on the other hand place the correct configuration when creating in-memory tables for the UAT manually before deployment to it. This would be one time deal for creating the tables as doing smart deployment would be best.

There should be a support for Azure in memory tables or Other cloud providers for such support in creating such tables.

thanks

Mike