2

I'm trying to get database migrations into my release pipeline definition, roughly following this aproach - dotnet ef migrations script --idempotent as part of the build pipeline, and then an Invoke-SqlCmd task pointing to the resulting script as part of the release pipeline.

However, I have made one change from that blog post, that might be of importance: in order to avoid corrupt states where half a migration is deployed, I wrap the entire script in a transaction, so that it is effectively

SET XACT_ABORT ON

BEGIN TRANSACTION

-- output of dotnet ef migrations script --idempotent

COMMIT

However, when I try to run the script as part of my release pipeline, in an Azure SQL Database task just as the one he uses in the blog post, it fails on e.g. references to things that don't exist in the schema yet, but will by the point the script reaches the part that includes the reference.

For example, considering the following migrations script:

SET XACT_ABORT ON

BEGIN TRANSACTION
-- in the actual script, each of these statements are, individually, wrapped in an IF that
-- checks whether the migration has been run before or not, similar to the first one in
-- this listing

IF NOT EXISTS (SELECT * FROM [__EFMigrationsHistory] WHERE MigrationId = 'AddColumnBar')
BEGIN
    ALTER TABLE Foo ADD Bar int NULL
END

GO

UPDATE Foo SET Bar = 3
GO

ALTER TABLE Foo ALTER COLUMN Bar int NOT NULL
GO

COMMIT

Executing this script with Invoke-SqlCmd as suggested in the blog post yields an error stating that the Foo column does not exist.

How do I tell Invoke-SqlCmd that I know it doesn't, but it will when it needs to? If that's not possible, what's the best approach to fix this deployment flow for Azure DevOps?

Additional info:

Here's the complete log from the SQL step in the release pipeline:

