Why are all programmable objects deploying (deploy) to a target DB that is already baselined?

PeterDanielsCRB

I made a SCA project with a baseline from our prod DB which has >6000 programmable objects.  I made a couple of minor migrations, and now I'm using the powershell cmdlets to validate the build, make a build artifact, and make a release artifact for deployment.  ALL of the programmable objects are in the release artifact's TargetedDeploymentScript.sql.  I do not want ALL of the programmable objects to deploy.  I would think only the ones that have changed should deploy.  Advice?

Thanks,

-Peter

Nick_Foster

PeterDanielsCRB said:

What if I hacked it by creating a __MigrationLog table and copying the data over from the dev DB?

That's what I've been doing to get new projects up and running.  Clone the prod database to a test server, run the deploy on the clone and then copy the __MigrationLog and __SchemaSnapshot tables to the production box.  It's awkward but better than redeploying all the views and sprocs to a busy production server.

Ivo_Miller

When the baseline script is generated, nothing is written to the target database used for the baseline. It is safer for that to be a read-only operation. Users might need to edit the baseline script, or associated programmable objects, before proceeding.
The consequence of this is that we have no concrete record of the database state used to generate the baseline, so don't really have a simple basis upon which to mark the programmable objects as applied rather than adding them to the script.
There is a flag, "BaselineAllDeployChangesMigrations", which can be used to mark all programmable objects as applied instead of deploying them, but it should be used with caution, as it only makes sense when deploying the baseline and nothing more. Currently the only way of setting it is by adding <BaselineAllDeployChangesMigrations>True</BaselineAllDeployChangesMigrations> to the project file. It needs to be set to false (or removed) for any subsequent changes to the programmable objects to be handled correctly.

Hacking it by copying across the __MigrationLog table should work.

Sergio R

Hi Peter,

Currently, it is not possible to change this behavior, I will pass your feedback to the development team to see if this is something that we can improve on in future releases.

Kind Regards,

Ivo_Miller

Hi,

Just to qualify this. When a release artifact is generated only the programmable objects which have not been deployed should be included in the release artifact script.

If it is the very first deployment to the target database, then the __MigrationLog table will not yet exist in the target database, so these scripts will be included in the deployment script, as they will not yet have been marked as deployed.

PeterDanielsCRB

Thanks, @Ivo_Miller and @Sergio R.  It seems that if we made a baseline from prod, then it should still know that only programmable objects modified since the baseline should be included in the deployment, regardless of the existence of the __MigrationLog table and a row for the programmable objects.  I will go ahead an "re-deploy" all 6000+ programmable objects to the dev integration DB, but it doesn't feel good.
I'll make some more comments here when it's time to go to prod.  Hmmm.  I just looked at prod, and it doen't have a __MigrationLog table, so I'm assuming it will want to redeploy all 6000+ objects there, too.  Wondering why there is no __MigrationLog table in the prod DB that we used for the baseline/target when I created the project?

What if I hacked it by creating a __MigrationLog table and copying the data over from the dev DB?

PeterDanielsCRB

Thanks, @Ivo_Miller and @Nick_Foster.  I think between these options, I've got enough to move forward.

PeterDanielsCRB

FWIW, I made a powershell function to copy the SCA meta objects + __MigrationLog data to a target DB.  It relies on the dbatools module.

