How SCA determines whether a db is empty or not?

catalogdbcatalogdb Posts: 21 New member
in SQL Change Automation
Wondering how SCA is determining if DB is empty or not. Because I clearly see there is some difference when I try to deploy a baselined project from VS on an empty db and via ADO CD agent on an empty Azure SQL DB.  All our SCA projects were created from production schema as a baseline. This is the first time we are building an sca project which is supposed to do deploy in an empty project.

When I run from VS on Azure DB, I get the log (truncated):
Database considered empty
5 migration(s) pending deployment
(Pre-Script) Pre-Deployment\01_Create_Database.sql
 (Deploy) Migrations\1.0.0-Baseline\001_20200220-0529_Baseline.sql


Same when run from ADO CD pipeline, logs from CD pipeline (truncated):
2020-03-02T17:53:52.2158737Z Database not considered empty. 11 object differences found
2020-03-02T17:53:52.2159990Z xxxxdb: 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.
2020-03-02T17:53:52.2161069Z 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.
2020-03-02T17:53:52.2162032Z Generating patch script C:\Users\VssAdministrator\AppData\Local\Temp\DLM Automation\iqn5ppqe.xuy\artifacts\xxxxxx_Patch.sql
2020-03-02T17:53:52.2191719Z ##[debug]Including the [dbo].[__MigrationLog] table script...
2020-03-02T17:53:52.2192962Z 1 migration(s) in baseline, 4 migration(s) pending deployment
2020-03-02T17:53:52.2193739Z   (Pre-Script) Pre-Deployment\01_Create_Database.sql
2020-03-02T17:53:52.2194577Z   (Mark as Deployed) 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)

Database not considered empty. 1 object differences found
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 D:\source\xxxzzxx\obj\Debug\xxxx_SCA.sql
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


· Share on Twitter

Answers

  • ullullullull Posts: 9 New member
    edited August 3, 2020 7:36PM
    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 D:\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 D:\BuildAgentB\work\3367df887a9829e1\Engines\Migrations\Core\DatabaseInteraction.SqlServer\ScriptExecution\SqlCmdProcessRunner.cs:line 6
    0
    etc etc etc
    · Share on Twitter
  • ullullullull Posts: 9 New member
    More interesting behavior observed: 
    1. made the change to the baseline script noted here: 

    https://documentation.red-gate.com/sca/getting-started/system-requirements/supported-sql-server-versions/sql-change-automation-and-amazon-rds.

    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 successfully 

    I 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.

    · Share on Twitter
Sign In or Register to comment.