Pre and Post Deployment scripts and use of XACT_ABORT = ON

Slurpee

According to ReadyRoll's transaction handling documentation, Pre and Deployment scripts are not executed in a transaction. It also documents that it sets XACT_ABORT ON and goes into some good reasoning for doing so.
https://documentation.red-gate.com/rr1/key-concepts/transaction-handling

However, I think the scope of XACT_ABORT being set to ON should only apply to scripts documented as being used in a transaction (migrations, programmable objects, and additional scripts), and NOT those documented as NOT being used in a transaction (pre and post-deployment scripts).

The currently generated output sets XACT_ABORT ON at the very beginning of the script, and it stays on for everything, including Pre-Deployment and Post-Deployment scripts. As far as I know, there's no way to set Custom transaction handling in a pre or post deployment script since there's no metadata. I tried adding

-- <TransactionHandling="Custom" />

at the top and it did nothing.

This is problematic if I want to do certain things in post deployment scripts, such as
EXEC sys.sp_fulltext_load_thesaurus_file
which fails if XACT_ABORT = ON becuase it relies on handling its own transaction errors appropriately, and if it can't, it fails with some confusing error messages that the caller likely was never intended to see in the first place.

As a workaround, I can turnit off myself for the post deployment script (which is what I've done), but I don't feel like I should need to.

I would argue that things documented as not being transactionalized (pre/post-deployment scripts) shouldn't have their transaction behavior changed from the default, which is OFF for XACT_ABORT.
https://docs.microsoft.com/en-us/sql/t-sql/statements/set-xact-abort-transact-sql

Is there some rationale for having this on for everything, not just what ReadyRoll handles within transactions, or is this just an oversight?
Is there some custom control of this for post deployment scripts that I'm not aware of?

Thanks!

dnlnln

Thank you for your detailed and thoughtful question. The short answer is that the issue you're encountering is due to an oversight in the design of the ReadyRoll deployment script.

The fact is that a scenario exists in which having the XACT_ABORT set to the ON position could be detrimental to the deployment outcome is simply not something that has been anticipated, hence no consideration has been given to it up until now.

The reason why we don't set it to OFF as part of the deployment script, in scenarios where ReadyRoll's automated transaction wrapping does not apply, was instituted primarily to ensure the early termination of deployments that encounter errors at execution time. Doing so ensures that, in the event of a T-SQL exception occurring, that no further statements are executed prior to returning the execution context to the caller. It also means that the user can rely on the XACT_ABORT context being consistent throughout the deployment.

However, given that XACT_ABORT ON isn't the default setting, which could lead to the type of confusing behaviour you mentioned within your non-transactionalised scripts, I take your point that Pre/Post-Deployment scripts should probably use the default SQL Server setting (i.e. OFF) during the execution of those scripts. This would leave the question of how managed scripts (migrations/programmable objects/additional scripts) should behave when their TransactionHandling attribute is set to Custom, but perhaps that's one for another day.

I'll log an issue with your concerns so that the team can give this some more thought. If we were to change this behaviour, we would need to give some consideration to how this might impact existing projects (some customers may be relying on the current mode of execution flow in their deployments).

But as you've pointed out, unfortunately the only currently available workaround is to manually include the SET XACT_ABORT OFF within your project scripts.

I hope that helps! Please let me know if you have any further questions.

Slurpee

Thanks for the detailed reply. Now that I understand the behavior of ReadyRoll script generation, the workaround isn't too extreme, especially since the need for it is so uncommon. It definitely took me by surprise and caused some extensive research figuring it out. :-) I hope the team finds an elegant solution or enhancement to account for this.

dnlnln

Great, thank you once again for your feedback.

While the team considers this further, I've added a note to the Transaction Handling article to clarify how the predicate works within Pre/Post-Deployment scripts.