<div>function&nbsp;Copy-ScaMigrationLog&nbsp;{</div><div>&nbsp;&nbsp;&nbsp;&nbsp;[CmdletBinding()]</div><div>&nbsp;&nbsp;&nbsp;&nbsp;param&nbsp;(</div><div>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;#&nbsp;Parameter&nbsp;help&nbsp;description</div><div>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;[Parameter()]</div><div>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;[string]</div><div>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;$SourceSqlInstance&nbsp;=&nbsp;'localhost'</div><br><div>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;,#&nbsp;Parameter&nbsp;help&nbsp;description</div><div>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;[Parameter(Mandatory=$true)]</div><div>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;[string]</div><div>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;$SourceDatabase&nbsp;</div><br><div>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;,#&nbsp;Parameter&nbsp;help&nbsp;description</div><div>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;[Parameter(Mandatory=$true)]</div><div>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;[string]</div><div>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;$TargetSqlInstance&nbsp;</div><br><div>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;,#&nbsp;Parameter&nbsp;help&nbsp;description</div><div>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;[Parameter(Mandatory=$true)]</div><div>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;[string]</div><div>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;$TargetDatabase&nbsp;</div><div>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;)</div><div>&nbsp;&nbsp;&nbsp;&nbsp;</div><div>&nbsp;&nbsp;&nbsp;&nbsp;begin&nbsp;{</div><div>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;</div><div>&nbsp;&nbsp;&nbsp;&nbsp;}</div><div>&nbsp;&nbsp;&nbsp;&nbsp;</div><div>&nbsp;&nbsp;&nbsp;&nbsp;process&nbsp;{</div><div>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;#&nbsp;Get&nbsp;the&nbsp;migration&nbsp;log&nbsp;tables&nbsp;and&nbsp;views&nbsp;from&nbsp;the&nbsp;src&nbsp;DB&nbsp;and&nbsp;copy&nbsp;them&nbsp;to&nbsp;the&nbsp;target&nbsp;DB</div><br><div>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;#&nbsp;First,&nbsp;make&nbsp;sure&nbsp;we&nbsp;don't&nbsp;already&nbsp;have&nbsp;the&nbsp;object&nbsp;in&nbsp;the&nbsp;target&nbsp;DB</div><div>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;$Table&nbsp;=&nbsp;Get-DbaDbTable&nbsp;-SqlInstance&nbsp;$TargetSqlInstance&nbsp;-Database&nbsp;$TargetDatabase&nbsp;-Table&nbsp;'dbo.__MigrationLog'</div><div>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;if&nbsp;($Table)&nbsp;{</div><div>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;Write-Error&nbsp;-Message&nbsp;'dbo.__MigrationLog&nbsp;already&nbsp;exists.&nbsp;&nbsp;Aborting.'</div><div>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;return&nbsp;#TODO:&nbsp;Return?</div><div>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;}</div><div>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;</div><div>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;#&nbsp;Copy-DbaDbTableData&nbsp;does&nbsp;not&nbsp;get&nbsp;anything&nbsp;but&nbsp;the&nbsp;base&nbsp;table&nbsp;DDL&nbsp;-&nbsp;no&nbsp;PK,&nbsp;no&nbsp;defaults,&nbsp;no&nbsp;extended&nbsp;properties,&nbsp;etc.</div><div>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;#&nbsp;And&nbsp;it&nbsp;has&nbsp;no&nbsp;ScriptingOptions&nbsp;parm.</div><div>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;#Copy-DbaDbTableData&nbsp;-SqlInstance&nbsp;$SourceSqlInstance&nbsp;-Database&nbsp;$SourceDatabase&nbsp;-Destination&nbsp;localhost&nbsp;-DestinationDatabase&nbsp;test&nbsp;-Table&nbsp;'dbo.__MigrationLog'&nbsp;-AutoCreateTable</div><br><div>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;$ScriptingOptions&nbsp;=&nbsp;New-DbaScriptingOption</div><div>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;$ScriptingOptions.DriAll&nbsp;=&nbsp;$true</div><div>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;$ScriptingOptions.Indexes&nbsp;=&nbsp;$true</div><div>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;$ScriptingOptions.ExtendedProperties&nbsp;=&nbsp;$true</div><div>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;$ScriptingOptions.ScriptBatchTerminator&nbsp;=&nbsp;$true</div><div>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;$ScriptingOptions.NoCommandTerminator&nbsp;=&nbsp;$false&nbsp;#&nbsp;I&nbsp;think&nbsp;we&nbsp;need&nbsp;this&nbsp;for&nbsp;views&nbsp;-&nbsp;nope&nbsp;-&nbsp;fail.&nbsp;Still&nbsp;not&nbsp;getting&nbsp;the&nbsp;GOs&nbsp;that&nbsp;we&nbsp;need.</div><div>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;#&nbsp;TODO:&nbsp;parm:</div><div>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;#&nbsp;$ScriptingOptions.TargetDatabaseEngineEdition&nbsp;=&nbsp;'Standard'</div><div>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;#&nbsp;$ScriptingOptions.TargetServerVersion&nbsp;=&nbsp;'Version100'</div><div>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;$ScriptingOptions.EnforceScriptingOptions&nbsp;=&nbsp;$true</div><br><div>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;$tables&nbsp;=&nbsp;@('dbo.__MigrationLog',&nbsp;'dbo.__SchemaSnapshot')</div><br><div>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;#&nbsp;Let's&nbsp;try&nbsp;to&nbsp;just&nbsp;script&nbsp;the&nbsp;DDL&nbsp;from&nbsp;the&nbsp;table&nbsp;object</div><div>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;#&nbsp;Yes,&nbsp;this&nbsp;works.</div><div>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;$SQLDDL&nbsp;=&nbsp;[string](Get-DbaDbTable&nbsp;-SqlInstance&nbsp;$SourceSqlInstance&nbsp;-Database&nbsp;$SourceDatabase&nbsp;-Table&nbsp;$tables).Script($ScriptingOptions)&nbsp;</div><br><div>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;#&nbsp;$Table&nbsp;=&nbsp;Get-DbaDbTable&nbsp;-SqlInstance&nbsp;$SourceSqlInstance&nbsp;-Database&nbsp;$SourceDatabase&nbsp;-Table&nbsp;'dbo.__MigrationLog'</div><div>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;#&nbsp;$Table.Script($ScriptingOptions)</div><br><div>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;#&nbsp;This&nbsp;works,&nbsp;too&nbsp;-&nbsp;needed&nbsp;to&nbsp;convert&nbsp;the&nbsp;output&nbsp;to&nbsp;a&nbsp;string.&nbsp;&nbsp;Seems&nbsp;hack-ish,&nbsp;tho.</div><div>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;#&nbsp;[string]$SQLDDL&nbsp;=&nbsp;[string](Get-DbaDbTable&nbsp;-SqlInstance&nbsp;$SourceSqlInstance&nbsp;-Database&nbsp;$SourceDatabase&nbsp;-Table&nbsp;$tables&nbsp;|</div><div>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;#&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;Export-DbaScript&nbsp;-Passthru&nbsp;-ScriptingOptionsObject&nbsp;$ScriptingOptions)</div><div>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;Invoke-DbaQuery&nbsp;-SqlInstance&nbsp;$TargetSqlInstance&nbsp;-Database&nbsp;$TargetDatabase&nbsp;-Query&nbsp;$SQLDDL</div><br><div>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;#&nbsp;Copy&nbsp;the&nbsp;data&nbsp;over</div><div>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;Copy-DbaDbTableData&nbsp;-SqlInstance&nbsp;$SourceSqlInstance&nbsp;-Database&nbsp;$SourceDatabase&nbsp;-Destination&nbsp;$TargetSqlInstance&nbsp;-DestinationDatabase&nbsp;$TargetDatabase&nbsp;-Table&nbsp;'dbo.__MigrationLog'&nbsp;-KeepNulls</div><br><div>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;#&nbsp;now&nbsp;we&nbsp;need&nbsp;to&nbsp;get&nbsp;the&nbsp;view</div><div>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;$SQLDDL&nbsp;=&nbsp;[string](Get-Dbadbview&nbsp;-sqlinstance&nbsp;$SourceSqlInstance&nbsp;-Database&nbsp;$SourceDatabase&nbsp;|&nbsp;Where-object&nbsp;Name&nbsp;-eq&nbsp;'__MigrationLogCurrent').Script($ScriptingOptions)</div><div>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;#&nbsp;TODO:&nbsp;This&nbsp;is&nbsp;a&nbsp;hack&nbsp;-&nbsp;due&nbsp;to&nbsp;no&nbsp;GO&nbsp;or&nbsp;statement&nbsp;terminator&nbsp;after&nbsp;the&nbsp;SET&nbsp;statements.&nbsp;&nbsp;Sigh.</div><div>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;#&nbsp;OK&nbsp;-&nbsp;add&nbsp;my&nbsp;own&nbsp;GOs</div><div>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;$SQLDDL&nbsp;=&nbsp;$SQLDDL.Replace('CREATE&nbsp;VIEW&nbsp;',&nbsp;"`r`nGO`r`nCREATE&nbsp;VIEW&nbsp;")</div><div>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;$SQLDDL&nbsp;=&nbsp;$SQLDDL.Replace('EXEC&nbsp;sys.sp_addextendedproperty&nbsp;',&nbsp;"`r`nGO`r`nEXEC&nbsp;sys.sp_addextendedproperty&nbsp;")</div><div>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;Invoke-DbaQuery&nbsp;-SqlInstance&nbsp;$TargetSqlInstance&nbsp;-Database&nbsp;$TargetDatabase&nbsp;-Query&nbsp;$SQLDDL</div><br><div>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;#&nbsp;Now&nbsp;we&nbsp;just&nbsp;need&nbsp;to&nbsp;delete&nbsp;the&nbsp;rows&nbsp;for&nbsp;our&nbsp;migrations&nbsp;(after&nbsp;the&nbsp;baseline)&nbsp;and&nbsp;the&nbsp;progr.&nbsp;objects&nbsp;we&nbsp;want&nbsp;to&nbsp;deploy.</div><div>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;#&nbsp;TODO:&nbsp;This&nbsp;should&nbsp;be&nbsp;parameterized.</div><div>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;#&nbsp;ReplaceExpressionsInFileAndFolderNames</div><div>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;#&nbsp;GenerateExpressionReplacementSQL</div><br><div>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;$SQL&nbsp;=&nbsp;"</div><div>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;DELETE&nbsp;&nbsp;dbo.__MigrationLog</div><div>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;WHERE</div><div>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;script_filename&nbsp;LIKE&nbsp;'Migrations\1.1.0-Changes\%'</div><div>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;"</div><div>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;Invoke-DbaQuery&nbsp;-SqlInstance&nbsp;$TargetSqlInstance&nbsp;-Database&nbsp;$TargetDatabase&nbsp;-Query&nbsp;$SQL</div><div>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;</div><div>&nbsp;&nbsp;&nbsp;&nbsp;}</div><div>&nbsp;&nbsp;&nbsp;&nbsp;</div><div>&nbsp;&nbsp;&nbsp;&nbsp;end&nbsp;{</div><div>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;</div><div>&nbsp;&nbsp;&nbsp;&nbsp;}</div><div>}</div>

PeterDanielsCRB

Apologies for my difficulty in getting the whole code block to format correctly.