SQL Compare使用教程:在Argfiles中使用SQL Compare命令行-控件新闻-慧都网

SQL Compare使用教程:在Argfiles中使用SQL Compare命令行

翻译|使用教程|编辑：杨鹏连|2021-03-18 11:07:15.083|阅读 197 次

概述：Phil Factor演示了如何使用XML argfiles将参数传递给SQL Compare，从而消除了每个目标数据库所需的许多修改数据库模式比较和部署过程所涉及的繁琐脚本。

SQL Compare是一款比较和同步SQL Server数据库结构的工具。现有超过150,000的数据库管理员、开发人员和测试人员在使用它。当测试本地数据库，暂存或激活远程服务器的数据库时，SQL Compare将分配数据库的过程自动化。

点击下载SQL Compare试用版

Phil Factor演示了如何使用XML argfiles将参数传递给SQL Compare，从而消除了每个目标数据库所需的许多修改数据库模式比较和部署过程所涉及的繁琐脚本。

有很多方法可以使用PowerShell自动执行需要执行SQL Compare的许多常规开发工作。您选择的方法取决于您的要求。本文旨在指出从命令行界面（CLI）运行XML时，使用XML将详细指令传递给SQL Compare的一些优点。

命令行许可

需要在多台计算机上安装SQL比较命令行的自动化程序需要Redgate Deploy或SQL Toolbelt许可证。有关完整的详细信息，请参阅文档的“更改分发的命令行”页面。

将参数传递给SQL Compare CLI

SQL Compare CLI允许您从XML参数文件（argfile）或使用项目文件传递参数，以及在命令行尾部使用开关的更常用方法。如果您需要大量参数但无法从GUI生成项目文件，则此功能很有用。

使用XML argfile传递参数的原因有很多。自动生成项目文件不是一个好主意，而支持XML argfile。这意味着您可以从头开始为每个任务创建XML argfile，并可以按计划执行所有任务。您还可以使用SQL Compare有时需要的所有许多细节来执行一般任务，而只需填写更改的细节，例如服务器，数据库或凭据，而所有其他选项，开关等保持不变。我在使用Linux或Windows上的SQL Compare命令行比较和部署SQL Server数据库的文章中给出了所有这些说明。

我们可以总结这些方法的一些优点和缺点。

使用PowerShell的XML方法入门

当重复很多或使用大量命令行选项时，使用XML argfile为命令行界面指定参数特别有用。这是用于SQL比较的简单XML参数文件（argfile）：

<?xml version="1.0"?>
<commandline>
  <database1>FirstDatabase</database1>
  <server1>SecondHostname</server1>
  <database2>SecondDatabase</database2>
  <server2>SecondHostname</server2>
</commandline>

这里是一个更复杂的：

<?xml version="1.0" encoding="UTF-8"?>
<commandline>
   <sourcecontrol1 />
   <version1>HEAD</version1>
   <server2>TargetDBServer</server2>
   <database2>TargetDB</database2>
   <scriptsfolderxml>MyScriptFolderXml.sfx</scriptsfolderxml>
   <migrationfolderxml>MyMigrationFolderXml.mfx</migrationfolderxml>
   <include>Assembly</include>
   <include>FullTextCatalog</include>
   <include>Function</include>
   <include>Schema</include>
   <include>StoredProcedure</include>
   <include>Table</include>
   <include>View</include>
   <include>identical</include>
   <Synchronize />
</commandline>

将所有这些设置保存在XML argfile中之后，您可以从Bash，PowerShell或命令提示符处执行它：

sqlcompare /Argfile:MyXMLfilename.xml

如果执行此操作，则可以添加的唯一其他命令行开关是/verbose或/quiet。其余的必须在XML argfile中。这给我们带来了诸如密码之类的敏感信息的问题，这些信息我们无法存储在XML argfile中，因此我们也无法将其分别传递给CLI。稍后，我将向您展示如何解决此问题，但是如果您使用命令提示符或Bash，这仍然是一个问题。

指定开关

