Scripted SQL Deployment in a Transaction with Rollback [duplicate]

Question

Note: I believe this differs from other questions simply due to the size of the creation script I want to run under a single transaction.

---

Background

I'm working on automating the deployment of the data side of an application. I'm working with the team's existing scripts. They seem to drop everything and recreate. I don't have much time to make it perfect.

I'm thinking of running their drop-all script outside of a transaction, but running a single long "genesis" creation script inside a transaction at least so if that part should fail, its at a clean point and easier to recover from.

Genesis.sql contains 34k lines and seems to all be CREAT(ions) of functions, procedures, tables, types etc.

Question

Can I do this:

SET XACT_ABORT ON
BEGIN TRAN

# Genesis!

COMMIT TRAN

?

What's really confusing everyone is the impact of the GOs in the script with some saying that it will commit the transactions and other people (on the web) saying GO is just an instruction for the client to send a batch.

Follow-up

So far, this hasn't worked, new stuff is written and I see this message:

The COMMIT TRANSACTION request has no corresponding BEGIN TRANSACTION.

However, I think its mostly due to syntax errors in one or two scripts (that all get concatenated to 30k lines) and the parser is thrown out.

It seems I'll need to dummy run the scripts into an empty, local DB first, to discover syntax errors, then attempt against the target environment server.

Answer 1

Is your database online during your release or do you have a maintenance window?

If you have a maintenance window and are running Enterprise Edition you could create a database snapshot pre-deployment.

Then you can easily revert to the snapshot if your release process does not execute properly.

Answer 2

SET XACT_ABORT ON;, by itself, won't do anything. You need to have a BEGIN TRAN; just after it to start the explicit Transaction that will include all of the statements.

I would recommend including the DROP operations as part of the Transaction so that if there is a failure, nothing changed. The main downside here is that if you are dropping large tables and/or doing other large-scale data changes, that could make for a rather large Transaction log and take a lot of time. In such cases where you are doing this in a "zero-downtime" scenario (i.e. no maintenance window), it could lock tables for a longer-than-acceptable amount of time. A very large Transaction can also take a very long time to roll back, so if you spend 1 hour updating data and an error occurs, it can take at least 1 hour to do the rollback, and then you get to try again. So, this option is not great if you are modifying A LOT of data.

Ideally, you would have a set of scripts that represent each project. Each script would do ALTERs, etc with their own, self-contained Transactions and TRY...CATCH logic. If the changes to the system went in per-project during development, then they should go in the same way to testing and then to Production. Of course, I could be preaching to the choir here since you said you are working with existing scripts and don't have time to make it perfect.

Regarding the GO statement confusion: I'm not sure why this is confusing anyone since it is easily testable. The GO statement is not something that SQL Server ever sees, and if it does you will get a nice error message about it. The GO statement is the default batch separator used by client programs such as SQL Server Management Studio (SSMS), SQLCMD, Visual Studio (VS), etc. These client programs will parse the script, break up sets of queries on the GO statements (GO on its own line, optionally with a integer value following it for the number of times to repeat the batch just above it), and submit each set of GO-separated queries to SQL Server. This is why you can't put a GO into Dynamic SQL. It has nothing to do with Transactions. But in terms of rollout scripts, if you are using local variables for any reason, those do not survive beyond a GO statement since local variables are scoped to a batch of statements. But the batches are all executed in the same Session, so you can use session-based items such as CONTEXT_INFO, temporary tables, temporary stored procedures, and starting in SQL Server 2016, SESSION_CONTEXT to pass information from one batch to another that follows.