SQL Change Automation: How to Ignore Index WITH FILLFACTOR and Constraint WITH NOCHECK

Steve_TR

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.

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. 

Kendra_Little

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

Steve_TR

Thanks Kendra! I was guessing that the .SQLPROJ file might be the place to do this.  I started with creating a *.SQLPROJ file approach, but my baseline script for a "real" database is 30K lines long and fails validation giving several "error near ) on line XXX" messages with no obvious issues in the code (the procedure code runs fine). Testing a SQLPROJ approach on a very small demo database containing under 10 objects worked fine.    I think my next step will be to open a support ticket when I get back in the office, since I'm also working around a rights issue preventing the SHADOW database create/drop in our shared dev-environment with cross-database querying and 3 linked server references which pretty much preclude an individual dev-environment setup approach.    Thanks again, I have enjoyed your training videos!