开关可以以长或短的形式包含在其开关名称中，作为名称，其值作为XML值。如果您有多个值（例如在/options开关中允许的值），则可以将它们用逗号分隔：

<options> n，oc，t </ options>

创建表示开关的更复杂的argfile键的最简单方法是检出等效的SQL Compare项目文件。我不知道为什么两者之间的XML结构和键会有很大的不同，因为它们几乎是并行的。也就是说，大多数有趣且复杂的键都是相同的，您可以在它们之间剪切和粘贴。这使您可以在GUI中进行繁重的工作，保存项目文件，并挑选出代表所需开关的所需位。

从哈希表中的参数列表创建Argfiles

对我而言，在PowerShell中创建这些XML argfile的最简单方法是将参数放入哈希表中，并将其传递给帮助函数以将其转换为正确的XML格式。它消除了手工制作XML的许多烦人的错误。

<# Unless you like writing XML, the easiest way of creating these files in PowerShell is probably this. We just put our parameters into a hash table and pass it to a function. 
 #>
$MyParameters = @{
    'Database1' = 'Sigrid';
    'Server1' = 'MyFirstServer';
    'Database2' = 'Sigrid'
    'Server2' = 'MySecondServer';
    'userName1' = 'MyUsername1';
    'password1' = 'MyPassword1';
    'userName2' = 'MyUsername2';
    'password2' = 'MyPassword2';
    'report' = "${env:temp}\MyReport.html";
       'force' = $null;
    'reportType' = 'HTML';
    'loglevel' = 'Warning';
    'options' = 'default'
}
# just a helper function to translate the hash table to the XML format expected
# neither of the built-in cmdlets can cut it because they wrap the hash table up
Function SaveHashTableAsSQLCompareXML([hashtable]$TheHashTableParameters,[string]$WhereToStoreIt)
{
    $xmlDoc = [System.Xml.XmlDocument]'<?xml version="1.0"?>';
    $commandline = $xmlDoc.AppendChild($xmlDoc.CreateElement('commandline'));
    $TheHashTableParameters.GetEnumerator() | foreach {
        $Element = $commandline.AppendChild($xmlDoc.CreateElement($_.Name));
        if ($_.Value -ne $null) # if it is a switch with a parameter
        {
            $textNode = $Element.AppendChild($xmlDoc.CreateTextNode($_.Value));
        }
    }
    $xmlDoc.Save("$WhereToStoreIt"); #save it to the user temp area
}
SaveHashTableAsSQLCompareXML $MyParameters "${$env:temp}\Paramfile.xml"
sqlCompare /Argfile:"${env:temp}\Paramfile.xml" >"${env:temp}\summary.txt"
start "${env:temp}\MyReport.html" #display the HTML result report

用于常规比较任务的简单Argfiles

这是一些用于各种任务的argfile示例。对于未加密的密码，读者会有些不解，但稍后再解决。

编写脚本的新版本

<?xml version="1.0"?>
<commandline> <!-- Script out a new version of the script folder -->
  <database1>Sigrid</database1>
  <server1>MyFirstServer</server1>
  <userName1>MyUsername</userName1>
  <password1>MyP@55w0rd</password1>
  <makescripts>MyPathTo\Tryout</makescripts>
  <force/>
</commandline>

生成一个构建脚本

<?xml version="1.0"?>
<commandline> <!-- generate a build script -->
  <database1>Sigrid</database1>
  <server1>MyFirstServer</server1>
  <userName1>MyUsername</userName1>
  <password1>MyP@55w0rd</password1>
   <empty2/> 
  <scriptFile>MyPathTo\MyScriptFile.sql</scriptFile>
  <force/>
</commandline>

报告两个数据库之间的任何差异

<?xml version="1.0"?>
<commandline> <!--report on any differences between two databases -->
  <loglevel>Warning</loglevel>
  <password1>MyP@55w0rd</password1>
  <force />
  <Server2>MyOtherServer</Server2>
  <password2>MyP@55w0rd</password2>
  <userName2>MyUsername</userName2>
  <options>default</options>
  <Server1>MyFirstServer</Server1>
  <reportType>HTML</reportType>
  <userName1>MyUsername</userName1>
  <Database2>Sigrid</Database2>
  <Database1>Sigrid</Database1>
  <report>MyPathTo\MyReport.html</report>
