Performance issues when deploying databases through octopus

We have an octopus step that deploys our db changes using the redgate command line tools.  Our deployments are taking longer and longer due to dropping keys / indexes etc....  I've tried using the three options:  IgnoreChecks,IgnoreKeys,IgnoreIndexes but then the builds blow up usually due to foreign key constraints when trying to drop keys.  I don't understand why it's trying to drop these in the first place.  Has anyone run into this?  What options can prevent this from happening?


Tagged:

Answers

  • Alex BAlex B Posts: 1,152 Diamond 4
    Hi @mightymiracleman,

    Depending on the circumstances it could be due to several things.

    In SQL Compare, if the table needs to be rebuilt it may require FK's to be dropped on other tables so it can rebuild the table.

    In SQL Data Compare (or deploying a schema change along with static data) it may be due to the default option to disable foreign keys.

    If those don't help, we would need a bit more information to investigate further - hopefully including the log for the operation and/or some form of the source and target (whether scripts folder, snapshots, backups, etc).

    If the two suggestions above do not help and you are able to send information in please let me know and I will reach out via a support ticket to get them as you are supported.

    Kind regards,
    Alex
    Product Support Engineer | Redgate Software

    Have you visited our Help Center?
  • mightymiraclemanmightymiracleman Posts: 3 New member
    edited August 20, 2019 1:07PM

    Thanks for the response, so I gather that those options don't stop dropping FK all together, looks like it can still happen if (as you mentioned) the tables need to be rebuilt and there are dependencies. 


    Thank you!

  • Alex BAlex B Posts: 1,152 Diamond 4
    Hi @mightymiracleman,

    I imagine if the table needs to be rebuilt then it would have to drop the FK to do so, and this process may be separate from the bit that is ignoring the FKs.

    Ideally we would get the source and target in some form (preferably schema snapshots created by SQL Compare or a scripts folder of each) - that is if the issue occurs for the comparison of these the same as it does in whatever the original comparison.  Also the project file and/or the comparison options used so we can recreate it here to see what's going on.

    If you are able to provide these I can reach out via a support ticket to get them from you so the schemas are posted on the forum.  Do let me know!

    Kind regards,
    Alex
    Product Support Engineer | Redgate Software

    Have you visited our Help Center?
Sign In or Register to comment.