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

scott1060

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?

Jessica R

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.

scott1060

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 R

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?

scott1060

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.

scott1060

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 R

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.

scott1060

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 R

No worries, I hope that helps!

scott1060

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 

scott1060

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 R

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.