</commandline>

编写并报告两个数据库之间的差异

<?xml version="1.0"?>
<commandline> 
<!-- Script out, and report on, the differences between two databases -->
  <loglevel>Warning</loglevel>
  <password1>MyP@55w0rd</password1>
  <force />
  <Server2>MyOtherServer</Server2>
  <password2>MyP@55w0rd</password2>
  <userName2>MyUsername</userName2>
  <options>default</options>
  <Server1>MyFirstServer</Server1>
  <reportType>HTML</reportType>
  <userName1>MyUsername</userName1>
  <database2>Sigrid</database2>
  <database1>Sigrid</database1>
  <exclude>Identical</exclude>  
  <scriptfile>MyPathTo\MyDatabaseBuild.sql</scriptfile>
  <report>MyPathTo\MyReport.html</report>
</commandline>

从数据库创建快照

<?xml version="1.0"?>
<commandline> 
<!-- make a snapshot of a database -->
  <Server1>MyFirstServer</Server1>
  <database1>Sigrid</database1>
  <userName1>MyUsername</userName1>
  <password1>MyP@55w0rd</password1>
  <loglevel>Warning</loglevel>
  <force />
  <makesnapshot>MyPathTo\MyDatabase.snp</makesnapshot>
  <options>default</options>
</commandline>

使用XML argfile执行SQL Compare CLI

以下1-liner将在ArgFile目录中执行所有XML argfile：

<# now we can execute sql Compare CLI with all the argfiles #>
Get-ChildItem -Path "${env:temp}" -Filter '*.xml'| 
   foreach{SQLCompare  "/Argfile:$($_.fullname)"}

动态生成argfile

如果您没有任何argfiles怎么办？我们可以即时生成它们！一旦存在argfile，您就可以在每次要重新执行它时使用上面的一个划线。

这是我们的第一个简单版本，可即时生成用于为每个提供的数据库制作快照的argfile。尽管它很好，但是如果您使用的是SQL Server身份验证而不是Windows身份验证，则存在未加密的密码问题，如果您使用的是Linux或MacOS，则可能会出现这种情况。

<# We just have a list of servers, databases and (in this case userids and passwords) #>
@(
    @{
        'Database' = 'Sigrid'; 'Server' = 'MyOtherServer';
          'userid' = 'MyUsername'; 'password' = 'MyP@55w0rd' },
    @{
        'Database' = 'Abnego'; 'Server' = 'MyOtherServer';
        'userid' = 'MyUsername'; 'password' = 'MyP@55w0rd' },
    @{
        'Database' = 'Antipas'; 'Server' = 'MyOtherServer';
        'userid' = 'MyUsername'; 'password' = 'MyP@55w0rd' },
    @{
        'Database' = 'Archaelus'; 'Server' = 'MyOtherServer';
        'userid' = 'MyUsername'; 'password' = 'MyP@55w0rd' },
    @{ 'Database' = 'Adeliza'; 'Server' = 'MyFirstServer' },
    @{ 'Database' = 'Sigrid'; 'Server' = 'MyFirstServer' }
    
) | foreach{
    "<?xml version=""1.0""?>
<!-- make a snapshot of a database $($_.Database) on $($_.Server) -->
<commandline> 
  <Server1>$($_.Server)</Server1>
  <database1>$($_.Database)</database1>
  $(if ($_userid -ne $null)
        {
            "<userName1>$($_.userid)</userName1>
  <password1>$($_.password)</password1>"
        })
  <loglevel>Warning</loglevel>
  <force />
  <makesnapshot>${env:temp}\$($_.Database)-$($_.Server).snp</makesnapshot>
  <options>default</options>
</commandline>
">"${env:temp}\Snap-$($_.Database)-$($_.Server).xml"
}
<# now we can execute sql Compare CLI with all the argfiles #>
Get-ChildItem -Path "${env:temp}" -Filter 'Snap*.xml' |
foreach{ SQLCompare  "/Argfile:$($_.fullname)" }

