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