2019-01-09T14:57:52.7983184Z ##[section]Starting: Apply EF Migrations
2019-01-09T14:57:52.7989024Z ==============================================================================
2019-01-09T14:57:52.7989311Z Task         : Azure SQL Database Deployment
2019-01-09T14:57:52.7989427Z Description  : Deploy Azure SQL DB using DACPAC or run scripts using SQLCMD
2019-01-09T14:57:52.7989514Z Version      : 1.2.9
2019-01-09T14:57:52.7989608Z Author       : Microsoft Corporation
2019-01-09T14:57:52.7989703Z Help         : [More Information](https://aka.ms/sqlazuredeployreadme)
2019-01-09T14:57:52.7989823Z ==============================================================================
2019-01-09T14:57:58.8012013Z Sql file: D:\a\r1\a\_db-migrations\migrations-with-transaction.sql
2019-01-09T14:57:58.8189093Z Invoke-Sqlcmd -ServerInstance "***" -Database "***" -Username "***"  -Password ******  -Inputfile "D:\a\r1\a\db-migrations\migrations-with-transaction.sql"  -ConnectionTimeout 120
2019-01-09T14:58:04.3140758Z ##[error]Invalid column name 'Group_DocumentTagGroupId'.Check out how to troubleshoot failures at https://aka.ms/sqlazuredeployreadme#troubleshooting-
2019-01-09T14:58:04.3480044Z ##[section]Finishing: Apply EF Migrations

Here are all the references to Group_DocumentTagGroupId in the script:

-- starting around line 1740
IF NOT EXISTS(SELECT * FROM [__EFMigrationsHistory] WHERE [MigrationId] = N'20181026122735_AddEntityForDocumentTagGroup')
BEGIN
    ALTER TABLE [DocumentTags] ADD [Group_DocumentTagGroupId] bigint NOT NULL DEFAULT 0;
END;

GO

-- about 20 lines with no mention of the column (but several GO statements)

IF NOT EXISTS(SELECT * FROM [__EFMigrationsHistory] WHERE [MigrationId] = N'20181026122735_AddEntityForDocumentTagGroup')
BEGIN
    EXEC('
                    UPDATE [dbo].[DocumentTags] SET [Group_DocumentTagGroupId] = (SELECT TOP 1 [DocumentTagGroupId] FROM [dbo].[DocumentTagGroups] g WHERE g.[Name] = [Type])')
END;

GO

IF NOT EXISTS(SELECT * FROM [__EFMigrationsHistory] WHERE [MigrationId] = N'20181026122735_AddEntityForDocumentTagGroup')
BEGIN
    CREATE INDEX [IX_DocumentTags_Group_DocumentTagGroupId] ON [DocumentTags] ([Group_DocumentTagGroupId]);
END;

GO

IF NOT EXISTS(SELECT * FROM [__EFMigrationsHistory] WHERE [MigrationId] = N'20181026122735_AddEntityForDocumentTagGroup')
BEGIN
    ALTER TABLE [DocumentTags] ADD CONSTRAINT [FK_DocumentTags_DocumentTagGroups_Group_DocumentTagGroupId] FOREIGN KEY ([Group_DocumentTagGroupId]) REFERENCES [DocumentTagGroups] ([DocumentTagGroupId]) ON DELETE CASCADE;
END;

GO

The SQL file that is sent to Invoke-SqlCmd is generated with the following PowerShell script

$migrationsWithoutTransaction = "./Migrations/scripts/migrations-without-transaction.sql"
dotnet ef migrations script --configuration Release --idempotent --output $migrationsWithoutTransaction

$migrationsWithTransaction = "./Migrations/scripts/migrations-with-transaction.sql"
Get-Content "./Migrations/begin-transaction.sql" | Out-File -Encoding Utf8 $migrationsWithTransaction
Get-Content $migrationsWithoutTransaction | Out-File -Encoding Utf8 -Append $migrationsWithTransaction
Get-Content "./Migrations/commit-transaction.sql" | Out-File -Encoding Utf8 -Append  $migrationsWithTransaction

where this is the contents of the auxiliary sql scripts:

-- Migrations/begin-transaction.sql
SET XACT_ABORT ON

BEGIN TRANSACTION

-- Migrations/end-transaction.sql
COMMIT
Tomas Aschan
  • 58,548
  • 56
  • 243
  • 402
  • The problem was caused by that `BEGIN TRAN`. That's a script, not a SQL statement or batch of statements. Script commands like `GO` are understood by the tool itself (SSMS, sqlcmd or Invoke-SqlCmd) as batch delimiters. It probably contains other things that won't work in a SQL batch like script variables for database, names etc. – Panagiotis Kanavos Jan 09 '19 at 15:25
  • This means that the server now sees a single batch that contains `BEGIN TRANSACTION` and other statements that refer to columns that aren't there and fails to parse the batch. In fact, by blindly adding that transaction and `SET XACT_ABORT ON` you probably *prevent* the script from recovering from failures. If you intend to modify the script, study it first to understand what it does. If you do want to use transactions, add a `GO` after your changes. Test the script against a local SQL Server installation – Panagiotis Kanavos Jan 09 '19 at 15:27
  • I did test the script, including the transaction wrapping, on a local SQL server installation (albeit running it from SSMS, not through Invoke-SqlCmd). It did what I wanted it to do, including reverting everything within the transaction if I added a `THROW` statement at the end. The script is generated by `dotnet ef migrate script`, and although I could study the script today to see what this version of it does, the next version will be (additively) different. Modifying the script anywhere but at the top and bottom is not an option; I want to interfere with it as little as possible. – Tomas Aschan Jan 09 '19 at 15:40
  • @PanagiotisKanavos The problem seems to me not to be the execution of the script, but some sort of validation of it before it is executed. That validation concludes that the column does not exist, and errors out, without giving the previous parts of the script a chance to create the column. – Tomas Aschan Jan 09 '19 at 15:42
  • 1
    You didn't post the actual script, actual error or the line where this occured. It's not possible to help without these. Right now I'm creating a new webapi project just to see what the script looks like. This has nothing to do with any tool limitations, that's how scripts work. If there's a syntax error you need to see where it occured and why. The *script language* is the same it always was and used in Management Studio – Panagiotis Kanavos Jan 09 '19 at 15:44
  • Apart from that, migrations is a *simple* tool, built for quick migrations. It's the same with any ORM. No ORM will produce produce as robust a script as SSDT schema diff. SSDT won't produce as robust a script as *Redgate's* diff tools or migration scripts. You are asking about adding columns but what about the *data*? How do you handle column splitting, renames, missing default values, table splits and refactorings? – Panagiotis Kanavos Jan 09 '19 at 15:47
  • +1 to more info needed.There's nothing about using a transaction that should change the way invoke-sqlcmd breaks scripts into batches, or SQL Server parses batches. – David Browne - Microsoft Jan 09 '19 at 15:48
  • @TomasAschan btw no repro. I added `SET XACT_ABORT ON begin tran ` before the start of the generated script, `commit tran` at the end and got no error. Somehow, somewhere there was a modification in your script that caused statements that should go in different batches to appear in the same batch. Did you *delete* a `GO` perhaps, resulting in the `ALTER TABLE ADD` statement to appear in the same batch as eg a constraint or index statement? – Panagiotis Kanavos Jan 09 '19 at 15:50
  • @PanagiotisKanavos: No, I did not. Here's how I generate the script: ``` $migrationsWithTransaction = "./migrations-with-transaction.sql" Get-Content "./Migrations/begin-transaction.sql" | Out-File -Encoding Utf8 $migrationsWithTransaction Get-Content $migrationsWithoutTransaction | Out-File -Encoding Utf8 -Append $migrationsWithTransaction Get-Content "./Migrations/commit-transaction.sql" | Out-File -Encoding Utf8 -Append $migrationsWithTransaction ``` where the two `begin-transaction.sql` and `commit-transaction.sql` contains exactly the lines i've shown above. – Tomas Aschan Jan 09 '19 at 16:03
  • (dammit markdown formatting... each `GetContent` should be on a new line) – Tomas Aschan Jan 09 '19 at 16:04
  • @TomasAschan update the *question*. That still doesn't show the contents of the script. That's a rather convoluted way of copying multiple files together though. Check [this question](https://dba.stackexchange.com/questions/93599/transaction-with-multiple-sqlcmd-calls-in-a-batch-file) which uses a single `Get-Content` in a loop to concatenate multiple scripts and wrap them in a transaction – Panagiotis Kanavos Jan 09 '19 at 16:10
  • @PanagiotisKanavos I've updated the question. As you can see, there's very little that differs from the example script that was originally there - I don't see how this can give you more information than you already had. – Tomas Aschan Jan 09 '19 at 16:12
  • @TomasAschan where did that `Exec` come from? I'll have to check but if `EXEC` doesn't use the same transaction it can't see the changes made inside it. There's no reason to use dynamic SQL in any case. that `UPDATE [dbo].[DocumentTags] SET` could stand on its own – Panagiotis Kanavos Jan 09 '19 at 16:23
  • `Exec` is fine. Dynamic SQL will the existing transaction. "I don't see how this can give you more information" post a verbatim copy of a script that reproduces the error. – David Browne - Microsoft Jan 09 '19 at 17:59
  • @DavidBrowne-Microsoft: The verbatim script that reproduces the error is 2000 lines long and assumes the existence of a database schema which I am not at liberty to disclose publicly in full. I've posted verbatim excerpts of that script, including all lines that contain the column mentioned in the error message. I simply don't see what I could provide more than this without giving you access to my client's code and database (which you understand as well as I that I can't do), unless there are any _specific questions_ you want me to answer about the script or setup. – Tomas Aschan Jan 09 '19 at 18:14
  • Perhaps building a simplified repro will help you locate the issue, and leave you with something to post if not. One possibility is that your script is failing, and then running the subsequent batches then fail with the error you are seeing. Invoke-sqlcmd is pretty notourous for having poor error handing. Using SqlCmd.exe or breaking and executing the script with SqlConnection/SqlCommand might be more reliable. In any case you can use SQL Profiler to see the actual commands and any error messages. – David Browne - Microsoft Jan 09 '19 at 19:26

1 Answers1

0

Is using Azure Release pipeline (classic) an option for you? Because I am successfully running EF migrations on it, while creating the migrations script on the fly.

See my answer here: how to execute sql script using azure devops pipeline

Ε Г И І И О
  • 11,199
  • 1
  • 48
  • 63