What are the challenges you face when working across database platforms? Take the survey
Options

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

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
Tagged:

Best Answers

  • Options
    Nick_FosterNick_Foster Posts: 17 Bronze 2
    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.
  • Options
    Ivo_MillerIvo_Miller Posts: 31 Silver 3
    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.
    Ivo Miller
    RedGate Software Developer

Answers

  • Options
    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,
    Sergio
    Product Support Engineer
    Redgate Software Ltd
    Please see our Help Center for detailed guides on how to use our tools
  • Options
    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.
    Ivo Miller
    RedGate Software Developer
  • Options
    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?
  • Options
    Thanks, @Ivo_Miller and @Nick_Foster.  I think between these options, I've got enough to move forward.
  • Options
    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>


  • Options
    PeterDanielsCRBPeterDanielsCRB Posts: 126 Bronze 3
    edited February 12, 2020 3:29PM
    Apologies for my difficulty in getting the whole code block to format correctly.
Sign In or Register to comment.