The Create Database Release step for Octopus is slow

swinghouse

Hi,

We've just migrated from SQL Automation Pack to DLM Automation Suite and I am now in the process of updating our Octopus database deployment project to make use of the new build templates for SQL Release.

SQL Release gets the job done, no doubt about that, but our database deployments are significantly slower now than with SQL Automation Pack. The main culprit is the Create Database Release step which took a whopping 11 minutes to complete in my latest deployment while deployments to the same target database took 5 minutes or less - in total for all deployment steps - with SQL Automation Pack.

Is this a general issue or am I doing something wrong? I took my cue from the guide at http://documentation.red-gate.com/displ ... +templates when I revised our database deployment project.

/Mattias

Chris Lambrou

Hi Mattias,

Yes, SQL Release is unfortunately currently slower than similar functionality available either through SQL CI or when calling SQLCompare.exe directly. Although SQL Release is essentially using the SQL Compare comparison engine under the covers, it's currently performing more database schema parsing, comparison and reporting steps than either SQL CI or when calling SQLCompare.exe directly. There are two different causes of this:

1. As a relatively new product, we've initially favoured correctness and straightforwardness of implementation over performance. There are a number of situations where we internally transform the database schema more times than we theoretically need to. We currently have some work planned for December and January that will address this issue (plans are always subject to change, of course!), so I hope your continued patience will pay off.

2. As part of the workflow put forward in the Octopus Step templates, there are a bunch of extra operations beyond those carried out by SQL CI. In particular:

a) the Create Database Release step spends time creating an HTML change report, and then exporting the report along with the before and after database states as scripts folders.

b) the subsequent Deploy From Database Release step then also performs two additional validation database comparisons when actually applying the schema change to the target database. This is based on the presumption that there will be a review step between creating the Database Release and deploying it, during which there may have been database changes that need to be guarded against.

You could improve performance be removing some of the checks in the Deploy From Database Release step, but that alone won't restore the performance of SQL CI. If you're feeling confident, I'd suggest having a look at the PowerShell in the Octopus Step Templates, and then think about replacing the template steps with your own PowerShell script that only carries out the work that you're most interested in.

We're also about to start some work (hopefully finishing around December) to extend SQL Release, exposing the functionality of SQL CI and SQLCompare.exe through additional PowerShell cmdlets. This will give you more control over the work that actually gets carried out by SQL Release.

I realise this isn't the best of news in the short term, but I hope you'll bear with us over the next few months when we expect the functionality and performance to improve significantly.

Also, you can get in touch with the SQL Release development team directly by emailing us at dlmautomationsupport@red-gate.com

//Chris

swinghouse

Hi Chris,

Thanks for the detailed and prompt response - much appreciated!

/Mattias

davejhahn

Any update on this? It's still extremely slow. Just ran it and it took just over 11 minutes to complete. And, I have pre/post validation removed (because it fails looking for #temp tables created in a stored procedure).

Andrew P

Hey Dave,

We have kept the same structure for the SQL Release plugins - so unless you edit the PowerShell script to reduce the number of Cmdlet calls to the minimum acceptable (as Chris Lambrou mentioned above) it will be the same speed until the Engine is fully refactored (which is a major bit of work).

Apologies for the inconvenience!

Kind Regards,

twurm

I'm just currious if this is still something you are working on, completed or haven't yet started.  We are seeing 5 min runtimes even if the the schemas are identical.  Is there anyway to tell Octopus to skip the Deploy Database from Release IF there aren't any changes?