Is there a way to deploy Memory Optimized tables through source control?

scott1060scott1060 Posts: 12 Bronze 1
Is there a way to deploy memory optimized tables through source control without solely using pre/post scripts and disabling the IncludeDependencies?  Can this be done through, I guess what would be considered, typical source control processes?
Tagged:

Answers

  • Jessica RJessica R Posts: 1,319 Rose Gold 4
    Hi @scott1060!

    Just to confirm, are you getting the error "DDL statements ALTER, DROP and CREATE inside user transactions are not supported with memory optimized tables" error?

    This will occur if you have memory optimized tables and are using transactions. You should be able to get around this, however, by turning off transactions in the comparison options.

    Jessica Ramos | Product Support Engineer | Redgate Software

    Have you visited our Help Center?


  • scott1060scott1060 Posts: 12 Bronze 1
    edited May 14, 2020 2:39AM
    Yes, I have already disabled transactions in the pre/post scripts.  The issue is that we have objects in our repository that reference these memory optimized tables to extract data after the ETL process, but they fail on deployment unless we disable IncludeDependencies which decreases our data/object integrity of the database.  Which then we can remove the memory tables from the repository definition, but then our users do not get the objects installed on their local machine through source control unless they run the post scripts manually.
  • Jessica RJessica R Posts: 1,319 Rose Gold 4
    Thanks @scott1060!

    My apologies, just to make sure I'm understanding correctly, can you please provide more detail on how the deployment fails if the memory optimized tables are included? What error does it fail with?

    Jessica Ramos | Product Support Engineer | Redgate Software

    Have you visited our Help Center?


  • scott1060scott1060 Posts: 12 Bronze 1
    If the memory optimized tables are included in our actual repository, the deployment fails with the error "DDL statements ALTER, DROP and CREATE inside user transactions are not supported with memory optimized tables."  Which means we can't include them in the repository, just in a post scripts.
  • scott1060scott1060 Posts: 12 Bronze 1
    edited May 20, 2020 6:52PM
    I was able to get the process to work by doing this, but I also have to run custom scripts to drop memory objects out of our build server database and test database instances, since once they get created by our post scripts, the core repo deployment cannot remove these objects because they are within a transaction.  Which then I've also created filters to ignore objects/schemas, but this is getting very complicated to do something as simple as creating a table.  Now our developers have to remember to go and run the post script to create the memory tables on their local instance in order to test.

  • Jessica RJessica R Posts: 1,319 Rose Gold 4

    Hi @scott1060

    Ah I see, just to confirm (and apologies if I'm still misunderstanding!), would the Comparison options>"Don't use transactions in deployments scripts" option be a suitable workaround?

    Using that option, you would be able to include the memory optimized tables and dependencies in the repository, and do normal get latest/commits for them instead of via post scripts.

    Jessica Ramos | Product Support Engineer | Redgate Software

    Have you visited our Help Center?


  • scott1060scott1060 Posts: 12 Bronze 1
    Thanks for the response.  I may have misunderstood the scope of that option.  I thought it was limited to the pre and post scripts sections only.  I thought I had tested that option within the repository without pre/post scripts, but now I'm not sure it I tested that correctly.  I will have to try that and see if it works.  Thanks!
  • Jessica RJessica R Posts: 1,319 Rose Gold 4
    No worries, I hope that helps! :)

    Jessica Ramos | Product Support Engineer | Redgate Software

    Have you visited our Help Center?


  • scott1060scott1060 Posts: 12 Bronze 1
    edited May 26, 2020 4:54PM
    I still get the error when a deployment tries to drop a memory optimized table unless it's stated in our post scripts.  Even with "Don't use transactions in deployment script" selected.



    WARNING: The error 'DDL statements ALTER, DROP and CREATE inside user transactions are not supported with memory  
    optimized tables.' occurred when executing the following SQL: 
    WARNING: DROP TABLE [dbo].[Test] 
    The database post script failed 
    The database update succeeded 
    NotSpecified: Applying update script failed: DDL statements ALTER, DROP and CREATE inside user transactions are not supported with memory optimized tables. 
    At C:\Octopus\Work\20200526160027-126559-269\Script.ps1:289 char:46 
    + ... xportPath | Use-DatabaseReleaseArtifact -DeployTo $databaseConnection ... 
    +                 ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ 
    at <ScriptBlock>, C:\Octopus\Work\20200526160027-126559-269\Script.ps1: line 289 
    at <ScriptBlock>, C:\Octopus\Work\20200526160027-126559-269\Bootstrap.Script.ps1: line 2799 
    at <ScriptBlock>, <No file>: line 1 
    at <ScriptBlock>, <No file>: line 1 
    Applying update script failed: DDL statements ALTER, DROP and CREATE inside user transactions are not supported with memory optimized tables. 
    The remote script failed with exit code 1 
  • scott1060scott1060 Posts: 12 Bronze 1
    Now I'm wondering if I need to add the compare option to the Octopus deployment process.  The compare option is saved with the repository, but the default options could be overriding the settings.  On the other hand, the option does work for pre/post scripts without adding the compare option to the Octopus deploy process.  I can see in the deploy artifacts that there is no BEGIN TRANSACTION in the pre/post scripts.
  • Jessica RJessica R Posts: 1,319 Rose Gold 4
    Hi @scott1060!

    You would need to apply the "no transactions" option wherever the SQL Compare engine is used, so this would include any build or release steps.

    Jessica Ramos | Product Support Engineer | Redgate Software

    Have you visited our Help Center?


Sign In or Register to comment.