突然之间，我们现在有了很多快照，还有一种将实时数据库与快照进行比较的方法，使我们能够确定更改了哪些内容，然后也许可以用来保存这些更改。

密码存储问题

如前所述，许多读者会为将未加密的密码放入文件中而大惊小怪。这总是一个坏主意。好的，将它们保存在您的用户区域中，以便获得NTFS访问控制所提供的保护措施。但是，密码也必须加密。

实际上，如果您完全使用SQL Server身份验证，那么您的SQL Compare项目文件也应存储在您的用户区中（在PowerShell中，“ ${env:temp}”是指您用户区中的临时目录）。这是因为，尽管密码是在项目文件中加密的，但这样做的方式是，无论Windows / Linux身份如何，任何人都可以使用加密的密码，并且他们可以粘贴到其项目文件中以使用SQL访问数据库。

要存储凭据，Microsoft建议在PowerShell中使用Import-CliXml和Export-CliXml。该Export-Clixmlcmdlet使用Windows数据保护API加密凭据对象。加密可确保只能通过您的用户帐户以及仅在该计算机上解密凭据对象的内容。导出的CLIXML argfile不能在其他计算机上或该计算机上的其他用户使用。请参阅使用PowerShell在磁盘上安全存储凭据和使用PowerShell安全密码：加密凭据。

这是如何在磁盘上存储密码的示例。我只想列出有关服务器上我的数据库的所有详细信息，并且我希望有一个例程可以安全地执行此操作，而不管我使用的是Windows身份验证还是SQL Server身份验证：

import-Module sqlserver #import all the libraries for SMO
$SQLserver = 'MyFirstServer'
$SqlUserName = 'MyUsername'
if ($SqlUserName -ne $null)
{
$SqlEncryptedPasswordFile = `
"$env:USERPROFILE\$($SqlUserName)-$($SQLserver).xml"
# test to see if we know about the password in a secure string stored in the user area
    if (Test-Path -path $SqlEncryptedPasswordFile -PathType leaf)
    {
        #has already got this set for this login so fetch it
        $SqlCredentials = Import-CliXml $SqlEncryptedPasswordFile
        
    }
    else #then we have to ask the user for it (once only)
    {
        #hasn't got this set for this login
        $SqlCredentials = get-credential -Credential $SqlUserName
        $SqlCredentials | Export-CliXml -Path $SqlEncryptedPasswordFile
    }
    $ServerConnection = new-object `
        "Microsoft.SqlServer.Management.Common.ServerConnection" `
        ('MyFirstServer', $SqlCredentials.UserName, $SqlCredentials.Password)
}
else
{
    $ServerConnection =
    new-object "Microsoft.SqlServer.Management.Common.ServerConnection" `
    ($csb.server)
}
$s = new-object ("Microsoft.SqlServer.Management.Smo.Server") $ServerConnection
$s.Databases

SQL Compare argfiles的问题在于命令行界面通过CLI直接从磁盘读取它们，并且我们不允许将敏感信息（例如密码）作为参数单独传递。这意味着必须在产品中解决在argfile中以纯文本格式存储密码的问题。

在Argfiles中存储密码的解决方案

为了快速解决此问题，并为SQL Compare的现有用户提供解决方案，我们笨拙地添加了“即时”密码。

让我们从那些argfiles重新开始。我们现在不打算在其中输入密码。如果他们有用户名，那么我们需要即时添加适当的密码，以创建XML argfile的临时版本，然后将其传递给SQL Compare。它可以用于username1or username2，也可以用于password1or password2。

