Comments
2 comments
-
I have the similar observations. I am trying to deploy to a fresh/newly created AWS RDS SQL Server database created expressly for the purpose of receiving the new deployment via SCA. I have no issues doing the same deployment to on-prem databases. How can I get my AWS deploy to succeed the way my on-prem deployments succeed?
$iReleaseArtifact = New-DatabaseReleaseArtifact -source "xxxx.nupkg" -Target $AwsDbConn
New-DatabaseReleaseArtifact, SQL Change Automation 4.2.20189.21070, Copyright © Red Gate Software Ltd 2014-2019
Creating SQL Change Automation release artifact
Generating patch script xxxx.sql for the [xxxx] database
Database not considered empty. 2 object differences found
xxxx: A baseline build/deploy of the existing non-empty database will be required as the MigrationLog table doesn't exist and there are unapplied migrations. ProjectCount: 20.
If you were expecting to run the baseline scripts against the database, see http://www.red-gate.com/sca/dev/baseline-troubleshoot for information on how to set up appropriate filtering.
1 migration(s) in baseline, 19 migration(s) pending deployment
(Pre-Script) Pre-Deployment\01_Initialize_Deployment.sql
(Mark as Deployed) Migrations\1.0.0-Baseline\001_xxxx.sql
(Deploy) Migrations\1.1.0-Changes\002_xxxx.sql
(Deploy) Migrations\1.1.0-Changes\003_xxxx.sql
...
(Deploy) Migrations\1.1.0-Changes\009_xxxx.sql
(Deploy) Programmable Objects\xxx\Functions\xxxx.sql
(Deploy) Programmable Objects\yyy\Stored Procedures\xxx1.sql
(Deploy) Programmable Objects\yyy\Stored Procedures\xxx2.sql
...
Release artifact created successfully
Continuing on to the deployment step:
Use-DatabaseReleaseArtifact $iReleaseArtifact -DeployTo $AwsDbConn
Use-DatabaseReleaseArtifact, SQL Change Automation 4.2.20189.21070, Copyright © Red Gate Software Ltd 2014-2019
Deploying SQL Change Automation release artifact
Checking the target database is in the expected state before deploying.
Pre-check completed successfully.
Deploying "xxxxi\PatchScript.sql" to [CSI_SSIS]
----- executing pre-deployment script "Pre-Deployment\01_Initialize_Deployment.sql" -----
# Beginning transaction
# Setting up migration log table
# Creating a new migration log table
Creating extended properties
# Setting up __SchemaSnapshot table
# Truncating __SchemaSnapshot
----- baselined: Migrations\1.0.0-Baseline\001_xxxx.sql (marked as deployed) -----
***** EXECUTING MIGRATION "Migrations\xxx\001_xxx.sql", ID: {1234} *****
Creating xxxx
***** FINISHED EXECUTING MIGRATION "Migrations\xxx\001_xxx.sql", ID: {1234} *****
***** EXECUTING MIGRATION "Migrations\xxx\002_xxx.sql", ID: {1234} *****
Altering xxxx
Creating zzzz
***** FINISHED EXECUTING MIGRATION "Migrations\xxx\002_xxx.sql", ID: {1234} *****
***** EXECUTING MIGRATION "Migrations\xxx\003_xxx.sql", ID: {1234} *****
Dropping zzzz
***** FINISHED EXECUTING MIGRATION "Migrations\xxx\002_xxx.sql", ID: {1234} *****
<then it continues happily until the last migration script:>
***** EXECUTING MIGRATION "Migrations\xxx\009_xxxx.sql", ID: {1234} *****
Altering yyyy.zzzzz
Msg 4902, Level 16, State 1, Server EC2AMAZ-1RO5S6R, Line 1
Cannot find the object "yyyy.zzzzz" because it does not exist or you do not have permissions.
WARNING: RedGate.Versioning.Engine.Core.DatabaseInteraction.ScriptExecution.Exceptions.DeploymentException: An error occurred during script deployment. Output from SQL Server follows.---------------------------------------------------------------
Msg 4902, Level 16, State 1, Server EC2AMAZ-1RO5S6R, Line 1
Cannot find the object "yyyy.zzzzz" because it does not exist or you do not have permissions.
---------------------------------------------------------------
<then the stack dump>
***This error may have originated in the following script: Migrations\1.1.0-Changes\009_xxxx.sql
at RedGate.Versioning.Engine.Core.DatabaseInteraction.SqlServer.ScriptExecution.SqlCmdErrorHandler.ReportError(String processErrorOutput, String processOutput, String displayName) in
\BuildA
gentB\work\3367df887a9829e1\Engines\Migrations\Core\DatabaseInteraction.SqlServer\ScriptExecution\SqlCmdErrorHandler.cs:line 37
at RedGate.Versioning.Engine.Core.DatabaseInteraction.SqlServer.ScriptExecution.SqlCmdProcessRunner.ExecuteSqlCmdProcess(String sqlCmdExecutablePath, ProcessStartInfo processStartInfo, IProgre
ssLogger progress, CancellationToken cancellationToken) in\BuildAgentB\work\3367df887a9829e1\Engines\Migrations\Core\DatabaseInteraction.SqlServer\ScriptExecution\SqlCmdProcessRunner.cs:line 6
0
etc etc etc
-
More interesting behavior observed:
1. made the change to the baseline script noted here:2. Re-ran New-DatabaseReleaseArtifact and Use-DatabaseReleaseArtifact
3. Observed no change in behavior. But this time, i recognized that the baseline script (Migrations\1.0.0-Baseline\001_xxxx.sql) had not executed, whereas the "changes" scripts (Migrations\1.1.0-Changes\002-009) appeared to execute. So i executed the baseline script manually.
4. Re-ran New-DatabaseReleaseArtifact and Use-DatabaseReleaseArtifact. This time, no stackdump. Here's the result:
1 migration(s) baselined, 19 migration(s) deployed successfully
----- executing post-deployment script "Post-Deployment\01_Finalize_Deployment.sql" -----
Deployment completed successfully.
Attempting to write snapshot to database
Inserting schema snapshot
(1 rows affected)
WARNING: xp_logevent failed to log deployment for SQL Monitor.
SQL script failed to execute: The EXECUTE permission was denied on the object 'xp_logevent', database 'mssqlsystemresource', schema 'sys'.
Release artifact deployed successfullyI unsure about whether the warning is to be ignored or not, but the RDS database appears to be where I need it to be now. I will try some end-to-end test releases with no manual intervention to confirm.
Add comment
Please sign in to leave a comment.
When I run from VS on Azure DB, I get the log (truncated):
(Deploy) Migrations\1.0.0-Baseline\001_20200220-0529_Baseline.sql
The same project when deployed from VS on an On-prem db: (This scenario is not important for me because the target db was set to Azure SQL and this is the wrong testing scenario. However, it is intresting to know why SCA is treating it as nont an epty db)
xxxdb: A baseline build/deploy of the existing non-empty database will be required as the MigrationLog table doesn't exist and there are unapplied migrations. ProjectCount: 5.
If you were expecting to run the baseline scripts against the database, see http://www.red-gate.com/sca/dev/baseline-troubleshoot for information on how to set up appropriate filtering.
Generating patch script
1 migration(s) in baseline, 4 migration(s) pending deployment
(Pre-Script) Pre-Deployment\01_Create_Database.sql
(Mark as Deployed) Migrations\1.0.0-Baseline\001_20200220-0529_Baseline.sql