Compare-DbaDbSchema - use of sqlpackage to do schema compare #5342
Description
name: New Command - Compare-DbaDbSchema
Summary of new feature
Other commands written around sqlpackage for building dacpac files we can utilize sqlpackage to also do schema compare and generate the xml file, then simply read back in to output an object.
Proposed technical details (if applicable)
An example code that references the file generated:
# add path for SQLPackage.exe
IF (-not ($env:Path).Contains( "C:\program files\microsoft sql server\130\DAC\bin"))
{ $env:path = $env:path + ";C:\program files\microsoft sql server\130\DAC\bin;" }
sqlpackage /a:extract /of:true /scs:"server=.\sql2016;database=db_source;trusted_connection=true" /tf:"C:\test\db_source.dacpac";
sqlpackage.exe /a:deployreport /op:"c:\test\report.xml" /of:True /sf:"C:\test\db_source.dacpac" /tcs:"server=.\sql2016; database=db_target;trusted_connection=True"
[xml]$x = gc -Path "c:\test\report.xml";
$x.DeploymentReport.Operations.Operation |
% -Begin {$a=@();} -process {$name = $_.name; $_.Item | % {$r = New-Object PSObject -Property @{Operation=$name; Value = $_.Value; Type = $_.Type} ; $a += $r;} } -End {$a}Example output just for reference and ideas:
- We would need to expand it to be our standard output.
- Have option to compare two dacpac (for offline work)
- Have option to compare two databases online (via connection strings or other parameters for connecting)(tested and this is not an option, sqlpackage throws message when you try to compare two online databases) - One issue is use of connection string would be plain text for SQL Login password, but we have no control over that use.
- All parameters available would have to be handled
- Minimum required parameters would be validated
- Parallel support for live connection comparison between a source and multiple targets? // @potatoqualitee
Doc on sqlpackage DeployReport options.