<# We just have a list of servers, databases and (in this case Userids and logins) #>
@(
    @{
        'Database' = 'Sigrid'; 'Server' = 'MyOtherServer'
    },
    @{
        'Database' = 'Abednego'; 'Server' = 'MyOtherServer'
    },
    @{
        'Database' = 'Antipas'; 'Server' = 'MyOtherServer'
    },
    @{
        'Database' = 'Archaelus'; 'Server' = 'MyOtherServer';
        'userid' = 'MyUsername'
    },
    @{
        'Database' = 'Adeliza'; 'Server' = 'MyFirstServer';
        'userid' = 'MyUsername'
    },
    @{
        'Database' = 'Sigrid'; 'Server' = 'MyFirstServer';
        'userid' = 'MyUsername'
    }
    
) | foreach{
    "<?xml version=""1.0""?>
<!-- make a snapshot of a database $($_.Database) on $($_.Server) -->
<commandline> 
  <Server1>$($_.Server)</Server1>
  <database1>$($_.Database)</database1>
  $(if ($_.userid -ne $null)
        {
            "<userName1>$($_.userid)</userName1>
  <password1>$($_.password)</password1>"
        })
  <loglevel>Warning</loglevel>
  <force />
  <makesnapshot>${env:temp}\$($_.Database)-$($_.Server).snp</makesnapshot>
  <options>default</options>
</commandline>
">"${env:temp}\Snap-$($_.Database)-$($_.Server).xml"
}

现在，我们必须创建一个帮助函数来获取密码。首次对任何用户和服务器运行此功能时，都会从您那里获取密码，并且您必须输入该密码。此后，它将从安全存储中获取该密码。

function SavedPassword ($SqlUserName, $server)
{
    $SqlEncryptedPasswordFile = `
    "$env:USERPROFILE\$($SqlUserName)-$($server).xml"
    # test to see if we know about the password in a secure string stored in the user area
    if (Test-Path -path $SqlEncryptedPasswordFile -PathType leaf)
    {
        #has already got this set for this login so fetch it
        $SqlCredentials = Import-CliXml $SqlEncryptedPasswordFile
        
    }
    else #then we have to ask the user for it (once only)
    {
        #hasn't got this set for this login
        $SqlCredentials = get-credential -Credential $SqlUserName
        $SqlCredentials | Export-CliXml -Path $SqlEncryptedPasswordFile
    }
    $SqlCredentials.GetNetworkCredential().password
}

现在，我们重新营业。可悲的是，它在代码中有点复杂，但这丝毫不会减慢速度。我们依次提取每个argfile，对其进行对等以查看其是否需要密码，如果需要，请插入密码。我们将每个副本复制到一个新的临时文件，并将其传递给SQL Compare。使用它后，我们会立即将其删除！

Get-ChildItem -Path "${env:temp}" -Filter 'Snap-*.xml' |
foreach{
    $content = [System.IO.File]::ReadAllText($_.fullname);
    $xmlContent = [xml]$content
    write-output "$($xmlContent.'#comment')"
    $server1 = $xmlContent.commandline.server1
    $server2 = $xmlContent.commandline.server2
    $username1 = $xmlContent.commandline.username1
    $username2 = $xmlContent.commandline.username2
    
    if ($username1 -ne $null)
    {
        $xmlContent.commandline.password1 = (SavedPassword  $username1 $server1)
    }
    if ($username2 -ne $null)
    {
        $xmlContent.commandline.password2 = (SavedPassword  $username2 $server2)
    }
    $Tempfile="${env:temp}\Temp_$($_.basename).xml"
    $xmlContent.Save($Tempfile)
    SQLCompare  "/Argfile:$Tempfile" 
    Remove-Item -Path "$Tempfile"
}

结论

我最喜欢使用argfiles将参数传递给SQL Compare的地方是，您可以通过将特定任务所需的所有argfile收集到一个目录中，然后依次将每个argfile传递给SQL Compare来完成很多工作。它省去了很多脚本，意味着您可以通过添加，修改或删除argfiles来修改整个任务。与项目文件不同，这些XML argfile可以通过脚本或在文本编辑器中轻松修改。

如前所述，如果您选择使用argfiles并使用PowerShell，则需要解决未加密密码的问题。