Post-update schema check failed
swinghouse
Posts: 120 Bronze 2
Hi,
I'm attempting to deploy a database schema plus static data to an empty database. I use SQL Release + Octopus Deploy.
My Octopus Deploy project has the following steps:
Everything seems to work fine up until what I believe is the last phase in the final step ("Deploy from Database Release") where the post validation fails with the following messages:
The error messages don't tell me exactly which differences it found. Is it possible to extract that information?
Any help would be most appreciated!
/Mattias
I'm attempting to deploy a database schema plus static data to an empty database. I use SQL Release + Octopus Deploy.
My Octopus Deploy project has the following steps:
-
Download and extract database package
Create database release
Review database deployment resources (Manual intervention)
Deploy from Database Release
Everything seems to work fine up until what I believe is the last phase in the final step ("Deploy from Database Release") where the post validation fails with the following messages:
11:35:19 Info | The database update succeeded
11:35:19 Info | Update script successfully applied to database 'BagheraDataDev_TestDeploy' on server 'bagheradev2.beab.nusqlexpress'.
11:35:19 Info | Checking target database schema is in expected state:
11:35:30 Info | Comparing the schema in the Database Release object (created at 2016-07-22 11:27:58 +02:00) to the current schema of database 'BagheraDataDev_TestDeploy' on server 'bagheradev2.beab.nusqlexpress'.
11:35:30 Info | VERBOSE: Calling: C:Program Files (x86)Red GateDLM Automation Suite
11:35:30 Info | 1SCSQLCompare.exe "/scripts1:C:WindowsTEMPSQL Release2m5qblqm.vkm"
11:35:30 Info | /server2:bagheradev2.beab.nusqlexpress /database2:BagheraDataDev_TestDeploy
11:35:30 Info | /username2:sa /password2:******** "/filter:C:WindowsTEMPSQL
11:35:30 Info | Releasei5ibn1q0.scpf"
11:35:30 Info | /options:ConsiderNextFilegroupInPartitionSchemes,DecryptPost2kEncryptedObjects,
11:35:30 Info | DoNotOutputCommentHeader,ForceColumnOrder,IgnoreCertificatesAndCryptoKeys,Ignor
11:35:30 Info | eDatabaseAndServerName,IgnoreTSQLT,IgnoreUserProperties,IgnoreUsersPermissionsA
11:35:30 Info | ndRoleMemberships,IgnoreWhiteSpace,IgnoreWithElementOrder,IncludeDependencies,T
11:35:30 Info | hrowOnFileParseFailed,UseMigrationsV2 /include:staticData /OutputWidth:1024
11:35:30 Info | /out:"C:WindowsTEMPSQL Releasefy3as4kr.log"
11:39:51 Info | VERBOSE: C:Program Files (x86)Red GateDLM Automation Suite
11:39:51 Info | 1SCSQLCompare.exe ended with exit code 0
11:39:51 Info | VERBOSE: SQLCompare.exe completed successfully.
11:39:51 Error | Use-DatabaseRelease : Post-update schema check failed: the schema in the Databa
11:39:51 Error | se Release object (created at 2016-07-22 11:27:58 +02:00) and database 'Baghera
11:39:51 Error | DataDev_TestDeploy' on server 'bagheradev2.beab.nusqlexpress' are different, o
11:39:51 Error | r static data is different.
11:39:51 Error | At C:Octopus TentacleWork20160722093005-1365Script.ps1:54 char:47
11:39:51 Error | + Import-DatabaseRelease $exportPath -Verbose | Use-DatabaseRelease -DeployTo $
11:39:51 Error | dat ...
11:39:51 Error | + ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
11:39:51 Error | ~~~
11:39:51 Error | + CategoryInfo : InvalidResult: (:) [Use-DatabaseRelease], Termin
11:39:51 Error | atingException
11:39:51 Error | + FullyQualifiedErrorId : SchemasAndStaticDataAreNotIdentical,RedGate.SQLR
11:39:51 Error | elease.PowerShell.Commands.UseDatabaseReleaseCommand
11:39:51 Fatal | The remote script failed with exit code 1
The error messages don't tell me exactly which differences it found. Is it possible to extract that information?
Any help would be most appreciated!
/Mattias
Comments
Do you have a SQL Compare license? The easiest thing to do is the following -
1. Extract the contents of your NuGet package to a local directory.
2. Using SQL Compare, compare the contents of the dbstate directory within the directory in step 1 with the database you were trying to deploy to.
You may need to adjust the comparison options to match those that the SQL Compare command line executable is using in your logfile.
Redgate Software
Yes, I'm a longtime SQL Compare user. :-)
Following your instructions, I could extract the db directory from the NuGet package and, with SQL Compare, compare it with the target database. The target database didn't contain any of the changes from source control. This was expected since DLM Automation rolled back all changes in the failed deployment that prompted this discussion.
I then attempted to deploy the changes via SQL Compare. Interestingly, the deployment failed because the database user I provided in the connection string didn't have sufficient permissions to add new roles to the target database. When SQL Compare ran a new comparison after the deployment, it consequently found that there were roles in the source database that didn't exist in the target database. A sproc was missing as well. (This sproc is linked to one of the new database role. This explains why the sproc was omitted.)
At this point I thought we had found the root cause of the problem: insufficient user rights on the target database.
However, I then realized that I hadn't used the same user account in the SQL Compare project as in the failed deployment on Octopus Deploy / DLM Automation. Switching to the same (system administrator) account, SQL Compare was able to deploy all database objects.
Furthermore, shouldn't DLM Automation have reported an error if it was unable to deploy some database objects because of insufficient user rights? The log in Octopus Deploy didn't contain any messages indicating any errors.
To continue the troubleshooting, I think it would be of great help if I could temporarily tell DLM Automation not to roll back the changes in case of a failed deployment. Is this possible?
/Mattias
Hi Mattias,
In the Create database release step, enter NoTransactions in the textbox labelled SQL Compare options (optional) - this will generate a script without transcations, so it won't roll back the changes if the deployment fails.
Obviously, I would only recommend using this for troubleshooting purposes!
Redgate Software
Seeing your post I tried to rack my brain for how we ended up solving the issue, but I'm sorry I drew a blank. :-( I would suspect we reverted to manual adjustments of the target database to work around the issue.
Sorry I can't give you a more exact answer than that - it's been a while. I'll post again if I remember more details.
/Mattias