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

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

Related products: Software Factory

Dear all,

As per current documentation the In-memory OLTP functionality in SF supports regular on Premise SQL server. Since many of us are going to cloud hosted SQL server we need to have this option enabled for cloud as well.

Personally my projects are on Azure SQL and have researched only for the specific one. Other might use AWS, Google etc. 

Requesting that this option is enabled and during Creation process to recognize if In-Memory is Supported by the DB and accordinlgy set the correct options on the tables script creation or upgrade script.

Example:

On my Dev I have a lower Pricing Tier with no OLTP support. I should be able to run create and my In-Memory configured tables should be created as normal.

On my Acceptance (UAT) which is production like my Pricing Tier is higher with the support of OLTP so when connecting during creation to build my code for a deployment it should detect that this is supported and my tables should be created with related option for in-memory use.

Thanks

Mike

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.

Open→Needs feedback

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

Needs feedback→Open
Terms & ConditionsCookie settings