Options
SQL Change Automation: How to Ignore Index WITH FILLFACTOR and Constraint WITH NOCHECK
Steve_TR
Posts: 2 New member
In the SQLCompare product, it is easy to have it ignore the minor differences of Indexes with or without the FILLFACTOR or STATISICS_NORECOMPUTE settings, so that those objects do not show as needed migrated. How can I make the same settings in SQL Change Automation to ignore these minor differences?
Here is the PowerShell script used to generate a ReleaseArtifact object between two databases, and export that to disk for inspection. It runs without issue, and I've been able to exclude most everything by tweeking my *.scpf Filter File (also used with SQLCompare), but that does not have separate controls for indexes and constraints.
Here is the PowerShell script used to generate a ReleaseArtifact object between two databases, and export that to disk for inspection. It runs without issue, and I've been able to exclude most everything by tweeking my *.scpf Filter File (also used with SQLCompare), but that does not have separate controls for indexes and constraints.
Clear-Host;
Import-Module SqlChangeAutomation;
[string]$ExportFolder = "C:\SCA\Export";
[string]$SQLCompareFilter = "C:\SCA\SCA-FilterSettings.scpf";
#empty the export folder
Remove-Item $ExportFolder -Recurse -Force -Confirm:$false;
$SourceConnection = New-DatabaseConnection -ServerInstance "s-sw-sqldev" -Database "demoDEV";
$TargetConnection = New-DatabaseConnection -ServerInstance "s-sw-sqltest" -Database "demoTEST";
$iReleaseArtifact = New-DatabaseReleaseArtifact `
-Source $SourceConnection `
-Target $TargetConnection `
-SQLCompareOptions 'NoTransactions' `
-FilterPath $SQLCompareFilter `
-AbortOnWarningLevel None;
Export-DatabaseReleaseArtifact $iReleaseArtifact -Path $ExportFolder
# clean up objects
Remove-Variable SourceConnection
Remove-Variable TargetConnection
Remove-Variable iReleaseArtifact
# open the report html file in Chrome
Start-Process "chrome.exe" "$('file:///' + $ExportFolder + '/Reports/Changes.html#changes')"
----
The long term goal is (obviously) to use source control as the "Source" for New-DatabaseReleaseArtifact, but I'm new to SCA and trying to step through this manually first with as few moving parts as possible.
# open the report html file in Chrome
Start-Process "chrome.exe" "$('file:///' + $ExportFolder + '/Reports/Changes.html#changes')"
----
The long term goal is (obviously) to use source control as the "Source" for New-DatabaseReleaseArtifact, but I'm new to SCA and trying to step through this manually first with as few moving parts as possible.
Tagged:
Best Answer
-
Options
Kendra_Little
Posts: 139 Gold 3
Hi Steve,I actually recommend starting with SCA with version control -- I think you'll find that things like this are simpler, because you can choose to ignore things like fillfactor in the project settings in either SQL Source Control (if you prefer a state-first approach) or SQL Change Automation (if you prefer a migrations-based approach). If you start this way, you'll need to re-adjust how you handle things like this when you add in the version control bit, so it may not end up reducing overall complexity by much.That being said, I believe you should be able to specify things like IgnoreFillFactor and other SQLCompare options as additional -SQLCompareOptions when you create the release artifact. I believe the reason this isn't called out in the documentation is that most people don't need to do it (as it's handled by the source control project settings).Hope this helps!Kendra
Answers