How can we help you today? How can we help you today?

Use-DatabaseReleaseArtifact and snapshot creation

I have created a small use case script that will vary if the snapshot is inserted or deleted based on how the project object was created.  If created with Invoke-DatabaseBuild the snaphot is inserted. If created with New-DatabaseProjectObject the snapshot is deleted. (from table __SchemaSnapshot). Is this expected behavior?

I am trying to avoid using Invoke-DatabaseBuild because it takes around 30 mins and frankly I am never going to rebuild the database from scratch using scripts anyway. I don't see why Use-DatabaseReleaseArtifact should be affected by this.

Here is a sample script which can be run either way without the specific variables declared.

Write-Host " 1. Use Invoke-DatabaseBuild"
Write-Host " 2. Use New-DatabaseProjectObject"
$answer = read-host "Please Make a Selection"


Switch ($answer) {
1 {
$temporaryDatabase = New-DatabaseConnection -ServerInstance $EAMDBServer -Database $PQTempDB
$ProjectObject = $ProjectFile | Invoke-DatabaseBuild -TemporaryDatabase $temporaryDatabase
; break
}
2 {
$ProjectObject = New-DatabaseProjectObject -InputObject $ProjectFile
; break
}
}

if ($null -eq $ProjectObject)
{exit}


$DBDeployFile = $ProjectObject | New-DatabaseBuildArtifact -PackageId EAMDatabase -PackageVersion $PackageVersion
$DBDeployFile | Export-DatabaseBuildArtifact -Path $CopyLocation




$SqlCmdVariables = @{ReleaseVersion = $IterationNumber;}
$DBConnection = New-DatabaseConnection -ServerInstance $EAMDBServer -Database $PQDBName
$DBUpdate = New-DatabaseReleaseArtifact -Source $DBDeployFile -Target $DBConnection -SqlCmdVariables $SqlCmdVariables
Use-DatabaseReleaseArtifact $DBUpdate -DeployTo $DBConnection
Monday
0

Comments

5 comments

Sort by
  • Alex B
    Hi @Monday,

    The difference is that Invoke-DatabaseBuild is actually building the project on the shadow database to verify it, which is needed for the snapshot to be created.  The New-DatabaseProjectObject just passes the files through into the correct object type, assuming you have validated externally.

    From this page:

    Shadow database

    The shadow database is a temporary database where SQL Change Automation can run your migration scripts. By running the migration scripts on a real database, SQL Server will check that they are valid SQL with no syntax errors, missing dependencies, etc.  

    As part of the shadow deployment process, a file containing a snapshot of your schema is produced and, upon deployment, will be inserted into the target database. During subsequent builds, the snapshot will be retrieved from the target database and used to generate the deployment preview and drift reports mentioned above. If at deployment time a snapshot file cannot be not found (i.e. because the shadow was not deployed at build time), a warning will be raised to indicate the impact to report generation.

    I hope that helps clarify why the two cmdlets behave differently.

    Kind regards,
    Alex
    Alex B
    0
  • Monday
    So the snapshot is of the shadow database and not the target database? It used for drift? I was thinking it was a reference to what the target DB schema looked like before deployment.
    Monday
    0
  • Alex B
    Hi @Monday,

    The snapshot is the "current schema" so on subsequent deployments from the first it compares the current (what's being deployed) to the previous snapshot (what's on the deployment target); there is no previous entry for the first deployment which is why the drift and diff reports are not able to be done the first time.

    Kind regards,
    Alex
    Alex B
    0
  • Monday
    So when this command is run:  Use-DatabaseReleaseArtifact $DBUpdate -DeployTo $DBConnection 

    The snaphot is the "current schema" of the -DeployTo target?  If so it should not matter if a shadow database was created or not since shadow schema should not be used. When I run Invoke-DatabaseBuild and it creates the shadow database it does not know what my target is going to be at that time so I am a little confused at which schema is actually being used for the snapshot.


    Monday
    0
  • AdamB
    The snapshot is the current schema of the project (i.e. the shadow, which is created from the project, which is why the shadow database is needed to make it), and not the target. This snapshot is then compared to the current schema of the target database to create the drift report. It is compared to the previously deployed snapshot to create the diff report.
    AdamB
    0

Add comment

Please sign in to leave a comment.